Product metrics

The main task of any commercial product is to bring profit. To do this, it is necessary that the product has as wide an audience as possible and that users of the product buy from it as often as possible. The task of a product analyst is to constantly observe how profit changes, find the reasons for changes and look for profit growth points. Product metrics help the analyst to do this.

A product metric is a numerical value that reflects how well a product is moving toward its business goal.

For example, at the beginning of 2025, the managers of an online movie theater set a goal of increasing the audience from 1 million to 5 million users per month by the beginning of next year. To understand how close the goal is, the movie theater team asks an analyst to count the number of active users each month. The number of active users is a product metric.

Types of product metrics:

There is no universally accepted classification of product metrics. It is quite possible that in the process you will come up with your own classification. This one reflects the main aspects of product analysis.

· Audience metrics describe the size and dynamics of the product’s active audience. Usually DAU and MAU are analyzed — the number of unique users who used the product during the day and month; the share of users who use different features of the service; the number of new visitors.

· Satisfaction metrics are retention and NPS. Retention evaluates how actively users continue to use the product. NPS shows how satisfied users are with the quality of the product.

· User engagement metrics allow you to determine whether the actual behavior of active users of the product coincides with the expected behavior. Two metrics are especially important: conversion — the share of new visitors who made their first purchase, and the average number of targeted actions.

These three types of metrics can be combined into “user metrics”, as they show the number of users in the product and describe their behavior.

· Monetization metrics describe everything from product revenue to user payment behavior. Obviously, revenue is especially important in business, so monetization is studied from many different angles. They examine revenue per month, average check, revenue per user over the entire time the product is in use (LTV), the cost of advertising that helps attract new users to the product (CAC), and return on advertising (ROI, or ROMI).

· Performance metrics serve to monitor the technical health of a product. They allow you to find out what bugs users encounter when using the product, or to make sure there are no bugs. Analysts pay attention to session length, average number of bugs per session, and average number of sessions per user per day.

In the following article , you will learn about the metrics of the All.Kino online movie theater and explore how well it is performing.
The plan is as follows: familiarize yourself with the type of metrics, learn more about each of them, understand by example how to count them, and then solve a problem.

Cheat sheets and helpful materials

Audience metrics (DAU, MAU, sticky factor)

For a product to be profitable, someone needs to use it. That is why analysts study the dynamics of the number of users of the system. Audience metrics help in this:

· number of active users (DAU and MAU),

· number of new users.

In addition to audience size, a product analyst examines user behavior. For this purpose, he calculates the shares of active users who use the main product features.

In this article, you will learn what DAU and MAU mean and how to calculate them using SQL queries. There will be a lot of code.

DAU and MAU

DAU is an abbreviation for daily active users, . This is the simplest and most commonly used audience metric.

DAU is the number of unique users who used the product during a calendar day.

DAU shows how interesting the product is to users. The more users who use the service on a daily basis, the more money the product usually brings in. If the analyst sees that the DAU has significantly decreased, he informs the project team to understand the reasons and make decisions.

The number of users is estimated not only by days, but also by months. For this purpose, the MAU metric is used. MAU is an abbreviation for monthly active users.

MAU is the number of unique users who used the product during a calendar month.

It is incorrect to count MAU as the sum of DAUs. If a user accessed the application several times a month, he or she will be counted in MAU several times.
There are different situations when a product analyst should pay attention to MAU and when to DAU. Try to solve a product problem: choose which metric you want to calculate — DAU or MAU.

Sticky factor

DAU and MAU are combined into another popular metric, the sticky factor. The sticky factor is calculated using the formula:

To develop a product mindset, it is helpful for the analyst to look “under the hood” of formulas and understand what the value of a particular metric is. Analyze a factor sticky formula and determine what it helps to evaluate.

Task: DAU Calculation using SQL

1. Calculating the total DAU

Let’s start with a simple task:

Calculate the total DAU grouped by days. To make the query work faster, do not count all types of events, but take only analytical events like “pageOpen”. Every active user sends at least one such event when logging into the product.

SELECT 
    log_date,
    COUNT(DISTINCT user_id) AS unique_users
FROM
    events_log
WHERE
    name = 'pageOpen'
GROUP BY
    log_date;

2. Calculating the total DAU

Let’s move on to more complex tasks. Yura, a product manager, comes to you and says that six months ago the share of Android users in the theater was 10%, and recently the product team released a new and improved version of Android. Yura asks for an estimate of what percentage of the active audience are Android users now.

WITH dau AS
(
  SELECT
    log_date,
    app_id, -- Specify platform grouping: using the 'app_id' column
    COUNT(DISTINCT user_id) AS uniques
  FROM events_log
  WHERE name = 'pageOpen'
  GROUP BY log_date, app_id -- Specify platform grouping: grouping by 'log_date' and 'app_id'
)
SELECT *
FROM
(
  SELECT *,
    SUM(uniques) OVER (PARTITION BY log_date) AS total_dau,
    CAST(uniques AS FLOAT) / SUM(uniques) OVER (PARTITION BY log_date) AS perc_dau
  FROM dau
) r
WHERE app_id = 'Android' -- Specify condition for the Android platform 
ORDER BY log_date;

Other audience metrics

In this part, you’ll learn about the metrics:
. number of new users
. the share of feature usage
. average number of target actions

Number of new users

Businesses are constantly attracting new users to the product. Therefore, not only the active audience of the product is evaluated, but also the number of attracted users. If the application requires registration, the number of new users is the number of new registrations. If the application does not require registration, the number of fresh installations is analyzed.
The DAU, MAU, and sticky-factor metrics and the number of new users examine audience size. But depending on the business problem, only one or two of them may be indicative.

