1. Registrations and active users
Welcome back!
In the previous chapter, you calculated revenue, cost, and profit. In particular, you learned that profit is a core KPI for companies.
In this chapter, you'll learn about other, more user-centric KPIs.
2. User-centric KPIs
This chapter covers four important KPIs: Registrations, active users, growth, and retention. Revenue, cost, and profit were purely financial KPIs, but these KPIs track user activity.
Why are these user-centric KPIs important? They measure performance well in B2Cs, business-to-customer companies that target the mass market.
They're especially used by investors to assess B2C startups, particularly those that haven't generated any revenue or profit.
3. Registrations - overview
Let's start with registrations.
For startups like Delivr, a registration happens when a user first signs up for, or registers, an account on a mobile app or a webapp. The registrations KPI counts registrations over time, usually per month. It's good at measuring a company's success in attracting new users.
To calculate this KPI, you'll need each user's registration date. Usually, companies store registration dates in a table containing users' metadata.
Delivr doesn't have such a table. Instead, a user's registration date is the date of that user's first order using Delivr's app.
Let's write a query to count Delivr's registrations in each month.
4. Registrations - setup
To calculate the registrations KPI, you must first get each user's registration date.
When MIN is used on a date column, it returns the earliest date in that column. Since you're using it on the order date column and you're grouping by user ID, it returns the date of each user's first order in the reg_date column.
5. Registrations - query
Store the previous query's results in a CTE to store each user's registration date in the reg_dates CTE.
In the final query, truncate the reg_date column to the month to get the month in which each registration occurs. Then, count the distinct user IDs in each month. This counts the registrations in each month in the regs column. Order by month and limit to 3 to return the first 3 months' registrations.
6. Registrations - result
This is the result, a table of registrations by month. In June 2018, 123 new users registered for accounts on Delivr. In July 2018, 140 users did so, and so on.
7. Active users - overview
Let's move to the active users KPI.
Whereas the registrations KPI focused on new users, the active users KPI focuses on existing users. The active users KPI counts the active users of a company's app over a time period, usually by day (called daily active users, or DAU) or by month (called monthly active users, or MAU).
Stickiness, the ratio of DAU to MAU, measures how often users engage with a service on average. For example, if Delivr's stickiness is 30%, then users use Delivr for 30% * 30 days in a month = 9 days each month on average.
8. Active users - query
This query returns the first three months' MAU.
To calculate MAU, truncate the order date to the month and count the distinct user IDs in each month. Each user with an order in a month is active in that month.
In June 2018, Delivr had 123 active users; in July, Delivr had 226 active users, and so on.
9. Registrations and active users
Registrations and active users are the two main user-centric KPIs. Practice writing queries to calculate them in the following exercises.