| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368 | # Author renyupeng# coding=utf-8# @Time    : 2021/9/15 5:10 下午# @Site    :# @File    : MateirialVideoDailyReport.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cnimport loggingfrom Apietl.utlis.Utils import Utilsfrom Apietl.conn.TidbConn import TidbConnclass 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,                                                                                     shot_leader_id, shot_leader_name)                            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`,                           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_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 (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.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 -1if __name__ == '__main__':    status = MateirialVideoDailyReport().daily_report()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |