Grafana मा ग्राफको रूपमा प्रयोगकर्ता समूहहरू कसरी सङ्कलन गर्ने [+ उदाहरणको साथ डकर छवि]

हामीले Grafana प्रयोग गरेर प्रोमोपल्ट सेवामा प्रयोगकर्ताहरूको समूहको दृश्यावलोकन गर्ने समस्यालाई कसरी समाधान गर्यौं।

प्रवर्द्धन - प्रयोगकर्ताहरूको ठूलो संख्याको साथ एक शक्तिशाली सेवा। सञ्चालनको १० वर्षमा, प्रणालीमा दर्ताको संख्या १० लाख नाघेको छ। समान सेवाहरू सामना गर्नेहरूलाई थाहा छ कि प्रयोगकर्ताहरूको यो एर्रे एकरूपताबाट टाढा छ।

कसैले साइन अप गर्यो र सदाको लागि "निदायो"। कसैले आफ्नो पासवर्ड बिर्स्यो र छ महिनाको अवधिमा दुई पटक थप दर्ता गर्यो। कसैले नगद दर्तामा पैसा ल्याउँछ, र कोही सित्तैमा आयो उपकरण। र सबैबाट केही लाभ प्राप्त गर्न राम्रो हुनेछ।

हाम्रो जस्तो ठूला डाटा सेटहरूमा, व्यक्तिगत प्रयोगकर्ताको व्यवहारको विश्लेषण र सूक्ष्म निर्णयहरू गर्नु व्यर्थ छ। तर प्रवृत्तिहरू समात्न र ठूला समूहहरूसँग काम गर्न सम्भव र आवश्यक छ। जुन हामी वास्तवमा के गर्छौं।

सारांश

1. कोहोर्ट विश्लेषण के हो र यो किन आवश्यक छ?
2. SQL मा प्रयोगकर्ता दर्ताको महिनामा कोहोर्टहरू कसरी बनाउने।
3. कोहोर्टहरूलाई कसरी स्थानान्तरण गर्ने ग्राफाना.

यदि तपाइँ पहिले नै कोहोर्ट विश्लेषण के हो र यसलाई SQL मा कसरी गर्ने भनेर थाहा छ भने, सिधै अन्तिम खण्डमा जानुहोस्।

1. कोहोर्ट विश्लेषण के हो र यो किन आवश्यक छ?

कोहोर्ट विश्लेषण भनेको प्रयोगकर्ताहरूको विभिन्न समूहहरू (समूहहरू) तुलना गर्ने तरिका हो। प्रायजसो, हाम्रा समूहहरू हप्ता वा महिनामा बनाइन्छ जुन प्रयोगकर्ताले सेवा प्रयोग गर्न थाले। यहाँबाट प्रयोगकर्ताको जीवनकाल गणना गरिएको छ, र यो एक सूचक हो जसको आधारमा एक जटिल विश्लेषण गर्न सकिन्छ। उदाहरणका लागि, बुझ्नुहोस्:

• कसरी अधिग्रहण च्यानलले प्रयोगकर्ताको जीवनकाललाई प्रभाव पार्छ;
• कुनै पनि सुविधा वा सेवाको प्रयोगले जीवनभरलाई कसरी असर गर्छ;
• गत वर्षको तुलनामा सुविधा X को सुरुवातले जीवनकाललाई कसरी असर गर्यो।

2. SQL मा कोहोर्ट्स कसरी बनाउने?

लेखको आकार र सामान्य ज्ञानले हामीलाई हाम्रो वास्तविक डाटा यहाँ प्रस्तुत गर्न अनुमति दिँदैन - परीक्षण डम्पमा, डेढ वर्षको तथ्याङ्क: 1200 प्रयोगकर्ताहरू र 53 लेनदेनहरू। तपाईं यो डाटासँग खेल्न सक्नुहुन्छ भनेर, हामीले MySQL र Grafana सँग एक डकर छवि तयार गरेका छौं जसमा तपाईं आफैले यो सबै अनुभव गर्न सक्नुहुन्छ। लेखको अन्त्यमा GitHub मा लिङ्क गर्नुहोस्।

र यहाँ हामी एक सरल उदाहरण प्रयोग गरेर कोहोर्टहरूको सिर्जना देखाउनेछौं।

मानौं हामीसँग सेवा छ। प्रयोगकर्ताहरू त्यहाँ दर्ता गर्छन् र सेवाहरूमा पैसा खर्च गर्छन्। समयको साथ, प्रयोगकर्ताहरू छोड्छन्। हामी प्रयोगकर्ताहरू कति लामो समयसम्म बाँच्न चाहन्छौं, र तिनीहरूमध्ये कति सेवा प्रयोग गरेको पहिलो र दोस्रो महिना पछि हट्छन् भन्ने कुरा पत्ता लगाउन चाहन्छौं।

यी प्रश्नहरूको जवाफ दिन, हामीले दर्ताको महिनामा आधारित समूहहरू निर्माण गर्न आवश्यक छ। हामी प्रत्येक महिनामा खर्च द्वारा गतिविधि मापन गर्नेछौं। खर्चको सट्टा, त्यहाँ अर्डर, सदस्यता शुल्क, वा कुनै अन्य समय-आधारित गतिविधि हुन सक्छ।

स्रोत डाटा

उदाहरणहरू MySQL मा बनाइएका थिए, तर अन्य DBMS को लागि त्यहाँ कुनै महत्त्वपूर्ण भिन्नता हुनु हुँदैन।

प्रयोगकर्ता तालिका - प्रयोगकर्ताहरू:

प्रयोगकर्ता ID
दर्ता मिति

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

लागत तालिका - बिलिङ:

प्रयोगकर्ता ID
मिति
योग

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

परिणाम:

प्रयोगकर्ता ID
मिति
दर्ता मिति

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

प्रयोगकर्ता ID
बिलिङ महिना
दर्ता मिति
महिनाको डिफ

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, र एक फेब्रुअरी प्रयोगकर्ता मार्चमा सक्रिय थिए।

स्वाभाविक रूपमा, ढाँचाहरू ठूलो डेटा सेटमा राम्रो देखिने छन्।

Grafana मा कोहोर्टहरू कसरी स्थानान्तरण गर्ने

हामीले कोहोर्टहरू कसरी बनाउने भनेर सिकेका छौं, तर जब त्यहाँ धेरै रेकर्डहरू छन्, अब तिनीहरूलाई विश्लेषण गर्न सजिलो छैन। रेकर्डहरू एक्सेलमा निर्यात गर्न सकिन्छ र सुन्दर तालिकाहरूमा गठन गर्न सकिन्छ, तर यो हाम्रो विधि होइन!

कोहोर्टहरूलाई अन्तरक्रियात्मक ग्राफको रूपमा देखाउन सकिन्छ ग्राफाना.

यो गर्नको लागि, हामी डेटालाई 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 सम्म डेमो वर्षको डेमो डेटा समावेश गर्दछ।

यदि तपाइँ चाहनुहुन्छ भने, तपाइँ यो छविमा तपाइँको डाटा लोड गर्न सक्नुहुन्छ।

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