AccountReport.py 18 KB

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