123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2022/2/17 4:13 下午
- # @Site :
- # @File : KuaishouUnitAudienceGenderReportDaily.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- # @Tel 1501435553
- import logging
- from Apietl.conn.TidbConn import TidbConn
- from Apietl.utlis.Utils import Utils
- class KuaishouUnitAudienceGenderReportDaily:
- def __init__(self):
- self.conn = TidbConn.get_connection()
- self.parm = Utils.get_args()
- self.start_time = self.parm[0]
- def genderReport(self):
- try:
- sql = """
- replace into application.kuaishou_unit_audience_gender_report_daily_d
- select gender.account_id,
- campaign_id,
- gender.unit_id,
- event_register_cost,
- gender,
- photo_click,
- key_action_cost,
- event_next_day_stay,
- photo_click_ratio,
- photo_click_cost,
- charge,
- event_pay,
- action_cost,
- conversion_cost,
- bclick,
- key_action_ratio,
- play_3s_ratio,
- play_5s_ratio,
- activation,
- event_app_invoked_ratio,
- `show`,
- event_new_user_pay_cost,
- aclick,
- deep_conversion_num,
- action_ratio,
- conversion_ratio,
- event_pay_purchase_amount_first_day,
- event_pay_first_day,
- conversion_num_cost,
- event_new_user_pay_ratio,
- event_app_invoked,
- event_next_day_stay_ratio,
- event_register,
- event_new_user_pay,
- event_next_day_stay_cost,
- deep_conversion_ratio,
- deep_conversion_cost,
- `like`,
- event_app_invoked_cost,
- key_action,
- ad_photo_played_10s,
- form_cost,
- event_pay_purchase_amount,
- click_1k_cost,
- impression_1k_cost,
- conversion_num,
- stat_date,
- ad_photo_played_2s_ratio,
- follow,
- signature,
- project_id,
- project_name
- from (select account_id,
- campaign_id,
- unit_id,
- event_register_cost,
- gender,
- photo_click,
- key_action_cost,
- event_next_day_stay,
- photo_click_ratio,
- photo_click_cost,
- charge,
- event_pay,
- action_cost,
- conversion_cost,
- bclick,
- key_action_ratio,
- play_3s_ratio,
- play_5s_ratio,
- activation,
- event_app_invoked_ratio,
- `show`,
- event_new_user_pay_cost,
- aclick,
- deep_conversion_num,
- action_ratio,
- conversion_ratio,
- event_pay_purchase_amount_first_day,
- event_pay_first_day,
- conversion_num_cost,
- event_new_user_pay_ratio,
- event_app_invoked,
- event_next_day_stay_ratio,
- event_register,
- event_new_user_pay,
- event_next_day_stay_cost,
- deep_conversion_ratio,
- deep_conversion_cost,
- `like`,
- event_app_invoked_cost,
- key_action,
- ad_photo_played_10s,
- form_cost,
- event_pay_purchase_amount,
- click_1k_cost,
- impression_1k_cost,
- conversion_num,
- stat_date,
- ad_photo_played_2s_ratio,
- follow
- from media_api.kuaishou_unit_audience_gender_report_daily
- where stat_date = {stat_date}) gender
- inner join
- (select account_id, unit_id, photo_id
- from `jeecg-boot`.ctop_ai_kuaishou_creative_level_operation_record
- group by account_id, unit_id, photo_id) operation
- on gender.account_id = operation.account_id and gender.unit_id = operation.unit_id
- inner join
- (select account_id, signature, photo_id
- from media_api.kuaishou_video_list
- group by account_id, signature, photo_id) video
- on operation.account_id = video.account_id and operation.photo_id = video.photo_id
- left join (
- select account_id, project_id
- from `jeecg-boot`.ctop_user_allocation
- ) acc_info
- on gender.account_id = acc_info.account_id
- left join (
- select id, project_name
- from `jeecg-boot`.ctop_project
- ) pro
- on acc_info.project_id = pro.id
- """.format(stat_date=self.start_time)
- TidbConn.upsert(conn=self.conn, sql_buff=sql)
- self.conn.commit()
- TidbConn.conn_close(conn=self.conn)
- return 0
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- KuaishouUnitAudienceGenderReportDaily().genderReport()
|