🥇如何在 Grafana 中以图表的形式收集用户群体 [+带有示例的 docker 图像]

我们如何使用 Grafana 解决 Promopult 服务中用户群体可视化的问题。

推动 - 拥有大量用户的强大服务。运行10年来，系统注册量已突破XNUMX万。那些遇到过类似服务的人都知道，这群用户远非同质。

有人报名后就永远“睡着了”。有人忘记了密码，并在六个月内又注册了几次。有人把钱带到收银台，有人来领取免费赠品仪器。如果能从每个人身上获得一些利润那就太好了。

在像我们这样的大数据集上，分析单个用户的行为并做出微观决策是毫无意义的。但捕捉趋势并与大型团体合作是可能且必要的。这正是我们所做的。

摘要

什么是队列分析以及为什么需要它？
如何在 SQL 中按用户注册月份创建群组。
如何将群组转移至格拉法纳.

如果您已经知道什么是群组分析以及如何在 SQL 中进行分析，请直接跳到最后一部分。

1. 什么是队列分析以及为什么需要它？

群组分析是一种基于比较不同用户群组（群组）的方法。大多数情况下，我们的组是根据用户开始使用该服务的周或月组成的。从这里计算用户的生命周期，这是一个可以进行相当复杂的分析的指标。例如理解：

获取渠道如何影响用户的生命周期；
任何功能或服务的使用如何影响生命周期；
与去年相比，功能 X 的推出对寿命有何影响。

2.如何在SQL中创建队列？

文章的篇幅和常识不允许我们在这里展示我们的真实数据 - 在测试转储中，一年半的统计数据：1200 个用户和 53 笔交易。为了让您可以使用这些数据，我们准备了一个包含 MySQL 和 Grafana 的 docker 镜像，您可以在其中亲自体验这一切。文章末尾有 GitHub 链接。

在这里，我们将使用一个简化的示例来展示群组的创建。

假设我们有一项服务。用户在那里注册并花钱购买服务。随着时间的推移，用户会退出。我们想知道用户的寿命有多长，以及有多少人在使用该服务的第一个月和第二个月后就消失了。

为了回答这些问题，我们需要根据注册月份构建队列。我们将按每个月的支出来衡量活动。可能不是费用，而是订单、订阅费或任何其他基于时间的活动。

初始数据

这些示例是在 MySQL 中制作的，但对于其他 DBMS 来说应该没有任何显着差异。

用户表-用户：

用户身份
登记日期

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

费用表-计费：

用户身份
日期
总和

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

选择所有用户注销和注册日期：

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

结果：

用户身份
日期
登记日期

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

我们按月建立群组；为此，我们将所有日期转换为月份：

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

现在我们需要知道用户活跃了多少个月 - 这是借记月份和注册月份之间的差异。 MySQL 有一个函数 PERIOD_DIFF() - 两个月之间的差异。将 PERIOD_DIFF() 添加到请求中：

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

用户身份
帐单月份
登记日期
月差

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

我们计算每个月激活的用户 - 我们按 BillingMonth、RegistrationMonth 和 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

结果：

用户数
帐单月份
注册月份
月差

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

0 月、2019 月和 01 月，各出现一名新用户 - MonthsDiff = 2019。一名 02 月用户在 XNUMX 月处于活跃状态 - RegistrationMonth = XNUMX-XNUMX，BillingMonth = XNUMX-XNUMX，一名 XNUMX 月用户在 XNUMX 月份处于活跃状态。

当然，在大型数据集中，模式会更明显。

如何将群组转移到 Grafana

我们已经学会了如何形成群组，但是当记录很多时，分析它们就不再容易了。记录可以导出到Excel并形成漂亮的表格，但这不是我们的方法！

群组可以显示为交互式图表格拉法纳.

为此，我们添加另一个查询将数据转换为适合 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 (
  ## старый запрос, возвращающий когорты
  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

并将数据上传到Grafana。

示例图来自演示:

用手触摸：

带有示例的 GitHub 存储库是一个带有 MySQL 和 Grafana 的 docker 镜像，可以在您的计算机上运行。该数据库已包含从 2018 年 2019 月到 XNUMX 年 XNUMX 月一年半的演示数据。

如果您愿意，您可以将数据加载到该图像中。

PS 有关 SQL 队列分析的文章：

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

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

来源： habr.com

如何在 Grafana 中以图表形式收集用户群组 [+ 带有示例的 docker 图像]

摘要

1. 什么是队列分析以及为什么需要它？

2.如何在SQL中创建队列？

初始数据

如何将群组转移到 Grafana

添加评论取消回复

如何在 Grafana 中以图表形式收集用户群组 [+ 带有示例的 docker 图像]

摘要

1. 什么是队列分析以及为什么需要它？

2.如何在SQL中创建队列？

初始数据

如何将群组转移到 Grafana

添加评论 取消回复

添加评论取消回复