Cohort Analysis، Retention، Churn و چرخه عمر مشتری در Power BI

شروع جلسه؛ فروش امروز کافی نیست

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

در این فصل New، Returning، Lost و Reactivated Customer را هم به شکل قابل استفاده برای CRM تحلیل می‌کنیم.

۳۷.۱. Cohort یعنی چه؟

Cohort یعنی گروهی از مشتری‌ها که یک ویژگی شروع مشترک دارند. رایج‌ترین تعریف در فروش، ماه اولین خرید مشتری است.

۳۷.۲. مثال ساده

اگر اولین خرید ۵۰۰ مشتری در فروردین بوده، این ۵۰۰ نفر Cohort فروردین هستند. بعد بررسی می‌کنیم در ماه صفر، ماه یک، ماه دو و ... چند نفرشان دوباره فعال بوده‌اند.

۳۷.۳. مدل ذهنی Cohort

۳۷.۴. First Purchase Date

First Purchase Date =
CALCULATE(
    MIN(FactSales[OrderDate]),
    REMOVEFILTERS(DimDate)
)

۳۷.۵. نکته مهم First Purchase

Measure بالا باید در Customer Context ارزیابی شود. هدف این است که فیلتر تاریخ جاری حذف شود ولی Customer حفظ شود.

۳۷.۶. Cohort Month

Cohort Month =
VAR D = [First Purchase Date]
RETURN
DATE(YEAR(D), MONTH(D), 1)

۳۷.۷. Cohort را Column بسازیم یا Measure؟

اگر First Purchase/Cohort یک ویژگی پایدار مشتری است و در ETL قابل محاسبه است، نگهداری آن در Customer Dimension یا Warehouse معمولاً ساده‌تر و قابل Auditتر است. برای آموزش، منطق DAX را هم بررسی می‌کنیم.

۳۷.۸. Acquisition Month

Acquisition Month همان ماهی است که مشتری برای اولین بار وارد چرخه خرید شده است.

۳۷.۹. Activity Month

Activity Month ماهی است که تراکنش فعلی در آن رخ داده. فاصله Activity Month با Cohort Month، سن مشتری در Cohort را می‌سازد.

۳۷.۱۰. Month Index

Month Index =
DATEDIFF(
    [Cohort Month],
    MIN(DimDate[Date]),
    MONTH
)

۳۷.۱۱. چرا Month Index؟

به جای اینکه ستون‌ها را با نام ماه‌های تقویمی بسازیم، آن‌ها را Month 0، Month 1، Month 2 و ... می‌نامیم. این کار Cohortهای مختلف را قابل مقایسه می‌کند.

۳۷.۱۲. Month 0

Month 0 همان دوره Acquisition است. اگر Cohort بر اساس اولین خرید تعریف شده، مشتری Cohort در Month 0 فعال محسوب می‌شود.

۳۷.۱۳. Active Customers

Active Customers =
DISTINCTCOUNT(FactSales[CustomerKey])

۳۷.۱۴. Cohort Size

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

۳۷.۱۵. Cohort Size Concept

Cohort Size =
/* تعداد مشتریان Acquisition Cohort در Month 0 */

۳۷.۱۶. Retained Customers

برای هر Cohort و Month Index باید تعداد مشتری‌های همان Cohort را که در آن دوره Activity داشته‌اند محاسبه کنیم.

۳۷.۱۷. Retention Rate

Retention % =
DIVIDE(
    [Retained Customers],
    [Cohort Size]
)

۳۷.۱۸. Retention Matrix

۳۷.۱۹. خواندن Matrix

هر Row یک Cohort و هر Column سن Cohort است. اگر M1 ضعیف باشد، مشکل احتمالاً در بازگشت سریع مشتری بعد از خرید اول است.

۳۷.۲۰. چرا خانه‌های آخر Blank هستند؟

چون Cohortهای جدید هنوز فرصت نکرده‌اند به Monthهای آینده برسند. Blank را نباید صفر واقعی تلقی کنیم.

۳۷.۲۱. Retention vs Repeat Purchase

Repeat Purchase می‌گوید چند مشتری دوباره خرید کرده‌اند؛ Cohort Retention زمان بازگشت را هم در ساختار Acquisition Cohort نشان می‌دهد.

۳۷.۲۲. Returning Customer

Returning Customer مشتری‌ای است که قبل از دوره جاری سابقه خرید داشته و در دوره جاری هم فعال است.

۳۷.۲۳. New Customer

