ग्राफाना में उपयोगकर्ता समूहों को ग्राफ़ के रूप में कैसे एकत्रित करें [+ उदाहरण के साथ डॉकर छवि]

हमने ग्राफाना का उपयोग करके प्रोमोपल्ट सेवा में उपयोगकर्ताओं के समूहों को देखने की समस्या को कैसे हल किया।

को बढ़ावा देना - बड़ी संख्या में उपयोगकर्ताओं के साथ एक शक्तिशाली सेवा। संचालन के 10 वर्षों में, सिस्टम में पंजीकरण की संख्या दस लाख से अधिक हो गई है। जिन लोगों ने समान सेवाओं का सामना किया है वे जानते हैं कि उपयोगकर्ताओं की यह श्रृंखला सजातीय से बहुत दूर है।

किसी ने साइन अप किया और हमेशा के लिए "सो गया"। कोई अपना पासवर्ड भूल गया और छह महीने के दौरान कुछ और बार पंजीकरण कराया। कोई कैश रजिस्टर में पैसा लाता है, और कोई मुफ्त के लिए आया है उपकरणों. और हर किसी से कुछ न कुछ लाभ प्राप्त करना अच्छा रहेगा।

हमारे जैसे बड़े डेटा सेट पर, किसी व्यक्तिगत उपयोगकर्ता के व्यवहार का विश्लेषण करना और सूक्ष्म निर्णय लेना व्यर्थ है। लेकिन रुझानों को पकड़ना और बड़े समूहों के साथ काम करना संभव और आवश्यक है। हम बिल्कुल यही करते हैं।

सारांश

1. समूह विश्लेषण क्या है और इसकी आवश्यकता क्यों है?
2. SQL में उपयोगकर्ता पंजीकरण के महीने के अनुसार समूह कैसे बनाएं।
3. समूहों को कैसे स्थानांतरित करें ग्राफाना.

यदि आप पहले से ही जानते हैं कि समूह विश्लेषण क्या है और इसे SQL में कैसे करना है, तो सीधे अंतिम अनुभाग पर जाएँ।

1. समूह विश्लेषण क्या है और इसकी आवश्यकता क्यों है?

समूह विश्लेषण उपयोगकर्ताओं के विभिन्न समूहों (समूहों) की तुलना करने पर आधारित एक विधि है। अक्सर, हमारे समूह उस सप्ताह या महीने के अनुसार बनते हैं जिसमें उपयोगकर्ता ने सेवा का उपयोग करना शुरू किया था। यहां से उपयोगकर्ता के जीवनकाल की गणना की जाती है, और यह एक संकेतक है जिसके आधार पर एक जटिल विश्लेषण किया जा सकता है। उदाहरण के लिए, समझें:

• अधिग्रहण चैनल उपयोगकर्ता के जीवनकाल को कैसे प्रभावित करता है;
• किसी सुविधा या सेवा का उपयोग जीवनकाल को कैसे प्रभावित करता है;
• पिछले वर्ष की तुलना में फीचर एक्स के लॉन्च ने जीवनकाल को कैसे प्रभावित किया।

2. SQL में कोहोर्ट कैसे बनाएं?

लेख का आकार और सामान्य ज्ञान हमें अपना वास्तविक डेटा यहां प्रस्तुत करने की अनुमति नहीं देता है - परीक्षण डंप में, डेढ़ साल के आंकड़े: 1200 उपयोगकर्ता और 53 लेनदेन। ताकि आप इस डेटा के साथ खेल सकें, हमने MySQL और Grafana के साथ एक डॉकर छवि तैयार की है जिसमें आप यह सब स्वयं अनुभव कर सकते हैं। लेख के अंत में 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

हम प्रत्येक माह में सक्रिय उपयोगकर्ताओं की गणना करते हैं - हम बिलिंगमांथ, रजिस्ट्रेशनमांथ और मंथडिफ के आधार पर रिकॉर्ड को समूहित करते हैं:

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, बिलिंग माह = 2019-02, और एक फरवरी उपयोगकर्ता मार्च में सक्रिय था।

स्वाभाविक रूप से, बड़े डेटा सेट में पैटर्न बेहतर दिखाई देते हैं।

ग्रुपों को ग्राफाना में कैसे स्थानांतरित करें

हमने समूह बनाना सीख लिया है, लेकिन जब बहुत सारे रिकॉर्ड हों तो उनका विश्लेषण करना आसान नहीं रह जाता है। रिकॉर्ड्स को एक्सेल में निर्यात किया जा सकता है और सुंदर तालिकाओं में बनाया जा सकता है, लेकिन यह हमारी विधि नहीं है!

समूहों को एक इंटरैक्टिव ग्राफ़ के रूप में दिखाया जा सकता है ग्राफाना.

ऐसा करने के लिए, हम डेटा को ग्राफाना के लिए उपयुक्त प्रारूप में बदलने के लिए एक और क्वेरी जोड़ते हैं:

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

और डेटा को ग्राफाना पर अपलोड करें।

उदाहरण ग्राफ से डेमो:

अपने हाथों से स्पर्श करें:

उदाहरण के साथ GitHub रिपॉजिटरी MySQL और Grafana के साथ एक डॉकर छवि है जिसे आप अपने कंप्यूटर पर चला सकते हैं। डेटाबेस में पहले से ही जनवरी 2018 से जुलाई 2019 तक, डेढ़ साल का डेमो डेटा शामिल है।

यदि आप चाहें, तो आप अपना डेटा इस छवि में लोड कर सकते हैं।

SQL में समूह विश्लेषण के बारे में PS लेख:

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

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

स्रोत: www.habr.com