1WITH reg AS ( 2 SELECT 3 DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS reg_week, 4 user_pseudo_id 5 FROM `tc-da-1.turing_data_analytics.raw_events` 6 WHERE event_name = 'first_visit' 7 GROUP BY 1,2), 8 purch AS ( 9 SELECT 10 DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), WEEK) AS purch_week, 11 user_pseudo_id, 12 SUM(purchase_revenue_in_usd) AS rev 13 FROM `tc-da-1.turing_data_analytics.raw_events` 14 WHERE event_name = 'purchase' 15 GROUP BY 1,2), 16 diff AS ( 17 SELECT 18 purch.rev, 19 reg_week, 20 reg.user_pseudo_id, 21 DATE_DIFF(purch.purch_week, reg.reg_week, WEEK) AS week_number 22 FROM reg LEFT JOIN purch ON reg.user_pseudo_id = purch.user_pseudo_id), 23 rev_cohort AS ( 24 SELECT 25 reg_week, 26 SUM(CASE WHEN week_number = 0 THEN rev ELSE NULL END) AS w0, 27 SUM(CASE WHEN week_number = 1 THEN rev ELSE NULL END) AS w1, 28 SUM(CASE WHEN week_number = 2 THEN rev ELSE NULL END) AS w2, 29 SUM(CASE WHEN week_number = 3 THEN rev ELSE NULL END) AS w3, 30 SUM(CASE WHEN week_number = 4 THEN rev ELSE NULL END) AS w4, 31 SUM(CASE WHEN week_number = 5 THEN rev ELSE NULL END) AS w5, 32 SUM(CASE WHEN week_number = 6 THEN rev ELSE NULL END) AS w6, 33 SUM(CASE WHEN week_number = 7 THEN rev ELSE NULL END) AS w7, 34 SUM(CASE WHEN week_number = 8 THEN rev ELSE NULL END) AS w8, 35 SUM(CASE WHEN week_number = 9 THEN rev ELSE NULL END) AS w9, 36 SUM(CASE WHEN week_number = 10 THEN rev ELSE NULL END) AS w10, 37 SUM(CASE WHEN week_number = 11 THEN rev ELSE NULL END) AS w11, 38 SUM(CASE WHEN week_number = 12 THEN rev ELSE NULL END) AS w12, 39 COUNT(user_pseudo_id) AS IDs 40 FROM diff 41 GROUP BY 1) 42 43SELECT 44 rev_cohort.reg_week, 45 ROUND(CASE WHEN rev_cohort.w0 != 0 THEN rev_cohort.w0 / IDs ELSE NULL END, 3) AS w0, 46 ROUND(CASE WHEN rev_cohort.w1 != 0 THEN rev_cohort.w1 / IDs ELSE NULL END, 3) AS w1, 47 ROUND(CASE WHEN rev_cohort.w2 != 0 THEN rev_cohort.w2 / IDs ELSE NULL END, 3) AS w2, 48 ROUND(CASE WHEN rev_cohort.w3 != 0 THEN rev_cohort.w3 / IDs ELSE NULL END, 3) AS w3, 49 ROUND(CASE WHEN rev_cohort.w4 != 0 THEN rev_cohort.w4 / IDs ELSE NULL END, 3) AS w4, 50 ROUND(CASE WHEN rev_cohort.w5 != 0 THEN rev_cohort.w5 / IDs ELSE NULL END, 3) AS w5, 51 ROUND(CASE WHEN rev_cohort.w6 != 0 THEN rev_cohort.w6 / IDs ELSE NULL END, 3) AS w6, 52 ROUND(CASE WHEN rev_cohort.w7 != 0 THEN rev_cohort.w7 / IDs ELSE NULL END, 3) AS w7, 53 ROUND(CASE WHEN rev_cohort.w8 != 0 THEN rev_cohort.w8 / IDs ELSE NULL END, 3) AS w8, 54 ROUND(CASE WHEN rev_cohort.w9 != 0 THEN rev_cohort.w9 / IDs ELSE NULL END, 3) AS w9, 55 ROUND(CASE WHEN rev_cohort.w10 != 0 THEN rev_cohort.w10 / IDs ELSE NULL END, 3) AS w10, 56 ROUND(CASE WHEN rev_cohort.w11 != 0 THEN rev_cohort.w11 / IDs ELSE NULL END, 3) AS w11, 57 ROUND(CASE WHEN rev_cohort.w12 != 0 THEN rev_cohort.w12 / IDs ELSE NULL END, 3) AS w12 58 FROM rev_cohort 59 WHERE rev_cohort.reg_week <= '2021-01-24' 60 ORDER BY 1 61
Edvinas Vensas
Created: May 3rd 2023