| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/9/15 5:10 下午
- # @Site :
- # @File : MateirialVideoDailyReport.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- import logging
- from Apietl.conn.TidbConn import TidbConn
- from Apietl.utlis.Utils import Utils
- class AdqMaterialReportDaily(object):
- def __init__(self):
- self.conn = TidbConn.get_connection()
- self.parm = Utils.get_args()
- self.start_time = self.parm[0]
- self.end_time = self.parm[1]
- def daily_report(self):
- try:
- sql = """
- select advertiser_id from media_api.adq_material_report_daily where
- stat_time_day >= {start_time} and stat_time_day<={end_time} group by advertiser_id
- """.format(start_time=self.start_time, end_time=self.end_time)
- account_list = TidbConn.quary(self.conn, sql)
- for account_id in account_list:
- sql = """
- replace into application.adq_material_report_daily_dw(account_id,
- project_id,
- product_id,
- advertiser_id,
- company_id,
- account_name,
- project_name,
- advertiser_name,
- company_name,
- sale_id,
- user_id,
- clip_id,
- shot_id,
- plan_id,
- leader_id,
- org_code,
- photo_url,
- material_id,
- stat_date,
- material_type,
- cost,
- view_count,
- valid_click_count,
- conversions_count,
- deep_conversions_count,
- activated_count,
- reg_pv,
- retention_count,
- video_outer_play_count,
- video_outer_play100_count,
- key_behavior_conversions_count,
- purchase_pv,
- purchase_amount,
- first_pay_count,
- ad_monetization_amount,
- app_ad_paying_users,
- income_pv_pla,
- income_val_1,
- ad_paying_users_d1,
- income_pv_1d_pla,
- income_val_3,
- signature,
- channel_type,
- clip_name,
- shot_name,
- plan_name,
- leader_name,
- video_name,
- channel_name,
- clip_company_id,
- play_3s_count,
- shot_leader_id,
- shot_leader_name)
- select report.advertiser_id as account_id,
- cp.id as project_id,
- product.id as product_id,
- ca.id as advertiser_id,
- uc.company_id,
- cua.auth_name as account_name,
- cp.project_name as project_name,
- ca.name as advertiser_name,
- uc.company_name,
- cp.sale_id,
- cua.user_id,
- clip_id,
- shot_id,
- plan_id,
- cma.leader_id,
- leader.org_code,
- video.preview_url as photo_url,
- report.material_id,
- report.stat_time_day as stat_date,
- report.material_type,
- report.cost / 100 as cost,
- report.view_count,
- report.valid_click_count,
- report.conversions_count,
- report.deep_conversions_count,
- report.activated_count,
- report.reg_pv,
- report.retention_count,
- report.video_outer_play_count,
- report.video_outer_play100_count,
- report.key_behavior_conversions_count,
- report.purchase_pv,
- report.purchase_amount / 100 as purchase_amount,
- report.first_pay_count,
- report.ad_monetization_amount,
- report.app_ad_paying_users,
- report.income_pv_pla,
- report.income_val_1 / 100 as income_val_1,
- report.ad_paying_users_d1,
- report.income_pv_1d_pla,
- report.income_val_3 / 100 as income_val_3,
- video.signature,
- null as channel_type,
- clip.realname,
- shot.realname,
- plan.realname,
- leader.realname,
- video.description as video_name,
- null channel_name,
- uc.company_id,
- 0 as play_3s_count,
- cma.shot_leader_id,
- cma.shot_leader_name
- from media_api.adq_material_report_daily report
- left join media_api.adq_video_info video
- on report.material_id = video.video_id
- left join (select code, material_name from `jeecg-boot`.ctop_material_info where status = 1) cmi
- on video.signature = cmi.code
- left join `jeecg-boot`.ctop_material_ascription cma on cma.material_id = video.signature
- left join `jeecg-boot`.ctop_user_allocation cua on cua.account_id = report.advertiser_id
- left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
- left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
- left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
- left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
- left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
- left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
- left join `jeecg-boot`.user_company uc on uc.user_id = clip.id
- left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
- where report.stat_time_day >= {start_time}
- and stat_time_day <= {end_time}
- and report.advertiser_id = {account_id}
- """.format(start_time=self.start_time, end_time=self.end_time, account_id=account_id)
- TidbConn.execute(self.conn, sql)
- except Exception as e:
- logging.error(e)
|