🥇How to build user cohorts in the form of charts in Grafana [+docker image with example]

How we solved the problem of visualizing user cohorts in the Promopult service using Grafana.

Promopult — a powerful service with a large number of users. In 10 years of operation, the number of registrations in the system has exceeded a million. Those who have encountered similar services know that this user base is far from homogeneous.

Some registered and then 'fell asleep' forever. Some forgot their passwords and registered a couple more times within six months. Some are contributing money, while others are just here for the free tools. It would be great to derive some profit from each of them.

With such large datasets as ours, analyzing the behavior of individual users and making micro-decisions is pointless. However, catching trends and working with large groups is both possible and necessary. That’s what we are actually doing.

Summary

What is cohort analysis and why is it needed.
How to create cohorts by user registration month in SQL.
How to transfer cohorts to Grafana.

If you already know what cohort analysis is and how to do it in SQL, feel free to jump to the last section.

1. What is cohort analysis and why is it needed

Cohort analysis is a method based on comparing different groups (cohorts) of users. Most often, we form groups by the week or month in which the user started using the service. From this, the user lifespan is calculated, which is already an indicator on the basis of which more complex analysis can be conducted. For example, to understand:

how the acquisition channel affects user lifespan;
how the usage of a particular feature or service impacts lifespan;
how the launch of feature X affected lifespan compared to last year.

2. How to create cohorts in SQL?

The length of the article and common sense do not allow us to present real data here — in the test dump, the stats cover a year and a half: 1,200 users and 53,000 transactions. To allow you to experiment with this data, we have prepared a docker image with MySQL and Grafana, where you can explore everything yourself. The link to GitHub is at the end of the article.

Here we will show the creation of cohorts using a simplified example.

Let's suppose we have a service. Users register and spend money on services. Over time, users drop off. We want to know how long users stay and how many drop off after the 1st and 2nd month of using the service.

To answer these questions, we need to build cohorts by registration month. We will measure activity based on spending in each month. Instead of expenses, it could be orders, subscriptions, or any other time-bound activity.

Source Data

The examples are made in MySQL, but there should be no significant differences for other DBMS.

Users table - users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Billing table - billing:

userId
Date
Sum

1
2019-01-02
11

1
2019-02-22
11

2
2019-02-12
12

3
2019-02-11
13

3
2019-03-11
13

4
2019-03-01
14

4
2019-03-02
14

We select all user charges and registration dates:

SELECT 
  b.userId, 
  b.Date,
  u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Result:

userId
Date
RegistrationDate

1
2019-01-02
2019-01-02

1
2019-02-22
2019-01-02

2
2019-02-12
2019-02-01

3
2019-02-11
2019-02-10

3
2019-03-11
2019-02-10

4
2019-03-01
2019-03-01

4
2019-03-02
2019-03-01

We build cohorts by month, for this we convert all dates to months:

DATE_FORMAT(Date, '%Y-%m')

Now we need to know how many months the user was active — this is the difference between the month of charge and the month of registration. MySQL has a PERIOD_DIFF() function — the difference between two months. We add PERIOD_DIFF() to the query:

SELECT
    b.userId,
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

userId
BillingMonth
RegistrationDate
MonthsDiff

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

3
2019-02
2019-02
0

3
2019-03
2019-02
1

4
2019-03
2019-03
0

We count the activated users in each month — group records by BillingMonth, RegistrationMonth, and MonthsDiff:

SELECT
    COUNT(DISTINCT(b.userId)) AS UsersCount,
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
GROUP BY BillingMonth, RegistrationMonth, MonthsDiff

Result:

UsersCount
BillingMonth
RegistrationMonth
MonthsDiff

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

1
2019-03
2019-02
1

1
2019-03
2019-03
0

In January, February, and March, there was one new user each — MonthsDiff = 0. One January user was active in February — RegistrationMonth = 2019-01, BillingMonth = 2019-02, as was one February user active in March.

With a large dataset, patterns are naturally more visible.

How to transfer cohorts to Grafana

We learned to create cohorts, but when the records become numerous, analyzing them is no longer easy. Records can be exported to Excel to create nice tables, but that's not our method!

Cohorts can be displayed as an interactive graph in Grafana.

To do this, we add another query to convert the data into a format suitable for Grafana:

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## old query returning cohorts
  SELECT 
    COUNT(DISTINCT(b.userId)) AS Users, 
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
  FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
  WHERE
    u.RegistrationDate BETWEEN '2018-01-01' AND CURRENT_DATE
  GROUP BY 
    BillingMonth, RegistrationMonth, MonthsDiff 
) AS s
GROUP BY 
  time_sec, metric

And we export the data to Grafana.

An example chart from demo:

Get hands-on:

GitHub repository with the example is a docker image with MySQL and Grafana that can be run on your computer. The database already has demo data for a year and a half, from January 2018 to July 2019.

If desired, you can upload your own data into this image.

P.S. Articles on cohort analysis in SQL:

https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/

https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/

Source: habr.com