ഗ്രാഫാനയിലെ ഉപയോക്തൃ കൂട്ടങ്ങളെ ഗ്രാഫുകളായി എങ്ങനെ ശേഖരിക്കാം [+ ഡോക്കർ ചിത്രം ഉദാഹരണം]

ഗ്രാഫാന ഉപയോഗിച്ച് പ്രൊമോപൾട്ട് സേവനത്തിലെ ഉപയോക്താക്കളുടെ കൂട്ടങ്ങളെ ദൃശ്യവൽക്കരിക്കുന്നതിനുള്ള പ്രശ്നം ഞങ്ങൾ എങ്ങനെ പരിഹരിച്ചു.

പ്രോത്സാഹിപ്പിക്കുക - ധാരാളം ഉപയോക്താക്കളുള്ള ശക്തമായ സേവനം. 10 വർഷത്തെ പ്രവർത്തനത്തിനിടയിൽ, സിസ്റ്റത്തിലെ രജിസ്ട്രേഷനുകളുടെ എണ്ണം ഒരു ദശലക്ഷം കവിഞ്ഞു. സമാന സേവനങ്ങൾ നേരിട്ടവർക്ക് ഈ ഉപയോക്താക്കളുടെ നിര ഏകതാനതയിൽ നിന്ന് വളരെ അകലെയാണെന്ന് അറിയാം.

ആരോ സൈൻ അപ്പ് ചെയ്ത് എന്നെന്നേക്കുമായി "ഉറങ്ങി". ആരോ അവരുടെ പാസ്‌വേഡ് മറന്ന് ആറ് മാസത്തിനിടെ രണ്ട് തവണ കൂടി രജിസ്റ്റർ ചെയ്തു. ആരോ ക്യാഷ് രജിസ്റ്ററിലേക്ക് പണം കൊണ്ടുവരുന്നു, ഒരാൾ സൗജന്യമായി വന്നു ഉപകരണങ്ങൾ. പിന്നെ എല്ലാവരിൽ നിന്നും കുറച്ച് ലാഭം കിട്ടിയാൽ നന്നായിരിക്കും.

നമ്മുടേത് പോലുള്ള വലിയ ഡാറ്റാ സെറ്റുകളിൽ, ഒരു വ്യക്തിഗത ഉപയോക്താവിന്റെ പെരുമാറ്റം വിശകലനം ചെയ്യുകയും സൂക്ഷ്മമായ തീരുമാനങ്ങൾ എടുക്കുകയും ചെയ്യുന്നത് അർത്ഥശൂന്യമാണ്. എന്നാൽ ട്രെൻഡുകൾ പിടിച്ചെടുക്കുന്നതും വലിയ ഗ്രൂപ്പുകളുമായി പ്രവർത്തിക്കുന്നതും സാധ്യമാണ്. ഞങ്ങൾ കൃത്യമായി എന്താണ് ചെയ്യുന്നത്.

സംഗ്രഹം

1. എന്താണ് കോഹോർട്ട് വിശകലനം, എന്തുകൊണ്ട് അത് ആവശ്യമാണ്?
2. SQL-ൽ ഉപയോക്തൃ രജിസ്ട്രേഷൻ മാസത്തിൽ എങ്ങനെ കോഹോർട്ടുകൾ ഉണ്ടാക്കാം.
3. ഇതിലേക്ക് കോഹോർട്ടുകളെ എങ്ങനെ കൈമാറാം ഗ്രാഫാന.

കോഹോർട്ട് വിശകലനം എന്താണെന്നും അത് SQL-ൽ എങ്ങനെ ചെയ്യാമെന്നും നിങ്ങൾക്ക് ഇതിനകം അറിയാമെങ്കിൽ, അവസാന ഭാഗത്തേക്ക് പോകുക.

1. എന്താണ് കോഹോർട്ട് വിശകലനം, എന്തുകൊണ്ട് അത് ആവശ്യമാണ്?

