محاسبه ریترن کاربران با استفاده از کوهورت در postgreSql

اگر در فضای استارت آپی قرار گرفته باشید می‌دانید که تحلیل داده‌ها اهمیت زیادی برای استارت آپ‌ها و بخصوص سرمایه‌گذارها دارد، به طور مثال داده‌هایی از قبیل اینکه کاربران چه میزان هستند چطور از محصول ما استفاده می کنند، آیا محصول ما برایشان جذاب بوده که دوباره برگردند و …
محاسبه ی داده ها روی نمودار کوهورت مزیت های زیادی دارد از جمله اینکه میتوان رفتار کاربران را گروه بندی و با یکدیگر مقایسه کرد.
در این نوشته می‌خواهم برای شما توضیح دهم که چطور کوهورت را برای داده هایتان بدست آورید. ابتدا با مفهوم ریترن و کوهورت آشنا شویم سپس سراغ محاسبه ی آن میرویم.

ریترن

یکی از داده‌های مهم که در پیش‌بینی مالی یک محصول و میزان پایداری آن بهتر است بدانیم، میزان ریترن و چرن کاربران آن محصول است. این داده‌ها میزان رشد یک استارت آپ را مشخص می‌کنند.
ریترن در واقع نرخ بازگشت و چرن نرخ ریزش کاربران جذب شده می‌باشد. جذب در صورتی موفق است که کاربران دوباره برگردند وگرنه ناگزیریم هزینه‌ی زیادی را در بخش مارکتینگ خرج کنیم و همچنین یک زنگ خطر برای محصول ماست.
نگهداری کاربر جذب شده راحت‌تر و کم هزینه‌تر از گرفتن کاربر جدید است. احتمال اینکه کاربر جذب شده هزینه‌ی مالی/زمانی بیشتری روی محصول ما انجام دهد بیشتر از کاربر جدید است. البته این را در نظر داشته باشید که همواره درصدی از مشتریان از دست خواهند رفت و تلاش بر کم کردن این عدد می‌باشد.


نمودار نرخ بازگشت یا ریترن ریت
نمودار نرخ بازگشت یا ریترن ریت

بطور مثال محصول ما یک نوع مارکت پلیس است. در ماه ژانویه 150 نفر خرید انجام داده‌اند. در ماه فوریه تعداد 260 کاربر خرید میکند و ازین میان 45 نفر از کاربرانِ ژانویه حضور دارند. دراین حالت 45 نفر از کاربران ژانویه ریترن و 105 نفرشان چرن شده‌اند.
نرخ ریترن برای ماه ژانویه برابر است با: تعداد کاربران ریترن شده در ماه بعد به تعداد کل کاربران در ماه ژانویه.
که در این حالت 30 درصد است.

کوهورت

در کوهورت کاربران را به گروه‌های مرتبط زمانی که ویژگی‌ها یا تجربه های مشترکی دارند تقسیم بندی می‌کنیم. بر اساس نوع کوهورت ریترن یا چرن را محاسبه میکنیم. تحلیل داده های کاربران روی کوهورت این مزیت را دارد که کاربران هم گروه را بطور مستقل و هم با گروه های دیگر مقایسه شوند.

نمودار کوهورت
نمودار کوهورت

مثلا روی نمودار بالا که کوهورت ریترن خرید کاربران بصورت ماهانه میباشد، مشخص است که کوهورت ماه may نرخ ریترن بهتری داشتند. مقطع‌هایی که ریزش کاربر داشتیم مشخص است و میتوان تصمیم گرفت در چه بازه هایی باید برنامه‌هایی برای بازگشت کاربران به محصول در نظر گرفت.
کاربران فعالیت‌های مختلفی از قبیل بازدید از سایت، فعالیت در اپ، خرید و… روی محصول می‌توانند انجام دهند. برای هر کدام میتوان کوهورت بدست آورد.


ابزارهای آماده محاسبه کوهورت

گوگل آنالیتیکس و فایربیس از جمله ابزارهای آماده‌ای هستند که می‌توان با استفاده از آنها کوهورت بدست آورد.
گوگل آنالیتیکس ابزار بسیار مفیدی در حوزه‌ی سئو و مارکتینگ است. و امکانات بسیار جالبی در اختیار ما قرار می‌دهد. یکی از ابزارهای مفید آن آنالیز کوهورت است.

گوگل آنالیتیکس
گوگل آنالیتیکس

هرچند ابزارهای آماده‌ای در این زمینه وجود دارد ولی گاهی ما نیاز به داده‌هایی پیدا می‌کنیم که با این ابزارها قابل دستیابی نیستند. گاه داده‌های مورد نیاز ما در پایگاه داده وجود دارند یا فیلترهای آماده‌ی آنها در مساله ما قابل استفاده نیستند. در این زمان ما ناگزیریم خودمان دست بکار شویم و برای حل مسأله‌مان کوهورت را محاسبه کنیم.


محاسبه ی کوهورت در postgreSql

اکنون که با مفهوم و کاربرد کوهورت آشنا شدید به نحوه‌ی محاسبه آن در زبان postgreSql می‌پردازیم.

فرض کنیم ما یک سایت خرید و فروش داریم و مسأله‌ی ‌‌‌‌ ما این هست:
مقایسه‌ی ریترن خرید کاربران در نیمه‌ی اول سال 2018 بصورت ماهیانه

برای حل این مساله ابتدا موارد زیر را مشخص کنیم:
نوع، سایز، محدوده ی زمانی و معیار

نوع: خرید
سایز: ماهیانه
محدوده ی زمانی: نیمه اول سال 2018
معیار: اولین خرید

چون معیار ما اولین خرید کاربر و نوع هم روی خرید کاربر پس ما نیاز به جدول خرید با حداقل فیلدهای زیر داریم:

