LiveCampaignHourlyReport.py 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2022/7/27 2:49 下午
  4. # @Site :
  5. # @File : LiveCampaignHourlyReport.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 LiveCampaignHourlyReport:
  13. def __init__(self):
  14. self.conn = TidbConn.get_connection()
  15. self.parm = Utils.get_live_args()
  16. self.stat_date = self.parm
  17. def handler(self):
  18. try:
  19. sql = """select account_id from kuaishou_live.kuaishou_live_campaign_report_hourly where report_date={stat_date}
  20. group by account_id""".format(stat_date=self.stat_date)
  21. account_list = TidbConn.quary(self.conn, sql)
  22. for account_id in account_list:
  23. sql = """
  24. replace into ruixuan.kuaishou_live_campaign_report_hourly_dw
  25. select report.account_id,
  26. account_name,
  27. campaign_id,
  28. campaign_name,
  29. campaign_type_str,
  30. project_id,
  31. project_name,
  32. ks_id,
  33. ks_name,
  34. sale_leader_id,
  35. sale_leader,
  36. operator_leader_id,
  37. operator_leader,
  38. designer_leader_id,
  39. designer_leader,
  40. enterprise_name,
  41. collaborator_id,
  42. collaborator,
  43. cost_total/100,
  44. ad_show,
  45. ad_show1k_cost/100,
  46. impression,
  47. photo_click,
  48. photo_click_ratio,
  49. click,
  50. actionbar_click,
  51. actionbar_click_cost/100,
  52. esp_click_ratio,
  53. action_ratio,
  54. ad_item_click_count,
  55. esp_live_played_seconds,
  56. played_three_seconds,
  57. play3s_ratio,
  58. played_five_seconds,
  59. play5s_ratio,
  60. played_end,
  61. play_end_ratio,
  62. share,
  63. comment,
  64. likes,
  65. report,
  66. block,
  67. item_negative,
  68. live_share,
  69. live_comment,
  70. live_reward,
  71. effective_play_count,
  72. effective_play_ratio,
  73. ad_live_played1m_num,
  74. conversion_num,
  75. conversion_cost_esp/100,
  76. gmv/100,
  77. t0_gmv/100,
  78. t1_gmv/100,
  79. t7_gmv/100,
  80. t15_gmv/100,
  81. t30_gmv,
  82. roi,
  83. t0_roi,
  84. t1_roi,
  85. t7_roi,
  86. t15_roi,
  87. t30_roi,
  88. paied_order,
  89. order_ratio,
  90. t0_order_cnt,
  91. t0_order_cnt_cost/100,
  92. t0_order_cnt_ratio,
  93. t1_order_cnt,
  94. t7_order_cnt,
  95. t15_order_cnt,
  96. t30_order_cnt,
  97. merchant_reco_fans,
  98. t1_retention,
  99. t7_retention,
  100. t15_retention,
  101. t30_retention,
  102. t1_retention_ratio,
  103. t7_retention_ratio,
  104. t15_retention_ratio,
  105. t30_retention_ratio,
  106. reservation_success,
  107. reservation_cost/100,
  108. standard_live_played_started,
  109. live_audience_cost/100,
  110. live_event_goods_view,
  111. goods_click_ratio,
  112. direct_attr_plat_new_buyer_cnt,
  113. t30_attr_plat_total_buyer_cnt,
  114. direct_attr_seller_new_buyer_cnt,
  115. t30_attr_seller_total_buyer_cnt,
  116. fans_t0_gmv/100,
  117. fans_t1_gmv/100,
  118. fans_t7_gmv/100,
  119. fans_t15_gmv/100,
  120. fans_t30_gmv/100,
  121. fans_t0_roi,
  122. fans_t1_roi,
  123. fans_t7_roi,
  124. fans_t15_roi,
  125. fans_t30_roi,
  126. report_date,
  127. `hour`
  128. from kuaishou_live.kuaishou_live_campaign_report_hourly report
  129. left join
  130. (select acc.account_id,
  131. account_name,
  132. project_id,
  133. project_name,
  134. ks_id,
  135. ks_name,
  136. sale_leader_id,
  137. sale.user_name as sale_leader,
  138. operator_leader_id,
  139. user.user_name as operator_leader,
  140. designer_leader_id,
  141. designer.user_name as designer_leader,
  142. enterprise_name,
  143. collaborator_id,
  144. collaborator
  145. from ruixuan.kuaishou_live_user_account acc
  146. left join
  147. ruixuan.kuaishou_live_project pro
  148. on acc.project_id = pro.id
  149. left join ruixuan.sys_user sale
  150. on acc.sale_leader_id = sale.user_id
  151. left join ruixuan.sys_user user
  152. on acc.operator_leader_id = user.user_id
  153. left join ruixuan.sys_user designer
  154. on acc.designer_leader_id = designer.user_id
  155. left join(
  156. select account_id,
  157. group_concat(collaborator_id) as collaborator_id,
  158. group_concat(collaborator) as collaborator
  159. from ruixuan.kuaishou_live_user_account_part
  160. group by account_id
  161. ) collaborator
  162. on acc.account_id = collaborator.account_id) acc_info
  163. on report.account_id = acc_info.account_id
  164. where report.report_date={stat_date} and report.account_id={account_id};
  165. """.format(stat_date=self.stat_date, account_id=account_id[0])
  166. print(sql)
  167. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  168. self.conn.commit()
  169. TidbConn.conn_close(conn=self.conn)
  170. return 0
  171. except Exception as e:
  172. logging.error(e)
  173. return -1
  174. if __name__ == '__main__':
  175. status = LiveCampaignHourlyReport().handler()
  176. if status == 0:
  177. print("作业执行成功")
  178. exit(0)
  179. else:
  180. print("作业执行失败")
  181. exit(-1)