കോഹോർട്ട് വിശകലനം എന്നത് ഉപയോക്താക്കളുടെ വ്യത്യസ്ത ഗ്രൂപ്പുകളെ (കോഹോർട്ടുകൾ) താരതമ്യം ചെയ്യുന്നതിനെ അടിസ്ഥാനമാക്കിയുള്ള ഒരു രീതിയാണ്. മിക്കപ്പോഴും, ഉപയോക്താവ് സേവനം ഉപയോഗിക്കാൻ തുടങ്ങിയ ആഴ്‌ചയോ മാസമോ അനുസരിച്ചാണ് ഞങ്ങളുടെ ഗ്രൂപ്പുകൾ രൂപപ്പെടുന്നത്. ഇവിടെ നിന്ന് ഉപയോക്താവിന്റെ ആയുസ്സ് കണക്കാക്കുന്നു, ഇത് സങ്കീർണ്ണമായ ഒരു വിശകലനം നടത്താൻ കഴിയുന്ന ഒരു സൂചകമാണ്. ഉദാഹരണത്തിന്, മനസ്സിലാക്കുക:

• എങ്ങനെ ഏറ്റെടുക്കൽ ചാനൽ ഉപയോക്താവിന്റെ ജീവിതത്തെ സ്വാധീനിക്കുന്നു;
• ഏതെങ്കിലും ഫീച്ചറിന്റെയോ സേവനത്തിന്റെയോ ഉപയോഗം ജീവിതത്തെ എങ്ങനെ ബാധിക്കുന്നു;
• ഫീച്ചർ എക്‌സിന്റെ ലോഞ്ച് കഴിഞ്ഞ വർഷത്തെ അപേക്ഷിച്ച് ജീവിതകാലത്തെ എങ്ങനെ ബാധിച്ചു.

2. SQL-ൽ കോഹോർട്ടുകൾ എങ്ങനെ ഉണ്ടാക്കാം?

ലേഖനത്തിന്റെ വലുപ്പവും സാമാന്യബുദ്ധിയും ഞങ്ങളുടെ യഥാർത്ഥ ഡാറ്റ ഇവിടെ അവതരിപ്പിക്കാൻ ഞങ്ങളെ അനുവദിക്കുന്നില്ല - ടെസ്റ്റ് ഡമ്പിൽ, ഒന്നര വർഷത്തെ സ്ഥിതിവിവരക്കണക്കുകൾ: 1200 ഉപയോക്താക്കളും 53 ഇടപാടുകളും. നിങ്ങൾക്ക് ഈ ഡാറ്റ ഉപയോഗിച്ച് കളിക്കാൻ കഴിയുന്ന തരത്തിൽ, MySQL, Grafana എന്നിവ ഉപയോഗിച്ച് ഞങ്ങൾ ഒരു ഡോക്കർ ഇമേജ് തയ്യാറാക്കിയിട്ടുണ്ട്, അതിൽ നിങ്ങൾക്ക് എല്ലാം അനുഭവിക്കാനാകും. ലേഖനത്തിന്റെ അവസാനം GitHub-ലേക്കുള്ള ലിങ്ക്.

ലളിതമായ ഒരു ഉദാഹരണം ഉപയോഗിച്ച് കോഹോർട്ടുകളുടെ സൃഷ്ടി ഞങ്ങൾ ഇവിടെ കാണിക്കും.

നമുക്ക് ഒരു സേവനം ഉണ്ടെന്ന് കരുതുക. ഉപയോക്താക്കൾ അവിടെ രജിസ്റ്റർ ചെയ്യുകയും സേവനങ്ങൾക്കായി പണം ചെലവഴിക്കുകയും ചെയ്യുന്നു. കാലക്രമേണ, ഉപയോക്താക്കൾ ഉപേക്ഷിക്കുന്നു. ഉപയോക്താക്കൾ എത്ര കാലം ജീവിക്കുന്നുവെന്നും അവരിൽ എത്ര പേർ സേവനം ഉപയോഗിച്ചതിന്റെ 1-ഉം 2-ാം മാസത്തിനും ശേഷം വീഴ്ത്തപ്പെടുന്നവരാണെന്നും കണ്ടെത്താൻ ഞങ്ങൾ ആഗ്രഹിക്കുന്നു.