--payments table
user_id
paid_at

فایل زیر حاوی دیتای نمونه این جدول می‌باشد.

https://github.com/abdiz/return_cohort/blob/master/cohort_data_source.sql


حالا یک دستور ساده‌ Select از جدول payment را اجرا می‌کنیم.

SELECT *
FROM payments

خروجی:

payments
payments
  • قدم اول : لیست کاربران به همراه اولین خریدشان

به دستور بالا تابع min روی فیلد paid_at را اضافه می‌کنیم تا زمان اولین خرید کاربر را بدست آوریم. چون اولین خرید را به ازای هر کاربر نیاز داریم، پس روی فیلد user_id گروه‌بندی(Group By) می‌کنیم.

باید کاربرانی را مورد مطالعه قرار دهیم که اولین خریدشان نیمه‌ی اول سال 2018 هستند پس به دستور، شرطی را با استفاده از Having اعمال می‌کنیم که تنها کاربران در محدوده‌ی زمانی مسأله‌ی ‌‌‌‌‌‌‌‌‌‌خواسته شده را در خروجی بیاورد.

دقت داشته باشید که این شرط نباید با Where اعمال شود زیرا تابع min در Where در دسترس نیست.

-- users_with_first_payments
SELECT user_id,
                min(paid_at) as first_paid
FROM payments
GROUP BY user_id
HAVING min(paid_at) >= '2019-01-01 00:00:00'
                and min(paid_at) <'2019-07-01 00:00:00'

خروجی:

users_with_first_payments
users_with_first_payments
  • قدم دوم: دسته‌بندی کاربران از روی اولین خریدشان

بر اساس سایزی که در مساله تعریف شده(ماه) کاربرانی که ماه اولین خریدشان مشترک است را در یک گروه قرار می‌دهیم. ولی فیلد تاریخ خرید ما از نوع timestamp هست که حاوی روز و ساعت و … هست. پس از تابع date_trunc در postgres برای کوتاه کردن زمان به ماه کمک می‌گیریم.

-- users_cohort_group
SELECT users_with_first_payments.user_id,
                date_trunc('month', first_paid) as cohort_group
FROM users_with_first_payments

خروجی:

users_cohort_group
users_cohort_group


  • قدم سوم: لیست کاربران به همراه سایر خریدهایشان

حال باید ماه‌هایی که کاربر در آن فعالیت خرید داشته را از روی جداول users_cohort_group و users_with_payments و Join بین آنها بدست آوریم.

تابع extract بخشی از تاریخ را جدا می‌کند و برمی‌گرداند. چون به این تابع ورودی month داده‌ایم عدد ماه را از مقدار زمانی که گرفته است برمی‌گرداند.

-- user_activity
SELECT users_cohort_group.user_id,
                EXTRACT(MONTH FROM payments.paid_at) as month
FROM users_cohort_group
INNER JOIN payments ON users_cohort_group.user_id = payments.user_id

خروجی:

user_activity
user_activity


  • قدم چهارم: میزان بازگشت کاربران در ماه های بعد از خرید اول به صورت دسته‌بندی شده

درنهایت می‌توانیم بر اساس کوهورت کاربران میزان خریدشان را در ماه‌های بعد بدست آوریم.

SELECT users_cohort_group.cohort_group as &quotCohort Group&quot,
        count(distinct user_activity.user_id) as &quotSize&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 1) as &quotMounth 01&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 2) as &quotMounth 02&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 3) as &quotMounth 03&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 4) as &quotMounth 04&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 5) as &quotMounth 05&quot,
        count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 6) as &quotMounth 06&quot
FROM user_activity
INNER JOIN users_cohort_group ON user_activity.user_id = users_cohort_group.user_id
GROUP BY users_cohort_group.cohort_group;

خروجی:

cohort
cohort


  • قدم نهایی: محاسبه‌ی درصد بازگشت کاربران

برای محاسبه‌ی درصد از فرمول زیر به ازای هر ماه استفاده می‌کنیم.

(count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 1) * 100) / count(distinct user_activity.user_id)


در نهایت کوئری ما به این شکل در می آید:

WITH users_with_first_payments as (
    SELECT user_id,
                    min(paid_at) as first_paid
    FROM payments
    GROUP BY user_id
    HAVING min(paid_at) >= '2019-01-01 00:00:00' and min(paid_at) <'2019-07-01 00:00:00'
),
users_cohort_group as (
    SELECT users_with_first_payments.user_id,
                    date_trunc('month', first_paid) as cohort_group
    FROM users_with_first_payments
),
user_activity as (
    SELECT users_cohort_group.user_id,
                    EXTRACT(MONTH FROM payments.paid_at) as month
    FROM users_cohort_group
    INNER JOIN payments ON users_cohort_group.user_id = payments.user_id
)
SELECT users_cohort_group.cohort_group as &quotCohort Group&quot,
    count(distinct user_activity.user_id) as &quotSize&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 1) * 100) / count(distinct user_activity.user_id) as &quotMounth 01&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 2) * 100) / count(distinct user_activity.user_id) as &quotMounth 02&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 3) * 100) / count(distinct user_activity.user_id) as &quotMounth 03&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 4) * 100) / count(distinct user_activity.user_id) as &quotMounth 04&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 5) * 100) / count(distinct user_activity.user_id) as &quotMounth 05&quot,
    (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 6) * 100) / count(distinct user_activity.user_id) as &quotMounth 06&quot
FROM user_activity
INNER JOIN users_cohort_group ON user_activity.user_id = users_cohort_group.user_id
GROUP BY users_cohort_group.cohort_group;

این هم از کوهورت ما

cohort
cohort

امیدوارم مطلب برای شما مفید بوده باشد.