AccountReport.py 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : AccountReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. import logging
  9. from Apietl.conn.TidbConn import TidbConn
  10. from Apietl.kwai_etl.report.ReplaceAccountInfo import ReplaceAccountInfo
  11. from Apietl.utlis.Utils import Utils
  12. class AccountReport:
  13. def __init__(self):
  14. self.conn = TidbConn.get_connection()
  15. def daily_report(self, account_id, start_date, end_date):
  16. ReplaceAccountInfo.handler()
  17. try:
  18. sql = """
  19. replace into application.kuaishou_account_report_daily_dw
  20. (account_id, stat_date, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name,
  21. company_name, sale_id, user_id, org_code, charge, `show`, photo_click, aclick, bclick, photo_click_ratio,
  22. play_3s_ratio, action_ratio, impression_1k_cost, photo_click_cost, action_cost, share, comment, `like`, follow,
  23. cancel_follow, report, block, negative, submit, download_started, download_completed, activation, event_pay_first_day,
  24. event_pay_purchase_amount_first_day, event_pay_first_day_roi, event_pay, event_pay_purchase_amount, event_pay_roi,
  25. event_register, event_register_cost, event_register_ratio, event_jin_jian_app, event_jin_jian_app_cost,
  26. event_credit_grant_app, event_credit_grant_app_cost, event_credit_grant_app_ratio, event_order_paid,
  27. event_order_paid_purchase_amount, event_order_paid_cost, event_next_day_stay, event_next_day_stay_cost,
  28. event_next_day_stay_ratio, form_count, form_cost, form_action_ratio, event_jin_jian_landing_page,
  29. event_jin_jian_landing_page_cost, event_credit_grant_landing_page, event_credit_grant_landing_page_cost,
  30. event_credit_grant_landing_page_ratio, event_valid_clues, event_valid_clues_cost, event_add_wechat,
  31. event_add_wechat_cost, event_add_wechat_ratio, event_get_through, event_get_through_cost, event_get_through_ratio,
  32. event_app_invoked, event_app_invoked_cost, event_app_invoked_ratio, event_credit_grant_landing_ratio, play_5s_ratio,
  33. play_end_ratio, event_new_user_pay, event_new_user_pay_cost, event_new_user_pay_ratio, click_1k_cost, ad_product_cnt,
  34. event_goods_view, merchant_reco_fans, event_order_amount_roi, event_goods_view_cost, merchant_reco_fans_cost,
  35. event_button_click, event_button_click_cost, event_button_click_ratio, event_order_paid_roi,
  36. event_new_user_jinjian_app, event_new_user_jinjian_app_cost, event_new_user_jinjian_app_roi,
  37. event_new_user_credit_grant_app, event_new_user_credit_grant_app_cost, event_new_user_credit_grant_app_roi,
  38. event_new_user_jinjian_page, event_new_user_jinjian_page_cost, event_new_user_jinjian_page_roi,
  39. event_new_user_credit_grant_page, event_new_user_credit_grant_page_cost, event_new_user_credit_grant_page_roi,
  40. event_appoint_form, event_appoint_form_cost, event_appoint_form_ratio, event_appoint_jump_click,
  41. event_appoint_jump_click_cost, event_appoint_jump_click_ratio, union_event_pay_purchase_amount_7d,
  42. union_event_pay_purchase_amount_7d_roi, placement_type, ad_scene, event_order_successed, ad_photo_played_10s_ratio,
  43. key_action_cost, event_add_shopping_cart_cost, event_ad_watch_times_ratio, event_outbound_call_cost,
  44. event_outbound_call, action_new_ratio, event_watch_app_ad, event_multi_conversion_cost, event_phone_card_activate,
  45. ad_photo_played_75percent_ratio, key_action_ratio, event_ad_watch_times_cost, event_add_shopping_cart, key_action,
  46. event_multi_conversion, event_wechat_connected, event_dsp_gift_form, event_intention_confirmed,
  47. event_phone_get_through, event_multi_conversion_ratio, event_measurement_house, ad_photo_played_2s_ratio,
  48. event_outbound_call_ratio, event_ad_watch_times)
  49. select report.advertiser_id as account_id,
  50. stat_date,
  51. project_id,
  52. product_id,
  53. sys.advertiser_id,
  54. company_id,
  55. account_name,
  56. project_name,
  57. advertiser_name,
  58. company_name,
  59. sale_id,
  60. user_id,
  61. org_code,
  62. charge,
  63. `show`,
  64. photo_click,
  65. aclick,
  66. bclick,
  67. photo_click_ratio,
  68. play_3s_ratio,
  69. action_ratio,
  70. impression_1k_cost,
  71. photo_click_cost,
  72. action_cost,
  73. share,
  74. comment,
  75. `like`,
  76. follow,
  77. cancel_follow,
  78. report,
  79. block,
  80. negative,
  81. submit,
  82. download_started,
  83. download_completed,
  84. activation,
  85. event_pay_first_day,
  86. event_pay_purchase_amount_first_day,
  87. event_pay_first_day_roi,
  88. event_pay,
  89. event_pay_purchase_amount,
  90. event_pay_roi,
  91. event_register,
  92. event_register_cost,
  93. event_register_ratio,
  94. event_jin_jian_app,
  95. event_jin_jian_app_cost,
  96. event_credit_grant_app,
  97. event_credit_grant_app_cost,
  98. event_credit_grant_app_ratio,
  99. event_order_paid,
  100. event_order_paid_purchase_amount,
  101. event_order_paid_cost,
  102. event_next_day_stay,
  103. event_next_day_stay_cost,
  104. event_next_day_stay_ratio,
  105. form_count,
  106. form_cost,
  107. form_action_ratio,
  108. event_jin_jian_landing_page,
  109. event_jin_jian_landing_page_cost,
  110. event_credit_grant_landing_page,
  111. event_credit_grant_landing_page_cost,
  112. event_credit_grant_landing_page_ratio,
  113. event_valid_clues,
  114. event_valid_clues_cost,
  115. event_add_wechat,
  116. event_add_wechat_cost,
  117. event_add_wechat_ratio,
  118. event_get_through,
  119. event_get_through_cost,
  120. event_get_through_ratio,
  121. event_app_invoked,
  122. event_app_invoked_cost,
  123. event_app_invoked_ratio,
  124. event_credit_grant_landing_ratio,
  125. play_5s_ratio,
  126. play_end_ratio,
  127. event_new_user_pay,
  128. event_new_user_pay_cost,
  129. event_new_user_pay_ratio,
  130. click_1k_cost,
  131. ad_product_cnt,
  132. event_goods_view,
  133. merchant_reco_fans,
  134. event_order_amount_roi,
  135. event_goods_view_cost,
  136. merchant_reco_fans_cost,
  137. event_button_click,
  138. event_button_click_cost,
  139. event_button_click_ratio,
  140. event_order_paid_roi,
  141. event_new_user_jinjian_app,
  142. event_new_user_jinjian_app_cost,
  143. event_new_user_jinjian_app_roi,
  144. event_new_user_credit_grant_app,
  145. event_new_user_credit_grant_app_cost,
  146. event_new_user_credit_grant_app_roi,
  147. event_new_user_jinjian_page,
  148. event_new_user_jinjian_page_cost,
  149. event_new_user_jinjian_page_roi,
  150. event_new_user_credit_grant_page,
  151. event_new_user_credit_grant_page_cost,
  152. event_new_user_credit_grant_page_roi,
  153. event_appoint_form,
  154. event_appoint_form_cost,
  155. event_appoint_form_ratio,
  156. event_appoint_jump_click,
  157. event_appoint_jump_click_cost,
  158. event_appoint_jump_click_ratio,
  159. union_event_pay_purchase_amount_7d,
  160. union_event_pay_purchase_amount_7d_roi,
  161. placement_type,
  162. ad_scene,
  163. event_order_successed,
  164. ad_photo_played_10s_ratio,
  165. key_action_cost,
  166. event_add_shopping_cart_cost,
  167. event_ad_watch_times_ratio,
  168. event_outbound_call_cost,
  169. event_outbound_call,
  170. action_new_ratio,
  171. event_watch_app_ad,
  172. event_multi_conversion_cost,
  173. event_phone_card_activate,
  174. ad_photo_played_75percent_ratio,
  175. key_action_ratio,
  176. event_ad_watch_times_cost,
  177. event_add_shopping_cart,
  178. key_action,
  179. event_multi_conversion,
  180. event_wechat_connected,
  181. event_dsp_gift_form,
  182. event_intention_confirmed,
  183. event_phone_get_through,
  184. event_multi_conversion_ratio,
  185. event_measurement_house,
  186. ad_photo_played_2s_ratio,
  187. event_outbound_call_ratio,
  188. event_ad_watch_times
  189. from media_api.kuaishou_account_report_daily report
  190. left join
  191. `jeecg-boot`.ctop_account_attribution_info sys
  192. on report.advertiser_id = sys.account_id
  193. where report.stat_date between {start_date} and {end_date}
  194. and report.advertiser_id={account_id}
  195. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  196. print(sql)
  197. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  198. self.conn.commit()
  199. TidbConn.conn_close(conn=self.conn)
  200. except Exception as e:
  201. logging.error('失败详细信息:', repr(e))
  202. def hourly_report(self, account_id, start_date, end_date):
  203. try:
  204. sql = """
  205. replace into application.kuaishou_account_report_hourly_dw
  206. (account_id, stat_date,`hour`, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name,
  207. company_name, sale_id, user_id, org_code, charge, `show`, photo_click, aclick, bclick, photo_click_ratio,
  208. play_3s_ratio, action_ratio, impression_1k_cost, photo_click_cost, action_cost, share, comment, `like`, follow,
  209. cancel_follow, report, block, negative, submit, download_started, download_completed, activation, event_pay_first_day,
  210. event_pay_purchase_amount_first_day, event_pay_first_day_roi, event_pay, event_pay_purchase_amount, event_pay_roi,
  211. event_register, event_register_cost, event_register_ratio, event_jin_jian_app, event_jin_jian_app_cost,
  212. event_credit_grant_app, event_credit_grant_app_cost, event_credit_grant_app_ratio, event_order_paid,
  213. event_order_paid_purchase_amount, event_order_paid_cost, event_next_day_stay, event_next_day_stay_cost,
  214. event_next_day_stay_ratio, form_count, form_cost, form_action_ratio, event_jin_jian_landing_page,
  215. event_jin_jian_landing_page_cost, event_credit_grant_landing_page, event_credit_grant_landing_page_cost,
  216. event_credit_grant_landing_page_ratio, event_valid_clues, event_valid_clues_cost, event_add_wechat,
  217. event_add_wechat_cost, event_add_wechat_ratio, event_get_through, event_get_through_cost, event_get_through_ratio,
  218. event_app_invoked, event_app_invoked_cost, event_app_invoked_ratio, event_credit_grant_landing_ratio, play_5s_ratio,
  219. play_end_ratio, event_new_user_pay, event_new_user_pay_cost, event_new_user_pay_ratio, click_1k_cost, ad_product_cnt,
  220. event_goods_view, merchant_reco_fans, event_order_amount_roi, event_goods_view_cost, merchant_reco_fans_cost,
  221. event_button_click, event_button_click_cost, event_button_click_ratio, event_order_paid_roi,
  222. event_new_user_jinjian_app, event_new_user_jinjian_app_cost, event_new_user_jinjian_app_roi,
  223. event_new_user_credit_grant_app, event_new_user_credit_grant_app_cost, event_new_user_credit_grant_app_roi,
  224. event_new_user_jinjian_page, event_new_user_jinjian_page_cost, event_new_user_jinjian_page_roi,
  225. event_new_user_credit_grant_page, event_new_user_credit_grant_page_cost, event_new_user_credit_grant_page_roi,
  226. event_appoint_form, event_appoint_form_cost, event_appoint_form_ratio, event_appoint_jump_click,
  227. event_appoint_jump_click_cost, event_appoint_jump_click_ratio, union_event_pay_purchase_amount_7d,
  228. union_event_pay_purchase_amount_7d_roi, placement_type, ad_scene, event_order_successed, ad_photo_played_10s_ratio,
  229. key_action_cost, event_add_shopping_cart_cost, event_ad_watch_times_ratio, event_outbound_call_cost,
  230. event_outbound_call, action_new_ratio, event_watch_app_ad, event_multi_conversion_cost, event_phone_card_activate,
  231. ad_photo_played_75percent_ratio, key_action_ratio, event_ad_watch_times_cost, event_add_shopping_cart, key_action,
  232. event_multi_conversion, event_wechat_connected, event_dsp_gift_form, event_intention_confirmed,
  233. event_phone_get_through, event_multi_conversion_ratio, event_measurement_house, ad_photo_played_2s_ratio,
  234. event_outbound_call_ratio, event_ad_watch_times)
  235. select report.advertiser_id as account_id,
  236. stat_date,
  237. `hour`,
  238. project_id,
  239. product_id,
  240. sys.advertiser_id,
  241. company_id,
  242. account_name,
  243. project_name,
  244. advertiser_name,
  245. company_name,
  246. sale_id,
  247. user_id,
  248. org_code,
  249. charge,
  250. `show`,
  251. photo_click,
  252. aclick,
  253. bclick,
  254. photo_click_ratio,
  255. play_3s_ratio,
  256. action_ratio,
  257. impression_1k_cost,
  258. photo_click_cost,
  259. action_cost,
  260. share,
  261. comment,
  262. `like`,
  263. follow,
  264. cancel_follow,
  265. report,
  266. block,
  267. negative,
  268. submit,
  269. download_started,
  270. download_completed,
  271. activation,
  272. event_pay_first_day,
  273. event_pay_purchase_amount_first_day,
  274. event_pay_first_day_roi,
  275. event_pay,
  276. event_pay_purchase_amount,
  277. event_pay_roi,
  278. event_register,
  279. event_register_cost,
  280. event_register_ratio,
  281. event_jin_jian_app,
  282. event_jin_jian_app_cost,
  283. event_credit_grant_app,
  284. event_credit_grant_app_cost,
  285. event_credit_grant_app_ratio,
  286. event_order_paid,
  287. event_order_paid_purchase_amount,
  288. event_order_paid_cost,
  289. event_next_day_stay,
  290. event_next_day_stay_cost,
  291. event_next_day_stay_ratio,
  292. form_count,
  293. form_cost,
  294. form_action_ratio,
  295. event_jin_jian_landing_page,
  296. event_jin_jian_landing_page_cost,
  297. event_credit_grant_landing_page,
  298. event_credit_grant_landing_page_cost,
  299. event_credit_grant_landing_page_ratio,
  300. event_valid_clues,
  301. event_valid_clues_cost,
  302. event_add_wechat,
  303. event_add_wechat_cost,
  304. event_add_wechat_ratio,
  305. event_get_through,
  306. event_get_through_cost,
  307. event_get_through_ratio,
  308. event_app_invoked,
  309. event_app_invoked_cost,
  310. event_app_invoked_ratio,
  311. event_credit_grant_landing_ratio,
  312. play_5s_ratio,
  313. play_end_ratio,
  314. event_new_user_pay,
  315. event_new_user_pay_cost,
  316. event_new_user_pay_ratio,
  317. click_1k_cost,
  318. ad_product_cnt,
  319. event_goods_view,
  320. merchant_reco_fans,
  321. event_order_amount_roi,
  322. event_goods_view_cost,
  323. merchant_reco_fans_cost,
  324. event_button_click,
  325. event_button_click_cost,
  326. event_button_click_ratio,
  327. event_order_paid_roi,
  328. event_new_user_jinjian_app,
  329. event_new_user_jinjian_app_cost,
  330. event_new_user_jinjian_app_roi,
  331. event_new_user_credit_grant_app,
  332. event_new_user_credit_grant_app_cost,
  333. event_new_user_credit_grant_app_roi,
  334. event_new_user_jinjian_page,
  335. event_new_user_jinjian_page_cost,
  336. event_new_user_jinjian_page_roi,
  337. event_new_user_credit_grant_page,
  338. event_new_user_credit_grant_page_cost,
  339. event_new_user_credit_grant_page_roi,
  340. event_appoint_form,
  341. event_appoint_form_cost,
  342. event_appoint_form_ratio,
  343. event_appoint_jump_click,
  344. event_appoint_jump_click_cost,
  345. event_appoint_jump_click_ratio,
  346. union_event_pay_purchase_amount_7d,
  347. union_event_pay_purchase_amount_7d_roi,
  348. placement_type,
  349. ad_scene,
  350. event_order_successed,
  351. ad_photo_played_10s_ratio,
  352. key_action_cost,
  353. event_add_shopping_cart_cost,
  354. event_ad_watch_times_ratio,
  355. event_outbound_call_cost,
  356. event_outbound_call,
  357. action_new_ratio,
  358. event_watch_app_ad,
  359. event_multi_conversion_cost,
  360. event_phone_card_activate,
  361. ad_photo_played_75percent_ratio,
  362. key_action_ratio,
  363. event_ad_watch_times_cost,
  364. event_add_shopping_cart,
  365. key_action,
  366. event_multi_conversion,
  367. event_wechat_connected,
  368. event_dsp_gift_form,
  369. event_intention_confirmed,
  370. event_phone_get_through,
  371. event_multi_conversion_ratio,
  372. event_measurement_house,
  373. ad_photo_played_2s_ratio,
  374. event_outbound_call_ratio,
  375. event_ad_watch_times
  376. from media_api.kuaishou_account_report_hourly report
  377. left join
  378. `jeecg-boot`.ctop_account_attribution_info sys
  379. on report.advertiser_id = sys.account_id
  380. where report.stat_date between {start_date} and {end_date}
  381. and report.advertiser_id={account_id}
  382. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  383. print(sql)
  384. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  385. self.conn.commit()
  386. TidbConn.conn_close(conn=self.conn)
  387. except Exception as e:
  388. logging.error('失败详细信息:', repr(e))
  389. def handler(self, date_type, account_id, start_date, end_date):
  390. if date_type == "daily":
  391. self.daily_report(account_id, start_date, end_date)
  392. else:
  393. self.hourly_report(account_id, start_date, end_date)