NewMaterialVideoDailyReport.py 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : NewMaterialVideoDailyReport.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 NewMaterialVideoDailyReport:
  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 = """select advertiser_id from media_api.bytedance_material_video_daily where
  19. stat_datetime = {start_time}
  20. group by advertiser_id""".format(
  21. start_time=self.start_time)
  22. account_list = TidbConn.quary(self.conn, sql)
  23. for account_id in account_list:
  24. try:
  25. sql = """
  26. replace into application.bytedance_material_video_report_daily_dw(account_id, material_id, image_mode, inventory,
  27. stat_datetime, project_id, product_id, advertiser_id,
  28. company_id, account_name, project_name,
  29. advertiser_name, company_name, sale_id, user_id,
  30. clip_id, shot_id, plan_id, leader_id, org_code,
  31. signature, channel_type, clip_name, shot_name,
  32. plan_name, leader_name, video_name, channel_name,
  33. clip_company_id,
  34. cost,
  35. `show`,
  36. click,
  37. ctr,
  38. `convert`,
  39. convert_cost,
  40. convert_rate,
  41. deep_convert,
  42. deep_convert_cost,
  43. deep_convert_rate,
  44. download_start,
  45. download_start_cost,
  46. download_start_rate,
  47. download_finish,
  48. download_finish_cost,
  49. download_finish_rate,
  50. install_finish,
  51. install_finish_cost,
  52. install_finish_rate,
  53. active,
  54. active_cost,
  55. active_rate,
  56. register,
  57. active_register_rate,
  58. game_addiction,
  59. game_addiction_cost,
  60. game_addiction_rate,
  61. attribution_next_day_open_cnt,
  62. attribution_next_day_open_cost,
  63. attribution_next_day_open_rate,
  64. next_day_open,
  65. pay_count,
  66. active_pay_cost,
  67. active_pay_rate,
  68. game_pay_count,
  69. game_pay_cost,
  70. attribution_game_pay_7d_count,
  71. attribution_game_pay_7d_cost,
  72. in_app_uv,
  73. in_app_detail_uv,
  74. in_app_cart,
  75. in_app_pay,
  76. in_app_order, video_url, cover_url,shot_leader_id,shot_leader_name)
  77. select report.advertiser_id account_id, #'账户ID'
  78. report.material_id, #头条素材id
  79. '' as image_mode, #素材类型
  80. '' as inventory, #广告位
  81. stat_datetime, #数据起始时间
  82. cua.project_id, #项目id
  83. product.id product_id, #项目中产品id
  84. cua.advertiser_id, #广告主id
  85. uc.company_id, #运营人的公司id
  86. cua.auth_name account_name, #账户名称
  87. cp.project_name, #项目名称
  88. ca.name advertiser_name, #广告主名称
  89. uc.company_name, #运营人的公司名称
  90. cp.sale_id, #项目里的销售id
  91. cua.user_id, #运营id
  92. cma.clip_id, #剪辑id
  93. cma.shot_id, #拍摄id
  94. cma.plan_id, #编导id
  95. cma.leader_id, #负责人id
  96. leader.org_code, #机构编码
  97. video.signature, #素材id
  98. null chl_type, #渠道类型
  99. clip.realname clip_name, #剪辑名称
  100. shot.realname shot_name, #拍摄名称
  101. plan.realname plan_name, #编导名称
  102. leader.realname lead_name, #负责人名称
  103. case when cmi.material_name is not null then cmi.material_name else video.filename end as video_name, ##视频名称
  104. '' chl_name, #渠道名称
  105. up.company_id clip_company_id, #剪辑公司
  106. stat_cost as cost,
  107. show_cnt as `show`,
  108. click_cnt as click,
  109. ctr,
  110. convert_cnt as `convert`,
  111. conversion_cost as convert_cost,
  112. conversion_rate as convert_rate,
  113. deep_convert_cnt as deep_convert,
  114. deep_convert_cost,
  115. deep_convert_rate,
  116. click_start_cnt as download_start,
  117. click_start_cost as download_start_cost,
  118. click_start_rate as download_start_rate,
  119. download_finish_cnt as download_finish,
  120. download_finish_cost,
  121. download_finish_rate,
  122. install_finish_cnt as install_finish,
  123. install_finish_cost,
  124. install_finish_rate,
  125. active,
  126. active_cost,
  127. active_rate,
  128. active_register as register,
  129. active_register_rate,
  130. game_addiction,
  131. game_addiction_cost,
  132. game_addiction_rate,
  133. attribution_next_day_open_cnt,
  134. attribution_next_day_open_cost,
  135. attribution_next_day_open_rate,
  136. next_day_open,
  137. active_pay as pay_count,
  138. active_pay_cost,
  139. active_pay_rate,
  140. game_pay_count,
  141. game_pay_cost,
  142. attribution_game_pay_7d_count,
  143. attribution_game_pay_7d_cost,
  144. in_app_uv,
  145. in_app_detail_uv,
  146. in_app_cart,
  147. in_app_pay,
  148. in_app_order,
  149. ifnull(concat('http://i.snssdk.com/video/code/1/toutiao/',video.id),'-') as video_url ,
  150. cmi.cover_url,
  151. cma.shot_leader_id,
  152. cma.shot_leader_name
  153. from media_api.bytedance_material_video_daily report
  154. left join
  155. `media_api`.bytedance_file_video_get video
  156. on report.advertiser_id = video.advertiser_id and report.material_id = video.material_id
  157. left join (select code, material_name, cover_url from `jeecg-boot`.ctop_material_info where status = 1) cmi
  158. on video.signature = cmi.code
  159. left join `jeecg-boot`.ctop_material_ascription cma
  160. on video.signature = cma.material_id
  161. left join `jeecg-boot`.ctop_user_allocation cua
  162. on report.advertiser_id = cua.account_id
  163. left join `jeecg-boot`.ctop_advertiser ca
  164. on cua.advertiser_id = ca.id
  165. left join `jeecg-boot`.ctop_project cp
  166. on cua.project_id = cp.id
  167. left join `jeecg-boot`.user_company uc
  168. on cua.user_id = uc.user_id
  169. left join `jeecg-boot`.ctop_product product
  170. on cp.product_id = product.id
  171. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  172. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  173. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  174. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  175. left join `jeecg-boot`.user_company up on up.user_id = cma.clip_id
  176. where report.stat_datetime ={start_time}
  177. and report.advertiser_id = {account_id}
  178. """.format(start_time=self.start_time,
  179. account_id=account_id[0])
  180. print(sql)
  181. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  182. self.conn.commit()
  183. except Exception as e:
  184. logging.error(e)
  185. return -1
  186. TidbConn.conn_close(conn=self.conn)
  187. return 0
  188. except Exception as e:
  189. logging.error(e)
  190. return -1
  191. if __name__ == '__main__':
  192. status = NewMaterialVideoDailyReport().daily_report()
  193. if status == 0:
  194. print("作业执行成功")
  195. exit(0)
  196. else:
  197. print("作业执行失败")
  198. exit(-1)