New Customer در دوره جاری اولین خرید عمر مشتری را انجام داده است.

۳۷.۲۴. New Customer Count

New Customers =
COUNTROWS(
    FILTER(
        VALUES(DimCustomer[CustomerKey]),
        [First Purchase Date] >= MIN(DimDate[Date]) &&
        [First Purchase Date] <= MAX(DimDate[Date])
    )
)

۳۷.۲۵. Returning Customer Concept

مجموعه مشتریان فعال دوره جاری را می‌گیریم و مشتریانی را که First Purchase آن‌ها قبل از شروع دوره است جدا می‌کنیم.

۳۷.۲۶. Customer Set Thinking

جلسه قبل EXCEPT و INTERSECT را یاد گرفتیم. اینجا همان تفکر مجموعه‌ای برای چرخه عمر مشتری استفاده می‌شود.

۳۷.۲۷. Current Customers

VAR CurrentCustomers =
    VALUES(FactSales[CustomerKey])

۳۷.۲۸. Previous Customers

VAR PreviousCustomers =
    CALCULATETABLE(
        VALUES(FactSales[CustomerKey]),
        DATEADD(DimDate[Date], -1, MONTH)
    )

۳۷.۲۹. Retained from Previous Period

VAR Retained =
    INTERSECT(CurrentCustomers, PreviousCustomers)
RETURN COUNTROWS(Retained)

۳۷.۳۰. Lost from Previous Period

VAR Lost =
    EXCEPT(PreviousCustomers, CurrentCustomers)
RETURN COUNTROWS(Lost)

۳۷.۳۱. Lost با Churn یکی نیست

اگر مشتری ماه قبل خرید کرده و این ماه خرید نکرده، الزاماً Churn قطعی نیست. در B2B ممکن است Cycle خرید سه‌ماهه باشد. تعریف Lost باید Business Rule داشته باشد.

۳۷.۳۲. Churn چیست؟

Churn یعنی مشتری طبق تعریف سازمان از چرخه فعال خارج شده است. تعریف می‌تواند «۹۰ روز بدون خرید»، «عدم تمدید قرارداد» یا Rule دیگری باشد.

۳۷.۳۳. Days Since Last Purchase

Days Since Last Purchase =
DATEDIFF(
    [Last Purchase Date],
    [Reference Date],
    DAY
)

۳۷.۳۴. Churn Flag نمونه

Churn Flag =
IF(
    [Days Since Last Purchase] > 90,
    1,
    0
)

۳۷.۳۵. ۹۰ روز عدد مقدس نیست

Threshold ریزش باید با رفتار خرید کسب‌وکار هماهنگ باشد. برای قطعات صنعتی ممکن است ۱۸۰ روز منطقی‌تر باشد؛ برای سرویس اشتراکی شاید ۳۰ روز.

۳۷.۳۶. At Risk

At Risk معمولاً مرحله قبل از Churn است؛ مثلاً مشتری‌ای که ۶۰ تا ۹۰ روز خرید نکرده ولی هنوز Lost قطعی محسوب نمی‌شود.

۳۷.۳۷. Lifecycle Bands

۳۷.۳۸. Lifecycle Status

Lifecycle Status =
VAR D = [Days Since Last Purchase]
RETURN
SWITCH(
    TRUE(),
    D <= 30, "Active",
    D <= 60, "Warm",
    D <= 90, "At Risk",
    "Lost"
)

۳۷.۳۹. Reactivated Customer

مشتری Reactivated کسی است که مدتی طبق Rule غیرفعال بوده و دوباره خرید کرده است.

۳۷.۴۰. Reactivation اهمیت دارد

اگر کمپین Win-back اجرا کرده‌ای، فقط Revenue را نگاه نکن؛ تعداد و ارزش مشتری‌های Reactivated را هم اندازه بگیر.

۳۷.۴۱. چرخه عمر مشتری

۳۷.۴۲. Churn Rate

Churn Rate % =
DIVIDE(
    [Churned Customers],
    [Customers at Start]
)

۳۷.۴۳. مخرج Churn

یکی از خطاهای رایج همین مخرج است. Churn را معمولاً نسبت به جمعیت در معرض ریزش محاسبه می‌کنیم، نه همه Customerهای تاریخچه.

۳۷.۴۴. Retention Rate ساده دوره‌ای

Period Retention % =
DIVIDE(
    [Retained Customers],
    [Previous Period Customers]
)

