ProHoster > Blogs > Administrācija > Kā savākt lietotāju grupas kā diagrammas programmā Grafana [+ docker attēls ar piemēru]

Kā savākt lietotāju grupas kā diagrammas programmā Grafana [+ docker attēls ar piemēru]

Kā savākt lietotāju grupas kā diagrammas programmā Grafana [+ docker attēls ar piemēru]

Kā mēs atrisinājām lietotāju kohortu vizualizācijas problēmu pakalpojumā Promopult, izmantojot Grafana.

Reklāmas - spēcÄ«gs pakalpojums ar lielu lietotāju skaitu. 10 darbÄ«bas gadu laikā reÄ£istrāciju skaits sistēmā pārsniedzis miljonu. Tie, kas ir saskāruÅ”ies ar lÄ«dzÄ«giem pakalpojumiem, zina, ka Å”is lietotāju klāsts nebÅ«t nav viendabÄ«gs.

Kāds pierakstÄ«jās un ā€œaizmigaā€ uz visiem laikiem. Kāds aizmirsa savu paroli un seÅ”u mēneÅ”u laikā reÄ£istrējās vēl pāris reizes. Kāds atnes naudu uz kases aparātu, un kāds atbrauca pēc dāvanām instrumenti. Un bÅ«tu jauki no visiem gÅ«t kādu peļņu.

Tik lielām datu kopām kā mÅ«su, nav jēgas analizēt atseviŔķa lietotāja uzvedÄ«bu un pieņemt mikrolēmumus. Taču tendenču tverÅ”ana un darbs ar lielām grupām ir iespējams un nepiecieÅ”ams. Tas ir tieÅ”i tas, ko mēs darām.

Kopsavilkums

  1. Kas ir kohortas analÄ«ze un kāpēc tā ir nepiecieÅ”ama?
  2. Kā izveidot kohortas pēc lietotāja reÄ£istrācijas mēneÅ”a SQL.
  3. Kā pārsūtīt kohortas uz grafana.

Ja jau zināt, kas ir kohortas analÄ«ze un kā to izdarÄ«t SQL, pārejiet tieÅ”i uz pēdējo sadaļu.

1. Kas ir kohortas analÄ«ze un kāpēc tā ir nepiecieÅ”ama?

Kohortu analÄ«ze ir metode, kuras pamatā ir dažādu lietotāju grupu (kohortu) salÄ«dzināŔana. Visbiežāk mÅ«su grupas tiek veidotas pēc nedēļas vai mēneÅ”a, kurā lietotājs sāka lietot pakalpojumu. No Å”ejienes tiek aprēķināts lietotāja dzÄ«ves ilgums, un tas ir rādÄ«tājs, uz kura pamata var veikt diezgan sarežģītu analÄ«zi. Piemēram, saprotiet:

  • kā iegÅ«Å”anas kanāls ietekmē lietotāja dzÄ«ves ilgumu;
  • kā jebkuras funkcijas vai pakalpojuma izmantoÅ”ana ietekmē kalpoÅ”anas laiku;
  • Kā funkcijas X palaiÅ”ana ietekmēja kalpoÅ”anas laiku salÄ«dzinājumā ar pagājuÅ”o gadu.

2. Kā izveidot kohortas SQL?

Raksta apjoms un veselais saprāts neļauj Å”eit uzrādÄ«t mÅ«su reālos datus - testa izgāztuvē statistika par pusotru gadu: 1200 lietotāju un 53 000 darÄ«jumu. Lai jÅ«s varētu spēlēties ar Å”iem datiem, esam sagatavojuÅ”i docker attēlu ar MySQL un Grafana, kurā varat to visu piedzÄ«vot pats. Saite uz GitHub raksta beigās.

Un Å”eit mēs parādÄ«sim kohortu izveidi, izmantojot vienkārÅ”otu piemēru.

Pieņemsim, ka mums ir pakalpojums. Lietotāji tur reÄ£istrējas un tērē naudu par pakalpojumiem. Laika gaitā lietotāji atsakās. Mēs vēlamies noskaidrot, cik ilgi lietotāji dzÄ«vo un cik no tiem izkrÄ«t pēc pakalpojuma lietoÅ”anas 1. un 2. mēneÅ”a.

