adq_advertiser_report.py 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189
  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. self.conn.commit()
  91. TidbConn.conn_close(conn=self.conn)
  92. print(account_id, 'adq账户日报执行完成')
  93. except Exception as e:
  94. logging.error(account_id, '执行失败')
  95. def hourly_report(self, account_id, start_date, end_date):
  96. try:
  97. sql = """
  98. replace
  99. into application.adq_advertiser_report_hourly(account_id, stat_date,stat_hour, project_id, product_id,
  100. account_name, project_name, advertiser_name, company_name, sale_id,
  101. user_id, org_code,
  102. cost, view_count, valid_click_count, conversions_count,
  103. deep_conversions_count, activated_count,
  104. reg_pv, retention_count,
  105. video_outer_play_count, video_outer_play100_count,
  106. key_behavior_conversions_count, purchase_pv, purchase_amount,
  107. first_pay_count, income_pv_1d_pla, ad_paying_users_d1, income_val_1,
  108. income_pv_pla, app_ad_paying_users, ad_monetization_amount,
  109. create_time,
  110. update_time)
  111. select report.advertiser_id as account_id,
  112. stat_time_day as stat_date,
  113. `hour` as stat_hour,
  114. project_id,
  115. product_id,
  116. account_name,
  117. project_name,
  118. advertiser_name,
  119. company_name,
  120. sale_id,
  121. user_id,
  122. org_code,
  123. cost/100 as cost,
  124. view_count,
  125. valid_click_count,
  126. conversions_count,
  127. deep_conversions_count,
  128. activated_count,
  129. reg_pv,
  130. retention_count,
  131. video_outer_play_count,
  132. video_outer_play100_count,
  133. key_behavior_conversions_count,
  134. purchase_pv,
  135. purchase_amount/100 as purchase_amount,
  136. first_pay_count,
  137. income_pv_1d_pla,
  138. ad_paying_users_d1,
  139. income_val_1/100 as income_val_1,
  140. income_pv_pla,
  141. app_ad_paying_users,
  142. ad_monetization_amount/100 as ad_monetization_amount,
  143. create_time,
  144. update_time
  145. from media_api.adq_advertiser_report_hour report
  146. left join
  147. (SELECT cua.account_id id,
  148. cua.account_id account_id,
  149. cp.id project_id,
  150. product.id product_id,
  151. ca.id advertiser_id,
  152. uc.company_id company_id,
  153. cua.auth_name account_name,
  154. cp.project_name project_name,
  155. ca.`name` advertiser_name,
  156. uc.company_name company_name,
  157. cp.sale_id sale_id,
  158. su.id user_id,
  159. su.leader_id,
  160. su.org_code org_code
  161. FROM `jeecg-boot`.ctop_user_allocation cua
  162. LEFT JOIN `jeecg-boot`.ctop_project cp ON cua.project_id = cp.id
  163. LEFT JOIN `jeecg-boot`.ctop_advertiser ca ON ca.id = cua.advertiser_id
  164. LEFT JOIN `jeecg-boot`.sys_user su ON su.id = cua.user_id
  165. LEFT JOIN `jeecg-boot`.user_company uc ON uc.user_id = cua.user_id
  166. LEFT JOIN `jeecg-boot`.ctop_product product ON product.id = cp.product_id
  167. WHERE cua.account_id IS NOT NULL) sys
  168. on report.advertiser_id = sys.account_id
  169. where report.stat_time_day >= {start_date} and report.stat_time_day<={end_date}
  170. and report.advertiser_id = {account_id}
  171. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  172. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  173. self.conn.commit()
  174. TidbConn.conn_close(conn=self.conn)
  175. print(account_id, 'adq账户时报执行完成')
  176. except Exception as e:
  177. logging.error(account_id, '执行失败')
  178. def handler(self, date_type, account_id, start_date, end_date):
  179. if date_type == "daily":
  180. self.daily_report(account_id, start_date, end_date)
  181. else:
  182. self.hourly_report(account_id, start_date, end_date)