Competo: воронка, мульти-канал, time-lag и weekly retention на GA4

WITH evt AS (
  SELECT
    user_pseudo_id,
    event_name,
    TIMESTAMP_MICROS(event_timestamp) AS ts,
    (SELECT value.string_value FROM UNNEST(items) WHERE key = 'item_name') AS item_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20200101' AND '20201231'
    AND event_name IN ('select_item','view_item','add_to_cart','purchase')
),
firsts AS (
  SELECT user_pseudo_id, event_name, MIN(ts) AS first_ts
  FROM evt
  GROUP BY 1, 2
)
SELECT
  COUNT(DISTINCT IF(event_name='select_item',  user_pseudo_id, NULL)) AS s1,
  COUNT(DISTINCT IF(event_name='view_item' AND ts > sel.first_ts, user_pseudo_id, NULL)) AS s2,
  -- Закрытость → каждое следующее ts > предыдущего
  ...
FROM firsts;

WITH first_sess AS (
  SELECT user_pseudo_id,
         ARRAY_AGG(STRUCT(traffic_source.source AS src, traffic_source.medium AS med)
                   ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS first_src
  FROM events
  WHERE event_name = 'session_start'
  GROUP BY user_pseudo_id
),
chain AS (
  SELECT user_pseudo_id,
         STRING_AGG(DISTINCT traffic_source.source || '/' || traffic_source.medium,
                    ' → ' ORDER BY event_timestamp) AS path
  FROM events
  WHERE event_name = 'session_start'
    AND event_timestamp <= (SELECT MIN(event_timestamp) FROM events e2
                            WHERE e2.user_pseudo_id = events.user_pseudo_id
                              AND e2.event_name = 'purchase')
  GROUP BY user_pseudo_id
)
SELECT path, COUNT(*) AS users
FROM chain
GROUP BY path
ORDER BY users DESC;

SELECT
  user_pseudo_id,
  DATE_DIFF(DATE(MIN(IF(event_name='purchase', ts, NULL))),
            DATE(MIN(ts)),
            DAY) AS days_to_first_purchase
FROM evt
GROUP BY user_pseudo_id
HAVING days_to_first_purchase IS NOT NULL;
 
-- Среднее
SELECT AVG(days_to_first_purchase) FROM ...;

WITH first_visit AS (
  SELECT user_pseudo_id,
         DATE_TRUNC(DATE(TIMESTAMP_MICROS(MIN(event_timestamp))), WEEK) AS cohort
  FROM events GROUP BY 1
),
visits AS (
  SELECT user_pseudo_id,
         DATE_TRUNC(DATE(TIMESTAMP_MICROS(event_timestamp)), WEEK) AS visit_week
  FROM events
)
SELECT
  f.cohort,
  DATE_DIFF(v.visit_week, f.cohort, WEEK) AS week_n,
  COUNT(DISTINCT v.user_pseudo_id) AS users
FROM first_visit f
JOIN visits v USING(user_pseudo_id)
GROUP BY 1, 2
ORDER BY 1, 2;