SELECT *
FROM 'wise_funnel_events.csv'
SELECT
MIN(dt) AS first_date
, MAX(dt) AS last_date
FROM 'wise_funnel_events.csv'
SELECT
COUNT(DISTINCT user_id) AS distinct_users,
experience
FROM 'wise_funnel_events.csv'
GROUP BY experience
SELECT
event_name
, COUNT(*) AS number_transfers
, COUNT(DISTINCT user_id) AS number_unique_users
, experience
, region
, platform
, dt AS event_at
FROM 'wise_funnel_events.csv'
WHERE dt < '2024-03-01'
GROUP BY event_at, event_name, experience, region, platform
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-03-01'
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
-- Conversion to next step
-- Transfer created -> Transfer funded -> Transfer transferred
-- Segmented by all
WITH step_counts AS (
SELECT
event_name
, COUNT(*) AS occurrences
, region
, platform
, experience
FROM 'wise_funnel_events_regional.csv'
WHERE dt < '2024-03-01'
GROUP BY event_name, experience, region, platform
)
SELECT
event_name
, region
, platform
, experience
, occurrences
, ROUND((occurrences * 1.0 / FIRST_VALUE(occurrences) OVER (PARTITION BY platform, experience, region ORDER BY occurrences DESC)), 4) AS conversion_rate
FROM step_counts
ORDER BY occurrences DESC
WITH daily_steps AS (
SELECT
dt AS event_day,
CASE
WHEN dt < '2024-02-01' THEN 'before_1st_feb'
ELSE 'from_1st_feb'
END AS event_period,
CONCAT(region, ' - ', platform, ' - ', experience) AS segment,
event_name,
COUNT(*) AS occurrences
FROM 'wise_funnel_events.csv'
GROUP BY event_day, event_period, segment, event_name
)
, period_steps AS (
SELECT
event_period,
segment,
event_name,
SUM(occurrences) AS occurrences
FROM daily_steps
GROUP BY event_period, segment, event_name
)
, period_conversion AS (
SELECT
event_period,
segment,
event_name,
occurrences,
occurrences * 1.0 / FIRST_VALUE(occurrences) OVER (
PARTITION BY event_period, segment
ORDER BY occurrences DESC
) AS conversion_rate
FROM period_steps
)
, segment_volume AS (
SELECT
segment,
SUM(occurrences) FILTER (WHERE event_period = 'before_1st_feb') AS vol_before_1st_feb,
SUM(occurrences) FILTER (WHERE event_period = 'from_1st_feb') AS vol_from_1st_feb
FROM daily_steps
WHERE event_name = 'Transfer Created'
GROUP BY segment
)
, total_volume AS (
SELECT
SUM(vol_before_1st_feb) AS total_before_1st_feb,
SUM(vol_from_1st_feb) AS total_from_1st_feb
FROM segment_volume
)
, funded_conversion AS (
SELECT
segment,
MAX(conversion_rate) FILTER (WHERE event_period = 'before_1st_feb') AS cr_before_1st_feb,
MAX(conversion_rate) FILTER (WHERE event_period = 'from_1st_feb') AS cr_from_1st_feb
FROM period_conversion
WHERE event_name = 'Transfer Funded'
GROUP BY segment
)
SELECT
fc.segment,
sv.vol_before_1st_feb AS transfer_created_before_1st_feb,
ROUND(100.0 * sv.vol_before_1st_feb / tv.total_before_1st_feb, 2) AS vol_pct_before_1st_feb,
sv.vol_from_1st_feb AS transfer_created_from_1st_feb,
ROUND(100.0 * sv.vol_from_1st_feb / tv.total_from_1st_feb, 2) AS vol_pct_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((vol_pct_from_1st_feb - vol_pct_before_1st_feb) / vol_pct_before_1st_feb * 100, 2) AS vol_relative_growth,
ROUND((cr_from_1st_feb - cr_before_1st_feb) / cr_before_1st_feb * 100, 2) AS cr_relative_growth
FROM funded_conversion fc
LEFT JOIN segment_volume sv USING(segment)
CROSS JOIN total_volume tv
ORDER BY transfer_created_before_1st_feb DESC