KuaishouUnitAudienceCityReportDaily.py 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/2/17 4:13 下午
  4. # @Site :
  5. # @File : KuaishouUnitAudienceCityReportDaily.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 KuaishouUnitAudienceCityReportDaily:
  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 cityReport(self):
  19. try:
  20. sql = """select account_id from media_api.kuaishou_unit_audience_city_report_daily where
  21. stat_date = {start_time} group by account_id
  22. """.format(start_time=self.start_time)
  23. account_list = TidbConn.quary(self.conn, sql)
  24. for account_id in account_list:
  25. sql = """
  26. replace into application.kuaishou_unit_audience_city_report_daily_d
  27. select city.account_id,
  28. campaign_id,
  29. city.unit_id,
  30. event_register_cost,
  31. province,
  32. city,
  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. city,
  86. photo_click,
  87. key_action_cost,
  88. event_next_day_stay,
  89. photo_click_ratio,
  90. photo_click_cost,
  91. charge,
  92. event_pay,
  93. action_cost,
  94. conversion_cost,
  95. bclick,
  96. key_action_ratio,
  97. play_3s_ratio,
  98. play_5s_ratio,
  99. activation,
  100. event_app_invoked_ratio,
  101. `show`,
  102. event_new_user_pay_cost,
  103. aclick,
  104. deep_conversion_num,
  105. action_ratio,
  106. conversion_ratio,
  107. event_pay_purchase_amount_first_day,
  108. event_pay_first_day,
  109. conversion_num_cost,
  110. event_new_user_pay_ratio,
  111. event_app_invoked,
  112. event_next_day_stay_ratio,
  113. event_register,
  114. event_new_user_pay,
  115. event_next_day_stay_cost,
  116. deep_conversion_ratio,
  117. deep_conversion_cost,
  118. `like`,
  119. event_app_invoked_cost,
  120. key_action,
  121. ad_photo_played_10s,
  122. form_cost,
  123. event_pay_purchase_amount,
  124. click_1k_cost,
  125. impression_1k_cost,
  126. conversion_num,
  127. stat_date,
  128. ad_photo_played_2s_ratio,
  129. follow
  130. from media_api.kuaishou_unit_audience_city_report_daily
  131. where stat_date = 20210705 and account_id={account_id}) city
  132. inner join
  133. (select account_id, unit_id, photo_id
  134. from `jeecg-boot`.ctop_ai_kuaishou_creative_level_operation_record where account_id={account_id}
  135. group by account_id, unit_id, photo_id) operation
  136. on city.account_id = operation.account_id and city.unit_id = operation.unit_id
  137. inner join
  138. (select account_id, signature, photo_id
  139. from media_api.kuaishou_video_list where account_id={account_id}
  140. group by account_id, signature, photo_id) video
  141. on operation.account_id = video.account_id and operation.photo_id = video.photo_id
  142. left join (
  143. select account_id, project_id
  144. from `jeecg-boot`.ctop_user_allocation where account_id={account_id}
  145. ) acc_info
  146. on city.account_id = acc_info.account_id
  147. left join (
  148. select id, project_name
  149. from `jeecg-boot`.ctop_project
  150. ) pro
  151. on acc_info.project_id = pro.id
  152. """.format(stat_date=self.start_time, account_id=account_id[0])
  153. print(sql)
  154. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  155. self.conn.commit()
  156. TidbConn.conn_close(conn=self.conn)
  157. return 0
  158. except Exception as e:
  159. logging.error(e)
  160. return -1
  161. if __name__ == '__main__':
  162. KuaishouUnitAudienceCityReportDaily().cityReport()