NewMaterialImageDailyReport.py 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : NewMaterialimageDailyReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. import logging
  9. from Apietl.utlis.Utils import Utils
  10. from Apietl.conn.TidbConn import TidbConn
  11. class NewMaterialimageDailyReport:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. self.parm = Utils.get_live_args()
  15. self.start_time = self.parm
  16. def daily_report(self):
  17. try:
  18. sql = """
  19. replace into application.bytedance_material_image_report_daily_dw(account_id, material_id, image_mode, inventory,
  20. stat_datetime, project_id, product_id, advertiser_id,
  21. company_id, account_name, project_name,
  22. advertiser_name, company_name, sale_id, user_id,
  23. image_name,
  24. cost, `show`, avg_show_cost, click,
  25. avg_click_cost, ctr, `convert`, convert_cost,
  26. convert_rate, deep_convert, deep_convert_cost,
  27. deep_convert_rate, attribution_convert,
  28. attribution_convert_cost, attribution_deep_convert,
  29. attribution_deep_convert_cost, download_start,
  30. download_start_cost, download_start_rate,
  31. download_finish, download_finish_cost,
  32. download_finish_rate, click_install, install_finish,
  33. install_finish_cost, install_finish_rate, active,
  34. active_cost, active_rate, register,
  35. active_register_cost, active_register_rate,
  36. active_pay_amount, next_day_open, next_day_open_cost,
  37. next_day_open_rate, attribution_next_day_open_cnt,
  38. attribution_next_day_open_cost,
  39. attribution_next_day_open_rate, game_addiction,
  40. game_addiction_cost, game_addiction_rate, pay_count,
  41. active_pay_cost, active_pay_rate, loan_completion,
  42. loan_completion_cost, loan_completion_rate,
  43. pre_loan_credit, pre_loan_credit_cost, loan_credit,
  44. loan_credit_cost, loan_credit_rate, in_app_uv,
  45. in_app_detail_uv, in_app_cart, in_app_pay,
  46. in_app_order, attribution_game_pay_7d_count,
  47. attribution_game_pay_7d_cost,
  48. attribution_active_pay_7d_per_count, game_pay_cost,
  49. game_pay_count, phone, form, map_search, button, view,
  50. download, qq, lottery, vote, message, redirect,
  51. shopping, consult, wechat, phone_confirm,
  52. phone_connect, consult_effective, coupon,
  53. coupon_single_page, redirect_to_shop, poi_collect,
  54. poi_address_click, luban_order_cnt,
  55. luban_order_stat_amount, luban_order_roi,
  56. luban_live_enter_cnt, live_watch_one_minute_count,
  57. luban_live_follow_cnt, live_fans_club_join_cnt,
  58. luban_live_comment_cnt, luban_live_share_cnt,
  59. luban_live_gift_cnt, luban_live_gift_amount,
  60. luban_live_slidecart_click_cnt,
  61. luban_live_click_product_cnt,
  62. luban_live_pay_order_count,
  63. luban_live_pay_order_stat_cost, wechat_login_count,
  64. attribution_wechat_login_30d_count, wechat_login_cost,
  65. attribution_wechat_login_30d_cost,
  66. wechat_first_pay_count,
  67. attribution_wechat_first_pay_30d_count,
  68. wechat_first_pay_cost,
  69. attribution_wechat_first_pay_30d_cost,
  70. wechat_first_pay_rate,
  71. attribution_wechat_first_pay_30d_rate,
  72. wechat_pay_amount, attribution_wechat_pay_30d_amount,
  73. attribution_wechat_pay_30d_roi, phone_effective,
  74. total_play, valid_play, valid_play_cost,
  75. valid_play_rate, play_25_feed_break,
  76. play_50_feed_break, play_75_feed_break,
  77. play_100_feed_break, average_play_time_per_play,
  78. play_over_rate, wifi_play_rate, wifi_play,
  79. play_duration_sum, advanced_creative_phone_click,
  80. advanced_creative_counsel_click,
  81. advanced_creative_form_click,
  82. advanced_creative_coupon_addition,
  83. advanced_creative_form_submit, card_show, share,
  84. comment, `like`, follow, home_visited,
  85. ies_challenge_click, ies_music_click, location_click,
  86. message_action, click_landing_page, click_shopwindow,
  87. click_website, click_download, click_call_dy, cpc,
  88. cpm, cpa, interact_per_cost, convert_show_rate,
  89. play_duration_10s_rate, play_50_feed_break_rate,
  90. play_25_feed_break_rate, play_duration_5s_rate,
  91. play_duration_2s_rate, average_play_progress,
  92. play_100_feed_break_rate, play_duration_3s_rate,
  93. play_75_feed_break_rate, play_duration,
  94. play_duration_3s, play_duration_2s, play_duration_10s,
  95. average_video_play, avg_rank,
  96. submit_certification_count, approval_count,
  97. first_order_count, first_rental_order_count,
  98. commute_first_pay_count, image_url, signature)
  99. select report.advertiser_id account_id, #'账户ID'
  100. report.material_id, #头条素材id
  101. '' as image_mode, #素材类型
  102. '' as inventory, #广告位
  103. report.stat_datetime, #数据起始时间
  104. cua.project_id, #项目id
  105. product.id product_id, #项目中产品id
  106. cua.advertiser_id, #广告主id
  107. uc.company_id, #运营人的公司id
  108. cua.auth_name account_name, #账户名称
  109. cp.project_name, #项目名称
  110. ca.name advertiser_name, #广告主名称
  111. uc.company_name, #运营人的公司名称
  112. cp.sale_id, #项目里的销售id
  113. cua.user_id, #运营id
  114. image.filename,
  115. report.stat_cost,
  116. report.show_cnt,
  117. 0 as show_cost,
  118. report.`convert_cnt`,
  119. report.`cpc_platform`,
  120. report.`ctr`,
  121. report.`convert_cnt`,
  122. report.`conversion_cost`,
  123. report.`conversion_rate`,
  124. report.`deep_convert_cnt`,
  125. report.`deep_convert_cost`,
  126. report.`deep_convert_rate`,
  127. 0 as `attribution_convert`,
  128. 0 as `attribution_convert_cost`,
  129. 0 as `attribution_deep_convert`,
  130. 0 as `attribution_deep_convert_cost`,
  131. report.`click_start_cnt`,
  132. report.`click_start_cost`,
  133. report.`click_start_rate`,
  134. report.`download_finish_cnt`,
  135. report.`download_finish_cost`,
  136. report.`download_finish_rate`,
  137. 0 as `click_install`,
  138. report.`install_finish_cnt`,
  139. report.`install_finish_cost`,
  140. report.`install_finish_rate`,
  141. report.`active`,
  142. report.`active_cost`,
  143. report.`active_rate`,
  144. report.`active_register`,
  145. report.`active_register_cost`,
  146. report.`active_register_rate`,
  147. 0 as `active_pay_amount`,
  148. report.`next_day_open`,
  149. 0 as `next_day_open_cost`,
  150. 0 as `next_day_open_rate`,
  151. report.`attribution_next_day_open_cnt`,
  152. report.`attribution_next_day_open_cost`,
  153. report.`attribution_next_day_open_rate`,
  154. report.`game_addiction`,
  155. report.`game_addiction_cost`,
  156. report.`game_addiction_rate`,
  157. 0 as `cnt`,
  158. report.`active_pay_cost`,
  159. report.`active_pay_rate`,
  160. 0 as `loan_completion`,
  161. 0 as `loan_completion_cost`,
  162. 0 as `loan_completion_rate`,
  163. 0 as `pre_loan_credit`,
  164. 0 as `pre_loan_credit_cost`,
  165. 0 as `loan_credit`,
  166. 0 as `loan_credit_cost`,
  167. 0 as `loan_credit_rate`,
  168. report.`in_app_uv`,
  169. report.`in_app_detail_uv`,
  170. report.`in_app_cart`,
  171. report.`in_app_pay`,
  172. report.`in_app_order`,
  173. report.`attribution_game_pay_7d_count`,
  174. report.`attribution_game_pay_7d_cost`,
  175. 0 as `attribution_active_pay_7d_per_count`,
  176. report.`game_pay_cost`,
  177. report.`game_pay_count`,
  178. 0 as `phone`,
  179. 0 as `form`,
  180. 0 as `map_search`,
  181. 0 as `button`,
  182. 0 as `view`,
  183. 0 as `download`,
  184. 0 as `qq`,
  185. 0 as `lottery`,
  186. 0 as `vote`,
  187. 0 as `message`,
  188. 0 as `redirect`,
  189. 0 as `shopping`,
  190. 0 as `consult`,
  191. 0 as `wechat`,
  192. 0 as `phone_confirm`,
  193. 0 as `phone_connect`,
  194. 0 as `consult_effective`,
  195. 0 as `coupon`,
  196. 0 as `coupon_single_page`,
  197. 0 as `redirect_to_shop`,
  198. 0 as `poi_collect`,
  199. 0 as `poi_address_click`,
  200. 0 as `luban_order_cnt`,
  201. 0 as `luban_order_stat_amount`,
  202. 0 as `luban_order_roi`,
  203. 0 as `luban_live_enter_cnt`,
  204. 0 as `live_watch_one_minute_count`,
  205. 0 as `luban_live_follow_cnt`,
  206. 0 as `live_fans_club_join_cnt`,
  207. 0 as `luban_live_comment_cnt`,
  208. 0 as `luban_live_share_cnt`,
  209. 0 as `luban_live_gift_cnt`,
  210. 0 as `luban_live_gift_amount`,
  211. 0 as `luban_live_slidecart_click_cnt`,
  212. 0 as `luban_live_click_product_cnt`,
  213. 0 as `luban_live_pay_order_count`,
  214. 0 as `luban_live_pay_order_stat_cost`,
  215. 0 as `wechat_login_count`,
  216. 0 as `attribution_wechat_login_30d_count`,
  217. 0 as `wechat_login_cost`,
  218. 0 as `attribution_wechat_login_30d_cost`,
  219. 0 as `wechat_first_pay_count`,
  220. 0 as `attribution_wechat_first_pay_30d_count`,
  221. 0 as `wechat_first_pay_cost`,
  222. 0 as `attribution_wechat_first_pay_30d_cost`,
  223. 0 as `wechat_first_pay_rate`,
  224. 0 as `attribution_wechat_first_pay_30d_rate`,
  225. 0 as `wechat_pay_amount`,
  226. 0 as `attribution_wechat_pay_30d_amount`,
  227. 0 as `attribution_wechat_pay_30d_roi`,
  228. 0 as `phone_effective`,
  229. 0 as `total_play`,
  230. 0 as `valid_play`,
  231. 0 as `valid_play_cost`,
  232. 0 as `valid_play_rate`,
  233. 0 as `play_25_feed_break`,
  234. 0 as `play_50_feed_break`,
  235. 0 as `play_75_feed_break`,
  236. 0 as `play_100_feed_break`,
  237. 0 as `average_play_time_per_play`,
  238. 0 as `play_over_rate`,
  239. 0 as `wifi_play_rate`,
  240. 0 as `wifi_play`,
  241. 0 as `play_duration_sum`,
  242. 0 as `advanced_creative_phone_click`,
  243. 0 as `advanced_creative_counsel_click`,
  244. 0 as `advanced_creative_form_click`,
  245. 0 as `advanced_creative_coupon_addition`,
  246. 0 as `advanced_creative_form_submit`,
  247. 0 as `card_show`,
  248. 0 as `share`,
  249. 0 as `comment`,
  250. 0 as `like`,
  251. 0 as `follow`,
  252. 0 as `home_visited`,
  253. 0 as `ies_challenge_click`,
  254. 0 as `ies_music_click`,
  255. 0 as `location_click`,
  256. 0 as `message_action`,
  257. 0 as `click_landing_page`,
  258. 0 as `click_shopwindow`,
  259. 0 as `click_website`,
  260. 0 as `click_download`,
  261. 0 as `click_call_dy`,
  262. 0 as `cpc`,
  263. 0 as `cpm`,
  264. 0 as `cpa`,
  265. 0 as `interact_per_cost`,
  266. 0 as `convert_show_rate`,
  267. 0 as `play_duration_10s_rate`,
  268. 0 as `play_50_feed_break_rate`,
  269. 0 as `play_25_feed_break_rate`,
  270. 0 as `play_duration_5s_rate`,
  271. 0 as `play_duration_2s_rate`,
  272. 0 as `average_play_progress`,
  273. 0 as `play_100_feed_break_rate`,
  274. 0 as `play_duration_3s_rate`,
  275. 0 as `play_75_feed_break_rate`,
  276. 0 as `play_duration`,
  277. 0 as `play_duration_3s`,
  278. 0 as `play_duration_2s`,
  279. 0 as `play_duration_10s`,
  280. 0 as `average_video_play`,
  281. 0 as `avg_rank`,
  282. 0 as `submit_certification_count`,
  283. 0 as `approval_count`,
  284. 0 as `first_order_count`,
  285. 0 as `first_rental_order_count`,
  286. 0 as `commute_first_pay_count`,
  287. image.image_url,
  288. image.signature
  289. from media_api.bytedance_material_image_daily report
  290. left join
  291. `media_api`.bytedance_file_image_get image
  292. on report.material_id = image.material_id
  293. and report.advertiser_id = image.advertiser_id
  294. left join `jeecg-boot`.ctop_user_allocation cua
  295. on report.advertiser_id = cua.account_id
  296. left join `jeecg-boot`.ctop_advertiser ca
  297. on cua.advertiser_id = ca.id
  298. left join `jeecg-boot`.ctop_project cp
  299. on cua.project_id = cp.id
  300. left join `jeecg-boot`.user_company uc
  301. on cua.user_id = uc.user_id
  302. left join `jeecg-boot`.ctop_product product
  303. on cp.product_id = product.id
  304. left join `jeecg-boot`.sys_user su
  305. on cua.user_id = su.id
  306. where report.stat_datetime = {start_time}
  307. """.format(start_time=self.start_time)
  308. print(sql)
  309. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  310. self.conn.commit()
  311. TidbConn.conn_close(conn=self.conn)
  312. return 0
  313. except Exception as e:
  314. logging.error(e)
  315. return -1
  316. if __name__ == '__main__':
  317. status = NewMaterialimageDailyReport().daily_report()
  318. if status == 0:
  319. print("作业执行成功")
  320. exit(0)
  321. else:
  322. print("作业执行失败")
  323. exit(-1)