AccountReport.py 44 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681
  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. set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  18. insert into application.kuaishou_account_report_daily_dw
  19. (account_id, stat_date, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name,
  20. company_name, sale_id, user_id, org_code, charge, `show`, photo_click, aclick, bclick, photo_click_ratio,
  21. play_3s_ratio, action_ratio, impression_1k_cost, photo_click_cost, action_cost, share, comment, `like`, follow,
  22. cancel_follow, report, block, negative, submit, download_started, download_completed, activation, event_pay_first_day,
  23. event_pay_purchase_amount_first_day, event_pay_first_day_roi, event_pay, event_pay_purchase_amount, event_pay_roi,
  24. event_register, event_register_cost, event_register_ratio, event_jin_jian_app, event_jin_jian_app_cost,
  25. event_credit_grant_app, event_credit_grant_app_cost, event_credit_grant_app_ratio, event_order_paid,
  26. event_order_paid_purchase_amount, event_order_paid_cost, event_next_day_stay, event_next_day_stay_cost,
  27. event_next_day_stay_ratio, form_count, form_cost, form_action_ratio, event_jin_jian_landing_page,
  28. event_jin_jian_landing_page_cost, event_credit_grant_landing_page, event_credit_grant_landing_page_cost,
  29. event_credit_grant_landing_page_ratio, event_valid_clues, event_valid_clues_cost, event_add_wechat,
  30. event_add_wechat_cost, event_add_wechat_ratio, event_get_through, event_get_through_cost, event_get_through_ratio,
  31. event_app_invoked, event_app_invoked_cost, event_app_invoked_ratio, event_credit_grant_landing_ratio, play_5s_ratio,
  32. play_end_ratio, event_new_user_pay, event_new_user_pay_cost, event_new_user_pay_ratio, click_1k_cost, ad_product_cnt,
  33. event_goods_view, merchant_reco_fans, event_order_amount_roi, event_goods_view_cost, merchant_reco_fans_cost,
  34. event_button_click, event_button_click_cost, event_button_click_ratio, event_order_paid_roi,
  35. event_new_user_jinjian_app, event_new_user_jinjian_app_cost, event_new_user_jinjian_app_roi,
  36. event_new_user_credit_grant_app, event_new_user_credit_grant_app_cost, event_new_user_credit_grant_app_roi,
  37. event_new_user_jinjian_page, event_new_user_jinjian_page_cost, event_new_user_jinjian_page_roi,
  38. event_new_user_credit_grant_page, event_new_user_credit_grant_page_cost, event_new_user_credit_grant_page_roi,
  39. event_appoint_form, event_appoint_form_cost, event_appoint_form_ratio, event_appoint_jump_click,
  40. event_appoint_jump_click_cost, event_appoint_jump_click_ratio, union_event_pay_purchase_amount_7d,
  41. union_event_pay_purchase_amount_7d_roi, placement_type, ad_scene, event_order_successed, ad_photo_played_10s_ratio,
  42. key_action_cost, event_add_shopping_cart_cost, event_ad_watch_times_ratio, event_outbound_call_cost,
  43. event_outbound_call, action_new_ratio, event_watch_app_ad, event_multi_conversion_cost, event_phone_card_activate,
  44. ad_photo_played_75percent_ratio, key_action_ratio, event_ad_watch_times_cost, event_add_shopping_cart, key_action,
  45. event_multi_conversion, event_wechat_connected, event_dsp_gift_form, event_intention_confirmed,
  46. event_phone_get_through, event_multi_conversion_ratio, event_measurement_house, ad_photo_played_2s_ratio,
  47. event_outbound_call_ratio, event_ad_watch_times)
  48. (
  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. hcst_system.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. )
  196. on DUPLICATE KEY UPDATE account_id =values(account_id),
  197. stat_date =values(stat_date),
  198. project_id =values(project_id),
  199. product_id =values(product_id),
  200. advertiser_id =values(advertiser_id),
  201. company_id =values(company_id),
  202. account_name =values(account_name),
  203. project_name =values(project_name),
  204. advertiser_name =values(advertiser_name),
  205. company_name =values(company_name),
  206. sale_id =values(sale_id),
  207. user_id =values(user_id),
  208. org_code =values(org_code),
  209. charge =values(charge),
  210. `show` =values(`show`),
  211. photo_click =values(photo_click),
  212. aclick =values(aclick),
  213. bclick =values(bclick),
  214. photo_click_ratio =values(photo_click_ratio),
  215. play_3s_ratio =values(play_3s_ratio),
  216. action_ratio =values(action_ratio),
  217. impression_1k_cost =values(impression_1k_cost),
  218. photo_click_cost =values(photo_click_cost),
  219. action_cost =values(action_cost),
  220. share =values(share),
  221. comment =values(comment),
  222. `like` =values(`like`),
  223. follow =values(follow),
  224. cancel_follow =values(cancel_follow),
  225. report =values(report),
  226. block =values(block),
  227. negative =values(negative),
  228. submit =values(submit),
  229. download_started =values(download_started),
  230. download_completed =values(download_completed),
  231. activation =values(activation),
  232. event_pay_first_day =values(event_pay_first_day),
  233. event_pay_purchase_amount_first_day =values(event_pay_purchase_amount_first_day),
  234. event_pay_first_day_roi =values(event_pay_first_day_roi),
  235. event_pay =values(event_pay),
  236. event_pay_purchase_amount =values(event_pay_purchase_amount),
  237. event_pay_roi =values(event_pay_roi),
  238. event_register =values(event_register),
  239. event_register_cost =values(event_register_cost),
  240. event_register_ratio =values(event_register_ratio),
  241. event_jin_jian_app =values(event_jin_jian_app),
  242. event_jin_jian_app_cost =values(event_jin_jian_app_cost),
  243. event_credit_grant_app =values(event_credit_grant_app),
  244. event_credit_grant_app_cost =values(event_credit_grant_app_cost),
  245. event_credit_grant_app_ratio =values(event_credit_grant_app_ratio),
  246. event_order_paid =values(event_order_paid),
  247. event_order_paid_purchase_amount =values(event_order_paid_purchase_amount),
  248. event_order_paid_cost =values(event_order_paid_cost),
  249. event_next_day_stay =values(event_next_day_stay),
  250. event_next_day_stay_cost =values(event_next_day_stay_cost),
  251. event_next_day_stay_ratio =values(event_next_day_stay_ratio),
  252. form_count =values(form_count),
  253. form_cost =values(form_cost),
  254. form_action_ratio =values(form_action_ratio),
  255. event_jin_jian_landing_page =values(event_jin_jian_landing_page),
  256. event_jin_jian_landing_page_cost =values(event_jin_jian_landing_page_cost),
  257. event_credit_grant_landing_page =values(event_credit_grant_landing_page),
  258. event_credit_grant_landing_page_cost =values(event_credit_grant_landing_page_cost),
  259. event_credit_grant_landing_page_ratio =values(event_credit_grant_landing_page_ratio),
  260. event_valid_clues =values(event_valid_clues),
  261. event_valid_clues_cost =values(event_valid_clues_cost),
  262. event_add_wechat =values(event_add_wechat),
  263. event_add_wechat_cost =values(event_add_wechat_cost),
  264. event_add_wechat_ratio =values(event_add_wechat_ratio),
  265. event_get_through =values(event_get_through),
  266. event_get_through_cost =values(event_get_through_cost),
  267. event_get_through_ratio =values(event_get_through_ratio),
  268. event_app_invoked =values(event_app_invoked),
  269. event_app_invoked_cost =values(event_app_invoked_cost),
  270. event_app_invoked_ratio =values(event_app_invoked_ratio),
  271. event_credit_grant_landing_ratio =values(event_credit_grant_landing_ratio),
  272. play_5s_ratio =values(play_5s_ratio),
  273. play_end_ratio =values(play_end_ratio),
  274. event_new_user_pay =values(event_new_user_pay),
  275. event_new_user_pay_cost =values(event_new_user_pay_cost),
  276. event_new_user_pay_ratio =values(event_new_user_pay_ratio),
  277. click_1k_cost =values(click_1k_cost),
  278. ad_product_cnt =values(ad_product_cnt),
  279. event_goods_view =values(event_goods_view),
  280. merchant_reco_fans =values(merchant_reco_fans),
  281. event_order_amount_roi =values(event_order_amount_roi),
  282. event_goods_view_cost =values(event_goods_view_cost),
  283. merchant_reco_fans_cost =values(merchant_reco_fans_cost),
  284. event_button_click =values(event_button_click),
  285. event_button_click_cost =values(event_button_click_cost),
  286. event_button_click_ratio =values(event_button_click_ratio),
  287. event_order_paid_roi =values(event_order_paid_roi),
  288. event_new_user_jinjian_app =values(event_new_user_jinjian_app),
  289. event_new_user_jinjian_app_cost =values(event_new_user_jinjian_app_cost),
  290. event_new_user_jinjian_app_roi =values(event_new_user_jinjian_app_roi),
  291. event_new_user_credit_grant_app =values(event_new_user_credit_grant_app),
  292. event_new_user_credit_grant_app_cost =values(event_new_user_credit_grant_app_cost),
  293. event_new_user_credit_grant_app_roi =values(event_new_user_credit_grant_app_roi),
  294. event_new_user_jinjian_page =values(event_new_user_jinjian_page),
  295. event_new_user_jinjian_page_cost =values(event_new_user_jinjian_page_cost),
  296. event_new_user_jinjian_page_roi =values(event_new_user_jinjian_page_roi),
  297. event_new_user_credit_grant_page =values(event_new_user_credit_grant_page),
  298. event_new_user_credit_grant_page_cost =values(event_new_user_credit_grant_page_cost),
  299. event_new_user_credit_grant_page_roi =values(event_new_user_credit_grant_page_roi),
  300. event_appoint_form =values(event_appoint_form),
  301. event_appoint_form_cost =values(event_appoint_form_cost),
  302. event_appoint_form_ratio =values(event_appoint_form_ratio),
  303. event_appoint_jump_click =values(event_appoint_jump_click),
  304. event_appoint_jump_click_cost =values(event_appoint_jump_click_cost),
  305. event_appoint_jump_click_ratio =values(event_appoint_jump_click_ratio),
  306. union_event_pay_purchase_amount_7d =values(union_event_pay_purchase_amount_7d),
  307. union_event_pay_purchase_amount_7d_roi =values(union_event_pay_purchase_amount_7d_roi),
  308. placement_type =values(placement_type),
  309. ad_scene =values(ad_scene),
  310. event_order_successed =values(event_order_successed),
  311. ad_photo_played_10s_ratio =values(ad_photo_played_10s_ratio),
  312. key_action_cost =values(key_action_cost),
  313. event_add_shopping_cart_cost =values(event_add_shopping_cart_cost),
  314. event_ad_watch_times_ratio =values(event_ad_watch_times_ratio),
  315. event_outbound_call_cost =values(event_outbound_call_cost),
  316. event_outbound_call =values(event_outbound_call),
  317. action_new_ratio =values(action_new_ratio),
  318. event_watch_app_ad =values(event_watch_app_ad),
  319. event_multi_conversion_cost =values(event_multi_conversion_cost),
  320. event_phone_card_activate =values(event_phone_card_activate),
  321. ad_photo_played_75percent_ratio =values(ad_photo_played_75percent_ratio),
  322. key_action_ratio =values(key_action_ratio),
  323. event_ad_watch_times_cost =values(event_ad_watch_times_cost),
  324. event_add_shopping_cart =values(event_add_shopping_cart),
  325. key_action =values(key_action),
  326. event_multi_conversion =values(event_multi_conversion),
  327. event_wechat_connected =values(event_wechat_connected),
  328. event_dsp_gift_form =values(event_dsp_gift_form),
  329. event_intention_confirmed =values(event_intention_confirmed),
  330. event_phone_get_through =values(event_phone_get_through),
  331. event_multi_conversion_ratio =values(event_multi_conversion_ratio),
  332. event_measurement_house =values(event_measurement_house),
  333. ad_photo_played_2s_ratio =values(ad_photo_played_2s_ratio),
  334. event_outbound_call_ratio =values(event_outbound_call_ratio),
  335. event_ad_watch_times =values(event_ad_watch_times);
  336. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  337. print(sql)
  338. TidbConn.exec_sql(self.conn, sql)
  339. self.conn.commit()
  340. except Exception as e:
  341. logging.error('失败详细信息:', repr(e))
  342. def hourly_report(self, account_id, start_date, end_date):
  343. try:
  344. sql = """
  345. insert into application.kuaishou_account_report_hourly_dw
  346. (account_id, stat_date,`hour`, project_id, product_id, advertiser_id, company_id, account_name, project_name, advertiser_name,
  347. company_name, sale_id, user_id, org_code, charge, `show`, photo_click, aclick, bclick, photo_click_ratio,
  348. play_3s_ratio, action_ratio, impression_1k_cost, photo_click_cost, action_cost, share, comment, `like`, follow,
  349. cancel_follow, report, block, negative, submit, download_started, download_completed, activation, event_pay_first_day,
  350. event_pay_purchase_amount_first_day, event_pay_first_day_roi, event_pay, event_pay_purchase_amount, event_pay_roi,
  351. event_register, event_register_cost, event_register_ratio, event_jin_jian_app, event_jin_jian_app_cost,
  352. event_credit_grant_app, event_credit_grant_app_cost, event_credit_grant_app_ratio, event_order_paid,
  353. event_order_paid_purchase_amount, event_order_paid_cost, event_next_day_stay, event_next_day_stay_cost,
  354. event_next_day_stay_ratio, form_count, form_cost, form_action_ratio, event_jin_jian_landing_page,
  355. event_jin_jian_landing_page_cost, event_credit_grant_landing_page, event_credit_grant_landing_page_cost,
  356. event_credit_grant_landing_page_ratio, event_valid_clues, event_valid_clues_cost, event_add_wechat,
  357. event_add_wechat_cost, event_add_wechat_ratio, event_get_through, event_get_through_cost, event_get_through_ratio,
  358. event_app_invoked, event_app_invoked_cost, event_app_invoked_ratio, event_credit_grant_landing_ratio, play_5s_ratio,
  359. play_end_ratio, event_new_user_pay, event_new_user_pay_cost, event_new_user_pay_ratio, click_1k_cost, ad_product_cnt,
  360. event_goods_view, merchant_reco_fans, event_order_amount_roi, event_goods_view_cost, merchant_reco_fans_cost,
  361. event_button_click, event_button_click_cost, event_button_click_ratio, event_order_paid_roi,
  362. event_new_user_jinjian_app, event_new_user_jinjian_app_cost, event_new_user_jinjian_app_roi,
  363. event_new_user_credit_grant_app, event_new_user_credit_grant_app_cost, event_new_user_credit_grant_app_roi,
  364. event_new_user_jinjian_page, event_new_user_jinjian_page_cost, event_new_user_jinjian_page_roi,
  365. event_new_user_credit_grant_page, event_new_user_credit_grant_page_cost, event_new_user_credit_grant_page_roi,
  366. event_appoint_form, event_appoint_form_cost, event_appoint_form_ratio, event_appoint_jump_click,
  367. event_appoint_jump_click_cost, event_appoint_jump_click_ratio, union_event_pay_purchase_amount_7d,
  368. union_event_pay_purchase_amount_7d_roi, placement_type, ad_scene, event_order_successed, ad_photo_played_10s_ratio,
  369. key_action_cost, event_add_shopping_cart_cost, event_ad_watch_times_ratio, event_outbound_call_cost,
  370. event_outbound_call, action_new_ratio, event_watch_app_ad, event_multi_conversion_cost, event_phone_card_activate,
  371. ad_photo_played_75percent_ratio, key_action_ratio, event_ad_watch_times_cost, event_add_shopping_cart, key_action,
  372. event_multi_conversion, event_wechat_connected, event_dsp_gift_form, event_intention_confirmed,
  373. event_phone_get_through, event_multi_conversion_ratio, event_measurement_house, ad_photo_played_2s_ratio,
  374. event_outbound_call_ratio, event_ad_watch_times)
  375. (
  376. select report.advertiser_id as account_id,
  377. stat_date,
  378. `hour`,
  379. project_id,
  380. product_id,
  381. sys.advertiser_id,
  382. company_id,
  383. account_name,
  384. project_name,
  385. advertiser_name,
  386. company_name,
  387. sale_id,
  388. user_id,
  389. org_code,
  390. charge,
  391. `show`,
  392. photo_click,
  393. aclick,
  394. bclick,
  395. photo_click_ratio,
  396. play_3s_ratio,
  397. action_ratio,
  398. impression_1k_cost,
  399. photo_click_cost,
  400. action_cost,
  401. share,
  402. comment,
  403. `like`,
  404. follow,
  405. cancel_follow,
  406. report,
  407. block,
  408. negative,
  409. submit,
  410. download_started,
  411. download_completed,
  412. activation,
  413. event_pay_first_day,
  414. event_pay_purchase_amount_first_day,
  415. event_pay_first_day_roi,
  416. event_pay,
  417. event_pay_purchase_amount,
  418. event_pay_roi,
  419. event_register,
  420. event_register_cost,
  421. event_register_ratio,
  422. event_jin_jian_app,
  423. event_jin_jian_app_cost,
  424. event_credit_grant_app,
  425. event_credit_grant_app_cost,
  426. event_credit_grant_app_ratio,
  427. event_order_paid,
  428. event_order_paid_purchase_amount,
  429. event_order_paid_cost,
  430. event_next_day_stay,
  431. event_next_day_stay_cost,
  432. event_next_day_stay_ratio,
  433. form_count,
  434. form_cost,
  435. form_action_ratio,
  436. event_jin_jian_landing_page,
  437. event_jin_jian_landing_page_cost,
  438. event_credit_grant_landing_page,
  439. event_credit_grant_landing_page_cost,
  440. event_credit_grant_landing_page_ratio,
  441. event_valid_clues,
  442. event_valid_clues_cost,
  443. event_add_wechat,
  444. event_add_wechat_cost,
  445. event_add_wechat_ratio,
  446. event_get_through,
  447. event_get_through_cost,
  448. event_get_through_ratio,
  449. event_app_invoked,
  450. event_app_invoked_cost,
  451. event_app_invoked_ratio,
  452. event_credit_grant_landing_ratio,
  453. play_5s_ratio,
  454. play_end_ratio,
  455. event_new_user_pay,
  456. event_new_user_pay_cost,
  457. event_new_user_pay_ratio,
  458. click_1k_cost,
  459. ad_product_cnt,
  460. event_goods_view,
  461. merchant_reco_fans,
  462. event_order_amount_roi,
  463. event_goods_view_cost,
  464. merchant_reco_fans_cost,
  465. event_button_click,
  466. event_button_click_cost,
  467. event_button_click_ratio,
  468. event_order_paid_roi,
  469. event_new_user_jinjian_app,
  470. event_new_user_jinjian_app_cost,
  471. event_new_user_jinjian_app_roi,
  472. event_new_user_credit_grant_app,
  473. event_new_user_credit_grant_app_cost,
  474. event_new_user_credit_grant_app_roi,
  475. event_new_user_jinjian_page,
  476. event_new_user_jinjian_page_cost,
  477. event_new_user_jinjian_page_roi,
  478. event_new_user_credit_grant_page,
  479. event_new_user_credit_grant_page_cost,
  480. event_new_user_credit_grant_page_roi,
  481. event_appoint_form,
  482. event_appoint_form_cost,
  483. event_appoint_form_ratio,
  484. event_appoint_jump_click,
  485. event_appoint_jump_click_cost,
  486. event_appoint_jump_click_ratio,
  487. union_event_pay_purchase_amount_7d,
  488. union_event_pay_purchase_amount_7d_roi,
  489. placement_type,
  490. ad_scene,
  491. event_order_successed,
  492. ad_photo_played_10s_ratio,
  493. key_action_cost,
  494. event_add_shopping_cart_cost,
  495. event_ad_watch_times_ratio,
  496. event_outbound_call_cost,
  497. event_outbound_call,
  498. action_new_ratio,
  499. event_watch_app_ad,
  500. event_multi_conversion_cost,
  501. event_phone_card_activate,
  502. ad_photo_played_75percent_ratio,
  503. key_action_ratio,
  504. event_ad_watch_times_cost,
  505. event_add_shopping_cart,
  506. key_action,
  507. event_multi_conversion,
  508. event_wechat_connected,
  509. event_dsp_gift_form,
  510. event_intention_confirmed,
  511. event_phone_get_through,
  512. event_multi_conversion_ratio,
  513. event_measurement_house,
  514. ad_photo_played_2s_ratio,
  515. event_outbound_call_ratio,
  516. event_ad_watch_times
  517. from media_api.kuaishou_account_report_hourly report
  518. left join
  519. hcst_system.ctop_account_attribution_info sys
  520. on report.advertiser_id = sys.account_id
  521. where report.stat_date between {start_date} and {end_date}
  522. and report.advertiser_id={account_id}
  523. )
  524. on DUPLICATE KEY UPDATE account_id =values(account_id),
  525. stat_date =values(stat_date),
  526. `hour` =values (`hour`),
  527. project_id =values(project_id),
  528. product_id =values(product_id),
  529. advertiser_id =values(advertiser_id),
  530. company_id =values(company_id),
  531. account_name =values(account_name),
  532. project_name =values(project_name),
  533. advertiser_name =values(advertiser_name),
  534. company_name =values(company_name),
  535. sale_id =values(sale_id),
  536. user_id =values(user_id),
  537. org_code =values(org_code),
  538. charge =values(charge),
  539. `show` =values(`show`),
  540. photo_click =values(photo_click),
  541. aclick =values(aclick),
  542. bclick =values(bclick),
  543. photo_click_ratio =values(photo_click_ratio),
  544. play_3s_ratio =values(play_3s_ratio),
  545. action_ratio =values(action_ratio),
  546. impression_1k_cost =values(impression_1k_cost),
  547. photo_click_cost =values(photo_click_cost),
  548. action_cost =values(action_cost),
  549. share =values(share),
  550. comment =values(comment),
  551. `like` =values(`like`),
  552. follow =values(follow),
  553. cancel_follow =values(cancel_follow),
  554. report =values(report),
  555. block =values(block),
  556. negative =values(negative),
  557. submit =values(submit),
  558. download_started =values(download_started),
  559. download_completed =values(download_completed),
  560. activation =values(activation),
  561. event_pay_first_day =values(event_pay_first_day),
  562. event_pay_purchase_amount_first_day =values(event_pay_purchase_amount_first_day),
  563. event_pay_first_day_roi =values(event_pay_first_day_roi),
  564. event_pay =values(event_pay),
  565. event_pay_purchase_amount =values(event_pay_purchase_amount),
  566. event_pay_roi =values(event_pay_roi),
  567. event_register =values(event_register),
  568. event_register_cost =values(event_register_cost),
  569. event_register_ratio =values(event_register_ratio),
  570. event_jin_jian_app =values(event_jin_jian_app),
  571. event_jin_jian_app_cost =values(event_jin_jian_app_cost),
  572. event_credit_grant_app =values(event_credit_grant_app),
  573. event_credit_grant_app_cost =values(event_credit_grant_app_cost),
  574. event_credit_grant_app_ratio =values(event_credit_grant_app_ratio),
  575. event_order_paid =values(event_order_paid),
  576. event_order_paid_purchase_amount =values(event_order_paid_purchase_amount),
  577. event_order_paid_cost =values(event_order_paid_cost),
  578. event_next_day_stay =values(event_next_day_stay),
  579. event_next_day_stay_cost =values(event_next_day_stay_cost),
  580. event_next_day_stay_ratio =values(event_next_day_stay_ratio),
  581. form_count =values(form_count),
  582. form_cost =values(form_cost),
  583. form_action_ratio =values(form_action_ratio),
  584. event_jin_jian_landing_page =values(event_jin_jian_landing_page),
  585. event_jin_jian_landing_page_cost =values(event_jin_jian_landing_page_cost),
  586. event_credit_grant_landing_page =values(event_credit_grant_landing_page),
  587. event_credit_grant_landing_page_cost =values(event_credit_grant_landing_page_cost),
  588. event_credit_grant_landing_page_ratio =values(event_credit_grant_landing_page_ratio),
  589. event_valid_clues =values(event_valid_clues),
  590. event_valid_clues_cost =values(event_valid_clues_cost),
  591. event_add_wechat =values(event_add_wechat),
  592. event_add_wechat_cost =values(event_add_wechat_cost),
  593. event_add_wechat_ratio =values(event_add_wechat_ratio),
  594. event_get_through =values(event_get_through),
  595. event_get_through_cost =values(event_get_through_cost),
  596. event_get_through_ratio =values(event_get_through_ratio),
  597. event_app_invoked =values(event_app_invoked),
  598. event_app_invoked_cost =values(event_app_invoked_cost),
  599. event_app_invoked_ratio =values(event_app_invoked_ratio),
  600. event_credit_grant_landing_ratio =values(event_credit_grant_landing_ratio),
  601. play_5s_ratio =values(play_5s_ratio),
  602. play_end_ratio =values(play_end_ratio),
  603. event_new_user_pay =values(event_new_user_pay),
  604. event_new_user_pay_cost =values(event_new_user_pay_cost),
  605. event_new_user_pay_ratio =values(event_new_user_pay_ratio),
  606. click_1k_cost =values(click_1k_cost),
  607. ad_product_cnt =values(ad_product_cnt),
  608. event_goods_view =values(event_goods_view),
  609. merchant_reco_fans =values(merchant_reco_fans),
  610. event_order_amount_roi =values(event_order_amount_roi),
  611. event_goods_view_cost =values(event_goods_view_cost),
  612. merchant_reco_fans_cost =values(merchant_reco_fans_cost),
  613. event_button_click =values(event_button_click),
  614. event_button_click_cost =values(event_button_click_cost),
  615. event_button_click_ratio =values(event_button_click_ratio),
  616. event_order_paid_roi =values(event_order_paid_roi),
  617. event_new_user_jinjian_app =values(event_new_user_jinjian_app),
  618. event_new_user_jinjian_app_cost =values(event_new_user_jinjian_app_cost),
  619. event_new_user_jinjian_app_roi =values(event_new_user_jinjian_app_roi),
  620. event_new_user_credit_grant_app =values(event_new_user_credit_grant_app),
  621. event_new_user_credit_grant_app_cost =values(event_new_user_credit_grant_app_cost),
  622. event_new_user_credit_grant_app_roi =values(event_new_user_credit_grant_app_roi),
  623. event_new_user_jinjian_page =values(event_new_user_jinjian_page),
  624. event_new_user_jinjian_page_cost =values(event_new_user_jinjian_page_cost),
  625. event_new_user_jinjian_page_roi =values(event_new_user_jinjian_page_roi),
  626. event_new_user_credit_grant_page =values(event_new_user_credit_grant_page),
  627. event_new_user_credit_grant_page_cost =values(event_new_user_credit_grant_page_cost),
  628. event_new_user_credit_grant_page_roi =values(event_new_user_credit_grant_page_roi),
  629. event_appoint_form =values(event_appoint_form),
  630. event_appoint_form_cost =values(event_appoint_form_cost),
  631. event_appoint_form_ratio =values(event_appoint_form_ratio),
  632. event_appoint_jump_click =values(event_appoint_jump_click),
  633. event_appoint_jump_click_cost =values(event_appoint_jump_click_cost),
  634. event_appoint_jump_click_ratio =values(event_appoint_jump_click_ratio),
  635. union_event_pay_purchase_amount_7d =values(union_event_pay_purchase_amount_7d),
  636. union_event_pay_purchase_amount_7d_roi =values(union_event_pay_purchase_amount_7d_roi),
  637. placement_type =values(placement_type),
  638. ad_scene =values(ad_scene),
  639. event_order_successed =values(event_order_successed),
  640. ad_photo_played_10s_ratio =values(ad_photo_played_10s_ratio),
  641. key_action_cost =values(key_action_cost),
  642. event_add_shopping_cart_cost =values(event_add_shopping_cart_cost),
  643. event_ad_watch_times_ratio =values(event_ad_watch_times_ratio),
  644. event_outbound_call_cost =values(event_outbound_call_cost),
  645. event_outbound_call =values(event_outbound_call),
  646. action_new_ratio =values(action_new_ratio),
  647. event_watch_app_ad =values(event_watch_app_ad),
  648. event_multi_conversion_cost =values(event_multi_conversion_cost),
  649. event_phone_card_activate =values(event_phone_card_activate),
  650. ad_photo_played_75percent_ratio =values(ad_photo_played_75percent_ratio),
  651. key_action_ratio =values(key_action_ratio),
  652. event_ad_watch_times_cost =values(event_ad_watch_times_cost),
  653. event_add_shopping_cart =values(event_add_shopping_cart),
  654. key_action =values(key_action),
  655. event_multi_conversion =values(event_multi_conversion),
  656. event_wechat_connected =values(event_wechat_connected),
  657. event_dsp_gift_form =values(event_dsp_gift_form),
  658. event_intention_confirmed =values(event_intention_confirmed),
  659. event_phone_get_through =values(event_phone_get_through),
  660. event_multi_conversion_ratio =values(event_multi_conversion_ratio),
  661. event_measurement_house =values(event_measurement_house),
  662. ad_photo_played_2s_ratio =values(ad_photo_played_2s_ratio),
  663. event_outbound_call_ratio =values(event_outbound_call_ratio),
  664. event_ad_watch_times =values(event_ad_watch_times);
  665. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  666. print(sql)
  667. TidbConn.exec_sql(self.conn, sql)
  668. self.conn.commit()
  669. except Exception as e:
  670. logging.error('失败详细信息:', repr(e))
  671. def handler(self, date_type, account_id, start_date, end_date):
  672. if date_type == "daily":
  673. self.daily_report(account_id, start_date, end_date)
  674. else:
  675. self.hourly_report(account_id, start_date, end_date)