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

我们如何解决使用Grafana在Promopult服务中可视化用户群组的任务。

Promopult是一项功能强大的服务，拥有大量用户。 在运行的10年中，系统中的注册数量已超过一百万。 那些曾经使用过类似服务的人都知道，这一系列用户绝非同质。

有人注册并永久“入睡”。 有人忘记了密码，并在六个月内注册了两次。 有人带钱给收银员，有人来找免费工具 。 从每个人那里获得一些利润将是一件很高兴的事。

在像我们这样的大数据数组上，分析单个用户的行为并做出微观决策没有任何意义。 但是，赶上趋势并与大型团队合作是可能且必要的。 实际上，我们正在做什么。

总结

1. 什么是同类群组分析，为什么需要。
2. 如何按SQL中的用户注册月份进行分组。
3. 如何将队列转移到Grafana 。

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

1.什么是同类群组分析，为什么需要

同类群组分析是一种基于不同用户组（同类群组）比较的方法。 通常，我们的组是由用户开始使用服务的星期或月份组成的。 用户的生命周期是从此处计算出来的，这是一个指标，可以在此基础上进行相当复杂的分析。 例如，了解：

• 渠道如何吸引用户的生活；
• 功能或服务的使用如何影响生命周期；
• 与去年相比，功能X的发布如何影响使用寿命。

2.如何在SQL中进行同类群组？

文章的大小和常识不允许我们在此处显示真实数据-在测试转储中，一年半的统计数据是：1200个用户和53,000个事务。 为了使您可以处理这些数据，我们使用MySQL和Grafana准备了一个docker映像，您可以在其中感觉到所有这些。 在文章末尾链接到GitHub。

在这里，我们以简化的示例展示了同类群组的创建。

假设我们有服务。 用户在其中注册并在服务上花钱。 随着时间的流逝，用户会逐渐减少。 我们想知道用户的寿命长短，以及使用该服务的第一个月和第二个月后其中有多少人掉下来。

要回答这些问题，我们需要按注册月份建立队列。 活动将以每月的支出来衡量。 除了成本之外，还可以有订单，月租费或任何其他有时间限制的活动。

源数据

这些示例是在MySQL中制作的，但是对于其余的DBMS，应该没有太大的区别。

用户表-用户：

支出表-帐单：

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

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

结果：

我们按月建立同类群组，为此，我们会将所有日期转换为月：

 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 

我们计算每月激活的用户-我们按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月，2月和3月，出现了一个新用户-MonthsDiff =0。一个1月用户处于活动状态，而在2月RegistrationMonth = 2019-01，BillingMonth = 2019-02，一个2月用户处于活动状态。

在大型数据集上，自然会更好地看到模式。

如何将群组转移到Grafana

我们学习了如何形成队列，但是当有大量记录时，对其进行分析就不再那么容易了。 记录可以导出到Excel并创建漂亮的表，但这不是我们的方法！

群组可以在Grafana中显示为交互式图表。

为此，添加另一个查询以将数据转换为适用于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年1月到2019年7月，该数据库已经有一年半的演示数据。

如果愿意，可以将数据上传到该图像。

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

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

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