۳۷.۴۵. Customer Retention vs Revenue Retention

ممکن است تعداد مشتری حفظ شود ولی Revenue آن‌ها افت کند. بنابراین Customer Retention و Revenue Retention را جدا ببین.

۳۷.۴۶. Revenue Retention

Retained Customer Revenue =
/* Sales فقط برای مجموعه مشتریان retained */

۳۷.۴۷. Gross Revenue Retention

در مدل‌های Subscription، GRR و NRR مفاهیم مهمی‌اند. اگر کسب‌وکار پروژه شرکت هانا Subscription نیست، این KPIها را فقط وقتی وارد مدل کن که تعریف درآمد تکرارشونده وجود داشته باشد.

۳۷.۴۸. Cohort Revenue

به جای تعداد مشتری می‌توان Revenue هر Cohort را در طول زمان دنبال کرد.

۳۷.۴۹. Revenue per Cohort Customer

Cohort ARPC =
DIVIDE(
    [Cohort Revenue],
    [Cohort Size]
)

۳۷.۵۰. Cohort Profit

اگر Cost قابل اعتماد داری، Profit Retention می‌تواند از Revenue Retention هم مهم‌تر باشد.

۳۷.۵۱. Cohort by Channel

Cohort فقط ماه نیست. می‌توان Acquisition Channel، Campaign، Region یا Sales Rep را هم به تحلیل اضافه کرد.

۳۷.۵۲. Channel Quality

دو کمپین ممکن است تعداد Customer جدید یکسان بیاورند، ولی Retention ماه سوم آن‌ها متفاوت باشد. Cohort کمک می‌کند کیفیت Acquisition را بسنجی.

۳۷.۵۳. Cohort by Product

می‌توان بررسی کرد مشتریانی که اولین خریدشان از Product A بوده، در ادامه رفتار بهتری دارند یا Product B.

۳۷.۵۴. Cohort by Salesperson

برای B2B می‌توان Cohort را بر اساس Account Manager یا Sales Rep تحلیل کرد؛ البته مراقب حجم نمونه کوچک باش.

۳۷.۵۵. Sample Size

Retention 100% برای Cohort دو نفره با Cohort هزار نفره یک معنی ندارد. Cohort Size را کنار درصد نشان بده.

۳۷.۵۶. Seasonality

Cohortهای نوروز، نمایشگاه یا کمپین‌های فصلی ممکن است رفتار متفاوتی داشته باشند. مقایسه باید Context کسب‌وکار را در نظر بگیرد.

۳۷.۵۷. Partial Cohort

Cohort جدید هنوز Mature نشده. مقایسه M6 آن با Cohortهای قدیمی ممکن نیست چون هنوز شش ماه نگذشته.

۳۷.۵۸. Retention Heatmap

Matrix با Conditional Formatting یکی از بهترین Visualها برای Cohort است؛ درصدهای بالا و پایین سریع دیده می‌شوند.

۳۷.۵۹. Heatmap باید خوانا باشد

رنگ زیاد بدون Label و Scale مشخص باعث برداشت اشتباه می‌شود. درصد را داخل Cell هم نمایش بده.

۳۷.۶۰. Dashboard پیشنهادی

بالا Cardهای New Customers، Returning Customers، Retention %، At Risk و Churn Rate. وسط Cohort Heatmap. پایین Trend New vs Returning و Table مشتریان At Risk.

۳۷.۶۱. CRM Action List

۳۷.۶۲. Drill-through مشتری

از لیست At Risk به صفحه Customer 360 برو: تاریخچه سفارش، آخرین خرید، محصولات، Sales، Profit و تعاملات CRM.

۳۷.۶۳. Cohort و RFM

RFM وضعیت فعلی مشتری را خوب نشان می‌دهد؛ Cohort مسیر زمانی گروه‌ها را. ترکیب این دو دید بسیار قوی‌تری می‌دهد.

۳۷.۶۴. Cohort و Segmentation

می‌توان Retention را برای Champions، New Customers یا Channelهای مختلف مقایسه کرد تا بفهمیم کدام Segment پایدارتر است.

۳۷.۶۵. Data Model پیشنهادی

۳۷.۶۶. Snapshot چه زمانی لازم است؟

اگر می‌خواهی بدانیم «این مشتری در پایان خرداد At Risk بوده یا نه»، Snapshot ماهانه می‌تواند وضعیت تاریخی را ذخیره کند. Measure فعلی فقط وضعیت را بر اساس Context فعلی بازسازی می‌کند.

