როგორ შევაგროვოთ მომხმარებლის კოჰორტები, როგორც გრაფიკები Grafana-ში [+ დოკერის სურათი მაგალითით]

როგორ გადავწყვიტეთ პრომოპულტის სერვისში მომხმარებელთა კოჰორტების ვიზუალიზაციის პრობლემა Grafana-ს გამოყენებით.

ხელი შეუწყოს - ძლიერი სერვისი მომხმარებელთა დიდი რაოდენობით. ფუნქციონირების 10 წლის განმავლობაში სისტემაში რეგისტრაციების რაოდენობამ მილიონს გადააჭარბა. მათ, ვინც მსგავს სერვისებს წააწყდა, იცის, რომ მომხმარებელთა ეს მასივი შორს არის ერთგვაროვანი.

ვიღაცამ ხელი მოაწერა და სამუდამოდ "ჩაეძინა". ვიღაცამ დაავიწყდა პაროლი და კიდევ რამდენიმეჯერ დარეგისტრირდა ექვსი თვის განმავლობაში. ვიღაცას ფული მოაქვს სალაროში, ვიღაც კი უსასყიდლოდ მოვიდა ინსტრუმენტები. და კარგი იქნებოდა ყველასგან გარკვეული მოგების მიღება.

ისეთ დიდ მონაცემთა ნაკრებებზე, როგორიც ჩვენია, ინდივიდუალური მომხმარებლის ქცევის ანალიზი და მიკრო-გადაწყვეტილების მიღება უაზროა. მაგრამ ტენდენციების დაჭერა და დიდ ჯგუფებთან მუშაობა შესაძლებელია და აუცილებელია. რასაც ჩვენ ვაკეთებთ ზუსტად.

რეზიუმე

1. რა არის კოჰორტის ანალიზი და რატომ არის საჭირო?
2. როგორ შევქმნათ კოჰორტები SQL-ში მომხმარებლის რეგისტრაციის თვის მიხედვით.
3. როგორ გადავიტანოთ კოჰორტები გრაფანა.

თუ უკვე იცით რა არის კოჰორტის ანალიზი და როგორ გააკეთოთ ეს SQL-ში, გადადით პირდაპირ ბოლო განყოფილებაზე.

1. რა არის კოჰორტის ანალიზი და რატომ არის საჭირო?

კოჰორტის ანალიზი არის მეთოდი, რომელიც დაფუძნებულია მომხმარებელთა სხვადასხვა ჯგუფის (კოჰორტების) შედარებაზე. ყველაზე ხშირად, ჩვენი ჯგუფები იქმნება იმ კვირის ან თვის მიხედვით, როდესაც მომხმარებელმა დაიწყო სერვისის გამოყენება. აქედან გამოითვლება მომხმარებლის სიცოცხლის ხანგრძლივობა და ეს არის ინდიკატორი, რომლის საფუძველზეც შეიძლება ჩატარდეს საკმაოდ რთული ანალიზი. მაგალითად, გაიგე:

• როგორ მოქმედებს შეძენის არხი მომხმარებლის სიცოცხლეზე;
• როგორ მოქმედებს ნებისმიერი ფუნქციის ან სერვისის გამოყენება სიცოცხლის ხანგრძლივობაზე;
• როგორ იმოქმედა X ფუნქციის გაშვებამ სიცოცხლეზე გასულ წელთან შედარებით.

2. როგორ შევქმნათ კოჰორტები SQL-ში?

სტატიის ზომა და საღი აზრი არ გვაძლევს საშუალებას წარმოვადგინოთ ჩვენი რეალური მონაცემები აქ - სატესტო ნაგავსაყრელზე, სტატისტიკა წელიწადნახევრის განმავლობაში: 1200 მომხმარებელი და 53 ტრანზაქცია. იმისათვის, რომ ამ მონაცემებით თამაში შეგეძლოთ, ჩვენ მოვამზადეთ დოკერის სურათი MySQL-ით და Grafana-ით, რომელშიც თქვენ თავად შეგიძლიათ განიცადოთ ეს ყველაფერი. ბმული GitHub-ზე სტატიის ბოლოს.

და აქ ჩვენ ვაჩვენებთ კოჰორტების შექმნას გამარტივებული მაგალითის გამოყენებით.

დავუშვათ, გვაქვს სერვისი. მომხმარებლები დარეგისტრირდებიან იქ და ხარჯავენ ფულს სერვისებზე. დროთა განმავლობაში მომხმარებლები ტოვებენ თავს. გვინდა გავარკვიოთ რამდენ ხანს ცხოვრობენ მომხმარებლები და რამდენი მათგანი იშლება სერვისით სარგებლობის 1-ლი და მე-2 თვის შემდეგ.

ამ კითხვებზე პასუხის გასაცემად, ჩვენ უნდა ავაშენოთ კოჰორტები რეგისტრაციის თვის მიხედვით. ჩვენ გავზომავთ აქტივობას ხარჯების მიხედვით ყოველ თვეში. ხარჯების ნაცვლად, შეიძლება იყოს შეკვეთები, გამოწერის გადასახადი ან დროზე დაფუძნებული სხვა აქტივობა.

Raw მონაცემები

მაგალითები გაკეთდა MySQL-ში, მაგრამ სხვა DBMS-ებისთვის მნიშვნელოვანი განსხვავებები არ უნდა იყოს.

მომხმარებელთა ცხრილი - მომხმარებლები:

მომხმარებლის იდენტიფიკაცია
Რეგისტრაციის თარიღი

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

ღირებულების ცხრილი - ბილინგი:

მომხმარებლის იდენტიფიკაცია
თარიღი
Sum

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

ჩვენ ვითვლით ყოველ თვეში გააქტიურებულ მომხმარებლებს - ვაჯგუფებთ ჩანაწერებს 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

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, ხოლო თებერვლის ერთი მომხმარებელი აქტიური იყო მარტში.

ბუნებრივია, ნიმუშები უკეთ ჩანს მონაცემთა დიდ ნაკრებში.

როგორ გადავიტანოთ კოჰორტები გრაფანაში

ჩვენ ვისწავლეთ კოჰორტების ფორმირება, მაგრამ როცა ბევრი ჩანაწერია, მათი გაანალიზება ადვილი აღარ არის. ჩანაწერები შეიძლება ექსპორტირებული იყოს 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-ზე.

მაგალითი გრაფიკი საწყისი დემო:

ხელით შეეხეთ:

GitHub საცავი მაგალითით არის დოკერის სურათი MySQL-ით და Grafana-ით, რომელიც შეგიძლიათ გაუშვათ თქვენს კომპიუტერში. მონაცემთა ბაზა უკვე შეიცავს წელიწადნახევრის დემო მონაცემებს, 2018 წლის იანვრიდან 2019 წლის ივლისამდე.

თუ გსურთ, შეგიძლიათ ჩატვირთოთ თქვენი მონაცემები ამ სურათში.

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