Calculation of the number of newcomers and their share of the total DAU

New users can be considered to be those who sent analytic events on the day they started using the product. In other words, to calculate the DAU of newcomers, only those records with log_date coinciding with install_date are selected from the event log and their number is calculated:

SELECT log_date, 
       COUNT(DISTINCT user_id) as uniques 
FROM events_log 
/* Condition for newcomers: users who logged in on the same day they installed the application */
WHERE log_date = install_date
GROUP BY log_date
ORDER BY log_date;

This means that 400–500 new users start using the online cinema every day.
Let’s estimate the share of newcomers in the total DAU. To do this, let’s implement three steps. Let’s break them down one by one, and then combine them into a final query.

So, we calculate the share of newcomers from the total DAU:

Let’s split the DAU into age groups using the dau subquery. Users whose install_date coincides with the current log_date will be grouped into the “New Users” group, and all other users will be grouped into the “Old Users” group.

1. Let’s define the logic of the groups using the CASE operator.

dau AS (
    /* Step 1. Calculation of DAU with age breakdown */

    SELECT log_date, 
           CASE 
               WHEN install_date = log_date THEN 'New Users' 
               ELSE 'Existing Users'
           END AS new_old, 
           COUNT(DISTINCT user_id) AS uniques 
    FROM events_log 
    GROUP BY log_date,
             CASE 
                 WHEN install_date = log_date THEN 'New Users' 
                 ELSE 'Existing Users'
             END
    ORDER BY log_date, uniques DESC
)

2. Let’s calculate the total DAU and the proportion of DAUs in each age group in the perc_dau subquery. SQL Window functions will help in this, the logic of calculations is similar to the logic in the previous example.

perc_dau AS (   
    /* Step 2. Calculation of DAU percentages */

    SELECT log_date,
           new_old,
           uniques,
           SUM(uniques) OVER (PARTITION BY log_date) AS total_dau,          -- Total DAU for the current day
           CAST(uniques AS FLOAT) / SUM(uniques) OVER (PARTITION BY log_date) AS perc_dau   -- Percentage of total DAU
    FROM dau
)

3. In the last step, we will select only the rows that correspond to new users from the perc_dau results using the condition WHERE new_old = ‘New Users’.

/* Step 3. Final Filtering */

SELECT * FROM perc_dau
WHERE new_old = 'New Users';

Let’s compile the final code. The first two steps are subqueries using the WITH operator.

WITH dau AS (
    -- Step 1: Calculate DAU with Age Breakdown
    SELECT
        log_date,
        CASE WHEN install_date = log_date THEN 'New Users' ELSE 'Existing Users' END AS user_type,
        COUNT(DISTINCT user_id) AS unique_users
    FROM events_log
    GROUP BY log_date, CASE WHEN install_date = log_date THEN 'New Users' ELSE 'Existing Users' END
),

perc_dau AS (
    -- Step 2: Calculate DAU Percentages
    SELECT
        log_date,
        user_type,
        unique_users,
        SUM(unique_users) OVER (PARTITION BY log_date) AS total_dau,
        CAST(unique_users AS FLOAT) / SUM(unique_users) OVER (PARTITION BY log_date) AS user_percentage
    FROM dau
)

-- Step 3: Final Filtering
SELECT * FROM perc_dau
WHERE user_type = 'New Users';

result:

“новые пользователи” = “new user”

It turns out that new users make up only 6–7% of DAU. We can say that the project’s audience is kept mainly by the “old-timers”

Percentage of feature usage and average number of target actions

Knowing the size of the audience is not enough, it is important to understand user behavior. To analyze it, we estimate what share of users uses the main feature of the product and how many target actions are taken by each active user.
The share of users of a feature is calculated using the formula

For an online movie theater, movie watching is considered the main feature.
Let’s calculate the DAU share of users who watched at least one movie per day:

SELECT
    log_date,
    COUNT(DISTINCT user_id) as dau,
    COUNT(DISTINCT CASE WHEN name = 'startMovie' THEN user_id ELSE NULL END) as feature_dau,
    CAST(COUNT(DISTINCT CASE WHEN name = 'startMovie' THEN user_id ELSE NULL END) AS FLOAT) / CAST(COUNT(DISTINCT user_id) AS FLOAT) as perc_feature_dau
FROM events_log 
GROUP BY log_date
ORDER BY log_date;

The result of the query shows that usually about 70% of DAU start watching the movie. Of course, the product team would like to see movies watched by 100% of the active audience, but 70% is not a bad result either.
Now let’s complete the analysis and estimate how many movie starts per day there are per active user. Let’s calculate the average number of movies using the formula:

You can solve the problem of calculating the average number of target actions with a query:

SELECT log_date, 
       COUNT(DISTINCT user_id) as dau,
       COUNT(DISTINCT CASE WHEN name = 'startMovie' THEN user_id ELSE NULL END) as feature_dau,
       CAST(COUNT(DISTINCT CASE WHEN name = 'startMovie' THEN user_id ELSE NULL END) AS FLOAT) / CAST(COUNT(DISTINCT user_id) AS FLOAT) as perc_feature_dau,
       SUM(CASE WHEN name = 'startMovie' THEN 1 ELSE 0 END) as events,
       CAST (SUM(CASE WHEN name = 'startMovie' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(DISTINCT CASE WHEN name = 'startMovie' THEN user_id ELSE NULL END) AS FLOAT) as mean_events    
FROM events_log 
GROUP BY log_date
ORDER BY log_date 

Well, well, well, each visitor starts watching an average of 1.2 movies a day.
This is generally expected behavior. Hardly an average viewer finds time to watch more than one movie a day.

Happing coding ….