KuaishouUnitAudienceGenderReportDaily.py 4.6 KB

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