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

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

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

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

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

सारांश

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

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

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

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

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

2. एसक्यूएल में सहवास कैसे करें?

लेख का आकार और सामान्य ज्ञान हमें यहां अपना वास्तविक डेटा प्रदान करने की अनुमति नहीं देता है - परीक्षण डंप में, डेढ़ साल के आंकड़े हैं: 1200 उपयोगकर्ता और 53,000 लेनदेन। ताकि आप इस डेटा के साथ खेल सकें, हमने MySQL और Grafana के साथ एक डॉक इमेज तैयार किया है, जिसमें आप इसे खुद महसूस कर सकते हैं। लेख के अंत में GitHub से लिंक करें।

और यहां हम एक सरल उदाहरण के साथ सहकर्मियों के निर्माण को दिखाते हैं।

मान लीजिए हमारे पास एक सेवा है। उपयोगकर्ता इसमें पंजीकरण करते हैं और सेवाओं पर पैसा खर्च करते हैं। समय के साथ, उपयोगकर्ता गिर जाते हैं। हम जानना चाहते हैं कि उपयोगकर्ता कितने समय तक जीवित रहते हैं, और उनमें से कितने सेवा के उपयोग के 1 और 2 महीने बाद बंद हो जाते हैं।

इन सवालों के जवाब के लिए, हमें पंजीकरण के महीने तक सहकर्मियों का निर्माण करने की आवश्यकता है। गतिविधि को प्रत्येक महीने के खर्चों से मापा जाएगा। लागतों के बजाय, आदेश, एक मासिक शुल्क या किसी अन्य समयबद्ध गतिविधि हो सकती है।

स्रोत डेटा

उदाहरण 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 

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

 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 

परिणाम:

जनवरी, फरवरी और मार्च में, एक नया उपयोगकर्ता दिखाई दिया - MonthDiff = 0. एक जनवरी उपयोगकर्ता सक्रिय था और फरवरी में RegistrationMonth = 2019-01, BillingMonth = 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 

और डेटा को Grafana पर अपलोड करें।

डेमो से उदाहरण चार्ट:

यह स्पर्श करें:

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

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

पुनश्च लेख SQL में cohort विश्लेषण के बारे में:

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

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