🥇கிராஃபனாவில் வரைபட வடிவில் பயனர்களின் கூட்டங்களை எவ்வாறு சேகரிப்பது [+உதாரணத்துடன் டோக்கர் படம்]

கிராஃபானாவைப் பயன்படுத்தி ப்ரோமோபுல்ட் சேவையில் பயனர் கூட்டாளர்களைக் காட்சிப்படுத்துவதில் உள்ள சிக்கலை நாங்கள் எவ்வாறு தீர்த்தோம்.

ஊக்குவிக்க - அதிக எண்ணிக்கையிலான பயனர்களைக் கொண்ட சக்திவாய்ந்த சேவை. 10 வருட செயல்பாட்டில், கணினியில் பதிவு செய்யப்பட்டவர்களின் எண்ணிக்கை ஒரு மில்லியனைத் தாண்டியுள்ளது. இதே போன்ற சேவைகளை எதிர்கொண்டவர்கள் இந்த பயனர்களின் வரிசை ஒரே மாதிரியானவை அல்ல என்பதை அறிவார்கள்.

யாரோ பதிவு செய்து, நிரந்தரமாக "தூங்கிவிட்டார்கள்". யாரோ ஒருவர் தனது கடவுச்சொல்லை மறந்துவிட்டு, ஆறு மாதங்களில் மேலும் இரண்டு முறை பதிவு செய்துள்ளார். யாரோ பணப் பதிவேட்டில் பணத்தைக் கொண்டு வருகிறார்கள், யாரோ இலவசங்களுக்காக வந்தார்கள் கருவிகள். மேலும் எல்லோரிடமிருந்தும் ஓரளவு லாபம் கிடைத்தால் நன்றாக இருக்கும்.

எங்களுடையது போன்ற பெரிய தரவுத் தொகுப்புகளில், ஒரு தனிப்பட்ட பயனரின் நடத்தையை பகுப்பாய்வு செய்து நுண்ணிய முடிவுகளை எடுப்பது அர்த்தமற்றது. ஆனால் போக்குகளைப் பிடிப்பது மற்றும் பெரிய குழுக்களுடன் வேலை செய்வது சாத்தியம் மற்றும் அவசியமானது. நாம் சரியாக என்ன செய்கிறோம்.

சுருக்கம்

ஒருங்கிணைந்த பகுப்பாய்வு என்றால் என்ன, அது ஏன் தேவைப்படுகிறது?
SQL இல் பயனர் பதிவு செய்த மாதத்திற்கு ஏற்ப இணைகளை உருவாக்குவது எப்படி.
கூட்டாளிகளை எப்படி மாற்றுவது கிரபனா.

கூட்டு பகுப்பாய்வு என்றால் என்ன மற்றும் SQL இல் அதை எப்படி செய்வது என்பது உங்களுக்கு ஏற்கனவே தெரிந்திருந்தால், கடைசி பகுதிக்கு நேராக செல்லவும்.

1. ஒருங்கிணைந்த பகுப்பாய்வு என்றால் என்ன, அது ஏன் தேவைப்படுகிறது?

கோஹார்ட் பகுப்பாய்வு என்பது பயனர்களின் வெவ்வேறு குழுக்களை (கோஹார்ட்ஸ்) ஒப்பிடுவதை அடிப்படையாகக் கொண்ட ஒரு முறையாகும். பெரும்பாலும், எங்கள் குழுக்கள் பயனர் சேவையைப் பயன்படுத்தத் தொடங்கிய வாரம் அல்லது மாதத்தில் உருவாக்கப்படுகின்றன. இங்கிருந்து பயனரின் வாழ்நாள் கணக்கிடப்படுகிறது, மேலும் இது ஒரு சிக்கலான பகுப்பாய்வை மேற்கொள்ளக்கூடிய ஒரு குறிகாட்டியாகும். உதாரணமாக, புரிந்து கொள்ளுங்கள்:

கையகப்படுத்தல் சேனல் பயனரின் வாழ்நாளை எவ்வாறு பாதிக்கிறது;
எந்த அம்சம் அல்லது சேவையின் பயன்பாடு வாழ்நாளை எவ்வாறு பாதிக்கிறது;
அம்சம் X இன் வெளியீடு கடந்த ஆண்டை ஒப்பிடும்போது வாழ்நாளை எவ்வாறு பாதித்தது.

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

ஒவ்வொரு மாதமும் செயல்படுத்தப்பட்ட பயனர்களை நாங்கள் கணக்கிடுகிறோம் - 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 க்கு ஏற்றுமதி செய்து அழகான அட்டவணைகளாக உருவாக்கலாம், ஆனால் இது எங்கள் முறை அல்ல!

கோஹார்ட்ஸ் ஒரு ஊடாடும் வரைபடமாக காட்டப்படலாம் கிரபனா.

இதைச் செய்ய, தரவை 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 களஞ்சியம் உங்கள் கணினியில் இயக்கக்கூடிய MySQL மற்றும் Grafana கொண்ட டாக்கர் படம். தரவுத்தளத்தில் ஏற்கனவே ஜனவரி 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

கிராஃபானாவில் பயனர் கூட்டங்களை வரைபடங்களாக எவ்வாறு சேகரிப்பது [+ உதாரணத்துடன் டோக்கர் படம்]

சுருக்கம்

1. ஒருங்கிணைந்த பகுப்பாய்வு என்றால் என்ன, அது ஏன் தேவைப்படுகிறது?

2. SQL இல் கூட்டாளிகளை உருவாக்குவது எப்படி?

ரா தரவு

கூட்டாளிகளை கிராஃபனாவிற்கு மாற்றுவது எப்படி

கருத்தைச் சேர் Отменить ответ