ഈ ചോദ്യങ്ങൾക്ക് ഉത്തരം നൽകാൻ, രജിസ്ട്രേഷൻ മാസത്തെ അടിസ്ഥാനമാക്കി ഞങ്ങൾ കോഹോർട്ടുകൾ നിർമ്മിക്കേണ്ടതുണ്ട്. ഓരോ മാസത്തെയും ചെലവുകൾ അനുസരിച്ച് ഞങ്ങൾ പ്രവർത്തനം അളക്കും. ചെലവുകൾക്ക് പകരം, ഓർഡറുകൾ, സബ്‌സ്‌ക്രിപ്‌ഷൻ ഫീസ് അല്ലെങ്കിൽ മറ്റേതെങ്കിലും സമയാധിഷ്‌ഠിത പ്രവർത്തനം എന്നിവ ഉണ്ടായിരിക്കാം.

റോ ഡാറ്റ

ഉദാഹരണങ്ങൾ MySQL-ലാണ് നിർമ്മിച്ചിരിക്കുന്നത്, എന്നാൽ മറ്റ് DBMS-കൾക്ക് കാര്യമായ വ്യത്യാസങ്ങളൊന്നും ഉണ്ടാകരുത്.

ഉപയോക്തൃ പട്ടിക - ഉപയോക്താക്കൾ:

യൂസർ ഐഡി
രജിസ്ട്രേഷൻ തീയതി

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

യൂസർ ഐഡി
ബില്ലിംഗ് മാസം
രജിസ്ട്രേഷൻ തീയതി
മാസങ്ങൾ ഡിഫ്

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

ഫലം:

ഉപയോക്താക്കളുടെ എണ്ണം
ബില്ലിംഗ് മാസം
രജിസ്ട്രേഷൻ മാസം
മാസങ്ങൾ ഡിഫ്

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-ലേക്ക് കയറ്റുമതി ചെയ്യാനും മനോഹരമായ പട്ടികകളാക്കി മാറ്റാനും കഴിയും, എന്നാൽ ഇത് ഞങ്ങളുടെ രീതിയല്ല!

ഇൻററാക്റ്റീവ് ഗ്രാഫായി കോഹോർട്ടുകളെ കാണിക്കാം ഗ്രാഫാന.

ഇത് ചെയ്യുന്നതിന്, ഗ്രാഫാനയ്ക്ക് അനുയോജ്യമായ ഒരു ഫോർമാറ്റിലേക്ക് ഡാറ്റ പരിവർത്തനം ചെയ്യുന്നതിന് ഞങ്ങൾ മറ്റൊരു ചോദ്യം ചേർക്കുന്നു:

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 ഉം ഗ്രാഫാനയും ഉള്ള ഒരു ഡോക്കർ ഇമേജ് ആണ്, അത് നിങ്ങളുടെ കമ്പ്യൂട്ടറിൽ പ്രവർത്തിപ്പിക്കാൻ കഴിയും. ഡാറ്റാബേസിൽ 2018 ജനുവരി മുതൽ 2019 ജൂലൈ വരെയുള്ള ഒന്നര വർഷത്തേക്കുള്ള ഡെമോ ഡാറ്റ ഇതിനകം അടങ്ങിയിരിക്കുന്നു.

നിങ്ങൾക്ക് വേണമെങ്കിൽ, ഈ ചിത്രത്തിലേക്ക് നിങ്ങളുടെ ഡാറ്റ ലോഡ് ചെയ്യാം.

SQL-ലെ കോഹോർട്ട് വിശകലനത്തെക്കുറിച്ചുള്ള PS ലേഖനങ്ങൾ:

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

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

അവലംബം: www.habr.com