123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205 |
- # Author renyupeng
- # coding=utf-8
- # @Time : 2021/9/15 5:10 下午
- # @Site :
- # @File : NewMaterialVideoDailyReport.py
- # @Software: PyCharm
- # @contact: renyupeng@c-top.com.cn
- import logging
- from Apietl.utlis.Utils import Utils
- from Apietl.conn.TidbConn import TidbConn
- class NewMaterialVideoDailyReport:
- def __init__(self):
- self.conn = TidbConn.get_connection()
- self.parm = Utils.get_live_args()
- self.start_time = self.parm
- def daily_report(self):
- try:
- sql = """select advertiser_id from media_api.bytedance_material_video_daily where
- stat_datetime = {start_time}
- group by advertiser_id""".format(
- start_time=self.start_time)
- account_list = TidbConn.quary(self.conn, sql)
- for account_id in account_list:
- try:
- sql = """
- replace into application.bytedance_material_video_report_daily_dw(account_id, material_id, image_mode, inventory,
- stat_datetime, 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,
- signature, channel_type, clip_name, shot_name,
- plan_name, leader_name, video_name, channel_name,
- clip_company_id,
- cost,
- `show`,
- click,
- ctr,
- `convert`,
- convert_cost,
- convert_rate,
- deep_convert,
- deep_convert_cost,
- deep_convert_rate,
- download_start,
- download_start_cost,
- download_start_rate,
- download_finish,
- download_finish_cost,
- download_finish_rate,
- install_finish,
- install_finish_cost,
- install_finish_rate,
- active,
- active_cost,
- active_rate,
- register,
- active_register_rate,
- game_addiction,
- game_addiction_cost,
- game_addiction_rate,
- attribution_next_day_open_cnt,
- attribution_next_day_open_cost,
- attribution_next_day_open_rate,
- next_day_open,
- pay_count,
- active_pay_cost,
- active_pay_rate,
- game_pay_count,
- game_pay_cost,
- attribution_game_pay_7d_count,
- attribution_game_pay_7d_cost,
- in_app_uv,
- in_app_detail_uv,
- in_app_cart,
- in_app_pay,
- in_app_order, video_url, cover_url,shot_leader_id,shot_leader_name)
- select report.advertiser_id account_id, #'账户ID'
- report.material_id, #头条素材id
- '' as image_mode, #素材类型
- '' as inventory, #广告位
- stat_datetime, #数据起始时间
- cua.project_id, #项目id
- product.id product_id, #项目中产品id
- cua.advertiser_id, #广告主id
- uc.company_id, #运营人的公司id
- cua.auth_name account_name, #账户名称
- cp.project_name, #项目名称
- ca.name advertiser_name, #广告主名称
- uc.company_name, #运营人的公司名称
- cp.sale_id, #项目里的销售id
- cua.user_id, #运营id
- cma.clip_id, #剪辑id
- cma.shot_id, #拍摄id
- cma.plan_id, #编导id
- cma.leader_id, #负责人id
- leader.org_code, #机构编码
- video.signature, #素材id
- null chl_type, #渠道类型
- clip.realname clip_name, #剪辑名称
- shot.realname shot_name, #拍摄名称
- plan.realname plan_name, #编导名称
- leader.realname lead_name, #负责人名称
- case when cmi.material_name is not null then cmi.material_name else video.filename end as video_name, ##视频名称
- '' chl_name, #渠道名称
- up.company_id clip_company_id, #剪辑公司
- stat_cost as cost,
- show_cnt as `show`,
- click_cnt as click,
- ctr,
- convert_cnt as `convert`,
- conversion_cost as convert_cost,
- conversion_rate as convert_rate,
- deep_convert_cnt as deep_convert,
- deep_convert_cost,
- deep_convert_rate,
- click_start_cnt as download_start,
- click_start_cost as download_start_cost,
- click_start_rate as download_start_rate,
- download_finish_cnt as download_finish,
- download_finish_cost,
- download_finish_rate,
- install_finish_cnt as install_finish,
- install_finish_cost,
- install_finish_rate,
- active,
- active_cost,
- active_rate,
- active_register as register,
- active_register_rate,
- game_addiction,
- game_addiction_cost,
- game_addiction_rate,
- attribution_next_day_open_cnt,
- attribution_next_day_open_cost,
- attribution_next_day_open_rate,
- next_day_open,
- active_pay as pay_count,
- active_pay_cost,
- active_pay_rate,
- game_pay_count,
- game_pay_cost,
- attribution_game_pay_7d_count,
- attribution_game_pay_7d_cost,
- in_app_uv,
- in_app_detail_uv,
- in_app_cart,
- in_app_pay,
- in_app_order,
- ifnull(concat('http://i.snssdk.com/video/code/1/toutiao/',video.id),'-') as video_url ,
- cmi.cover_url,
- cma.shot_leader_id,
- cma.shot_leader_name
- from media_api.bytedance_material_video_daily report
- left join
- `media_api`.bytedance_file_video_get video
- on report.advertiser_id = video.advertiser_id and report.material_id = video.material_id
- left join (select code, material_name, cover_url from `jeecg-boot`.ctop_material_info where status = 1) cmi
- on video.signature = cmi.code
- left join `jeecg-boot`.ctop_material_ascription cma
- on video.signature = cma.material_id
- left join `jeecg-boot`.ctop_user_allocation cua
- on report.advertiser_id = cua.account_id
- left join `jeecg-boot`.ctop_advertiser ca
- on cua.advertiser_id = ca.id
- left join `jeecg-boot`.ctop_project cp
- on cua.project_id = cp.id
- left join `jeecg-boot`.user_company uc
- on cua.user_id = uc.user_id
- left join `jeecg-boot`.ctop_product product
- on cp.product_id = product.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 up on up.user_id = cma.clip_id
- where report.stat_datetime ={start_time}
- and report.advertiser_id = {account_id}
- """.format(start_time=self.start_time,
- account_id=account_id[0])
- print(sql)
- TidbConn.upsert(conn=self.conn, sql_buff=sql)
- self.conn.commit()
- except Exception as e:
- logging.error(e)
- return -1
- TidbConn.conn_close(conn=self.conn)
- return 0
- except Exception as e:
- logging.error(e)
- return -1
- if __name__ == '__main__':
- status = NewMaterialVideoDailyReport().daily_report()
- if status == 0:
- print("作业执行成功")
- exit(0)
- else:
- print("作业执行失败")
- exit(-1)
|