Registrations and active users

Analyzing Business Data in SQL

Michel Semaan

Data Scientist

User-centric KPIs

KPIs

Registrations
Active users
Growth
Retention

Benefits

Measure performance well in B2Cs
Used by investors to assess pre-revenue and -profit startups

Registrations - overview

Registration: When a user first signs up for an account on Delivr through its app
Registrations KPI: Counts registrations over time, usually per month
- Good at measuring a company's success in attracting new users
For Delivr, a user's registration date is the date of that user's first order

Registrations - setup

Query

SELECT
  user_id,
  MIN(order_date) AS reg_date
FROM orders
GROUP BY user_id
ORDER BY user_id
LIMIT 3;

Result

user_id  reg_date  
-------  ----------
0        2018-06-01
1        2018-06-01
2        2018-06-01

Registrations - query

WITH reg_dates AS (
  SELECT
    user_id,
    MIN(order_date) AS reg_date
  FROM orders
  GROUP BY user_id)

SELECT
  DATE_TRUNC('month', reg_date) :: DATE AS delivr_month,
  COUNT(DISTINCT user_id) AS regs
FROM reg_dates
GROUP BY delivr_month
ORDER BY delivr_month ASC
LIMIT 3;

Registrations - result

Result

delivr_month  regs
------------  ----
2018-06-01      123
2018-07-01      140
2018-08-01      157

Active users - overview

Active users KPI: Counts the active users of a company's app over a time period
- by day (daily active users, or DAU)
- by month (monthly active users, or MAU)
Stickiness (DAU / MAU), measures how often users engage with an app on average
- Example: If Delivr's stickiness is DAU / MAU = 0.3 (30%), users use Delivr for $30% x 30$ days = 9 days each month on average

Active users - query

SELECT
  DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
  COUNT(DISTINCT user_id) AS mau
FROM orders
GROUP BY delivr_month
ORDER BY delivr_month ASC
LIMIT 3;

delivr_month  mau
------------  ---
2018-06-01      123
2018-07-01      226
2018-08-01      337

Registrations and active users

Analyzing Business Data in SQL