AdvertiserSumDailyReport.py 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/27 10:50 上午
  4. # @Site :
  5. # @File : AdvertiserDailyReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import datetime
  10. import logging
  11. from math import ceil
  12. from Apietl.utlis.Logger import Logger
  13. from Apietl.utlis.Utils import Utils
  14. from Apietl.conn.TidbConn import TidbConn
  15. class AdvertiserSumDailyReport:
  16. def __init__(self):
  17. self.conn = TidbConn.get_connection()
  18. self.parm = Utils.get_args()
  19. self.start_time = (datetime.date.today() + datetime.timedelta(0)).strftime("%Y%m%d")
  20. def daily_report(self):
  21. try:
  22. sql = """select count(1) from media_api.bytedance_advertiser_report_hourly where
  23. stat_datetime = {start_time} """.format(
  24. start_time=self.start_time)
  25. totalNum = TidbConn.quary(self.conn, sql)[0][0]
  26. pageCount = ceil(totalNum / 20000)
  27. for i in range(pageCount):
  28. sql = """
  29. replace into application.bytedance_advertiser_report_daily_dw(account_id, stat_datetime, project_id, product_id,
  30. advertiser_id, company_id, account_name, project_name,
  31. advertiser_name, company_name, sale_id, user_id, org_code,
  32. cost, `show`, avg_show_cost, click, avg_click_cost, ctr,
  33. `convert`, convert_cost, convert_rate, deep_convert,
  34. deep_convert_cost, deep_convert_rate, attribution_convert,
  35. attribution_convert_cost, attribution_deep_convert,
  36. attribution_deep_convert_cost, download_start,
  37. download_start_cost, download_start_rate, download_finish,
  38. download_finish_cost, download_finish_rate, click_install,
  39. install_finish, install_finish_cost, install_finish_rate,
  40. active, active_cost, active_rate, register,
  41. active_register_cost, active_register_rate, next_day_open,
  42. next_day_open_cost, next_day_open_rate,
  43. attribution_next_day_open_cnt,
  44. attribution_next_day_open_cost,
  45. attribution_next_day_open_rate, game_addiction,
  46. game_addiction_cost, game_addiction_rate, pay_count,
  47. active_pay_cost, active_pay_rate, loan_completion,
  48. loan_completion_cost, loan_completion_rate,
  49. pre_loan_credit, pre_loan_credit_cost, loan_credit,
  50. loan_credit_cost, loan_credit_rate, in_app_uv,
  51. in_app_detail_uv, in_app_cart, in_app_pay, in_app_order,
  52. attribution_game_pay_7d_count,
  53. attribution_game_pay_7d_cost,
  54. attribution_active_pay_7d_per_count, game_pay_cost,
  55. game_pay_count, phone, form, map_search, button, view,
  56. download, qq, lottery, vote, message, redirect, shopping,
  57. consult, wechat, phone_confirm, phone_connect,
  58. consult_effective, coupon, coupon_single_page,
  59. redirect_to_shop, poi_collect, poi_address_click,
  60. luban_order_cnt, luban_order_stat_amount, luban_order_roi,
  61. luban_live_enter_cnt, live_watch_one_minute_count,
  62. luban_live_follow_cnt, live_fans_club_join_cnt,
  63. luban_live_comment_cnt, luban_live_share_cnt,
  64. luban_live_gift_cnt, luban_live_gift_amount,
  65. luban_live_slidecart_click_cnt,
  66. luban_live_click_product_cnt, luban_live_pay_order_count,
  67. luban_live_pay_order_stat_cost,
  68. live_component_click_count, live_component_click_cost,
  69. live_component_click_rate, wechat_login_count,
  70. attribution_wechat_login_30d_count, wechat_login_cost,
  71. attribution_wechat_login_30d_cost, wechat_first_pay_count,
  72. attribution_wechat_first_pay_30d_count,
  73. wechat_first_pay_cost,
  74. attribution_wechat_first_pay_30d_cost,
  75. wechat_first_pay_rate,
  76. attribution_wechat_first_pay_30d_rate, wechat_pay_amount,
  77. attribution_wechat_pay_30d_amount,
  78. attribution_wechat_pay_30d_roi, phone_effective,
  79. total_play, valid_play, valid_play_cost, valid_play_rate,
  80. play_25_feed_break, play_50_feed_break,
  81. play_75_feed_break, play_100_feed_break,
  82. average_play_time_per_play, play_over_rate,
  83. wifi_play_rate, wifi_play, play_duration_sum,
  84. advanced_creative_phone_click,
  85. advanced_creative_counsel_click,
  86. advanced_creative_form_click,
  87. advanced_creative_coupon_addition,
  88. advanced_creative_form_submit, card_show, share, comment,
  89. `like`, follow, home_visited, ies_challenge_click,
  90. ies_music_click, location_click, message_action,
  91. click_landing_page, click_shopwindow, click_website,
  92. click_download, click_call_dy, submit_certification_count,
  93. approval_count, first_rental_order_count,
  94. first_order_count, commute_first_pay_count,
  95. attribution_active_pay_intra_one_day_roi
  96. ,attribution_active_pay_intra_one_day_amount
  97. ,attribution_active_pay_intra_one_day_rate
  98. ,attribution_active_pay_intra_one_day_cost
  99. ,attribution_active_pay_intra_one_day_count,stat_pay_amount)
  100. select a.advertiser_id account_id,
  101. a.stat_datetime,
  102. b.project_id,
  103. b.product_id,
  104. b.advertiser_id,
  105. b.company_id,
  106. b.account_name,
  107. b.project_name,
  108. b.advertiser_name,
  109. b.company_name,
  110. b.sale_id,
  111. b.user_id,
  112. b.org_code,
  113. sum(a.cost) as cost,
  114. sum(a.show) as shows,
  115. sum(a.avg_show_cost) as avg_show_cost,
  116. sum(a.click) as click,
  117. sum(a.avg_click_cost) as avg_click_cost,
  118. sum(a.ctr) as ctr,
  119. sum(a.convert) as converts,
  120. sum(a.convert_cost) as convert_cost,
  121. sum(a.convert_rate) as convert_rate,
  122. sum(a.deep_convert) as deep_convert,
  123. sum(a.deep_convert_cost) as deep_convert_cost,
  124. sum(a.deep_convert_rate) as deep_convert_rate,
  125. sum(a.attribution_convert)as attribution_convert,
  126. sum(a.attribution_convert_cost) as attribution_convert_cost,
  127. sum(a.attribution_deep_convert) as attribution_deep_convert_cost,
  128. sum(a.attribution_deep_convert_cost) as attribution_deep_convert_cost,
  129. sum(a.download_start) as download_start,
  130. sum(a.download_start_cost) as download_start_cost,
  131. sum(a.download_start_rate) as download_start_rate,
  132. sum(a.download_finish) as download_finish,
  133. sum(a.download_finish_cost) as download_finish_cost,
  134. sum(a.download_finish_rate) as download_finish_rate,
  135. sum(a.click_install) as click_install,
  136. sum(a.install_finish) as install_finish,
  137. sum(a.install_finish_cost) as install_finish_cost,
  138. sum(a.install_finish_rate) as install_finish_rate,
  139. sum(a.active) as active,
  140. sum(a.active_cost) as active_cost,
  141. sum(a.active_rate) as active_rate,
  142. sum(a.register) as register,
  143. sum(a.active_register_cost) as active_register_cost,
  144. sum(a.active_register_rate)as active_register_rate,
  145. sum(a.next_day_open) as next_day_open,
  146. sum(a.next_day_open_cost)as next_day_open_cost,
  147. sum(a.next_day_open_rate) as next_day_open_rate,
  148. sum(a.attribution_next_day_open_cnt)as attribution_next_day_open_cost,
  149. sum(a.attribution_next_day_open_cost)as attribution_next_day_open_cost ,
  150. sum(a.attribution_next_day_open_rate)as attribution_next_day_open_cost ,
  151. sum(a.game_addiction) as game_addiction,
  152. sum(a.game_addiction_cost) as game_addiction_cost,
  153. sum(a.game_addiction_rate) as game_addiction_rate,
  154. sum(a.pay_count)as pay_count,
  155. sum(a.active_pay_cost) as active_pay_cost,
  156. sum(a.active_pay_rate) as active_pay_rate,
  157. sum(a.loan_completion)as loan_completion,
  158. sum(a.loan_completion_cost) as loan_completion_cost,
  159. sum(a.loan_completion_rate) as loan_completion_rate,
  160. sum(a.pre_loan_credit)as pre_loan_credit,
  161. sum(a.pre_loan_credit_cost) as pre_loan_credit_cost,
  162. sum(a.loan_credit)as loan_credit,
  163. sum(a.loan_credit_cost)as loan_credit_cost,
  164. sum(a.loan_credit_rate) as loan_credit_rate,
  165. sum(a.in_app_uv)as in_app_uv,
  166. sum(a.in_app_detail_uv)as in_app_detail_uv,
  167. sum(a.in_app_cart)as in_app_cart,
  168. sum(a.in_app_pay)as in_app_pay,
  169. sum(a.in_app_order)as in_app_order,
  170. sum(a.attribution_game_pay_7d_count)as attribution_game_pay_7d_count,
  171. sum(a.attribution_game_pay_7d_cost)as attribution_game_pay_7d_cost,
  172. sum(a.attribution_active_pay_7d_per_count)as attribution_active_pay_7d_count,
  173. sum(a.game_pay_cost)as game_pay_cost,
  174. sum(a.game_pay_count)as game_pay_count,
  175. sum(a.phone)as phone,
  176. sum(a.form)as form,
  177. sum(a.map_search)as map_search,
  178. sum(a.button)as button,
  179. sum(a.view)as view,
  180. sum(a.download)as download,
  181. sum(a.qq)as qq,
  182. sum(a.lottery)as lottery,
  183. sum(a.vote)as vote,
  184. sum(a.message)as message ,
  185. sum(a.redirect)as redirect,
  186. sum(a.shopping) as shopping,
  187. sum(a.consult)as consult,
  188. sum(a.wechat)as wechat,
  189. sum(a.phone_confirm)as phone_confirm,
  190. sum(a.phone_connect)as phone_connect,
  191. sum(a.consult_effective)as consult_effective,
  192. sum(a.coupon)as coupon,
  193. sum(a.coupon_single_page)as coupon_single_page,
  194. sum(a.redirect_to_shop)as redirect_to_shop,
  195. sum(a.poi_collect)as poi_collect,
  196. sum(a.poi_address_click)as poi_address_click,
  197. sum(a.luban_order_cnt)as luban_order_cnt,
  198. sum(a.luban_order_stat_amount)as luban_order_stat_amount,
  199. sum(a.luban_order_roi)as luban_order_roi,
  200. sum(a.luban_live_enter_cnt)as luban_live_enter_cnt,
  201. sum(a.live_watch_one_minute_count)as live_watch_one_minute_count,
  202. sum(a.luban_live_follow_cnt)as luban_live_follow_cnt,
  203. sum(a.live_fans_club_join_cnt)as live_fans_club_join_cnt,
  204. sum(a.luban_live_comment_cnt)as luban_live_comment_cnt,
  205. sum(a.luban_live_share_cnt)as luban_live_share_cnt,
  206. sum(a.luban_live_gift_cnt)as luban_live_gift_cnt,
  207. sum(a.luban_live_gift_amount)as luban_live_gift_amount,
  208. sum(a.luban_live_slidecart_click_cnt)as luban_live_slidecart_click_cnt,
  209. sum(a.luban_live_click_product_cnt)as luban_live_click_product_cnt,
  210. sum(a.luban_live_pay_order_count)as luban_live_pay_order_count,
  211. sum(a.luban_live_pay_order_stat_cost)as luban_live_pay_order_stat_cost,
  212. sum(a.live_component_click_count)as live_component_click_count,
  213. sum(a.live_component_click_cost)as live_component_click_cost,
  214. sum(a.live_component_click_rate)as live_component_click_rate,
  215. sum(a.wechat_login_count)as wechat_login_count,
  216. sum(a.attribution_wechat_login_30d_count)as attribution_wechat_login_30d_count,
  217. sum(a.wechat_login_cost)as wechat_login_cost,
  218. sum(a.attribution_wechat_login_30d_cost)as attribution_wechat_login_30d_cost,
  219. sum(a.wechat_first_pay_count)as wechat_first_pay_count,
  220. sum(a.attribution_wechat_first_pay_30d_count)as attribution_wechat_first_pay_30d_count,
  221. sum(a.wechat_first_pay_cost)as wechat_first_pay_cost,
  222. sum(a.attribution_wechat_first_pay_30d_cost)as attribution_wechat_first_pay_30d_cost,
  223. sum(a.wechat_first_pay_rate)as wechat_first_pay_rate,
  224. sum(a.attribution_wechat_first_pay_30d_rate)as attribution_wechat_first_pay_30d_rate,
  225. sum(a.wechat_pay_amount)as wechat_pay_amount,
  226. sum(a.attribution_wechat_pay_30d_amount)as attribution_wechat_pay_30d_amount,
  227. sum(a.attribution_wechat_pay_30d_roi)as attribution_wechat_pay_30d_roi,
  228. sum(a.phone_effective)as phone_effective,
  229. sum(a.total_play)as total_play,
  230. sum(a.valid_play)as valid_play,
  231. sum(a.valid_play_cost)as valid_play_cost,
  232. sum(a.valid_play_rate)as valid_play_rate,
  233. sum(a.play_25_feed_break)as play_25_feed_break,
  234. sum(a.play_50_feed_break)as play_50_feed_break,
  235. sum(a.play_75_feed_break)as play_75_feed_break,
  236. sum(a.play_100_feed_break)as play_100_feed_break,
  237. sum(a.average_play_time_per_play)as average_play_time_per_play,
  238. sum(a.play_over_rate)as play_over_rate,
  239. sum(a.wifi_play_rate)as wifi_play_rate,
  240. sum(a.wifi_play)as wifi_play,
  241. sum(a.play_duration_sum)as play_duration_sum,
  242. sum(a.advanced_creative_phone_click)as advanced_creative_phone_click,
  243. sum(a.advanced_creative_counsel_click)as advanced_creative_counsel_click,
  244. sum(a.advanced_creative_form_click)as advanced_creative_form_click,
  245. sum(a.advanced_creative_coupon_addition)as advanced_creative_coupon_addition,
  246. sum(a.advanced_creative_form_submit)as advanced_creative_form_submit,
  247. sum(a.card_show)as card_show,
  248. sum(a.share)as share,
  249. sum(a.comment)as comment,
  250. sum(a.like)as likes,
  251. sum(a.follow)as follow,
  252. sum(a.home_visited)as home_visited,
  253. sum(a.ies_challenge_click)as ies_challenge_click,
  254. sum(a.ies_music_click)as ies_music_click,
  255. sum(a.location_click)as location_click,
  256. sum(a.message_action)as message_action,
  257. sum(a.click_landing_page)as click_landing_page,
  258. sum(a.click_shopwindow)as click_shopwindow,
  259. sum(a.click_website)as click_website,
  260. sum(a.click_download)as click_download,
  261. sum(a.click_call_dy)as click_call_dy,
  262. sum(a.submit_certification_count)as submit_certification_count,
  263. sum(a.approval_count)as approval_count,
  264. sum(a.first_rental_order_count)as first_rental_order_count,
  265. sum(a.first_order_count)as first_order_count,
  266. sum(a.commute_first_pay_count)as commute_first_pay_count,
  267. sum(a.attribution_active_pay_intra_one_day_roi)as attribution_active_pay_intra_one_day_roi,
  268. sum(a.attribution_active_pay_intra_one_day_amount)as attribution_active_pay_intra_one_day_amount,
  269. sum(a.attribution_active_pay_intra_one_day_rate)as attribution_active_pay_intra_one_day_rate,
  270. sum(a.attribution_active_pay_intra_one_day_cost)as attribution_active_pay_intra_one_day_cost,
  271. sum(attribution_active_pay_intra_one_day_count)as attribution_active_pay_intra_one_day_count,
  272. sum(a.stat_pay_amount)as stat_pay_amount
  273. from `media_api`.bytedance_advertiser_report_hourly a
  274. left join
  275. (select cua.account_id id,
  276. cua.account_id account_id,
  277. cp.id project_id,
  278. product.id product_id,
  279. ca.id advertiser_id,
  280. uc.company_id company_id,
  281. cua.auth_name account_name,
  282. cp.project_name project_name,
  283. ca.name advertiser_name,
  284. uc.company_name company_name,
  285. cp.sale_id sale_id,
  286. su.id user_id,
  287. su.org_code org_code
  288. from `jeecg-boot`.ctop_user_allocation cua
  289. left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
  290. left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
  291. left join `jeecg-boot`.sys_user su on su.id = cua.user_id
  292. left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id
  293. left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
  294. where cua.account_id is not null) b
  295. on a.advertiser_id = b.account_id
  296. where a.stat_datetime = {start_time}
  297. group by a.advertiser_id
  298. order by account_id limit {num},20000;
  299. """.format(start_time=self.start_time, num=i * 20000)
  300. print(sql)
  301. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  302. self.conn.commit()
  303. TidbConn.conn_close(conn=self.conn)
  304. return 0
  305. except Exception as e:
  306. logging.error(e)
  307. return -1
  308. if __name__ == '__main__':
  309. status = AdvertiserSumDailyReport().daily_report()
  310. if status == 0:
  311. print("作业执行成功")
  312. exit(0)
  313. else:
  314. print("作业执行失败")
  315. exit(-1)