Lai atbildētu uz Å”iem jautājumiem, mums ir jāveido kohortas, pamatojoties uz reÄ£istrācijas mēnesi. Aktivitāti mērÄ«sim pēc izdevumiem katrā mēnesÄ«. Izdevumu vietā var bÅ«t pasÅ«tÄ«jumi, abonÄ“Å”anas maksas vai jebkura cita uz laiku balstÄ«ta darbÄ«ba.

Neapstrādāti dati

Piemēri tika veidoti MySQL, bet citām DBVS nevajadzētu bÅ«t bÅ«tiskām atŔķirÄ«bām.

Lietotāju tabula ā€” lietotāji:

Lietotāja ID
Reģistrācijas datums

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Izmaksu tabula ā€” norēķini:

Lietotāja ID
datums
Summa

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

Atlasiet visus lietotāju norakstīŔanas gadījumus un reģistrācijas datumu:

SELECT 
  b.userId, 
  b.Date,
  u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Rezultāts:

Lietotāja ID
datums
Reģistrācijas datums

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

Mēs veidojam kohortas pa mēneÅ”iem; lai to paveiktu, mēs pārvērÅ”am visus datumus mēneÅ”os:

DATE_FORMAT(Date, '%Y-%m')

Tagad mums ir jāzina, cik mēneÅ”us lietotājs bija aktÄ«vs - tā ir atŔķirÄ«ba starp debeta mēnesi un reÄ£istrācijas mēnesi. MySQL ir funkcija PERIOD_DIFF() - divu mēneÅ”u atŔķirÄ«ba. Pievienojiet pieprasÄ«jumam 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

Lietotāja ID
Norēķinu mēnesis
Reģistrācijas datums
MēneÅ”iAtŔķirÄ«ba

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

Mēs uzskaitām katrā mēnesÄ« aktivizētos lietotājus ā€” ierakstus sagrupējam pēc BillingMonth, RegistrationMonth un 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

Rezultāts:

Lietotāju skaits
Norēķinu mēnesis
Reģistrācijas mēnesis
MēneÅ”iAtŔķirÄ«ba

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

JanvārÄ«, februārÄ« un martā parādÄ«jās pa vienam jaunam lietotājam ā€” MonthsDiff = 0. Viens janvāra lietotājs bija aktÄ«vs februārÄ« ā€” ReÄ£istrācijas mēnesis = 2019-01, Norēķinu mēnesis = 2019-02, un viens februāra lietotājs bija aktÄ«vs martā.

Protams, modeļi ir labāk redzami lielā datu kopā.

Kā pārsūtīt kohortas uz Grafana

Mēs esam iemācÄ«juÅ”ies veidot kohortas, bet, ja ierakstu ir daudz, tos vairs nav viegli analizēt. Ierakstus var eksportēt uz Excel un veidot skaistās tabulās, taču tā nav mÅ«su metode!

Kohortas var parādīt kā interaktīvu grafiku grafana.

Lai to izdarītu, mēs pievienojam vēl vienu vaicājumu, lai pārveidotu datus Grafana piemērotā formātā:

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

Un augÅ”upielādējiet datus Grafana.

Piemēra diagramma no demo:

Kā savākt lietotāju grupas kā diagrammas programmā Grafana [+ docker attēls ar piemēru]

Pieskarieties ar rokām:

GitHub repozitorijs ar piemēru ir Docker attēls ar MySQL un Grafana, ko varat palaist savā datorā. Datu bāzē jau ir demonstrācijas dati par pusotru gadu, no 2018. gada janvāra līdz 2019. gada jūlijam.

Ja vēlaties, varat ielādēt savus datus Å”ajā attēlā.

PS raksti par kohortas analīzi SQL:

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

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

Avots: www.habr.com

Iegādājieties uzticamu mitināŔanu vietnēm ar DDoS aizsardzÄ«bu, VPS VDS serveriem šŸ”„ Iegādājieties uzticamu tÄ«mekļa vietņu mitināŔanu ar DDoS aizsardzÄ«bu, VPS VDS serveriem | ProHoster