۳۷.۶۷. Snapshot Fact

برای Customer Lifecycle تاریخی می‌توان FactCustomerSnapshot با CustomerKey، SnapshotDate، Status، Recency و KPIهای موردنیاز طراحی کرد.

۳۷.۶۸. Performance

Cohort روی میلیون‌ها Customer و تراکنش می‌تواند سنگین شود. First Purchase و Acquisition Cohort را در Warehouse/ETL پیش‌محاسبه کردن اغلب مفید است.

۳۷.۶۹. Performance؛ Set Size

EXCEPT و INTERSECT روی مجموعه‌های بزرگ را با Performance Analyzer و DAX Studio بررسی کن.

۳۷.۷۰. Performance؛ Grain

اگر فقط Customer-Month برای Retention لازم داری، Aggregation مناسب در Data Warehouse می‌تواند حجم تحلیل را بسیار کم کند.

۳۷.۷۱. خطای رایج؛ اولین خرید داخل Slicer

اگر First Purchase فقط داخل بازه انتخابی محاسبه شود، مشتری قدیمی ممکن است اشتباه New شناخته شود.

۳۷.۷۲. خطای رایج؛ صفر به جای Blank

برای Cohortی که هنوز به M6 نرسیده، صفر گذاشتن یعنی ادعا می‌کنی Retention صفر بوده؛ در حالی که دوره هنوز اتفاق نیفتاده.

۳۷.۷۳. خطای رایج؛ Churn بدون Business Rule

عدم خرید در یک ماه به تنهایی Churn نیست مگر اینکه مدل کسب‌وکار همین تعریف را داشته باشد.

۳۷.۷۴. خطای رایج؛ Cohort Size کوچک

درصدهای شدید روی Cohort کوچک را بدون نمایش تعداد تفسیر نکن.

۳۷.۷۵. خطای رایج؛ CustomerKey نامعتبر

اگر یک مشتری با چند شناسه تکراری در CRM وجود داشته باشد، Retention و New Customer Analysis خراب می‌شود. Master Data مهم است.

۳۷.۷۶. Best Practiceها

First Purchase را مستقل از Date Slicer تعریف کن.

Cohort Size را کنار Retention نشان بده.

Month Index استاندارد بساز.

Blank دوره آینده را صفر نکن.

Churn Threshold را با Business تأیید کن.

New/Returning/Lost را دقیق تعریف کن.

برای وضعیت تاریخی Snapshot را بررسی کن.

RFM و Cohort را مکمل هم ببین.

Performance را روی Customerهای زیاد تست کن.

۳۷.۷۷. تمرین ۱

First Purchase Date و Cohort Month هر Customer را بساز و ۱۰ مشتری را دستی Validate کن.

۳۷.۷۸. تمرین ۲

یک Retention Matrix با Cohort Month در Row و Month Index در Column طراحی کن.

۳۷.۷۹. تمرین ۳

New، Returning و Lost Customers را برای ماه جاری نسبت به ماه قبل محاسبه کن.

۳۷.۸۰. تمرین ۴

Lifecycle Status با Active، Warm، At Risk و Lost بساز و Threshold را با What-if Parameter قابل تغییر کن.

۳۷.۸۱. تمرین ۵

Retention مشتری را برای دو Region یا دو Acquisition Channel مقایسه کن.

۳۷.۸۲. تمرین چالشی

یک صفحه CRM بساز که Cohort Heatmap، Churn Trend، At Risk List و Drill-through Customer 360 داشته باشد. برای هر KPI تعریف Business Rule را کنار مستندات ثبت کن.

۳۷.۸۳. پروژه Session؛ Retention Intelligence Pack شرکت هانا

هدف نهایی این است که Dashboard فقط بگوید چند مشتری داریم؛ باید نشان دهد مشتری‌ها بعد از ورود چه سرنوشتی پیدا می‌کنند.

جمع‌بندی Session 37

Cohort Analysis به ما اجازه می‌دهد کیفیت رشد را ببینیم. رشد سالم فقط جذب مشتری جدید نیست؛ مشتری باید برگردد، ارزش ایجاد کند و قبل از ریزش قابل شناسایی باشد.

Acquire → Retain → Detect Risk → Reactivate → Grow

Session 38 را وارد طراحی KPI Framework حرفه‌ای می‌کنیم: KPI Tree، Actual/Target/Variance، Status، Dynamic KPI Selector، Scorecard و Executive Dashboard.