MaterialVideoDailyReport.py 26 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : MateirialVideoDailyReport.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 MateirialVideoDailyReport:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. self.parm = Utils.get_args()
  15. self.start_time = self.parm[0]
  16. self.end_time = self.parm[1]
  17. def daily_report(self):
  18. try:
  19. sql = """select advertiser_id from media_api.bytedance_material_report_daily where
  20. stat_datetime between {start_time} and {end_time}
  21. and image_mode in ('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
  22. group by advertiser_id""".format(
  23. start_time=self.start_time, end_time=self.end_time)
  24. account_list = TidbConn.quary(self.conn, sql)
  25. for account_id in account_list:
  26. try:
  27. sql = """
  28. replace into application.bytedance_material_video_report_daily_dw(account_id, material_id, image_mode, inventory,
  29. stat_datetime, project_id, product_id, advertiser_id,
  30. company_id, account_name, project_name,
  31. advertiser_name, company_name, sale_id, user_id,
  32. clip_id, shot_id, plan_id, leader_id, org_code,
  33. signature, channel_type, clip_name, shot_name,
  34. plan_name, leader_name, video_name, channel_name,
  35. clip_company_id, cost, `show`, avg_show_cost, click,
  36. avg_click_cost, ctr, `convert`, convert_cost,
  37. convert_rate, deep_convert, deep_convert_cost,
  38. deep_convert_rate, attribution_convert,
  39. attribution_convert_cost, attribution_deep_convert,
  40. attribution_deep_convert_cost, download_start,
  41. download_start_cost, download_start_rate,
  42. download_finish, download_finish_cost,
  43. download_finish_rate, click_install, install_finish,
  44. install_finish_cost, install_finish_rate, active,
  45. active_cost, active_rate, register,
  46. active_register_cost, active_register_rate,
  47. active_pay_amount, next_day_open, next_day_open_cost,
  48. next_day_open_rate, attribution_next_day_open_cnt,
  49. attribution_next_day_open_cost,
  50. attribution_next_day_open_rate, game_addiction,
  51. game_addiction_cost, game_addiction_rate, pay_count,
  52. active_pay_cost, active_pay_rate, loan_completion,
  53. loan_completion_cost, loan_completion_rate,
  54. pre_loan_credit, pre_loan_credit_cost, loan_credit,
  55. loan_credit_cost, loan_credit_rate, in_app_uv,
  56. in_app_detail_uv, in_app_cart, in_app_pay,
  57. in_app_order, attribution_game_pay_7d_count,
  58. attribution_game_pay_7d_cost,
  59. attribution_active_pay_7d_per_count, game_pay_cost,
  60. game_pay_count, phone, form, map_search, button, view,
  61. download, qq, lottery, vote, message, redirect,
  62. shopping, consult, wechat, phone_confirm,
  63. phone_connect, consult_effective, coupon,
  64. coupon_single_page, redirect_to_shop, poi_collect,
  65. poi_address_click, luban_order_cnt,
  66. luban_order_stat_amount, luban_order_roi,
  67. luban_live_enter_cnt, live_watch_one_minute_count,
  68. luban_live_follow_cnt, live_fans_club_join_cnt,
  69. luban_live_comment_cnt, luban_live_share_cnt,
  70. luban_live_gift_cnt, luban_live_gift_amount,
  71. luban_live_slidecart_click_cnt,
  72. luban_live_click_product_cnt,
  73. luban_live_pay_order_count,
  74. luban_live_pay_order_stat_cost, wechat_login_count,
  75. attribution_wechat_login_30d_count, wechat_login_cost,
  76. attribution_wechat_login_30d_cost,
  77. wechat_first_pay_count,
  78. attribution_wechat_first_pay_30d_count,
  79. wechat_first_pay_cost,
  80. attribution_wechat_first_pay_30d_cost,
  81. wechat_first_pay_rate,
  82. attribution_wechat_first_pay_30d_rate,
  83. wechat_pay_amount, attribution_wechat_pay_30d_amount,
  84. attribution_wechat_pay_30d_roi, phone_effective,
  85. total_play, valid_play, valid_play_cost,
  86. valid_play_rate, play_25_feed_break,
  87. play_50_feed_break, play_75_feed_break,
  88. play_100_feed_break, average_play_time_per_play,
  89. play_over_rate, wifi_play_rate, wifi_play,
  90. play_duration_sum, advanced_creative_phone_click,
  91. advanced_creative_counsel_click,
  92. advanced_creative_form_click,
  93. advanced_creative_coupon_addition,
  94. advanced_creative_form_submit, card_show, share,
  95. comment, `like`, follow, home_visited,
  96. ies_challenge_click, ies_music_click, location_click,
  97. message_action, click_landing_page, click_shopwindow,
  98. click_website, click_download, click_call_dy, cpc,
  99. cpm, cpa, interact_per_cost, convert_show_rate,
  100. play_duration_10s_rate, play_50_feed_break_rate,
  101. play_25_feed_break_rate, play_duration_5s_rate,
  102. play_duration_2s_rate, average_play_progress,
  103. play_100_feed_break_rate, play_duration_3s_rate,
  104. play_75_feed_break_rate, play_duration,
  105. play_duration_3s, play_duration_2s, play_duration_10s,
  106. average_video_play, avg_rank,
  107. submit_certification_count, approval_count,
  108. first_order_count, first_rental_order_count,
  109. commute_first_pay_count,video_url, cover_url,
  110. shot_leader_id, shot_leader_name)
  111. select
  112. report.advertiser_id account_id, #'账户ID'
  113. report.material_id, #头条素材id
  114. report.image_mode, #素材类型
  115. report.inventory, #广告位
  116. report.stat_datetime, #数据起始时间
  117. cua.project_id, #项目id
  118. product.id product_id, #项目中产品id
  119. cua.advertiser_id, #广告主id
  120. uc.company_id, #运营人的公司id
  121. cua.auth_name account_name, #账户名称
  122. cp.project_name, #项目名称
  123. ca.name advertiser_name, #广告主名称
  124. uc.company_name, #运营人的公司名称
  125. cp.sale_id, #项目里的销售id
  126. cua.user_id, #运营id
  127. cma.clip_id, #剪辑id
  128. cma.shot_id, #拍摄id
  129. cma.plan_id, #编导id
  130. cma.leader_id, #负责人id
  131. leader.org_code, #机构编码
  132. video.signature, #素材id
  133. null chl_type, #渠道类型
  134. clip.realname clip_name, #剪辑名称
  135. shot.realname shot_name, #拍摄名称
  136. plan.realname plan_name, #编导名称
  137. leader.realname lead_name, #负责人名称
  138. case when cmi.material_name is not null then cmi.material_name else video.filename end as video_name, ##视频名称
  139. '' chl_name, #渠道名称
  140. up.company_id clip_company_id, #剪辑公司
  141. report.`cost`,
  142. report.`show`,
  143. report.`avg_show_cost`,
  144. report.`click`,
  145. report.`avg_click_cost`,
  146. report.`ctr`,
  147. report.`convert`,
  148. report.`convert_cost`,
  149. report.`convert_rate`,
  150. report.`deep_convert`,
  151. report.`deep_convert_cost`,
  152. report.`deep_convert_rate`,
  153. report.`attribution_convert`,
  154. report.`attribution_convert_cost`,
  155. report.`attribution_deep_convert`,
  156. report.`attribution_deep_convert_cost`,
  157. report.`download_start`,
  158. report.`download_start_cost`,
  159. report.`download_start_rate`,
  160. report.`download_finish`,
  161. report.`download_finish_cost`,
  162. report.`download_finish_rate`,
  163. report.`click_install`,
  164. report.`install_finish`,
  165. report.`install_finish_cost`,
  166. report.`install_finish_rate`,
  167. report.`active`,
  168. report.`active_cost`,
  169. report.`active_rate`,
  170. report.`register`,
  171. report.`active_register_cost`,
  172. report.`active_register_rate`,
  173. report.`active_pay_amount`,
  174. report.`next_day_open`,
  175. report.`next_day_open_cost`,
  176. report.`next_day_open_rate`,
  177. report.`attribution_next_day_open_cnt`,
  178. report.`attribution_next_day_open_cost`,
  179. report.`attribution_next_day_open_rate`,
  180. report.`game_addiction`,
  181. report.`game_addiction_cost`,
  182. report.`game_addiction_rate`,
  183. report.`pay_count`,
  184. report.`active_pay_cost`,
  185. report.`active_pay_rate`,
  186. report.`loan_completion`,
  187. report.`loan_completion_cost`,
  188. report.`loan_completion_rate`,
  189. report.`pre_loan_credit`,
  190. report.`pre_loan_credit_cost`,
  191. report.`loan_credit`,
  192. report.`loan_credit_cost`,
  193. report.`loan_credit_rate`,
  194. report.`in_app_uv`,
  195. report.`in_app_detail_uv`,
  196. report.`in_app_cart`,
  197. report.`in_app_pay`,
  198. report.`in_app_order`,
  199. report.`attribution_game_pay_7d_count`,
  200. report.`attribution_game_pay_7d_cost`,
  201. report.`attribution_active_pay_7d_per_count`,
  202. report.`game_pay_cost`,
  203. report.`game_pay_count`,
  204. report.`phone`,
  205. report.`form`,
  206. report.`map_search`,
  207. report.`button`,
  208. report.`view`,
  209. report.`download`,
  210. report.`qq`,
  211. report.`lottery`,
  212. report.`vote`,
  213. report.`message`,
  214. report.`redirect`,
  215. report.`shopping`,
  216. report.`consult`,
  217. report.`wechat`,
  218. report.`phone_confirm`,
  219. report.`phone_connect`,
  220. report.`consult_effective`,
  221. report.`coupon`,
  222. report.`coupon_single_page`,
  223. report.`redirect_to_shop`,
  224. report.`poi_collect`,
  225. report.`poi_address_click`,
  226. report.`luban_order_cnt`,
  227. report.`luban_order_stat_amount`,
  228. report.`luban_order_roi`,
  229. report.`luban_live_enter_cnt`,
  230. report.`live_watch_one_minute_count`,
  231. report.`luban_live_follow_cnt`,
  232. report.`live_fans_club_join_cnt`,
  233. report.`luban_live_comment_cnt`,
  234. report.`luban_live_share_cnt`,
  235. report.`luban_live_gift_cnt`,
  236. report.`luban_live_gift_amount`,
  237. report.`luban_live_slidecart_click_cnt`,
  238. report.`luban_live_click_product_cnt`,
  239. report.`luban_live_pay_order_count`,
  240. report.`luban_live_pay_order_stat_cost`,
  241. report.`wechat_login_count`,
  242. report.`attribution_wechat_login_30d_count`,
  243. report.`wechat_login_cost`,
  244. report.`attribution_wechat_login_30d_cost`,
  245. report.`wechat_first_pay_count`,
  246. report.`attribution_wechat_first_pay_30d_count`,
  247. report.`wechat_first_pay_cost`,
  248. report.`attribution_wechat_first_pay_30d_cost`,
  249. report.`wechat_first_pay_rate`,
  250. report.`attribution_wechat_first_pay_30d_rate`,
  251. report.`wechat_pay_amount`,
  252. report.`attribution_wechat_pay_30d_amount`,
  253. report.`attribution_wechat_pay_30d_roi`,
  254. report.`phone_effective`,
  255. report.`total_play`,
  256. report.`valid_play`,
  257. report.`valid_play_cost`,
  258. report.`valid_play_rate`,
  259. report.`play_25_feed_break`,
  260. report.`play_50_feed_break`,
  261. report.`play_75_feed_break`,
  262. report.`play_100_feed_break`,
  263. report.`average_play_time_per_play`,
  264. report.`play_over_rate`,
  265. report.`wifi_play_rate`,
  266. report.`wifi_play`,
  267. report.`play_duration_sum`,
  268. report.`advanced_creative_phone_click`,
  269. report.`advanced_creative_counsel_click`,
  270. report.`advanced_creative_form_click`,
  271. report.`advanced_creative_coupon_addition`,
  272. report.`advanced_creative_form_submit`,
  273. report.`card_show`,
  274. report.`share`,
  275. report.`comment`,
  276. report.`like`,
  277. report.`follow`,
  278. report.`home_visited`,
  279. report.`ies_challenge_click`,
  280. report.`ies_music_click`,
  281. report.`location_click`,
  282. report.`message_action`,
  283. report.`click_landing_page`,
  284. report.`click_shopwindow`,
  285. report.`click_website`,
  286. report.`click_download`,
  287. report.`click_call_dy`,
  288. report.`cpc`,
  289. report.`cpm`,
  290. report.`cpa`,
  291. report.`interact_per_cost`,
  292. report.`convert_show_rate`,
  293. report.`play_duration_10s_rate`,
  294. report.`play_50_feed_break_rate`,
  295. report.`play_25_feed_break_rate`,
  296. report.`play_duration_5s_rate`,
  297. report.`play_duration_2s_rate`,
  298. report.`average_play_progress`,
  299. report.`play_100_feed_break_rate`,
  300. report.`play_duration_3s_rate`,
  301. report.`play_75_feed_break_rate`,
  302. report.`play_duration`,
  303. report.`play_duration_3s`,
  304. report.`play_duration_2s`,
  305. report.`play_duration_10s`,
  306. report.`average_video_play`,
  307. report.`avg_rank`,
  308. report.`submit_certification_count`,
  309. report.`approval_count`,
  310. report.`first_order_count`,
  311. report.`first_rental_order_count`,
  312. report.`commute_first_pay_count`,
  313. ifnull(concat('http://i.snssdk.com/video/code/1/toutiao/',video.id),'-') as video_url, cmi.cover_url,
  314. cma.shot_leader_id,
  315. cma.shot_leader_name
  316. from media_api.bytedance_material_report_daily report
  317. left join
  318. `media_api`.bytedance_file_video_get video
  319. on report.advertiser_id=video.advertiser_id and report.material_id=video.material_id
  320. left join (select code,material_name,cover_url from `jeecg-boot`.ctop_material_info where status=1) cmi
  321. on video.signature=cmi.code
  322. left join `jeecg-boot`.ctop_material_ascription cma
  323. on video.signature=cma.material_id
  324. left join `jeecg-boot`.ctop_user_allocation cua
  325. on report.advertiser_id=cua.account_id
  326. left join `jeecg-boot`.ctop_advertiser ca
  327. on cua.advertiser_id = ca.id
  328. left join `jeecg-boot`.ctop_project cp
  329. on cua.project_id = cp.id
  330. left join `jeecg-boot`.user_company uc
  331. on cua.user_id=uc.user_id
  332. left join `jeecg-boot`.ctop_product product
  333. on cp.product_id=product.id
  334. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  335. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  336. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  337. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  338. left join `jeecg-boot`.user_company up on up.user_id=cma.clip_id where
  339. report.image_mode in ('CREATIVE_IMAGE_MODE_VIDEO', 'CREATIVE_IMAGE_MODE_VIDEO_VERTICAL', 'CREATIVE_IMAGE_MODE_UNION_SPLASH_VIDEO')
  340. and report.stat_datetime between {start_time} and {end_time} and report.advertiser_id={account_id}
  341. """.format(start_time=self.start_time, end_time=self.end_time,
  342. account_id=account_id[0])
  343. print(sql)
  344. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  345. self.conn.commit()
  346. except Exception as e:
  347. logging.error(e)
  348. return -1
  349. TidbConn.conn_close(conn=self.conn)
  350. return 0
  351. except Exception as e:
  352. logging.error(e)
  353. return -1
  354. if __name__ == '__main__':
  355. status = MateirialVideoDailyReport().daily_report()
  356. if status == 0:
  357. print("作业执行成功")
  358. exit(0)
  359. else:
  360. print("作业执行失败")
  361. exit(-1)