| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349 | # Author renyupeng# coding=utf-8# @Time    : 2021/9/15 5:10 下午# @Site    :# @File    : MateirialImageDailyReport.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cnimport loggingfrom math import ceilfrom Apietl.kwai_etl.report.ReplaceAccountInfo import ReplaceAccountInfofrom Apietl.utlis.Logger import Loggerfrom Apietl.utlis.Utils import Utilsfrom Apietl.conn.TidbConn import TidbConnclass MateirialImageDailyReport:    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_SMALL', 'CREATIVE_IMAGE_MODE_LARGE', 'CREATIVE_IMAGE_MODE_LARGE_VERTICAL',       'CREATIVE_IMAGE_MODE_GROUP', 'CREATIVE_IMAGE_MODE_GIF', 'TOUTIAO_SEARCH_AD_IMAGE', 'SEARCH_AD_SMALL_IMAGE',       'CREATIVE_IMAGE_MODE_UNION_SPLASH')            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.bytedance_material_image_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,                                                                  image_name,                                                                  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,image_url,signature)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       image.filename,       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`,       image.image_url,       image.signaturefrom media_api.bytedance_material_report_daily report         left join     `media_api`.bytedance_file_image_get image     on report.material_id = image.material_id         and report.advertiser_id = image.advertiser_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 su                   on cua.user_id = su.idwhere report.image_mode in      ('CREATIVE_IMAGE_MODE_SMALL', 'CREATIVE_IMAGE_MODE_LARGE', 'CREATIVE_IMAGE_MODE_LARGE_VERTICAL',       'CREATIVE_IMAGE_MODE_GROUP', 'CREATIVE_IMAGE_MODE_GIF', 'TOUTIAO_SEARCH_AD_IMAGE', 'SEARCH_AD_SMALL_IMAGE',       'CREATIVE_IMAGE_MODE_UNION_SPLASH')  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()            TidbConn.conn_close(conn=self.conn)            return 0        except Exception as e:            logging.error(e)            return -1if __name__ == '__main__':    status = MateirialImageDailyReport().daily_report()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |