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

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

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

هرچند ابزارهای آمادهای در این زمینه وجود دارد ولی گاهی ما نیاز به دادههایی پیدا میکنیم که با این ابزارها قابل دستیابی نیستند. گاه دادههای مورد نیاز ما در پایگاه داده وجود دارند یا فیلترهای آمادهی آنها در مساله ما قابل استفاده نیستند. در این زمان ما ناگزیریم خودمان دست بکار شویم و برای حل مسألهمان کوهورت را محاسبه کنیم.
اکنون که با مفهوم و کاربرد کوهورت آشنا شدید به نحوهی محاسبه آن در زبان postgreSql میپردازیم.
فرض کنیم ما یک سایت خرید و فروش داریم و مسألهی ما این هست:
مقایسهی ریترن خرید کاربران در نیمهی اول سال 2018 بصورت ماهیانه
برای حل این مساله ابتدا موارد زیر را مشخص کنیم:
نوع، سایز، محدوده ی زمانی و معیار
نوع: خرید
سایز: ماهیانه
محدوده ی زمانی: نیمه اول سال 2018
معیار: اولین خرید
چون معیار ما اولین خرید کاربر و نوع هم روی خرید کاربر پس ما نیاز به جدول خرید با حداقل فیلدهای زیر داریم:
--payments table user_id paid_at
فایل زیر حاوی دیتای نمونه این جدول میباشد.
حالا یک دستور ساده Select از جدول payment را اجرا میکنیم.
SELECT * FROM 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'
خروجی:

بر اساس سایزی که در مساله تعریف شده(ماه) کاربرانی که ماه اولین خریدشان مشترک است را در یک گروه قرار میدهیم. ولی فیلد تاریخ خرید ما از نوع 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_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
خروجی:

درنهایت میتوانیم بر اساس کوهورت کاربران میزان خریدشان را در ماههای بعد بدست آوریم.
SELECT users_cohort_group.cohort_group as "Cohort Group", count(distinct user_activity.user_id) as "Size", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 1) as "Mounth 01", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 2) as "Mounth 02", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 3) as "Mounth 03", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 4) as "Mounth 04", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 5) as "Mounth 05", count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 6) as "Mounth 06" 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;
خروجی:

برای محاسبهی درصد از فرمول زیر به ازای هر ماه استفاده میکنیم.
(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 "Cohort Group", count(distinct user_activity.user_id) as "Size", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 1) * 100) / count(distinct user_activity.user_id) as "Mounth 01", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 2) * 100) / count(distinct user_activity.user_id) as "Mounth 02", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 3) * 100) / count(distinct user_activity.user_id) as "Mounth 03", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 4) * 100) / count(distinct user_activity.user_id) as "Mounth 04", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 5) * 100) / count(distinct user_activity.user_id) as "Mounth 05", (count(distinct user_activity.user_id) FILTER ( WHERE user_activity.month = 6) * 100) / count(distinct user_activity.user_id) as "Mounth 06" 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;
این هم از کوهورت ما

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