| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674 | # Author renyupeng# coding=utf-8# @Time    : 2021/11/10 3:37 下午# @Site    :# @File    : AppBytedanceCompanyDesignReportBusi.py# @Software: PyCharm# @contact: renyupeng@c-top.com.cn# @Tel 1501435553import loggingfrom Apietl.conn.TidbConn import TidbConnfrom Apietl.utlis.Utils import Utilsclass AppBytedanceCompanyDesignReportBusi:    def __init__(self):        self.conn = TidbConn.get_connection()        self.parm = Utils.get_task_args()        self.date_time = self.parm[0]        self.day_count = Utils.get_days()    def taskHandler(self):        try:            sql = """delete from application.app_bytedance_company_design_report_busi_m where stat_mon=date_format({date_time},'%Y%m');SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';REPLACE INTO application.app_bytedance_company_design_report_busi_mSELECT        date_format({date_time},'%Y%m')    as  stat_mon,       company_id,       company_name,       org_code,       org_type,       id,       depart_name,       leader_id,       leader_name,       count(DISTINCT signature)            num,       count(distinct innovate_code)        innovate_num,       sum(cost)                            cost,       sum(`show`)                          `show`,       round(sum(cost) / sum(`show`), 3)    avg_show_cost,       sum(click)                           click,       round(sum(cost) / sum(`click`), 3)   avg_click_cost,       round(               sum(click) / sum(`show`) * 100,               3           )                                ctr,       sum(`convert`)                       `convert`,       round(sum(cost) / sum(`convert`), 3) convert_cost,       round(               sum(`convert`) / sum(click) * 100,               3           )                                convert_rate,       sum(deep_convert)                    deep_convert,       round(               sum(cost) / sum(deep_convert),               3           )                                deep_convert_cost,       round(               sum(deep_convert) / sum(`convert`) * 100,               3           )                                deep_convert_rate,       sum(attribution_convert)             attribution_convert,       round(               sum(cost) / sum(attribution_convert),               3           )                                attribution_convert_cost,       sum(attribution_deep_convert)        attribution_deep_convert,       round(               sum(cost) / sum(attribution_deep_convert),               3           )                                attribution_deep_convert_cost,       sum(download_start)                  download_start,       round(               sum(cost) / sum(download_start),               3           )                                download_start_cost,       round(               sum(download_start) / sum(click),               3           )                                download_start_rate,       sum(download_finish)                 download_finish,       round(               sum(cost) / sum(download_finish),               3           )                                download_finish_cost,       round(               sum(download_finish) / sum(download_start) * 100,               3           )                                download_finish_rate,       sum(click_install)                   click_install,       sum(install_finish)                  install_finish,       round(               sum(cost) / sum(install_finish),               3           )                                install_finish_cost,       round(               sum(install_finish) / sum(download_finish) * 100,               3           )                                install_finish_rate,       sum(active)                          active,       round(sum(cost) / sum(active), 3)    active_cost,       round(               sum(active) / sum(click) * 100,               3           )                                active_rate,       sum(register)                        register,       round(sum(cost) / sum(register), 3)  active_register_cost,       round(               sum(active_register_rate) / {day_count} * 100,               3           )                                active_register_rate,       sum(active_pay_amount)               active_pay_amount,       sum(next_day_open)                   next_day_open,       round(               sum(cost) / sum(next_day_open),               3           )                                next_day_open_cost,       round(               sum(next_day_open) / sum(active) * 100,               3           )                                next_day_open_rate,       sum(               attribution_next_day_open_cnt           )                                attribution_next_day_open_cnt,       round(                   sum(cost) / sum(                       attribution_next_day_open_cnt                   ),                   3           )                                attribution_next_day_open_cost,       round(                       sum(                               attribution_next_day_open_cnt                           ) / sum(active) * 100,                       3           )                                attribution_next_day_open_rate,       sum(game_addiction)                  game_addiction,       round(               sum(cost) / sum(game_addiction),               3           )                                game_addiction_cost,       round(               sum(game_addiction_rate) / {day_count} * 100,               3           )                                game_addiction_rate,       sum(pay_count)                       pay_count,       round(sum(cost) / sum(pay_count), 3) active_pay_cost,       round(               sum(pay_count) / sum(active) * 100,               3           )                                active_pay_rate,       sum(loan_completion)                 loan_completion,       round(               sum(cost) / sum(loan_completion),               3           )                                loan_completion_cost,       round(               sum(loan_completion) / sum(register) * 100,               3           )                                loan_completion_rate,       round(sum(pre_loan_credit), 3)       pre_loan_credit,       round(               sum(cost) / sum(pre_loan_credit),               3           )                                pre_loan_credit_cost,       sum(loan_credit)                     loan_credit,       round(               sum(cost) / sum(loan_credit),               3           )                                loan_credit_cost,       round(               sum(loan_credit) / sum(loan_completion) * 100,               3           )                                loan_credit_rate,       sum(in_app_uv)                       in_app_uv,       sum(in_app_detail_uv)                in_app_detail_uv,       sum(in_app_cart)                     in_app_cart,       sum(in_app_pay)                      in_app_pay,       sum(in_app_order)                    in_app_order,       round(                   sum(                           attribution_game_pay_7d_count                       ) / {day_count},                   3           )                                attribution_game_pay_7d_count,       round(                   sum(                           attribution_game_pay_7d_cost                       ) / {day_count},                   3           )                                attribution_game_pay_7d_cost,       round(                   sum(                           attribution_active_pay_7d_per_count                       ) / {day_count},                   3           )                                attribution_active_pay_7d_per_count,       sum(game_pay_cost)                   game_pay_cost,       sum(game_pay_count)                  game_pay_count,       sum(phone)                           phone,       sum(form)                            form,       sum(map_search)                      map_search,       sum(button)                          button,       sum(`view`)                          `view`,       sum(download)                        download,       sum(qq)                              qq,       sum(lottery)                         lottery,       sum(vote)                            vote,       sum(message)                         message,       sum(redirect)                        redirect,       sum(shopping)                        shopping,       sum(consult)                         consult,       sum(wechat)                          wechat,       sum(phone_confirm)                   phone_confirm,       sum(phone_connect)                   phone_connect,       sum(consult_effective)               consult_effective,       sum(coupon)                          coupon,       sum(coupon_single_page)              coupon_single_page,       sum(redirect_to_shop)                redirect_to_shop,       sum(poi_collect)                     poi_collect,       sum(poi_address_click)               poi_address_click,       sum(luban_order_cnt)                 luban_order_cnt,       sum(luban_order_stat_amount)         luban_order_stat_amount,       sum(luban_order_roi)                 luban_order_roi,       sum(luban_live_enter_cnt)            luban_live_enter_cnt,       sum(               live_watch_one_minute_count           )                                live_watch_one_minute_count,       sum(luban_live_follow_cnt)           luban_live_follow_cnt,       sum(live_fans_club_join_cnt)         live_fans_club_join_cnt,       sum(luban_live_comment_cnt)          luban_live_comment_cnt,       sum(luban_live_share_cnt)            luban_live_share_cnt,       sum(luban_live_gift_cnt)             luban_live_gift_cnt,       sum(luban_live_gift_amount)          luban_live_gift_amount,       sum(               luban_live_slidecart_click_cnt           )                                luban_live_slidecart_click_cnt,       sum(               luban_live_click_product_cnt           )                                luban_live_click_product_cnt,       sum(luban_live_pay_order_count)      luban_live_pay_order_count,       sum(               luban_live_pay_order_stat_cost           )                                luban_live_pay_order_stat_cost,       sum(wechat_login_count)              wechat_login_count,       sum(               attribution_wechat_login_30d_count           )                                attribution_wechat_login_30d_count,       round(               sum(cost) / sum(wechat_login_count),               3           )                                wechat_login_cost,       round(                   sum(cost) / sum(                       attribution_wechat_login_30d_count                   ),                   3           )                                attribution_wechat_login_30d_cost,       sum(wechat_first_pay_count)          wechat_first_pay_count,       sum(               attribution_wechat_first_pay_30d_count           )                                attribution_wechat_first_pay_30d_count,       round(               sum(cost) / sum(wechat_first_pay_count),               3           )                                wechat_first_pay_cost,       round(                   sum(cost) / sum(                       attribution_wechat_first_pay_30d_count                   ),                   3           )                                attribution_wechat_first_pay_30d_cost,       round(               sum(wechat_first_pay_count) / sum(wechat_login_count) * 100,               3           )                                wechat_first_pay_rate,       round(                       sum(                               attribution_wechat_first_pay_30d_count                           ) / sum(                               attribution_wechat_login_30d_count                           ) * 100,                       3           )                                attribution_wechat_first_pay_30d_rate,       sum(wechat_pay_amount)               wechat_pay_amount,       sum(               attribution_wechat_pay_30d_amount           )                                attribution_wechat_pay_30d_amount,       sum(               attribution_wechat_pay_30d_roi           )                                attribution_wechat_pay_30d_roi,       sum(phone_effective)                 phone_effective,       sum(total_play)                      total_play,       sum(valid_play)                      valid_play,       round(               sum(cost) / sum(valid_play),               3           )                                valid_play_cost,       round(               sum(valid_play) / sum(`show`) * 100,               3           )                                valid_play_rate,       sum(play_25_feed_break)              play_25_feed_break,       sum(play_50_feed_break)              play_50_feed_break,       sum(play_75_feed_break)              play_75_feed_break,       sum(play_100_feed_break)             play_100_feed_break,       round(               sum(average_play_time_per_play) / {day_count},               3           )                                average_play_time_per_play,       round(               sum(play_over_rate) / {day_count} * 100,               3           )                                play_over_rate,       round(               sum(wifi_play) / sum(total_play) * 100,               3           )                                wifi_play_rate,       sum(wifi_play)                       wifi_play,       round(sum(play_duration_sum) / {day_count}, 3) play_duration_sum,       sum(               advanced_creative_phone_click           )                                advanced_creative_phone_click,       sum(               advanced_creative_counsel_click           )                                advanced_creative_counsel_click,       sum(               advanced_creative_form_click           )                                advanced_creative_form_click,       sum(               advanced_creative_coupon_addition           )                                advanced_creative_coupon_addition,       sum(               advanced_creative_form_submit           )                                advanced_creative_form_submit,       sum(card_show)                       card_show,       sum(`share`)                         `share`,       sum(`comment`)                       `comment`,       sum(`like`)                          `like`,       sum(follow)                          follow,       sum(home_visited)                    home_visited,       sum(ies_challenge_click)             ies_challenge_click,       sum(ies_music_click)                 ies_music_click,       sum(location_click)                  location_click,       sum(message_action)                  message_action,       sum(click_landing_page)              click_landing_page,       sum(click_shopwindow)                click_shopwindow,       sum(click_website)                   click_website,       sum(click_download)                  click_download,       sum(click_call_dy)                   click_call_dy,       sum(cpc)                             cpc,       sum(cpm)                             cpm,       sum(cpa)                             cpa,       round(sum(interact_per_cost) / {day_count}, 3) interact_per_cost,       round(sum(convert_show_rate) / {day_count}, 3) convert_show_rate,       round(               sum(round(play_duration_10s)) / sum(total_play) * 100,               3           )                                play_duration_10s_rate,       round(               sum(play_50_feed_break) / sum(total_play) * 100,               3           )                                play_50_feed_break_rate,       round(               sum(play_25_feed_break) / sum(total_play) * 100,               3           )                                play_25_feed_break_rate,       round(               sum(play_duration_5s_rate) / {day_count},               3           )                                play_duration_5s_rate,       round(               sum(play_duration_2s) / sum(total_play) * 100,               3           )                                play_duration_2s_rate,       round(               sum(average_play_progress) / {day_count},               3           )                                average_play_progress,       round(               sum(play_100_feed_break) / sum(total_play) * 100,               3           )                                play_100_feed_break_rate,       round(               sum(play_duration_3s) / sum(total_play) * 100,               3           )                                play_duration_3s_rate,       round(               sum(play_75_feed_break) / sum(total_play) * 100,               3           )                                play_75_feed_break_rate,       sum(play_duration)                   play_duration,       sum(play_duration_3s)                play_duration_3s,       sum(play_duration_2s)                play_duration_2s,       sum(play_duration_10s)               play_duration_10s,       round(               sum(average_video_play) / {day_count},               3           )                                average_video_play,       sum(avg_rank)                        avg_rank,       sum(submit_certification_count)      submit_certification_count,       sum(approval_count)                  approval_count,       sum(first_order_count)               first_rental_order_count,       sum(first_rental_order_count)        first_order_count,       sum(commute_first_pay_count)         commute_first_pay_countFROM (         SELECT video.signature,                uc.company_id,                CASE                    WHEN uc.company_id = 'd57fecdcf7a94d009736d9c850731582' THEN                        '北京汇创思拓数字科技有限公司'                    WHEN uc.company_id = '6608ed13c0dd42de93c790dbdf124234' THEN                        '广州汇创思拓数字科技有限公司'                    WHEN uc.company_id = '4b10089775c040119e139087517aed88' THEN                        '上海汇创思拓数字科技有限公司'                    ELSE                        uc.company_name                    END                                                              company_name,                cma.leader_id,                us.realname as leader_name,                ur.org_type,                ur.org_code,                ur.id,                ur.depart_name,                case when cmi.material_innovate = 2 then video.signature else '' end innovate_code,                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_rental_order_count,                report.first_order_count,                report.commute_first_pay_count         FROM media_api.bytedance_material_report_daily report                  LEFT JOIN (             SELECT advertiser_id,                    signature,                    material_id             FROM `media_api`.bytedance_file_video_get             GROUP BY advertiser_id,                      signature,                      material_id         ) video ON report.advertiser_id = video.advertiser_id             AND report.material_id = video.material_id                  LEFT JOIN (             SELECT CODE,                    material_innovate,                    cover_url             FROM `jeecg-boot`.ctop_material_info             GROUP BY CODE,                      material_innovate,                      cover_url         ) cmi ON video.signature = cmi.CODE                  LEFT JOIN (             SELECT material_id,                    leader_id             FROM (                      SELECT material_id,                             leader_id,                             row_number() over (                                 PARTITION BY material_id                                 ORDER BY                                     create_time                                 ) rn                      FROM `jeecg-boot`.ctop_material_ascription                  ) m             WHERE rn = 1         ) cma ON video.signature = cma.material_id                  LEFT JOIN `jeecg-boot`.user_company uc ON cma.leader_id = uc.user_id                  LEFT JOIN `jeecg-boot`.sys_user_depart de ON cma.leader_id = de.user_id                  LEFT JOIN `jeecg-boot`.sys_depart ur ON de.dep_id = ur.id                  LEFT JOIN  `jeecg-boot`.sys_user us on us.id =cma.leader_id         WHERE image_mode IN (                              'CREATIVE_IMAGE_MODE_VIDEO',                              'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL',                              'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO'             )           AND stat_datetime >= date_format(                 date_sub({date_time}, INTERVAL {day_count}   DAY),                 '%Y%m%d'             )     ) infoGROUP BY company_id,         company_name,         leader_id,         org_type,         org_code,         id,         depart_name;            """.format(date_time=self.date_time, day_count=self.day_count)            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 = AppBytedanceCompanyDesignReportBusi().taskHandler()    if status == 0:        print("作业执行成功")        exit(0)    else:        print("作业执行失败")        exit(-1)
 |