主持人 > 博客 > 管理 > 如何在 Grafana 中以圖表形式收集使用者群組 [+ docker 映像和範例]

如何在 Grafana 中以圖表形式收集使用者群組 [+ docker 映像和範例]

如何在 Grafana 中以圖表形式收集使用者群組 [+ docker 映像和範例]

我們如何使用 Grafana 解決 Promopult 服務中使用者群體視覺化的問題。

推動 - 擁有大量用戶的強大服務。 運行10年來，系統註冊量已突破XNUMX萬。 遇過類似服務的人都知道，這群用戶遠非同質。

有人報名後就永遠「睡著了」。 有人忘記了密碼，並在六個月內又註冊了幾次。 有人把錢帶到收銀台，有人來領取免費贈品 儀器。 如果能從每個人身上獲得一些利潤那就太好了。

在像我們這樣的大數據集上，分析單一使用者的行為並做出微觀決策是毫無意義的。 但捕捉趨勢並與大型團體合作是可能且必要的。 這正是我們所做的。

總結

  1. 什麼是隊列分析以及為什麼需要它？
  2. 如何在 SQL 中按使用者註冊月份建立群組。
  3. 如何將群組轉移至 格拉法納.

如果您已經知道什麼是群組分析以及如何在 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

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。

範例圖來自 演示:

如何在 Grafana 中以圖表形式收集使用者群組 [+ docker 映像和範例]

用手觸摸：

帶有範例的 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/

來源： www.habr.com

添加評論