كيفية جمع مجموعات المستخدمين في شكل رسوم بيانية في Grafana [+ صورة عامل ميناء مع مثال]

كيف قمنا بحل مهمة تصور مجموعات المستخدمين في خدمة Promopult باستخدام Grafana.

Promopult هي خدمة قوية مع عدد كبير من المستخدمين. على مدى 10 سنوات من التشغيل ، تجاوز عدد التسجيلات في النظام مليون. يعرف أولئك الذين واجهوا خدمات مماثلة أن هذه المجموعة من المستخدمين بعيدة عن التجانس.

شخص مسجل و "تغفو" إلى الأبد. شخص ما نسيت كلمة المرور وسجلت عدة مرات في ستة أشهر. شخص ما يجلب الأموال إلى أمين الصندوق ، وجاء شخص ما للحصول على أدوات مجانية. وسيكون من الجميل الحصول على بعض الأرباح من الجميع.

في صفائف البيانات الكبيرة مثل بياناتنا ، ليس من المنطقي تحليل سلوك المستخدم الفردي واتخاذ القرارات الدقيقة. لكن اصطياد الاتجاهات والعمل مع مجموعات كبيرة أمر ممكن وضروري. ما نقوم به في الواقع.

ملخص

1. ما هو تحليل الأتراب ولماذا هو مطلوب.
2. كيفية جعل الأفواج حسب شهر تسجيل المستخدم في SQL.
3. كيفية نقل الأفواج إلى جرافانا .

إذا كنت تعرف بالفعل ماهية تحليل الأتراب وكيفية القيام بذلك في SQL ، انتقل إلى القسم الأخير.

1. ما هو تحليل الأتراب ولماذا هو مطلوب

تحليل الأتراب هو طريقة تعتمد على مقارنة مجموعات مختلفة (مجموعات) من المستخدمين. في معظم الأحيان ، يتم تشكيل مجموعاتنا حسب الأسبوع أو الشهر الذي بدأ فيه المستخدم استخدام الخدمة. يتم حساب عمر المستخدم من هنا ، وهذا مؤشر يمكن على أساسه إجراء تحليل معقد إلى حد ما. على سبيل المثال ، فهم:

• كيف تجذب القناة حياة المستخدم؟
• كيف يؤثر استخدام وظيفة أو خدمة على العمر ؛
• كيف أثر إطلاق الميزات X على وقت الحياة مقارنة بالعام الماضي.

2. كيفية جعل الأفواج في SQL؟

لا يسمح لنا حجم المقالة والحس السليم بتقديم بياناتنا الحقيقية هنا - في اختبار التفريغ ، والإحصاءات لمدة عام ونصف هي: 1200 مستخدم و 53000 معاملة. حتى تتمكن من اللعب مع هذه البيانات ، قمنا بإعداد صورة عامل ميناء مع MySQL و Grafana ، والتي يمكنك أن تشعر بها بنفسك. رابط إلى جيثب في نهاية المقال.

وهنا نعرض إنشاء الأفواج مع مثال مبسط.

لنفترض أن لدينا خدمة. يسجل المستخدمون فيه وينفقون المال على الخدمات. مع مرور الوقت ، تسقط المستخدمين. نريد أن نعرف كم من الوقت يعيش المستخدمون ، وعددهم يسقط بعد الشهر الأول والثاني من استخدام الخدمة.

للإجابة على هذه الأسئلة ، نحتاج إلى بناء مجموعات من قبل شهر التسجيل. سيتم قياس النشاط بالنفقات في كل شهر. بدلاً من التكاليف ، قد تكون هناك أوامر أو رسوم شهرية أو أي نشاط آخر محدد زمنياً.

مصدر البيانات

يتم تقديم الأمثلة في MySQL ، ولكن بالنسبة لبقية قواعد البيانات ، يجب ألا تكون هناك اختلافات كبيرة.

جدول المستخدم - المستخدمين:

جدول الإنفاق - الفواتير:

حدد جميع رسوم المستخدم وتاريخ التسجيل:

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 

النتيجة:

في شهري يناير وفبراير ومارس ، ظهر مستخدم جديد واحد - MonthsDiff = 0. كان مستخدم يناير واحد نشطًا وفي فبراير RegistrationMonth = 2019-01 ، و BillingMonth = 2019-02 ، وكان مستخدم فبراير نشطًا في مارس.

في مجموعة كبيرة من البيانات ، من الطبيعي أن نرى الأنماط بشكل أفضل.

كيفية نقل الأفواج إلى جرافانا

لقد تعلمنا كيفية تكوين مجموعات ، ولكن عندما يكون هناك الكثير من السجلات ، لم يعد من السهل تحليلها. يمكن تصدير السجلات إلى 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 

وتحميل البيانات إلى غرافانا.

مثال الرسم البياني من العرض التوضيحي :

مسها:

مستودع مثال GitHub هو صورة لرسو السفن باستخدام MySQL و Grafana ، والتي يمكن تشغيلها على جهاز الكمبيوتر الخاص بك. تحتوي قاعدة البيانات بالفعل على بيانات تجريبية لمدة عام ونصف ، من يناير 2018 إلى يوليو 2019.

إذا كنت ترغب في ذلك ، يمكنك تحميل البيانات الخاصة بك إلى هذه الصورة.

مقالات عن تحليل الأتراب في SQL:

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

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