KuaishouUnitAudienceProvinceReportDaily.py 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/2/17 4:13 下午
  4. # @Site :
  5. # @File : KuaishouUnitAudienceProvinceReportDaily.py
  6. # @Software: PyCharm
  7. # @contact: renyupeng@c-top.com.cn
  8. # @Tel 1501435553
  9. import logging
  10. from math import ceil
  11. from Apietl.conn.TidbConn import TidbConn
  12. from Apietl.utlis.Utils import Utils
  13. class KuaishouUnitAudienceProvinceReportDaily:
  14. def __init__(self):
  15. self.conn = TidbConn.get_connection()
  16. self.parm = Utils.get_args()
  17. self.start_time = self.parm[0]
  18. def provinceReport(self):
  19. try:
  20. sql = """select count(1) from media_api.kuaishou_unit_audience_province_report_daily where
  21. stat_date = {start_time}""".format(
  22. start_time=self.start_time)
  23. totalNum = TidbConn.quary(self.conn, sql)[0][0]
  24. pageCount = ceil(totalNum / 40000)
  25. for i in range(pageCount):
  26. sql = """
  27. replace into application.kuaishou_unit_audience_province_report_daily_d
  28. select province.account_id,
  29. campaign_id,
  30. province.unit_id,
  31. event_register_cost,
  32. province,
  33. photo_click,
  34. key_action_cost,
  35. event_next_day_stay,
  36. photo_click_ratio,
  37. photo_click_cost,
  38. charge,
  39. event_pay,
  40. action_cost,
  41. conversion_cost,
  42. bclick,
  43. key_action_ratio,
  44. play_3s_ratio,
  45. play_5s_ratio,
  46. activation,
  47. event_app_invoked_ratio,
  48. `show`,
  49. event_new_user_pay_cost,
  50. aclick,
  51. deep_conversion_num,
  52. action_ratio,
  53. conversion_ratio,
  54. event_pay_purchase_amount_first_day,
  55. event_pay_first_day,
  56. conversion_num_cost,
  57. event_new_user_pay_ratio,
  58. event_app_invoked,
  59. event_next_day_stay_ratio,
  60. event_register,
  61. event_new_user_pay,
  62. event_next_day_stay_cost,
  63. deep_conversion_ratio,
  64. deep_conversion_cost,
  65. `like`,
  66. event_app_invoked_cost,
  67. key_action,
  68. ad_photo_played_10s,
  69. form_cost,
  70. event_pay_purchase_amount,
  71. click_1k_cost,
  72. impression_1k_cost,
  73. conversion_num,
  74. stat_date,
  75. ad_photo_played_2s_ratio,
  76. follow,
  77. signature,
  78. project_id,
  79. project_name
  80. from (select account_id,
  81. campaign_id,
  82. unit_id,
  83. event_register_cost,
  84. province,
  85. photo_click,
  86. key_action_cost,
  87. event_next_day_stay,
  88. photo_click_ratio,
  89. photo_click_cost,
  90. charge,
  91. event_pay,
  92. action_cost,
  93. conversion_cost,
  94. bclick,
  95. key_action_ratio,
  96. play_3s_ratio,
  97. play_5s_ratio,
  98. activation,
  99. event_app_invoked_ratio,
  100. `show`,
  101. event_new_user_pay_cost,
  102. aclick,
  103. deep_conversion_num,
  104. action_ratio,
  105. conversion_ratio,
  106. event_pay_purchase_amount_first_day,
  107. event_pay_first_day,
  108. conversion_num_cost,
  109. event_new_user_pay_ratio,
  110. event_app_invoked,
  111. event_next_day_stay_ratio,
  112. event_register,
  113. event_new_user_pay,
  114. event_next_day_stay_cost,
  115. deep_conversion_ratio,
  116. deep_conversion_cost,
  117. `like`,
  118. event_app_invoked_cost,
  119. key_action,
  120. ad_photo_played_10s,
  121. form_cost,
  122. event_pay_purchase_amount,
  123. click_1k_cost,
  124. impression_1k_cost,
  125. conversion_num,
  126. stat_date,
  127. ad_photo_played_2s_ratio,
  128. follow
  129. from media_api.kuaishou_unit_audience_province_report_daily
  130. where stat_date = {stat_date}) province
  131. inner join
  132. (select account_id, unit_id, photo_id
  133. from `jeecg-boot`.ctop_ai_kuaishou_creative_level_operation_record
  134. group by account_id, unit_id, photo_id) operation
  135. on province.account_id = operation.account_id and province.unit_id = operation.unit_id
  136. inner join
  137. (select account_id, signature, photo_id
  138. from media_api.kuaishou_video_list
  139. group by account_id, signature, photo_id) video
  140. on operation.account_id = video.account_id and operation.photo_id = video.photo_id
  141. left join (
  142. select account_id, project_id
  143. from `jeecg-boot`.ctop_user_allocation
  144. ) acc_info
  145. on province.account_id = acc_info.account_id
  146. left join (
  147. select id, project_name
  148. from `jeecg-boot`.ctop_project
  149. ) pro
  150. on acc_info.project_id = pro.id
  151. order by unit_id limit {num},20000
  152. """.format(stat_date=self.start_time, num=i * 40000)
  153. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  154. self.conn.commit()
  155. TidbConn.conn_close(conn=self.conn)
  156. return 0
  157. except Exception as e:
  158. logging.error(e)
  159. return -1
  160. if __name__ == '__main__':
  161. KuaishouUnitAudienceProvinceReportDaily().provinceReport()