Si sihte përdorin për të mbledhur grupe përdoruesish në forma grafike në Grafana [+docker-imazhi me shembuj]

Si e zgjidhëm çështjen e vizualizimit të grupeve të përdoruesve në shërbimin Promopult me ndihmën e Grafana.

Promopult — një shërbim i fuqishëm me një numër të madh përdoruesish. Gjatë 10 viteve të funksionimit, numri i regjistrimeve në sistem ka kaluar një milion. Ata që janë përballur me shërbime të ngjashme e dinë se kjo masë përdoruesish është shumë e ndryshme.

Disa janë regjistruar dhe kanë 'fjetur' përjetësisht. Disa kanë harruar fjalëkalimin dhe janë regjistruar disa herë brenda gjashtë muajve. Disa sjellin para në kasë, ndërsa disa kanë ardhur për mjetet falas të cilat do të ishte mirë të përfitonim diçka prej tyre.. Në masat e tilla të mëdha të të dhënave si tonat, analizimi i sjelljes së një përdoruesi të vetëm dhe marrja e mikro-vendimeve është pa kuptim. Por kapja e trendeve dhe puna me grupe të mëdha — mund dhe duhet. Këtë ne, në fakt, e bëjmë.

Përmbledhje

Çfarë është analiza e grupeve dhe për çfarë është e nevojshme.

1. Si të bëni grupe sipas muajit të regjistrimit të përdoruesve në SQL.
2. Si të transferoni grupet në
3. Grafana Nëse tashmë e dini se çfarë është analiza e grupeve dhe se si ta bëni në SQL, kaloni menjëherë në seksionin e fundit..

1. Çfarë është analiza e grupeve dhe për çfarë është e nevojshme

Analiza e grupeve është një metodë e bazuar në krahasimin e grupeve të ndryshme (grupimeve) të përdoruesve. Çdo herë formojmë grupe sipas javës ose muajit në të cilin përdoruesi filloi të përdorë shërbimin. Prej këtej llogaritet koha e jetës së përdoruesit, dhe kjo është një tregues mbi të cilin mund të kryhen analiza të ndërlikuara. Për shembull, të kuptojmë:

si ndikon kanali i tërheqjes në kohën e jetës së përdoruesit;

• si përdorimi i ndonjë funksioni ose shërbimi ndikon në kohën e jetës;
• si përmbyllja e karakteristikës X ndikon në kohën e jetës krahasuar me vitin e kaluar.
• 2. Si të bëni grupe në SQL?

Shkalla e artikullit dhe shëndeti i arsyes nuk lejojnë që të paraqesim të dhënat tona reale — në dump-in testues, statistika për një mundësi prej një viti dhe gjysmë: 1200 përdorues dhe 53,000 transaksione. Që ju të mund të luani me këto të dhëna, ne kemi përgatitur një docker-imazh me MySQL dhe Grafana, ku mund të eksperimentoni me gjithçka vetë. Lidhja në GitHub në fund të artikullit.

Dhe këtu ne do të tregojmë krijimin e grupeve me një shembull të thjeshtuar.

А здесь мы покажем создание когорт на упрощенном примере.

Supozoni se që kemi një shërbim. Në të regjistrohen përdoruesit dhe shpenzojnë para për shërbime. Me kalimin e kohës, përdoruesit largohen. Ne duam të dimë sa gjatë qëndrojnë përdoruesit dhe sa prej tyre largohen pas muajit të parë dhe të dytë të përdorimit të shërbimit.

Për të përgjigjur këtyre pyetjeve, na nevojitet të hartojmë koorta sipas muajit të regjistrimit. Aktiviteti do të matet sipas shpenzimeve në çdo muaj. Në vend të shpenzimeve, mund të përdoren porosi, abonime ose ndonjë aktivitet tjetër të lidhur me kohën.

Të dhënat burimore

Shembujt janë bërë në MySQL, por nuk duhet të ketë dallime të theksuara për sistemet e tjera të menaxhimit të bazës së të dhënave.

Tabela e përdoruesve — users:

userId
Data e Regjistrimit

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabela e shpenzimeve — billing:

userId
Data
Shuma

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

Zgjedhim të gjitha shpenzimet e përdoruesve dhe datën e regjistrimit:

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

Rezultati:

userId
Data
Data e Regjistrimit

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

Ndërtojmë koortat sipas muajve, për këtë konvertojmë të gjitha datat në muaj:

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

Tani na nevojitet të dimë sa muaj ka qenë aktiv përdoruesi — kjo është diferenca midis muajit të shpenzimit dhe muajit të regjistrimit. Në MySQL ka funksionin PERIOD_DIFF() — diferenca midis dy muajve. Shtojmë PERIOD_DIFF() në pyetje:

SELECT
    b.userId,
    DATE_FORMAT(b.Data, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Data, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

userId
BillingMonth
Data e Regjistrimit
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

Numërojmë përdoruesit e aktivizuar në çdo muaj — grupllojmë të dhënat sipas BillingMonth, RegistrationMonth dhe MonthsDiff:

SELECT
    COUNT(DISTINCT(b.userId)) AS UsersCount,
    DATE_FORMAT(b.Data, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Data, '%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

Rezultati:

UsersCount
BillingMonth
RegistrationMonth
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

Në janar, shkurt dhe mars erdhën nga një përdorues i ri — MonthsDiff = 0. Një përdorues i janarit ishte aktiv edhe në shkurt — RegistrationMonth = 2019-01, BillingMonth = 2019-02, ashtu siç ishte një përdorues i shkurtit aktiv në mars.

Në një grup të madh të dhënash, natyrisht, tendencat duken më mirë.

Si të transferoni koortat në Grafana

Ne mësuam të formojmë koorta, por kur numri i shënimeve rritet, analizimi i tyre nuk është më i lehtë. Shënimet mund të eksportohet në Excel dhe të formohen tabela të bukura, por kjo nuk është metoda jonë!

Koortat mund të shfaqen në formën e një grafiku interaktiv në Nëse tashmë e dini se çfarë është analiza e grupeve dhe se si ta bëni në SQL, kaloni menjëherë në seksionin e fundit..

Për këtë, shtojmë edhe një kërkesë tjetër për të konvertuar të dhënat në një format të përshtatshëm për 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 (
  ## kërkesa e vjetër që kthen kohrat
  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

Dhe nxjerrim të dhënat në Grafana.

Shembulli i grafikës nga demo:

Ta prekësh me dorë:

Repo GitHub me shembullin — është një imazh docker me MySQL dhe Grafana, i cili mund të aktivizohet në kompjuterin tuaj. Në bazë ka të dhëna demo për një gjysmë viti, nga janari 2018 deri në korrik 2019.

Nëse dëshironi, mund të ngarkoni të dhënat tuaja në këtë imazh.

P.S. Artikuj mbi analizën e kohrave në SQL:

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

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

Burimi: habr.com