Funnel analytics ⏬

Scenario

A bank is interested in understanding the conversion rates of their mortgage application process.

Their application funnel consists of the following stages, in order:

Full application
Decision
Documentation
Valuation inspection
Valuation made
Valuation submitted
Solicitation
Funds released

Question

The table applications tracks the dates that each mortgage application reached each stage.

Calculate the conversion rates between each stage for each cohort (defined below).

The output should have a row per cohort and stage, with the columns:

cohort as the month that the applications were started; e.g., an application started on 2024-01-15 would be cohort 2024-01.
stage
mortgages as the number of mortgages that reached the stage
step_rate as the percentage of mortgages that reached the stage compared to the previous stage
total_rate as the percentage of mortgages that reached the stage compared to the first stage

Round the step_rate and total_rate to two decimal places.

Note that each cohort should have all the stages, even if there are no mortgages that reached that stage -- the mortgages column should be 0 in that case.

Order the output by cohort and the stage order (e.g. full application should come before decision, and so on).

Expand for the DDL

create table applications (
    event_id    int primary key,
    event_date  date not null,
    mortgage_id int not null,
    stage       varchar not null
);
insert into applications
values
    (1,  '2024-01-02', 1,  'full application'),
    (2,  '2024-01-06', 1,  'decision'),
    (3,  '2024-01-12', 1,  'documentation'),
    (4,  '2024-01-14', 1,  'valuation inspection'),
    (5,  '2024-01-16', 2,  'full application'),
    (6,  '2024-01-17', 3,  'full application'),
    (7,  '2024-01-19', 2,  'decision'),
    (8,  '2024-01-25', 2,  'documentation'),
    (9,  '2024-01-27', 1,  'valuation made'),
    (10, '2024-01-27', 4,  'full application'),
    (11, '2024-01-29', 3,  'decision'),
    (12, '2024-02-02', 1,  'valuation submitted'),
    (13, '2024-02-03', 4,  'decision'),
    (14, '2024-02-04', 5,  'full application'),
    (15, '2024-02-05', 4,  'documentation'),
    (16, '2024-02-06', 4,  'valuation inspection'),
    (17, '2024-02-09', 6,  'full application'),
    (18, '2024-02-11', 5,  'decision'),
    (19, '2024-02-11', 7,  'full application'),
    (20, '2024-02-12', 2,  'valuation inspection'),
    (21, '2024-02-12', 6,  'decision'),
    (22, '2024-02-12', 7,  'decision'),
    (23, '2024-02-13', 2,  'valuation made'),
    (24, '2024-02-13', 6,  'documentation'),
    (25, '2024-02-14', 2,  'valuation submitted'),
    (26, '2024-02-15', 6,  'valuation inspection'),
    (27, '2024-02-16', 4,  'valuation made'),
    (28, '2024-02-17', 5,  'documentation'),
    (29, '2024-02-19', 4,  'valuation submitted'),
    (30, '2024-02-20', 5,  'valuation inspection'),
    (31, '2024-02-21', 8,  'full application'),
    (32, '2024-02-22', 5,  'valuation made'),
    (33, '2024-02-23', 9,  'full application'),
    (34, '2024-02-25', 6,  'valuation made'),
    (35, '2024-02-27', 5,  'valuation submitted'),
    (36, '2024-02-27', 6,  'valuation submitted'),
    (37, '2024-02-29', 9,  'decision'),
    (38, '2024-02-29', 10, 'full application'),
    (39, '2024-03-01', 9,  'documentation'),
    (40, '2024-03-02', 8,  'decision'),
    (41, '2024-03-05', 11, 'full application'),
    (42, '2024-03-07', 9,  'valuation inspection'),
    (43, '2024-03-07', 12, 'full application'),
    (44, '2024-03-08', 9,  'valuation made'),
    (45, '2024-03-08', 13, 'full application'),
    (46, '2024-03-10', 10, 'decision'),
    (47, '2024-03-12', 12, 'decision'),
    (48, '2024-03-15', 10, 'documentation'),
    (49, '2024-03-15', 13, 'decision'),
    (50, '2024-03-16', 11, 'decision'),
    (51, '2024-03-17', 13, 'documentation'),
    (52, '2024-03-18', 9,  'valuation submitted'),
    (53, '2024-03-18', 10, 'valuation inspection'),
    (54, '2024-03-20', 13, 'valuation inspection'),
    (55, '2024-03-21', 10, 'valuation made'),
    (56, '2024-03-22', 13, 'valuation made'),
    (57, '2024-03-27', 10, 'valuation submitted'),
    (58, '2024-03-28', 13, 'valuation submitted'),
    (59, '2024-04-12', 6,  'solicitation'),
    (60, '2024-04-17', 6,  'funds released'),
    (61, '2024-04-26', 1,  'solicitation'),
    (62, '2024-05-02', 1,  'funds released'),
    (63, '2024-05-17', 5,  'solicitation'),
    (64, '2024-05-28', 5,  'funds released'),
    (65, '2024-06-03', 9,  'solicitation'),
    (66, '2024-06-04', 9,  'funds released')
;

The solution can be found at:

funnel-analytics.md

Sample input

event_id	event_date	mortgage_id	stage
1	2024-01-02	1	full application
2	2024-01-06	1	decision
3	2024-01-12	1	documentation
4	2024-01-14	1	valuation inspection
5	2024-01-27	1	valuation made
6	2024-02-02	1	valuation submitted
7	2024-04-26	1	solicitation

with applications(event_id, event_date, mortgage_id, stage) as (
    values
        (1, '2024-01-02'::date, 1, 'full application'),
        (2, '2024-01-06'::date, 1, 'decision'),
        (3, '2024-01-12'::date, 1, 'documentation'),
        (4, '2024-01-14'::date, 1, 'valuation inspection'),
        (5, '2024-01-27'::date, 1, 'valuation made'),
        (6, '2024-02-02'::date, 1, 'valuation submitted'),
        (7, '2024-04-26'::date, 1, 'solicitation')
)

Sample output

cohort	stage	mortgages	step_rate	total_rate
2024-01	full application	1	100.00	100.00
2024-01	decision	1	100.00	100.00
2024-01	documentation	1	100.00	100.00
2024-01	valuation inspection	1	100.00	100.00
2024-01	valuation made	1	100.00	100.00
2024-01	valuation submitted	1	100.00	100.00
2024-01	solicitation	1	100.00	100.00
2024-01	funds released	0	0.00	0.00

solution(cohort, stage, mortgages, step_rate, total_rate) as (
    values
        ('2024-01', 'full application',     1, 100.00, 100.00),
        ('2024-01', 'decision',             1, 100.00, 100.00),
        ('2024-01', 'documentation',        1, 100.00, 100.00),
        ('2024-01', 'valuation inspection', 1, 100.00, 100.00),
        ('2024-01', 'valuation made',       1, 100.00, 100.00),
        ('2024-01', 'valuation submitted',  1, 100.00, 100.00),
        ('2024-01', 'solicitation',         1, 100.00, 100.00),
        ('2024-01', 'funds released',       0,   0.00,   0.00)
)

Hint 1

Determine each row's cohort before calculating the rates.

Hint 2

Use window functions to compare the current row with the historic rows.