# 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.utlis.Utils import Utils from Apietl.conn.TidbConn import TidbConn class MateirialVideoDailyReport: 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.bytedance_material_report_daily where stat_datetime between {start_time} and {end_time} and image_mode in ('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') 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: 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`, avg_show_cost, click, avg_click_cost, ctr, `convert`, convert_cost, convert_rate, deep_convert, deep_convert_cost, deep_convert_rate, attribution_convert, attribution_convert_cost, attribution_deep_convert, attribution_deep_convert_cost, download_start, download_start_cost, download_start_rate, download_finish, download_finish_cost, download_finish_rate, click_install, install_finish, install_finish_cost, install_finish_rate, active, active_cost, active_rate, register, active_register_cost, active_register_rate, active_pay_amount, next_day_open, next_day_open_cost, next_day_open_rate, attribution_next_day_open_cnt, attribution_next_day_open_cost, attribution_next_day_open_rate, game_addiction, game_addiction_cost, game_addiction_rate, pay_count, active_pay_cost, active_pay_rate, loan_completion, loan_completion_cost, loan_completion_rate, pre_loan_credit, pre_loan_credit_cost, loan_credit, loan_credit_cost, loan_credit_rate, in_app_uv, in_app_detail_uv, in_app_cart, in_app_pay, in_app_order, attribution_game_pay_7d_count, attribution_game_pay_7d_cost, attribution_active_pay_7d_per_count, game_pay_cost, game_pay_count, phone, form, map_search, button, view, download, qq, lottery, vote, message, redirect, shopping, consult, wechat, phone_confirm, phone_connect, consult_effective, coupon, coupon_single_page, redirect_to_shop, poi_collect, poi_address_click, luban_order_cnt, luban_order_stat_amount, luban_order_roi, luban_live_enter_cnt, live_watch_one_minute_count, luban_live_follow_cnt, live_fans_club_join_cnt, luban_live_comment_cnt, luban_live_share_cnt, luban_live_gift_cnt, luban_live_gift_amount, luban_live_slidecart_click_cnt, luban_live_click_product_cnt, luban_live_pay_order_count, luban_live_pay_order_stat_cost, wechat_login_count, attribution_wechat_login_30d_count, wechat_login_cost, attribution_wechat_login_30d_cost, wechat_first_pay_count, attribution_wechat_first_pay_30d_count, wechat_first_pay_cost, attribution_wechat_first_pay_30d_cost, wechat_first_pay_rate, attribution_wechat_first_pay_30d_rate, wechat_pay_amount, attribution_wechat_pay_30d_amount, attribution_wechat_pay_30d_roi, phone_effective, total_play, valid_play, valid_play_cost, valid_play_rate, play_25_feed_break, play_50_feed_break, play_75_feed_break, play_100_feed_break, average_play_time_per_play, play_over_rate, wifi_play_rate, wifi_play, play_duration_sum, advanced_creative_phone_click, advanced_creative_counsel_click, advanced_creative_form_click, advanced_creative_coupon_addition, advanced_creative_form_submit, card_show, share, comment, `like`, follow, home_visited, ies_challenge_click, ies_music_click, location_click, message_action, click_landing_page, click_shopwindow, click_website, click_download, click_call_dy, cpc, cpm, cpa, interact_per_cost, convert_show_rate, play_duration_10s_rate, play_50_feed_break_rate, play_25_feed_break_rate, play_duration_5s_rate, play_duration_2s_rate, average_play_progress, play_100_feed_break_rate, play_duration_3s_rate, play_75_feed_break_rate, play_duration, play_duration_3s, play_duration_2s, play_duration_10s, average_video_play, avg_rank, submit_certification_count, approval_count, first_order_count, first_rental_order_count, commute_first_pay_count,video_url, cover_url) select report.advertiser_id account_id, #'账户ID' report.material_id, #头条素材id report.image_mode, #素材类型 report.inventory, #广告位 report.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, #剪辑公司 report.`cost`, report.`show`, report.`avg_show_cost`, report.`click`, report.`avg_click_cost`, report.`ctr`, report.`convert`, report.`convert_cost`, report.`convert_rate`, report.`deep_convert`, report.`deep_convert_cost`, report.`deep_convert_rate`, report.`attribution_convert`, report.`attribution_convert_cost`, report.`attribution_deep_convert`, report.`attribution_deep_convert_cost`, report.`download_start`, report.`download_start_cost`, report.`download_start_rate`, report.`download_finish`, report.`download_finish_cost`, report.`download_finish_rate`, report.`click_install`, report.`install_finish`, report.`install_finish_cost`, report.`install_finish_rate`, report.`active`, report.`active_cost`, report.`active_rate`, report.`register`, report.`active_register_cost`, report.`active_register_rate`, report.`active_pay_amount`, report.`next_day_open`, report.`next_day_open_cost`, report.`next_day_open_rate`, report.`attribution_next_day_open_cnt`, report.`attribution_next_day_open_cost`, report.`attribution_next_day_open_rate`, report.`game_addiction`, report.`game_addiction_cost`, report.`game_addiction_rate`, report.`pay_count`, report.`active_pay_cost`, report.`active_pay_rate`, report.`loan_completion`, report.`loan_completion_cost`, report.`loan_completion_rate`, report.`pre_loan_credit`, report.`pre_loan_credit_cost`, report.`loan_credit`, report.`loan_credit_cost`, report.`loan_credit_rate`, report.`in_app_uv`, report.`in_app_detail_uv`, report.`in_app_cart`, report.`in_app_pay`, report.`in_app_order`, report.`attribution_game_pay_7d_count`, report.`attribution_game_pay_7d_cost`, report.`attribution_active_pay_7d_per_count`, report.`game_pay_cost`, report.`game_pay_count`, report.`phone`, report.`form`, report.`map_search`, report.`button`, report.`view`, report.`download`, report.`qq`, report.`lottery`, report.`vote`, report.`message`, report.`redirect`, report.`shopping`, report.`consult`, report.`wechat`, report.`phone_confirm`, report.`phone_connect`, report.`consult_effective`, report.`coupon`, report.`coupon_single_page`, report.`redirect_to_shop`, report.`poi_collect`, report.`poi_address_click`, report.`luban_order_cnt`, report.`luban_order_stat_amount`, report.`luban_order_roi`, report.`luban_live_enter_cnt`, report.`live_watch_one_minute_count`, report.`luban_live_follow_cnt`, report.`live_fans_club_join_cnt`, report.`luban_live_comment_cnt`, report.`luban_live_share_cnt`, report.`luban_live_gift_cnt`, report.`luban_live_gift_amount`, report.`luban_live_slidecart_click_cnt`, report.`luban_live_click_product_cnt`, report.`luban_live_pay_order_count`, report.`luban_live_pay_order_stat_cost`, report.`wechat_login_count`, report.`attribution_wechat_login_30d_count`, report.`wechat_login_cost`, report.`attribution_wechat_login_30d_cost`, report.`wechat_first_pay_count`, report.`attribution_wechat_first_pay_30d_count`, report.`wechat_first_pay_cost`, report.`attribution_wechat_first_pay_30d_cost`, report.`wechat_first_pay_rate`, report.`attribution_wechat_first_pay_30d_rate`, report.`wechat_pay_amount`, report.`attribution_wechat_pay_30d_amount`, report.`attribution_wechat_pay_30d_roi`, report.`phone_effective`, report.`total_play`, report.`valid_play`, report.`valid_play_cost`, report.`valid_play_rate`, report.`play_25_feed_break`, report.`play_50_feed_break`, report.`play_75_feed_break`, report.`play_100_feed_break`, report.`average_play_time_per_play`, report.`play_over_rate`, report.`wifi_play_rate`, report.`wifi_play`, report.`play_duration_sum`, report.`advanced_creative_phone_click`, report.`advanced_creative_counsel_click`, report.`advanced_creative_form_click`, report.`advanced_creative_coupon_addition`, report.`advanced_creative_form_submit`, report.`card_show`, report.`share`, report.`comment`, report.`like`, report.`follow`, report.`home_visited`, report.`ies_challenge_click`, report.`ies_music_click`, report.`location_click`, report.`message_action`, report.`click_landing_page`, report.`click_shopwindow`, report.`click_website`, report.`click_download`, report.`click_call_dy`, report.`cpc`, report.`cpm`, report.`cpa`, report.`interact_per_cost`, report.`convert_show_rate`, report.`play_duration_10s_rate`, report.`play_50_feed_break_rate`, report.`play_25_feed_break_rate`, report.`play_duration_5s_rate`, report.`play_duration_2s_rate`, report.`average_play_progress`, report.`play_100_feed_break_rate`, report.`play_duration_3s_rate`, report.`play_75_feed_break_rate`, report.`play_duration`, report.`play_duration_3s`, report.`play_duration_2s`, report.`play_duration_10s`, report.`average_video_play`, report.`avg_rank`, report.`submit_certification_count`, report.`approval_count`, report.`first_order_count`, report.`first_rental_order_count`, report.`commute_first_pay_count`, video.video_url, cmi.cover_url from media_api.bytedance_material_report_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 `jeecg-boot`.ctop_material_info 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.image_mode in ('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO') and report.stat_datetime between {start_time} and {end_time} and report.advertiser_id={account_id} """.format(start_time=self.start_time, end_time=self.end_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 = MateirialVideoDailyReport().daily_report() if status == 0: print("作业执行成功") exit(0) else: print("作业执行失败") exit(-1)