Level 5 · Activation rate by signup quarter

Activation asks: of the people who signed up, how many did the thing that makes them a real customer, within a fixed time? At Harbor & Pine that thing is a first completed order within 30 days of signup. It is a cohort metric: you group people by when they joined, not by when they bought.

Start from the people, not the events

The denominator is signups, so the query starts from customers and attaches each person’s first completed order with a LEFT JOIN:

WITH first_orders AS (
  SELECT customer_id, MIN(CAST(order_date AS DATE)) AS first_order_day
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT c.customer_id, c.signup_date,
       f.first_order_day - c.signup_date AS days_to_first_order
FROM customers AS c
LEFT JOIN first_orders AS f ON f.customer_id = c.customer_id;

Subtracting two DATEs in DuckDB and Postgres gives a whole number of days. Customers who never completed an order get NULL, and a NULL never passes <= 30, so they count as not activated, which is exactly right.

Worked example: 2024 to Q3 2025

signup_quarter	signups	activated_30d	activation_pct
2024-01-01	105	72	68.6
2024-07-01	111	81	73.0
2025-04-01	171	104	60.8
2025-07-01	171	102	59.6

Activation has drifted from around 70% in 2024 to around 60% in mid-2025. That is a real signal worth raising, with one honest caveat: these cohorts are 105 to 235 people, so a few points of movement between neighboring quarters can be noise.

Censoring: why the task stops at Q3 2025

The data ends on 2025-12-31. Someone who signed up on December 20 has had 11 days, not 30, to place an order. Run the same query by month and December 2025 shows 45.2% activation against 58.2% in November. December customers are not worse; they have not had time yet. This is called right censoring, and the fix is to report only cohorts whose full window has passed.

Common mistakes

• Inner join to orders. Non-buyers disappear from the denominator.
• Using any order instead of the first. “Ordered within 30 days” means the first completed order, not any order whose date happens to be close to signup.
• Mixing signup cohorts with order dates. Group by signup quarter; the order date only decides whether someone activated.

In SQL Server

Use DATEDIFF(day, c.signup_date, f.first_order_day) <= 30, since subtracting dates is not allowed. Quarter starts come from DATEFROMPARTS(YEAR(signup_date), (DATEPART(quarter, signup_date) - 1) * 3 + 1, 1), or DATETRUNC(quarter, signup_date) in SQL Server 2022 and later.