7-Day Activation Rate by Channel
A user is activated if they created a project within 7 days (168 hours) of their signup event. Compute the activation rate for each acquisition channel.
Expected output
acquisition_channel, users, activated, activation_pct (rounded to 1 decimal). Sorted by activation_pct descending, then acquisition_channel.
Row order is checked. Column names are not checked; numbers are compared to 2 decimals.
Dataset: B2B SaaS product events
A project-management SaaS: company accounts, their users, product event logs (Jan to Apr 2026) and subscription history.
accounts
One row per customer company.
| account_id | INTEGER | primary key |
| company | TEXT | |
| industry | TEXT | |
| country | TEXT | |
| created_at | TEXT | YYYY-MM-DD |
users
One row per user. Every user belongs to one account.
| user_id | INTEGER | primary key |
| account_id | INTEGER | |
| signup_date | TEXT | YYYY-MM-DD, same day as the user's signup event |
| acquisition_channel | TEXT | organic, paid_search, referral or partner |
| country | TEXT |
events
Product event log. Data ends on 2026-04-30.
| event_id | INTEGER | primary key, increases with event_time |
| user_id | INTEGER | |
| event_name | TEXT | signup, login, view_dashboard, export_report, create_project, invite_teammate, upgrade_click |
| event_time | TEXT | YYYY-MM-DD HH:MM:SS (UTC) |
subscriptions
Paid subscription periods per account. An account with no rows is on the free plan.
| subscription_id | INTEGER | primary key |
| account_id | INTEGER | |
| plan | TEXT | team or business |
| mrr | REAL | monthly recurring revenue in USD |
| started_at | TEXT | YYYY-MM-DD, inclusive |
| ended_at | TEXT | YYYY-MM-DD, exclusive; NULL while active. May be in the future for a scheduled cancellation. |
Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.
Related problems
- Revenue by Product Category (easy)
- Customers Who Never Ordered (easy)
- Monthly Revenue Trend (easy)
- Quarterly Cancellation Rate (medium)
- Days from Signup to First Order (medium)