AdvertiserReport.py 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : AdvertiserReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. import logging
  9. from Apietl.conn.TidbConn import TidbConn
  10. class AdvertiserReport:
  11. def __init__(self):
  12. self.conn = TidbConn.get_connection()
  13. def daily_report(self, account_id, start_date, end_date):
  14. try:
  15. sql = """
  16. replace into application.bytedance_advertiser_report_daily_dw(account_id, stat_datetime, project_id, product_id,
  17. advertiser_id, company_id, account_name, project_name,
  18. advertiser_name, company_name, sale_id, user_id, org_code,
  19. cost, `show`, avg_show_cost, click, avg_click_cost, ctr,
  20. `convert`, convert_cost, convert_rate, deep_convert,
  21. deep_convert_cost, deep_convert_rate, attribution_convert,
  22. attribution_convert_cost, attribution_deep_convert,
  23. attribution_deep_convert_cost, download_start,
  24. download_start_cost, download_start_rate, download_finish,
  25. download_finish_cost, download_finish_rate, click_install,
  26. install_finish, install_finish_cost, install_finish_rate,
  27. active, active_cost, active_rate, register,
  28. active_register_cost, active_register_rate, next_day_open,
  29. next_day_open_cost, next_day_open_rate,
  30. attribution_next_day_open_cnt,
  31. attribution_next_day_open_cost,
  32. attribution_next_day_open_rate, game_addiction,
  33. game_addiction_cost, game_addiction_rate, pay_count,
  34. active_pay_cost, active_pay_rate, loan_completion,
  35. loan_completion_cost, loan_completion_rate,
  36. pre_loan_credit, pre_loan_credit_cost, loan_credit,
  37. loan_credit_cost, loan_credit_rate, in_app_uv,
  38. in_app_detail_uv, in_app_cart, in_app_pay, in_app_order,
  39. attribution_game_pay_7d_count,
  40. attribution_game_pay_7d_cost,
  41. attribution_active_pay_7d_per_count, game_pay_cost,
  42. game_pay_count, phone, form, map_search, button, view,
  43. download, qq, lottery, vote, message, redirect, shopping,
  44. consult, wechat, phone_confirm, phone_connect,
  45. consult_effective, coupon, coupon_single_page,
  46. redirect_to_shop, poi_collect, poi_address_click,
  47. luban_order_cnt, luban_order_stat_amount, luban_order_roi,
  48. luban_live_enter_cnt, live_watch_one_minute_count,
  49. luban_live_follow_cnt, live_fans_club_join_cnt,
  50. luban_live_comment_cnt, luban_live_share_cnt,
  51. luban_live_gift_cnt, luban_live_gift_amount,
  52. luban_live_slidecart_click_cnt,
  53. luban_live_click_product_cnt, luban_live_pay_order_count,
  54. luban_live_pay_order_stat_cost,
  55. live_component_click_count, live_component_click_cost,
  56. live_component_click_rate, wechat_login_count,
  57. attribution_wechat_login_30d_count, wechat_login_cost,
  58. attribution_wechat_login_30d_cost, wechat_first_pay_count,
  59. attribution_wechat_first_pay_30d_count,
  60. wechat_first_pay_cost,
  61. attribution_wechat_first_pay_30d_cost,
  62. wechat_first_pay_rate,
  63. attribution_wechat_first_pay_30d_rate, wechat_pay_amount,
  64. attribution_wechat_pay_30d_amount,
  65. attribution_wechat_pay_30d_roi, phone_effective,
  66. total_play, valid_play, valid_play_cost, valid_play_rate,
  67. play_25_feed_break, play_50_feed_break,
  68. play_75_feed_break, play_100_feed_break,
  69. average_play_time_per_play, play_over_rate,
  70. wifi_play_rate, wifi_play, play_duration_sum,
  71. advanced_creative_phone_click,
  72. advanced_creative_counsel_click,
  73. advanced_creative_form_click,
  74. advanced_creative_coupon_addition,
  75. advanced_creative_form_submit, card_show, share, comment,
  76. `like`, follow, home_visited, ies_challenge_click,
  77. ies_music_click, location_click, message_action,
  78. click_landing_page, click_shopwindow, click_website,
  79. click_download, click_call_dy, submit_certification_count,
  80. approval_count, first_rental_order_count,
  81. first_order_count, commute_first_pay_count,
  82. attribution_active_pay_intra_one_day_roi
  83. ,attribution_active_pay_intra_one_day_amount
  84. ,attribution_active_pay_intra_one_day_rate
  85. ,attribution_active_pay_intra_one_day_cost
  86. ,attribution_active_pay_intra_one_day_count,
  87. stat_pay_amount)
  88. select a.advertiser_id account_id,
  89. a.stat_datetime,
  90. b.project_id,
  91. b.product_id,
  92. b.advertiser_id,
  93. b.company_id,
  94. b.account_name,
  95. b.project_name,
  96. b.advertiser_name,
  97. b.company_name,
  98. b.sale_id,
  99. b.user_id,
  100. b.org_code,
  101. a.cost,
  102. a.show,
  103. a.avg_show_cost,
  104. a.click,
  105. a.avg_click_cost,
  106. a.ctr,
  107. a.convert,
  108. a.convert_cost,
  109. a.convert_rate,
  110. a.deep_convert,
  111. a.deep_convert_cost,
  112. a.deep_convert_rate,
  113. a.attribution_convert,
  114. a.attribution_convert_cost,
  115. a.attribution_deep_convert,
  116. a.attribution_deep_convert_cost,
  117. a.download_start,
  118. a.download_start_cost,
  119. a.download_start_rate,
  120. a.download_finish,
  121. a.download_finish_cost,
  122. a.download_finish_rate,
  123. a.click_install,
  124. a.install_finish,
  125. a.install_finish_cost,
  126. a.install_finish_rate,
  127. a.active,
  128. a.active_cost,
  129. a.active_rate,
  130. a.register,
  131. a.active_register_cost,
  132. a.active_register_rate,
  133. a.next_day_open,
  134. a.next_day_open_cost,
  135. a.next_day_open_rate,
  136. a.attribution_next_day_open_cnt,
  137. a.attribution_next_day_open_cost,
  138. a.attribution_next_day_open_rate,
  139. a.game_addiction,
  140. a.game_addiction_cost,
  141. a.game_addiction_rate,
  142. a.pay_count,
  143. a.active_pay_cost,
  144. a.active_pay_rate,
  145. a.loan_completion,
  146. a.loan_completion_cost,
  147. a.loan_completion_rate,
  148. a.pre_loan_credit,
  149. a.pre_loan_credit_cost,
  150. a.loan_credit,
  151. a.loan_credit_cost,
  152. a.loan_credit_rate,
  153. a.in_app_uv,
  154. a.in_app_detail_uv,
  155. a.in_app_cart,
  156. a.in_app_pay,
  157. a.in_app_order,
  158. a.attribution_game_pay_7d_count,
  159. a.attribution_game_pay_7d_cost,
  160. a.attribution_active_pay_7d_per_count,
  161. a.game_pay_cost,
  162. a.game_pay_count,
  163. a.phone,
  164. a.form,
  165. a.map_search,
  166. a.button,
  167. a.view,
  168. a.download,
  169. a.qq,
  170. a.lottery,
  171. a.vote,
  172. a.message,
  173. a.redirect,
  174. a.shopping,
  175. a.consult,
  176. a.wechat,
  177. a.phone_confirm,
  178. a.phone_connect,
  179. a.consult_effective,
  180. a.coupon,
  181. a.coupon_single_page,
  182. a.redirect_to_shop,
  183. a.poi_collect,
  184. a.poi_address_click,
  185. a.luban_order_cnt,
  186. a.luban_order_stat_amount,
  187. a.luban_order_roi,
  188. a.luban_live_enter_cnt,
  189. a.live_watch_one_minute_count,
  190. a.luban_live_follow_cnt,
  191. a.live_fans_club_join_cnt,
  192. a.luban_live_comment_cnt,
  193. a.luban_live_share_cnt,
  194. a.luban_live_gift_cnt,
  195. a.luban_live_gift_amount,
  196. a.luban_live_slidecart_click_cnt,
  197. a.luban_live_click_product_cnt,
  198. a.luban_live_pay_order_count,
  199. a.luban_live_pay_order_stat_cost,
  200. a.live_component_click_count,
  201. a.live_component_click_cost,
  202. a.live_component_click_rate,
  203. a.wechat_login_count,
  204. a.attribution_wechat_login_30d_count,
  205. a.wechat_login_cost,
  206. a.attribution_wechat_login_30d_cost,
  207. a.wechat_first_pay_count,
  208. a.attribution_wechat_first_pay_30d_count,
  209. a.wechat_first_pay_cost,
  210. a.attribution_wechat_first_pay_30d_cost,
  211. a.wechat_first_pay_rate,
  212. a.attribution_wechat_first_pay_30d_rate,
  213. a.wechat_pay_amount,
  214. a.attribution_wechat_pay_30d_amount,
  215. a.attribution_wechat_pay_30d_roi,
  216. a.phone_effective,
  217. a.total_play,
  218. a.valid_play,
  219. a.valid_play_cost,
  220. a.valid_play_rate,
  221. a.play_25_feed_break,
  222. a.play_50_feed_break,
  223. a.play_75_feed_break,
  224. a.play_100_feed_break,
  225. a.average_play_time_per_play,
  226. a.play_over_rate,
  227. a.wifi_play_rate,
  228. a.wifi_play,
  229. a.play_duration_sum,
  230. a.advanced_creative_phone_click,
  231. a.advanced_creative_counsel_click,
  232. a.advanced_creative_form_click,
  233. a.advanced_creative_coupon_addition,
  234. a.advanced_creative_form_submit,
  235. a.card_show,
  236. a.share,
  237. a.comment,
  238. a.like,
  239. a.follow,
  240. a.home_visited,
  241. a.ies_challenge_click,
  242. a.ies_music_click,
  243. a.location_click,
  244. a.message_action,
  245. a.click_landing_page,
  246. a.click_shopwindow,
  247. a.click_website,
  248. a.click_download,
  249. a.click_call_dy,
  250. a.submit_certification_count,
  251. a.approval_count,
  252. a.first_rental_order_count,
  253. a.first_order_count,
  254. a.commute_first_pay_count,
  255. a.attribution_active_pay_intra_one_day_roi,
  256. a.attribution_active_pay_intra_one_day_amount,
  257. a.attribution_active_pay_intra_one_day_rate,
  258. a.attribution_active_pay_intra_one_day_cost,
  259. attribution_active_pay_intra_one_day_count,
  260. a.stat_pay_amount
  261. from `media_api`.bytedance_advertiser_report_daily a
  262. left join
  263. (select cua.account_id id,
  264. cua.account_id account_id,
  265. cp.id project_id,
  266. product.id product_id,
  267. ca.id advertiser_id,
  268. uc.company_id company_id,
  269. cua.auth_name account_name,
  270. cp.project_name project_name,
  271. ca.name advertiser_name,
  272. uc.company_name company_name,
  273. cp.sale_id sale_id,
  274. su.id user_id,
  275. su.org_code org_code
  276. from `jeecg-boot`.ctop_user_allocation cua
  277. left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
  278. left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
  279. left join `jeecg-boot`.sys_user su on su.id = cua.user_id
  280. left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id
  281. left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
  282. where cua.account_id is not null) b
  283. on a.advertiser_id = b.account_id
  284. where a.stat_datetime between {start_date} and {end_date}
  285. and a.advertiser_id = {account_id}
  286. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  287. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  288. self.conn.commit()
  289. TidbConn.conn_close(conn=self.conn)
  290. print(account_id, '头条账户日报执行完成')
  291. except Exception as e:
  292. logging.error(account_id, '执行失败')
  293. def hourly_report(self, account_id, start_date, end_date):
  294. try:
  295. sql = """
  296. replace into application.bytedance_advertiser_report_hourly_dw(account_id, stat_datetime, hour, project_id, product_id,
  297. advertiser_id, company_id, account_name, project_name,
  298. advertiser_name, company_name, sale_id, user_id,
  299. org_code, cost, `show`, avg_show_cost, click,
  300. avg_click_cost, ctr, `convert`, convert_cost,
  301. convert_rate, deep_convert, deep_convert_cost,
  302. deep_convert_rate, attribution_convert,
  303. attribution_convert_cost, attribution_deep_convert,
  304. attribution_deep_convert_cost, download_start,
  305. download_start_cost, download_start_rate,
  306. download_finish, download_finish_cost,
  307. download_finish_rate, click_install, install_finish,
  308. install_finish_cost, install_finish_rate, active,
  309. active_cost, active_rate, register, active_register_cost,
  310. active_register_rate, next_day_open, next_day_open_cost,
  311. next_day_open_rate, attribution_next_day_open_cnt,
  312. attribution_next_day_open_cost,
  313. attribution_next_day_open_rate, game_addiction,
  314. game_addiction_cost, game_addiction_rate, pay_count,
  315. active_pay_cost, active_pay_rate, loan_completion,
  316. loan_completion_cost, loan_completion_rate,
  317. pre_loan_credit, pre_loan_credit_cost, loan_credit,
  318. loan_credit_cost, loan_credit_rate, in_app_uv,
  319. in_app_detail_uv, in_app_cart, in_app_pay, in_app_order,
  320. attribution_game_pay_7d_count,
  321. attribution_game_pay_7d_cost,
  322. attribution_active_pay_7d_per_count, game_pay_cost,
  323. game_pay_count, phone, form, map_search, button, view,
  324. download, qq, lottery, vote, message, redirect, shopping,
  325. consult, wechat, phone_confirm, phone_connect,
  326. consult_effective, coupon, coupon_single_page,
  327. redirect_to_shop, poi_collect, poi_address_click,
  328. luban_order_cnt, luban_order_stat_amount,
  329. luban_order_roi, luban_live_enter_cnt,
  330. live_watch_one_minute_count, luban_live_follow_cnt,
  331. live_fans_club_join_cnt, luban_live_comment_cnt,
  332. luban_live_share_cnt, luban_live_gift_cnt,
  333. luban_live_gift_amount, luban_live_slidecart_click_cnt,
  334. luban_live_click_product_cnt, luban_live_pay_order_count,
  335. luban_live_pay_order_stat_cost,
  336. live_component_click_count, live_component_click_cost,
  337. live_component_click_rate, wechat_login_count,
  338. attribution_wechat_login_30d_count, wechat_login_cost,
  339. attribution_wechat_login_30d_cost,
  340. wechat_first_pay_count,
  341. attribution_wechat_first_pay_30d_count,
  342. wechat_first_pay_cost,
  343. attribution_wechat_first_pay_30d_cost,
  344. wechat_first_pay_rate,
  345. attribution_wechat_first_pay_30d_rate, wechat_pay_amount,
  346. attribution_wechat_pay_30d_amount,
  347. attribution_wechat_pay_30d_roi, phone_effective,
  348. total_play, valid_play, valid_play_cost, valid_play_rate,
  349. play_25_feed_break, play_50_feed_break,
  350. play_75_feed_break, play_100_feed_break,
  351. average_play_time_per_play, play_over_rate,
  352. wifi_play_rate, wifi_play, play_duration_sum,
  353. advanced_creative_phone_click,
  354. advanced_creative_counsel_click,
  355. advanced_creative_form_click,
  356. advanced_creative_coupon_addition,
  357. advanced_creative_form_submit, card_show, share, comment,
  358. `like`, follow, home_visited, ies_challenge_click,
  359. ies_music_click, location_click, message_action,
  360. click_landing_page, click_shopwindow, click_website,
  361. click_download, click_call_dy,
  362. submit_certification_count, approval_count,
  363. first_rental_order_count, first_order_count,
  364. commute_first_pay_count,
  365. attribution_active_pay_intra_one_day_count,
  366. attribution_active_pay_intra_one_day_cost,
  367. attribution_active_pay_intra_one_day_rate,
  368. attribution_active_pay_intra_one_day_amount,
  369. attribution_active_pay_intra_one_day_roi,stat_pay_amount)
  370. select a.advertiser_id account_id,
  371. a.stat_datetime,
  372. a.`hour`,
  373. b.project_id,
  374. b.product_id,
  375. b.advertiser_id,
  376. b.company_id,
  377. b.account_name,
  378. b.project_name,
  379. b.advertiser_name,
  380. b.company_name,
  381. b.sale_id,
  382. b.user_id,
  383. b.org_code,
  384. a.cost,
  385. a.show,
  386. a.avg_show_cost,
  387. a.click,
  388. a.avg_click_cost,
  389. a.ctr,
  390. a.convert,
  391. a.convert_cost,
  392. a.convert_rate,
  393. a.deep_convert,
  394. a.deep_convert_cost,
  395. a.deep_convert_rate,
  396. a.attribution_convert,
  397. a.attribution_convert_cost,
  398. a.attribution_deep_convert,
  399. a.attribution_deep_convert_cost,
  400. a.download_start,
  401. a.download_start_cost,
  402. a.download_start_rate,
  403. a.download_finish,
  404. a.download_finish_cost,
  405. a.download_finish_rate,
  406. a.click_install,
  407. a.install_finish,
  408. a.install_finish_cost,
  409. a.install_finish_rate,
  410. a.active,
  411. a.active_cost,
  412. a.active_rate,
  413. a.register,
  414. a.active_register_cost,
  415. a.active_register_rate,
  416. a.next_day_open,
  417. a.next_day_open_cost,
  418. a.next_day_open_rate,
  419. a.attribution_next_day_open_cnt,
  420. a.attribution_next_day_open_cost,
  421. a.attribution_next_day_open_rate,
  422. a.game_addiction,
  423. a.game_addiction_cost,
  424. a.game_addiction_rate,
  425. a.pay_count,
  426. a.active_pay_cost,
  427. a.active_pay_rate,
  428. a.loan_completion,
  429. a.loan_completion_cost,
  430. a.loan_completion_rate,
  431. a.pre_loan_credit,
  432. a.pre_loan_credit_cost,
  433. a.loan_credit,
  434. a.loan_credit_cost,
  435. a.loan_credit_rate,
  436. a.in_app_uv,
  437. a.in_app_detail_uv,
  438. a.in_app_cart,
  439. a.in_app_pay,
  440. a.in_app_order,
  441. a.attribution_game_pay_7d_count,
  442. a.attribution_game_pay_7d_cost,
  443. a.attribution_active_pay_7d_per_count,
  444. a.game_pay_cost,
  445. a.game_pay_count,
  446. a.phone,
  447. a.form,
  448. a.map_search,
  449. a.button,
  450. a.view,
  451. a.download,
  452. a.qq,
  453. a.lottery,
  454. a.vote,
  455. a.message,
  456. a.redirect,
  457. a.shopping,
  458. a.consult,
  459. a.wechat,
  460. a.phone_confirm,
  461. a.phone_connect,
  462. a.consult_effective,
  463. a.coupon,
  464. a.coupon_single_page,
  465. a.redirect_to_shop,
  466. a.poi_collect,
  467. a.poi_address_click,
  468. a.luban_order_cnt,
  469. a.luban_order_stat_amount,
  470. a.luban_order_roi,
  471. a.luban_live_enter_cnt,
  472. a.live_watch_one_minute_count,
  473. a.luban_live_follow_cnt,
  474. a.live_fans_club_join_cnt,
  475. a.luban_live_comment_cnt,
  476. a.luban_live_share_cnt,
  477. a.luban_live_gift_cnt,
  478. a.luban_live_gift_amount,
  479. a.luban_live_slidecart_click_cnt,
  480. a.luban_live_click_product_cnt,
  481. a.luban_live_pay_order_count,
  482. a.luban_live_pay_order_stat_cost,
  483. a.live_component_click_count,
  484. a.live_component_click_cost,
  485. a.live_component_click_rate,
  486. a.wechat_login_count,
  487. a.attribution_wechat_login_30d_count,
  488. a.wechat_login_cost,
  489. a.attribution_wechat_login_30d_cost,
  490. a.wechat_first_pay_count,
  491. a.attribution_wechat_first_pay_30d_count,
  492. a.wechat_first_pay_cost,
  493. a.attribution_wechat_first_pay_30d_cost,
  494. a.wechat_first_pay_rate,
  495. a.attribution_wechat_first_pay_30d_rate,
  496. a.wechat_pay_amount,
  497. a.attribution_wechat_pay_30d_amount,
  498. a.attribution_wechat_pay_30d_roi,
  499. a.phone_effective,
  500. a.total_play,
  501. a.valid_play,
  502. a.valid_play_cost,
  503. a.valid_play_rate,
  504. a.play_25_feed_break,
  505. a.play_50_feed_break,
  506. a.play_75_feed_break,
  507. a.play_100_feed_break,
  508. a.average_play_time_per_play,
  509. a.play_over_rate,
  510. a.wifi_play_rate,
  511. a.wifi_play,
  512. a.play_duration_sum,
  513. a.advanced_creative_phone_click,
  514. a.advanced_creative_counsel_click,
  515. a.advanced_creative_form_click,
  516. a.advanced_creative_coupon_addition,
  517. a.advanced_creative_form_submit,
  518. a.card_show,
  519. a.share,
  520. a.comment,
  521. a.like,
  522. a.follow,
  523. a.home_visited,
  524. a.ies_challenge_click,
  525. a.ies_music_click,
  526. a.location_click,
  527. a.message_action,
  528. a.click_landing_page,
  529. a.click_shopwindow,
  530. a.click_website,
  531. a.click_download,
  532. a.click_call_dy,
  533. a.submit_certification_count,
  534. a.approval_count,
  535. a.first_rental_order_count,
  536. a.first_order_count,
  537. a.commute_first_pay_count,
  538. a.attribution_active_pay_intra_one_day_count,
  539. a.attribution_active_pay_intra_one_day_cost,
  540. a.attribution_active_pay_intra_one_day_rate,
  541. a.attribution_active_pay_intra_one_day_amount,
  542. a.attribution_active_pay_intra_one_day_roi,
  543. a.stat_pay_amount
  544. from `media_api`.bytedance_advertiser_report_hourly a
  545. left join
  546. (select cua.account_id id,
  547. cua.account_id account_id,
  548. cp.id project_id,
  549. product.id product_id,
  550. ca.id advertiser_id,
  551. uc.company_id company_id,
  552. cua.auth_name account_name,
  553. cp.project_name project_name,
  554. ca.name advertiser_name,
  555. uc.company_name company_name,
  556. cp.sale_id sale_id,
  557. su.id user_id,
  558. su.org_code org_code
  559. from `jeecg-boot`.ctop_user_allocation cua
  560. left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
  561. left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
  562. left join `jeecg-boot`.sys_user su on su.id = cua.user_id
  563. left join `jeecg-boot`.user_company uc on uc.user_id = cua.user_id
  564. left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
  565. where cua.account_id is not null) b
  566. on a.advertiser_id = b.account_id
  567. where a.stat_datetime between {start_date} and {end_date}
  568. and a.advertiser_id = {account_id}
  569. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  570. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  571. self.conn.commit()
  572. TidbConn.conn_close(conn=self.conn)
  573. print(account_id, '头条账户时报执行完成')
  574. except Exception as e:
  575. logging.error(account_id, '执行失败')
  576. def handler(self, date_type, account_id, start_date, end_date):
  577. if date_type == "daily":
  578. self.daily_report(account_id, start_date, end_date)
  579. else:
  580. self.hourly_report(account_id, start_date, end_date)