adq_advertiser_report.py 9.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2021/9/15 5:10 下午
  4. # @Site :
  5. # @File : AdqAdvertiserReport.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. import logging
  9. from Apietl.conn.TidbConn import TidbConn
  10. class AdqAdvertiserReport:
  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
  17. into application.adq_advertiser_report_daily(account_id, stat_date, project_id, product_id,
  18. account_name, project_name, advertiser_name, company_name, sale_id,
  19. user_id, org_code,
  20. cost, view_count, valid_click_count, conversions_count,
  21. deep_conversions_count, activated_count,
  22. reg_pv, retention_count,
  23. video_outer_play_count, video_outer_play100_count,
  24. key_behavior_conversions_count, purchase_pv, purchase_amount,
  25. first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1,
  26. income_pv_pla, app_ad_paying_users, ad_monetization_amount,
  27. create_time,
  28. update_time)
  29. select report.advertiser_id as account_id,
  30. stat_time_day as stat_date,
  31. project_id,
  32. product_id,
  33. account_name,
  34. project_name,
  35. advertiser_name,
  36. company_name,
  37. sale_id,
  38. user_id,
  39. org_code,
  40. cost/100 as cost,
  41. view_count,
  42. valid_click_count,
  43. conversions_count,
  44. deep_conversions_count,
  45. activated_count,
  46. reg_pv,
  47. retention_count,
  48. video_outer_play_count,
  49. video_outer_play100_count,
  50. key_behavior_conversions_count,
  51. purchase_pv,
  52. purchase_amount/100 as purchase_amount,
  53. first_pay_count,
  54. income_pv_1d_pla,
  55. ad_paying_users_d1,
  56. income_val_1/100 as income_val_1,
  57. income_pv_pla,
  58. app_ad_paying_users,
  59. ad_monetization_amount/100 as ad_monetization_amount,
  60. create_time,
  61. update_time
  62. from media_api.adq_advertiser_report_daily report
  63. left join
  64. (SELECT cua.account_id id,
  65. cua.account_id account_id,
  66. cp.id project_id,
  67. product.id product_id,
  68. ca.id advertiser_id,
  69. uc.company_id company_id,
  70. cua.auth_name account_name,
  71. cp.project_name project_name,
  72. ca.`name` advertiser_name,
  73. uc.company_name company_name,
  74. cp.sale_id sale_id,
  75. su.id user_id,
  76. su.leader_id,
  77. su.org_code org_code
  78. FROM `jeecg-boot`.ctop_user_allocation cua
  79. LEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.id
  80. LEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_id
  81. LEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_id
  82. LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_id
  83. LEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_id
  84. WHERE cua.account_id IS NOT NULL) sys
  85. on report.advertiser_id = sys.account_id
  86. where report.stat_time_day >= {start_date} and report.stat_time_day<={end_date}
  87. and report.advertiser_id = {account_id}
  88. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  89. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  90. print(sql)
  91. self.conn.commit()
  92. TidbConn.conn_close(conn=self.conn)
  93. print(account_id, 'adq账户日报执行完成')
  94. except Exception as e:
  95. logging.error(account_id, '执行失败')
  96. def hourly_report(self, account_id, start_date, end_date):
  97. try:
  98. sql = """
  99. replace
  100. into application.adq_advertiser_report_hourly(account_id, stat_date,stat_hour, project_id, product_id,
  101. account_name, project_name, advertiser_name, company_name, sale_id,
  102. user_id, org_code,
  103. cost, view_count, valid_click_count, conversions_count,
  104. deep_conversions_count, activated_count,
  105. reg_pv, retention_count,
  106. video_outer_play_count, video_outer_play100_count,
  107. key_behavior_conversions_count, purchase_pv, purchase_amount,
  108. first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1,
  109. income_pv_pla, app_ad_paying_users, ad_monetization_amount,
  110. create_time,
  111. update_time)
  112. select report.advertiser_id as account_id,
  113. stat_time_day as stat_date,
  114. `hour` as stat_hour,
  115. project_id,
  116. product_id,
  117. account_name,
  118. project_name,
  119. advertiser_name,
  120. company_name,
  121. sale_id,
  122. user_id,
  123. org_code,
  124. cost/100 as cost,
  125. view_count,
  126. valid_click_count,
  127. conversions_count,
  128. deep_conversions_count,
  129. activated_count,
  130. reg_pv,
  131. retention_count,
  132. video_outer_play_count,
  133. video_outer_play100_count,
  134. key_behavior_conversions_count,
  135. purchase_pv,
  136. purchase_amount/100 as purchase_amount,
  137. first_pay_count,
  138. income_pv_1d_pla,
  139. ad_paying_users_d1,
  140. income_val_1/100 as income_val_1,
  141. income_pv_pla,
  142. app_ad_paying_users,
  143. ad_monetization_amount/100 as ad_monetization_amount,
  144. create_time,
  145. update_time
  146. from media_api.adq_advertiser_report_hour report
  147. left join
  148. (SELECT cua.account_id id,
  149. cua.account_id account_id,
  150. cp.id project_id,
  151. product.id product_id,
  152. ca.id advertiser_id,
  153. uc.company_id company_id,
  154. cua.auth_name account_name,
  155. cp.project_name project_name,
  156. ca.`name` advertiser_name,
  157. uc.company_name company_name,
  158. cp.sale_id sale_id,
  159. su.id user_id,
  160. su.leader_id,
  161. su.org_code org_code
  162. FROM `jeecg-boot`.ctop_user_allocation cua
  163. LEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.id
  164. LEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_id
  165. LEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_id
  166. LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_id
  167. LEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_id
  168. WHERE cua.account_id IS NOT NULL) sys
  169. on report.advertiser_id = sys.account_id
  170. where report.stat_time_day >= {start_date} and report.stat_time_day<={end_date}
  171. and report.advertiser_id = {account_id}
  172. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  173. print(sql)
  174. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  175. self.conn.commit()
  176. TidbConn.conn_close(conn=self.conn)
  177. print(account_id, 'adq账户时报执行完成')
  178. except Exception as e:
  179. logging.error(account_id, '执行失败')
  180. def handler(self, date_type, account_id, start_date, end_date):
  181. if date_type == "daily":
  182. self.daily_report(account_id, start_date, end_date)
  183. else:
  184. self.hourly_report(account_id, start_date, end_date)