adq_material_report_daily.py 7.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157
  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.conn.TidbConn import TidbConn
  10. from Apietl.utlis.Utils import Utils
  11. class AdqMaterialReportDaily(object):
  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 = """
  20. select advertiser_id from media_api.adq_material_report_daily where
  21. stat_time_day >= {start_time} and stat_time_day<={end_time} group by advertiser_id
  22. """.format(start_time=self.start_time, end_time=self.end_time)
  23. account_list = TidbConn.quary(self.conn, sql)
  24. for account_id in account_list:
  25. sql = """
  26. replace into application.adq_material_report_daily_dw(account_id,
  27. project_id,
  28. product_id,
  29. advertiser_id,
  30. company_id,
  31. account_name,
  32. project_name,
  33. advertiser_name,
  34. company_name,
  35. sale_id,
  36. user_id,
  37. clip_id,
  38. shot_id,
  39. plan_id,
  40. leader_id,
  41. org_code,
  42. photo_url,
  43. material_id,
  44. stat_date,
  45. material_type,
  46. cost,
  47. view_count,
  48. valid_click_count,
  49. conversions_count,
  50. deep_conversions_count,
  51. activated_count,
  52. reg_pv,
  53. retention_count,
  54. video_outer_play_count,
  55. video_outer_play100_count,
  56. key_behavior_conversions_count,
  57. purchase_pv,
  58. purchase_amount,
  59. first_pay_count,
  60. ad_monetization_amount,
  61. app_ad_paying_users,
  62. income_pv_pla,
  63. income_val_1,
  64. ad_paying_users_d1,
  65. income_pv_1d_pla,
  66. income_val_3,
  67. signature,
  68. channel_type,
  69. clip_name,
  70. shot_name,
  71. plan_name,
  72. leader_name,
  73. video_name,
  74. channel_name,
  75. clip_company_id,
  76. play_3s_count,
  77. shot_leader_id,
  78. shot_leader_name)
  79. select report.advertiser_id as account_id,
  80. cp.id as project_id,
  81. product.id as product_id,
  82. ca.id as advertiser_id,
  83. uc.company_id,
  84. cua.auth_name as account_name,
  85. cp.project_name as project_name,
  86. ca.name as advertiser_name,
  87. uc.company_name,
  88. cp.sale_id,
  89. cua.user_id,
  90. clip_id,
  91. shot_id,
  92. plan_id,
  93. cma.leader_id,
  94. leader.org_code,
  95. video.preview_url as photo_url,
  96. report.material_id,
  97. report.stat_time_day as stat_date,
  98. report.material_type,
  99. report.cost / 100 as cost,
  100. report.view_count,
  101. report.valid_click_count,
  102. report.conversions_count,
  103. report.deep_conversions_count,
  104. report.activated_count,
  105. report.reg_pv,
  106. report.retention_count,
  107. report.video_outer_play_count,
  108. report.video_outer_play100_count,
  109. report.key_behavior_conversions_count,
  110. report.purchase_pv,
  111. report.purchase_amount / 100 as purchase_amount,
  112. report.first_pay_count,
  113. report.ad_monetization_amount,
  114. report.app_ad_paying_users,
  115. report.income_pv_pla,
  116. report.income_val_1 / 100 as income_val_1,
  117. report.ad_paying_users_d1,
  118. report.income_pv_1d_pla,
  119. report.income_val_3 / 100 as income_val_3,
  120. video.signature,
  121. null as channel_type,
  122. clip.realname,
  123. shot.realname,
  124. plan.realname,
  125. leader.realname,
  126. video.description as video_name,
  127. null channel_name,
  128. uc.company_id,
  129. 0 as play_3s_count,
  130. cma.shot_leader_id,
  131. cma.shot_leader_name
  132. from media_api.adq_material_report_daily report
  133. left join media_api.adq_video_info video
  134. on report.material_id = video.video_id
  135. left join (select code, material_name from `jeecg-boot`.ctop_material_info where status = 1) cmi
  136. on video.signature = cmi.code
  137. left join `jeecg-boot`.ctop_material_ascription cma on cma.material_id = video.signature
  138. left join `jeecg-boot`.ctop_user_allocation cua on cua.account_id = report.advertiser_id
  139. left join `jeecg-boot`.ctop_project cp on cua.project_id = cp.id
  140. left join `jeecg-boot`.ctop_advertiser ca on ca.id = cua.advertiser_id
  141. left join `jeecg-boot`.sys_user clip on clip.id = cma.clip_id
  142. left join `jeecg-boot`.sys_user shot on shot.id = cma.shot_id
  143. left join `jeecg-boot`.sys_user plan on plan.id = cma.plan_id
  144. left join `jeecg-boot`.sys_user leader on leader.id = cma.leader_id
  145. left join `jeecg-boot`.user_company uc on uc.user_id = clip.id
  146. left join `jeecg-boot`.ctop_product product on product.id = cp.product_id
  147. where report.stat_time_day >= {start_time}
  148. and stat_time_day <= {end_time}
  149. and report.advertiser_id = {account_id}
  150. """.format(start_time=self.start_time, end_time=self.end_time, account_id=account_id)
  151. TidbConn.execute(self.conn, sql)
  152. except Exception as e:
  153. logging.error(e)