כיצד לאסוף קבוצות משתמשים כגרפים בגראפאנה [+ תמונת docker עם דוגמה]

כיצד פתרנו את בעיית הדמיית קבוצות של משתמשים בשירות פרומופולט באמצעות Grafana.

לקדם - שירות רב עוצמה עם מספר רב של משתמשים. במהלך 10 שנות הפעילות, עלה מספר הרישומים במערכת על מיליון. מי שנתקל בשירותים דומים יודע שמערך המשתמשים הזה רחוק מלהיות הומוגני.

מישהו נרשם ו"נרדם" לנצח. מישהו שכח את הסיסמה שלו ונרשם עוד כמה פעמים במהלך שישה חודשים. מישהו מביא כסף לקופה, ומישהו בא בשביל חינם מכשירים. וזה יהיה נחמד לקבל קצת רווח מכולם.

על מערכי נתונים כה גדולים כמו שלנו, ניתוח התנהגותו של משתמש בודד וקבלת מיקרו-החלטות הוא חסר טעם. אבל לתפוס טרנדים ולעבוד עם קבוצות גדולות זה אפשרי והכרחי. וזה בדיוק מה שאנחנו עושים.

סיכום

1. מהו ניתוח עוקבות ומדוע הוא נחוץ?
2. כיצד ליצור קבוצות לפי חודש של רישום משתמש ב-SQL.
3. איך להעביר קבוצות ל גרפנה.

אם אתה כבר יודע מה זה ניתוח עוקבות וכיצד לעשות זאת ב-SQL, דלג ישירות לסעיף האחרון.

1. מהו ניתוח עוקבות ומדוע הוא נחוץ?

ניתוח עוקבות הוא שיטה המבוססת על השוואה בין קבוצות (קוהורטות) שונות של משתמשים. לרוב, הקבוצות שלנו נוצרות לפי השבוע או החודש שבו המשתמש התחיל להשתמש בשירות. מכאן מחושב משך החיים של המשתמש, וזהו אינדיקטור שעל בסיסו ניתן לבצע ניתוח מורכב למדי. לדוגמה, הבן:

• כיצד משפיע ערוץ הרכישה על חיי המשתמש;
• כיצד השימוש בכל תכונה או שירות משפיע על חייו;
• כיצד ההשקה של תכונה X השפיעה על משך החיים בהשוואה לשנה שעברה.

2. איך עושים קבוצות ב-SQL?

גודל הכתבה והשכל הישר לא מאפשרים לנו להציג כאן את הנתונים האמיתיים שלנו - במזבלה של הבדיקות, סטטיסטיקה של שנה וחצי: 1200 משתמשים ו-53 עסקאות. כדי שתוכלו לשחק עם הנתונים האלה, הכנו תמונת docker עם MySQL ו-Grafana בה תוכלו לחוות הכל בעצמכם. קישור ל-GitHub בסוף המאמר.

וכאן נראה את יצירת הקבוצות באמצעות דוגמה פשוטה.

נניח שיש לנו שירות. משתמשים נרשמים שם ומוציאים כסף על שירותים. עם הזמן, משתמשים נושרים. אנו רוצים לגלות כמה זמן חיים המשתמשים, וכמה מהם נופלים לאחר החודש הראשון והשני של השימוש בשירות.

כדי לענות על שאלות אלו, עלינו לבנות קבוצות על סמך חודש הרישום. אנו נמדוד פעילות לפי הוצאות בכל חודש. במקום הוצאות, עשויות להיות הזמנות, דמי מנוי או כל פעילות אחרת המבוססת על זמן.

נתונים גולמיים

הדוגמאות נעשו ב-MySQL, אך עבור DBMSs אחרים לא אמורים להיות הבדלים משמעותיים.

טבלת משתמשים - משתמשים:

תעודת זהות של המשתמש
תאריך רישום

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

תעודת זהות של המשתמש
חודש החיוב
תאריך רישום
MonthsDiff

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

תוצאה:

ספירת משתמשים
חודש החיוב
חודש הרשמה
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

בחודשים ינואר, פברואר ומרץ, הופיע משתמש חדש אחד כל אחד - MonthsDiff = 0. משתמש אחד בינואר היה פעיל בפברואר - RegistrationMonth = 2019-01, BillingMonth = 2019-02, ומשתמש אחד בפברואר היה פעיל במרץ.

באופן טבעי, דפוסים נראים טוב יותר במערך נתונים גדול.

איך מעבירים קבוצות לגרפאנה

למדנו איך יוצרים קבוצות, אבל כשיש הרבה רשומות, כבר לא קל לנתח אותם. ניתן לייצא רשומות לאקסל וליצור לטבלאות יפות, אבל זו לא השיטה שלנו!

ניתן להציג קבוצות קבוצות כתרשים אינטראקטיבי ב גרפנה.

לשם כך, אנו מוסיפים שאילתה נוספת להמרת הנתונים לפורמט המתאים ל-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 עם דוגמה היא תמונת docker עם 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/

מקור: www.habr.com