SELECT
event_name
, COUNT(*) AS number_transfers
, COUNT(DISTINCT user_id) AS number_unique_users
, dt AS event_at
FROM 'wise_funnel_events.csv'
WHERE dt <= '2024-02-29'
GROUP BY event_at, event_name
ORDER BY event_name, event_at
-- Conversion to next step
-- Transfer created -> Transfer funded -> Transfer transferred
-- overall, no segmentation
WITH step_counts AS (
SELECT
dt AS event_day
, date_trunc('week', dt) AS event_week
, event_name
, COUNT(*) AS occurrences
FROM 'wise_funnel_events_regional.csv'
WHERE dt <= '2024-02-29'
GROUP BY event_day, event_name, event_week
)
SELECT
event_day
, event_week
, event_name
, occurrences
, ROUND((occurrences * 1.0 / FIRST_VALUE(occurrences) OVER (PARTITION BY event_day ORDER BY occurrences DESC)), 4) AS conversion_rate
FROM step_counts
ORDER BY event_day, occurrences DESC
WITH step_counts AS (
SELECT
CASE WHEN dt < '2024-02-01' THEN 'before_1st_feb'
ELSE 'from_1st_feb' END AS event_period
, dt AS event_day
, event_name
, COUNT(*) AS occurrences
, CONCAT(region, ' - ', platform, ' - ', experience) AS segment
FROM 'wise_funnel_events_regional.csv'
GROUP BY event_day, event_name, segment
)
, calculate_conversion AS (
SELECT
event_period
, event_day
, segment
, event_name
, occurrences
, (occurrences * 1.0 / FIRST_VALUE(occurrences) OVER (PARTITION BY event_day, segment ORDER BY occurrences DESC)) AS conversion_rate
FROM step_counts
--ORDER BY segment, event_day, occurrences DESC
)
, drop_on_period AS (
SELECT DISTINCT cc.segment AS segment, cr_before_1st_feb, cr_from_1st_feb
FROM calculate_conversion cc
LEFT JOIN (SELECT MEDIAN(conversion_rate) AS cr_before_1st_feb, segment
FROM calculate_conversion
WHERE event_name = 'Transfer Funded'
AND event_period = 'before_1st_feb'
GROUP BY segment) funded_before USING(segment)
LEFT JOIN (SELECT MEDIAN(conversion_rate) AS cr_from_1st_feb, segment
FROM calculate_conversion
WHERE event_name = 'Transfer Funded'
AND event_period = 'from_1st_feb'
GROUP BY segment) funded_from USING(segment)
)
, total_transfers_created AS (
SELECT
SUM(CASE WHEN event_period = 'before_1st_feb' THEN occurrences END) AS total_transfer_created_before_1st_feb
, SUM(CASE WHEN event_period = 'from_1st_feb' THEN occurrences END) AS total_transfer_created_from_1st_feb
FROM step_counts
WHERE event_name = 'Transfer Created'
)
, calculate_segment_volume AS (
SELECT sc.segment AS segment,
SUM(CASE WHEN event_period = 'before_1st_feb' THEN occurrences END) AS transfer_created_before_1st_feb,
SUM(CASE WHEN event_period = 'from_1st_feb' THEN occurrences END) AS transfer_created_from_1st_feb
FROM step_counts sc
WHERE event_name = 'Transfer Created'
GROUP BY sc.segment
)
SELECT
dp.segment
, transfer_created_before_1st_feb
, ROUND(100*transfer_created_before_1st_feb/(SELECT total_transfer_created_before_1st_feb FROM total_transfers_created), 2) AS volume_percentage_before_1st_feb
, transfer_created_from_1st_feb
, ROUND(100*transfer_created_from_1st_feb/(SELECT total_transfer_created_from_1st_feb FROM total_transfers_created), 2) AS volume_percentage_from_1st_feb
, ROUND(cr_before_1st_feb*100, 2) AS cr_before_1st_feb
, ROUND(cr_from_1st_feb*100, 2) AS cr_from_1st_feb
, ROUND((cr_from_1st_feb - cr_before_1st_feb)*100, 2) AS cr_drop
, ROUND((cr_before_1st_feb - cr_from_1st_feb) / cr_before_1st_feb * 100, 2) AS percentage_drop
FROM drop_on_period dp
LEFT JOIN calculate_segment_volume csv ON dp.segment = csv.segment
ORDER BY dp.segment ASC