Deviopath › PostgreSQL

Visit, Signup, Purchase Funnel

Hard · CTEs

A funnel counts how many users get through each step of a flow, in order. Build it step by step with CTEs: each step only looks at users who passed the previous step, and only at events that happened after it.

The steps are:

1. visit: the user has at least one visit event. Their visit time is their earliest visit.

2. signup: the user has a signup event strictly later than their visit time. Their signup time is the earliest such signup.

3. purchase: the user has a purchase event strictly later than their signup time.

Events in the wrong order do not count (a signup before any visit is ignored).

Return three rows with stage_no (1, 2, 3), stage (visit, signup, purchase), users (how many users reached the step) and pct_of_visitors (ROUND(100.0 * users / visitors, 1), where visitors is the step 1 count), sorted by stage_no.

Next: Longest Login Streak