| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365 | 
							- # 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 (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 -1
 
- if __name__ == '__main__':
 
-     status = MateirialVideoDailyReport().daily_report()
 
-     if status == 0:
 
-         print("作业执行成功")
 
-         exit(0)
 
-     else:
 
-         print("作业执行失败")
 
-         exit(-1)
 
 
  |