Skip to main content

Analyzing User Retention with Databend

User retention helps you analyze how many users return to your product or service. Let's go through an example and see how to analyze it in Databend.

It's easy and performance to use Databend Retention Function to do the user retention analysis.

Step 1. Databend​

1.1 Deploy Databend​

Make sure you have installed Databend, if not please see:

1.2 Create a Databend User​

Connect to Databend server with MySQL client:

mysql -h127.0.0.1 -uroot -P3307

Create a user:

CREATE USER user1 IDENTIFIED BY 'abc123';

Grant privileges for the user:

GRANT ALL ON *.* TO user1;

See also How To Create User.

1.3 Create a Table​

Connect to Databend server with MySQL client:

mysql -h127.0.0.1 -uuser1 -pabc123 -P3307 
CREATE TABLE events(`user_id` INT, `login_date` DATE);

We have a table with the following fields:

  • user_id - a unique identifier for user
  • login_date - user login date

Prepare data:

INSERT INTO events SELECT number AS user_id, '2022-05-15' FROM numbers(1000000);
INSERT INTO events SELECT number AS user_id, '2022-05-16' FROM numbers(900000);
INSERT INTO events SELECT number As user_id, '2022-05-17' FROM numbers(100000);

Step 2. User Retention Analysis​

SELECT
    sum(r[1]) AS r1,
    sum(r[2]) AS r2,
    sum(r[3]) AS r3
FROM
(
    SELECT
        user_id,
        retention(login_date = '2022-05-15', login_date = '2022-05-16', login_date = '2022-05-17') AS r
    FROM events
    GROUP BY user_id
);

The retention result is:

+---------+--------+--------+
| r1      | r2     | r3     |
+---------+--------+--------+
| 1000000 | 900000 | 100000 |
+---------+--------+--------+