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