LiveAccountReport.py 10 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313
  1. # Author renyupeng
  2. # coding=utf-8
  3. # @Time : 2023/3/22 9:56 上午
  4. # @Site :
  5. # @File : LiveAccountReport.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. class LiveAccountReport:
  12. def __init__(self):
  13. self.conn = TidbConn.get_connection()
  14. def daily_account(self, account_id, start_date, end_date):
  15. sql = """
  16. replace into ruixuan.kuaishou_live_account_report_daily_dw
  17. select report.account_id,
  18. account_name,
  19. project_id,
  20. acc_info.project_name,
  21. ks_id,
  22. ks_name,
  23. account_type,
  24. user_id,
  25. user_name,
  26. leader as lead_name,
  27. pro_user_id,
  28. pro_user_name,
  29. sale_id,
  30. sale_name,
  31. cost_total / 100,
  32. ad_show,
  33. ad_show1k_cost / 100,
  34. impression,
  35. photo_click,
  36. photo_click_ratio,
  37. click,
  38. actionbar_click,
  39. actionbar_click_cost / 100,
  40. esp_click_ratio,
  41. action_ratio,
  42. ad_item_click_count,
  43. esp_live_played_seconds,
  44. played_three_seconds,
  45. play3s_ratio,
  46. played_five_seconds,
  47. play5s_ratio,
  48. played_end,
  49. play_end_ratio,
  50. share,
  51. comment,
  52. likes,
  53. report,
  54. block,
  55. item_negative,
  56. live_share,
  57. live_comment,
  58. live_reward,
  59. effective_play_count,
  60. effective_play_ratio,
  61. ad_live_played1m_num,
  62. conversion_num,
  63. conversion_cost_esp / 100,
  64. gmv / 100,
  65. t0_gmv / 100,
  66. t1_gmv / 100,
  67. t7_gmv / 100,
  68. t15_gmv / 100,
  69. t30_gmv,
  70. roi,
  71. t0_roi,
  72. t1_roi,
  73. t7_roi,
  74. t15_roi,
  75. t30_roi,
  76. paied_order,
  77. order_ratio,
  78. t0_order_cnt,
  79. t0_order_cnt_cost / 100,
  80. t0_order_cnt_ratio,
  81. t1_order_cnt,
  82. t7_order_cnt,
  83. t15_order_cnt,
  84. t30_order_cnt,
  85. merchant_reco_fans,
  86. t1_retention,
  87. t7_retention,
  88. t15_retention,
  89. t30_retention,
  90. t1_retention_ratio,
  91. t7_retention_ratio,
  92. t15_retention_ratio,
  93. t30_retention_ratio,
  94. reservation_success,
  95. reservation_cost / 100,
  96. standard_live_played_started,
  97. live_audience_cost / 100,
  98. live_event_goods_view,
  99. goods_click_ratio,
  100. direct_attr_plat_new_buyer_cnt,
  101. t30_attr_plat_total_buyer_cnt,
  102. direct_attr_seller_new_buyer_cnt,
  103. t30_attr_seller_total_buyer_cnt,
  104. fans_t0_gmv / 100,
  105. fans_t1_gmv / 100,
  106. fans_t7_gmv / 100,
  107. fans_t15_gmv / 100,
  108. fans_t30_gmv / 100,
  109. fans_t0_roi,
  110. fans_t1_roi,
  111. fans_t7_roi,
  112. fans_t15_roi,
  113. fans_t30_roi,
  114. order_cost,
  115. reco_fans_cost,
  116. conversion_live_event_goods_view_cost,
  117. report_date
  118. from kuaishou_live.kuaishou_live_account_report_daily report
  119. left join
  120. (select acc.account_id,
  121. account_name,
  122. project_id,
  123. pro.project_name,
  124. leader,
  125. ks_id,
  126. ks_name,
  127. account_type,
  128. user.user_id as user_id,
  129. user.nick_name as user_name,
  130. dep.leader as leader_name,
  131. pro.operator_id as pro_user_id,
  132. pro.operator_name as pro_user_name,
  133. pro.sale_id as sale_id,
  134. pro.sale_name as sale_name
  135. from ruixuan.kuaishou_account acc
  136. left join
  137. ruixuan.kuaishou_project pro
  138. on acc.project_id = pro.id
  139. left join ruixuan.sys_user user
  140. on acc.operator_id=user.user_id
  141. left join ruixuan.sys_dept dep
  142. on user.dept_id=dep.dept_id
  143. ) acc_info
  144. on report.account_id = acc_info.account_id
  145. where report.report_date = {start_date} and report.report_date<={end_date}
  146. and report.account_id = {account_id};
  147. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  148. try:
  149. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  150. self.conn.commit()
  151. TidbConn.conn_close(conn=self.conn)
  152. return 0
  153. except Exception as e:
  154. logging.error(e)
  155. return -1
  156. def account_hourly(self, account_id, start_date, end_date):
  157. sql = """
  158. replace into ruixuan.kuaishou_live_account_report_hourly_dw
  159. select report.account_id,
  160. account_name,
  161. project_id,
  162. acc_info.project_name,
  163. ks_id,
  164. ks_name,
  165. account_type,
  166. user_id,
  167. user_name,
  168. leader as lead_name,
  169. pro_user_id,
  170. pro_user_name,
  171. sale_id,
  172. sale_name,
  173. cost_total / 100,
  174. ad_show,
  175. ad_show1k_cost / 100,
  176. impression,
  177. photo_click,
  178. photo_click_ratio,
  179. click,
  180. actionbar_click,
  181. actionbar_click_cost / 100,
  182. esp_click_ratio,
  183. action_ratio,
  184. ad_item_click_count,
  185. esp_live_played_seconds,
  186. played_three_seconds,
  187. play3s_ratio,
  188. played_five_seconds,
  189. play5s_ratio,
  190. played_end,
  191. play_end_ratio,
  192. share,
  193. comment,
  194. likes,
  195. report,
  196. block,
  197. item_negative,
  198. live_share,
  199. live_comment,
  200. live_reward,
  201. effective_play_count,
  202. effective_play_ratio,
  203. ad_live_played1m_num,
  204. conversion_num,
  205. conversion_cost_esp / 100,
  206. gmv / 100,
  207. t0_gmv / 100,
  208. t1_gmv / 100,
  209. t7_gmv / 100,
  210. t15_gmv / 100,
  211. t30_gmv,
  212. roi,
  213. t0_roi,
  214. t1_roi,
  215. t7_roi,
  216. t15_roi,
  217. t30_roi,
  218. paied_order,
  219. order_ratio,
  220. t0_order_cnt,
  221. t0_order_cnt_cost / 100,
  222. t0_order_cnt_ratio,
  223. t1_order_cnt,
  224. t7_order_cnt,
  225. t15_order_cnt,
  226. t30_order_cnt,
  227. merchant_reco_fans,
  228. t1_retention,
  229. t7_retention,
  230. t15_retention,
  231. t30_retention,
  232. t1_retention_ratio,
  233. t7_retention_ratio,
  234. t15_retention_ratio,
  235. t30_retention_ratio,
  236. reservation_success,
  237. reservation_cost / 100,
  238. standard_live_played_started,
  239. live_audience_cost / 100,
  240. live_event_goods_view,
  241. goods_click_ratio,
  242. direct_attr_plat_new_buyer_cnt,
  243. t30_attr_plat_total_buyer_cnt,
  244. direct_attr_seller_new_buyer_cnt,
  245. t30_attr_seller_total_buyer_cnt,
  246. fans_t0_gmv / 100,
  247. fans_t1_gmv / 100,
  248. fans_t7_gmv / 100,
  249. fans_t15_gmv / 100,
  250. fans_t30_gmv / 100,
  251. fans_t0_roi,
  252. fans_t1_roi,
  253. fans_t7_roi,
  254. fans_t15_roi,
  255. fans_t30_roi,
  256. order_cost,
  257. reco_fans_cost,
  258. conversion_live_event_goods_view_cost,
  259. report_date,
  260. `hour`
  261. from kuaishou_live.kuaishou_live_account_report_hourly report
  262. left join
  263. (select acc.account_id,
  264. account_name,
  265. project_id,
  266. pro.project_name,
  267. leader,
  268. ks_id,
  269. ks_name,
  270. account_type,
  271. user.user_id as user_id,
  272. user.nick_name as user_name,
  273. dep.leader as leader_name,
  274. pro.operator_id as pro_user_id,
  275. pro.operator_name as pro_user_name,
  276. pro.sale_id as sale_id,
  277. pro.sale_name as sale_name
  278. from ruixuan.kuaishou_account acc
  279. left join
  280. ruixuan.kuaishou_project pro
  281. on acc.project_id = pro.id
  282. left join ruixuan.sys_user user
  283. on acc.operator_id=user.user_id
  284. left join ruixuan.sys_dept dep
  285. on user.dept_id=dep.dept_id
  286. ) acc_info
  287. on report.account_id = acc_info.account_id
  288. where report.report_date = {start_date} and report.report_date<={end_date}
  289. and report.account_id = {account_id};
  290. """.format(start_date=start_date, end_date=end_date, account_id=account_id)
  291. try:
  292. TidbConn.upsert(conn=self.conn, sql_buff=sql)
  293. self.conn.commit()
  294. TidbConn.conn_close(conn=self.conn)
  295. return 0
  296. except Exception as e:
  297. logging.error(e)
  298. return -1
  299. def handler(self, date_type, account_id, start_date, end_date):
  300. if date_type == "daily":
  301. self.daily_account(account_id, start_date, end_date)
  302. else:
  303. self.account_hourly(account_id, start_date, end_date)