What Fraction of Signups Convert to a Purchase?
Kindled is putting together an investor update and one line on it is the headline funnel number: of the people who signed up, what share went on to buy something? It has to be a single figure, comparable with the same figure next quarter, so the definition matters more than the arithmetic.
Two details settle it. The bottom of the fraction is everybody who signed up, which is the user list itself. The top is people, not purchases — somebody who bought three times is one converted person, and the event log records one row per purchase, so counting purchase rows would overstate the answer for exactly the customers a business most wants.
users — one row per signed-up person. source is the channel that brought them in.
| id | name | source | signup_date |
|---|---|---|---|
| 1 | Aiden Cole | organic | 2023-05-01 |
| 2 | Bianca Reyes | paid_search | 2023-05-02 |
| 3 | Carlos Mora | organic | 2023-05-03 |
| 4 | Delia Frank | referral | 2023-05-04 |
| 5 | Ewan Blake | paid_search | 2023-05-05 |
| 6 | Fiona Grey | organic | 2023-05-06 |
| 7 | Gus Herrera | referral | 2023-05-07 |
| 8 | Hana Ito | paid_search | 2023-05-08 |
events — one row per action a user took. event_type is the kind of action.
| id | user_id | event_type | event_date |
|---|---|---|---|
| 1 | 1 | signup | 2023-05-01 |
| 2 | 1 | view_product | 2023-05-01 |
| 3 | 1 | add_to_cart | 2023-05-02 |
| 4 | 1 | purchase | 2023-05-03 |
| 5 | 2 | signup | 2023-05-02 |
| 6 | 2 | view_product | 2023-05-02 |
| 7 | 3 | signup | 2023-05-03 |
| 8 | 3 | view_product | 2023-05-03 |
| 9 | 3 | add_to_cart | 2023-05-04 |
| 10 | 4 | signup | 2023-05-04 |
| 11 | 4 | view_product | 2023-05-05 |
| 12 | 4 | add_to_cart | 2023-05-05 |
| 13 | 4 | purchase | 2023-05-06 |
| 14 | 5 | signup | 2023-05-05 |
| 15 | 6 | signup | 2023-05-06 |
| 16 | 6 | view_product | 2023-05-06 |
| 17 | 7 | signup | 2023-05-07 |
| 18 | 7 | view_product | 2023-05-07 |
| 19 | 7 | add_to_cart | 2023-05-08 |
| 20 | 7 | purchase | 2023-05-09 |
| 21 | 8 | signup | 2023-05-08 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single row with a single column, conversion_rate: the share of signed-up users who have at least one purchase event, as a value between 0 and 1, rounded to 4 decimal places. Somebody who bought several times counts once. Every row of users counts towards the bottom of the fraction, whether or not they ever did anything.
Example output — shape only. If a little under two thirds of the signups had gone on to buy, the single row would read:
| conversion_rate |
|---|
| 0.6154 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in