Cohort analysis in Power BI: building a retention and LTV model from scratch

The goal: to let you build merchant retention and lifetime value analysis end to end in Power BI, for a payment processing business. This guide covers both the practical steps (Power Query, DAX, model design) and how to read the result. At the end there is Python that generates synthetic sample data so you can test the whole thing before touching production. If you would rather have someone build this model on your own data, that is what our analytics side does.

Contents

• Why cohort analysis
• Data model and schema
• Power Query (ETL): cleaning and normalising
• Calculated columns: the cohort foundations
• The core DAX measures
• Generating sample data (Python)
• Testing and validation
• Real-world examples

Why cohort analysis, briefly

Cohort analysis follows the behaviour over time of the users who joined in a given period, usually a month. In this case the users are merchants. In payment processing it is particularly valuable, because it answers questions a monthly total never can.

• It shows the balance between merchant acquisition cost and lifetime value.
• It lets you judge whether the growth you are seeing is sustainable or simply new.
• It reveals which onboarding route and which integration actually works.
• It locates the critical churn window, which is what makes prevention plannable.
• It makes performance comparable between segments.

The reason a monthly total hides all of this is straightforward: strong acquisition in the current month masks decay in every earlier month. Revenue can grow for a year while every cohort inside it is getting worse.

Business questions this answers

Once this system is in place, these are the questions you can put to it.

Retention and churn

• What share of the merchants acquired in a given month are still active at month 1, 3, 6 and 12?
• Which month does the drop concentrate in? That is when intervention has to happen.
• Which cohorts retain better than others, and what was different about them?

Lifetime value and revenue

• Which cohorts build lifetime value quickly and which stay flat?
• How does average cumulative revenue develop over the months?
• How many months does acquisition cost take to pay back?

Segmentation

• Do retention rates differ by integration type (API, iframe, virtual POS)?
• How does merchant behaviour vary by line of business?
• Is there a real difference between high and low volume merchants, or only a size difference?

Operational decisions

• Which short-term metrics let me detect churn risk early?
• In which months is merchant acquisition more successful, and why?
• Which onboarding improvements would have the largest effect?

Data model and schema

These are the fields the model needs. Everything downstream assumes them.

Three things worth getting right before anything else. Period_Month has to be the first day of the month (YYYY-MM-01), because that is what makes Power BI’s time intelligence line up with your cohort buckets. Compute Net_Revenue during ETL rather than as a measure, which keeps the model lighter. And confirm merchant IDs are genuinely unique: if one merchant holds several IDs, deduplicate before you model, or every cohort will be wrong in a way that is very hard to see later.

Generating sample data (Python)

The script below produces about 10,000 merchants of monthly data from 2022-01 to 2024-12, with behaviour realistic enough to develop against.

 import pandas as pd
 import numpy as np
 import random
 from datetime import datetime, timedelta
 
 # Seed, so the output is reproducible
 np.random.seed(42)
 random.seed(42)
 
 # Parameters
 n_merchants = 10000
 start_date = datetime(2022, 1, 1)
 end_date = datetime(2024, 12, 1)
 months = pd.date_range(start=start_date, end=end_date, freq='MS').to_pydatetime().tolist()
 
 # Product types and category codes
 product_types = ['API', 'iframe', 'vPOS', 'POS']
 mcc_codes = ['5411', '5812', '5651', '5999', '5611', '5311', '5732', '5691']
 
 rows = []
 
 # Base attributes per merchant
 merchant_avg_tx_value = np.random.lognormal(mean=8.0, sigma=0.8, size=n_merchants)
 merchant_base_retention = np.random.choice([0.9, 0.7, 0.6, 0.5], size=n_merchants, p=[0.25, 0.25, 0.3, 0.2])
 
 for i in range(n_merchants):
 merchant_id = f"M{100000 + i}"
 cohort_month = random.choice(months)
 product = random.choice(product_types)
 mcc = random.choice(mcc_codes)
 avg_tx = merchant_avg_tx_value[i]
 base_ret = merchant_base_retention[i]
 
 # Retention adjusted by product type
 if product == 'API':
 base_ret *= 1.05
 elif product == 'iframe':
 base_ret *= 0.85
 elif product == 'vPOS':
 base_ret *= 0.9
 else:
 base_ret *= 0.95
 
 # For each month since the cohort started
 for month in months:
 if month < cohort_month: continue months_since=(month.year - cohort_month.year) * 12 + (month.month -
 cohort_month.month) # Probability of retention decays over time retention_prob=max(0.01, base_ret * np.exp(-0.25 *
 months_since)) active=np.random.rand() < retention_prob if not active: continue # Transaction count (Poisson)
 tx_count=max(1, np.random.poisson(lam=2 + 0.02 * months_since)) # Mevsimsellik etkisi seasonal=1.0 if month.month in
 [11, 12]: # November and December seasonal=1.2 elif month.month in [6, 7, 8]: # Summer months seasonal=1.05 # Transaction volume
 hesaplama tx_volume=max(0.0, np.random.normal( loc=tx_count * avg_tx * seasonal, scale=0.1 * tx_count * avg_tx ))
 gross_revenue=float(round(tx_volume, 2)) # Cost calculation cost_rate=np.random.uniform(0.01, 0.035)
 total_cost=round(gross_revenue * cost_rate, 2) net_revenue=round(gross_revenue - total_cost, 2)
 rows.append({ "Period_Month" : month.strftime("%Y-%m-01"), "Merchant_ID" : merchant_id, "Product_Type" :
 product, "Gross_Revenue" : gross_revenue, "Category_Code" : mcc, "Total_Costs" : total_cost, "Net_Revenue" :
 net_revenue, "Transaction_Volume" : round(gross_revenue, 2), "Transaction_Count" : int(tx_count) }) # Build the DataFrame
 df=pd.DataFrame(rows) df['Period_Month']=pd.to_datetime(df['Period_Month']) # Shuffle the rows df=df.sample(frac=1,
 random_state=42).reset_index(drop=True) # CSV olarak kaydetme df.to_csv("cohort_sample_10000_2022-2024.csv",
 index=False) print(f"Dataset built: {len(df)} rows, {df['Merchant_ID'].nunique()} unique merchants")
 print(f"Date range: {df['Period_Month'].min()} - {df['Period_Month'].max()}") print(f"Average monthly revenue:
 ₺{df['Net_Revenue'].mean():,.2f}") # Show the first few rows print("nSample rows:")
 print(df.head().to_string()) 

What the simulation models: each merchant’s average transaction value is drawn from a log-normal distribution; the monthly probability of continuing decays exponentially; seasonality is included, with the year-end peak; and retention differs by product type, which is what makes the segmentation sections worth reading.

Because Period_Month arrives as the first of the month, the output is ready for time intelligence with no further date work. And because the data is synthetic it contains no real customer information, so it is safe to develop and demo against.

Power Query (ETL): preparing the data

Here the data is read from the source, cleaned, and brought to a state where Period_Month and Net_Revenue are ready to use.

1. Read the source (CSV, SQL or parquet).
2. Normalise the date: build Period_Month with Date.FromText and Date.StartOfMonth.
3. Clean nulls: filter out rows with no merchant ID.
4. Compute Net_Revenue here rather than in DAX.
5. Drop unused columns and fix the types.

Why Date.StartOfMonth and not a formatted string. It produces an exact month bucket, keeps Power BI’s time intelligence consistent, and removes the class of bug where a cohort calculation silently disagrees with itself because two dates in the same month are not equal.

Alongside that, three quality controls are worth building in from the start: filter null and empty merchant IDs, remove negative revenue values, and trim text fields. On performance, Table.Buffer belongs in the last step to fix the transformations, and computing Net_Revenue in ETL keeps the model measurably lighter.

Modelling: the date table and relationships

Every model needs a date table. This one is generated in DAX rather than imported, so it cannot go stale.

DateTable =
 ADDCOLUMNS(
 CALENDAR(DATE(2021,1,1), DATE(2026,12,31)),
 "Year", YEAR([Date]),
 "Month", FORMAT([Date], "MMMM"),
 "MonthNumber", MONTH([Date]),
 "YearMonth", FORMAT([Date], "yyyy-MM"),
 "Ay_Start", DATE(YEAR([Date]),MONTH([Date]),1),
 "Quarter", "Q" & QUARTER([Date])
 )

Model relationships

One relationship carries the whole model: DateTable[Date] one-to-many to Transaction[Period_Month].

• It is what makes every time series analysis in the report correct rather than approximately correct.
• Functions like DATEADD and SAMEPERIODLASTYEAR only behave with a proper date table behind them.
• Date range handling lives in one place instead of being repeated in every measure.

Calculated columns: the cohort foundations

These two belong in a calculated column or in ETL rather than in a measure, for three reasons: they are constant per merchant, they do not need recomputing on every report query, and materialising them is a measurable performance gain on a large transaction table.

Cohort_Date, the merchant’s first active month.

Cohort_Date =
CALCULATE(
 MIN('Transaction'[Period_Month]),
 ALLEXCEPT('Transaction','Transaction'[Merchant_ID])
)

It computes the month each merchant first transacted, which is their acquisition month. Everything else in this article rests on it. One caveat that catches people: this is first transacting month, not signup month. If your business cares about the gap between the two, and in payments it usually should, carry the signup date as a separate column rather than redefining this one.

Months_Since_Cohort, the cohort index.

Months_Since_Cohort =
IF(
 ISBLANK('Transaction'[Cohort_Date]) || ISBLANK('Transaction'[Period_Month]),
 BLANK(),
 DATEDIFF('Transaction'[Cohort_Date], 'Transaction'[Period_Month], MONTH)
)

This gives the number of months elapsed since acquisition (0, 1, 2, 3 and so on) and is the axis every heatmap is built on. Set its data type to whole number, or the visuals will sort it as text and month 10 will appear between month 1 and month 2.

Sanity check: on rows where Months_Since_Cohort = 0, Cohort_Active_Count must equal Cohort_Size. If it does not, the problem is in these two columns and not in the measures below.

The core DAX measures

These are every measure needed for the cohort matrix and the headline KPIs.

Cohort_Size, how many merchants the cohort started with.

Cohort_Size =
 VAR CohortPeriod = SELECTEDVALUE('Transaction'[Cohort_Date])
 RETURN
 CALCULATE(
 DISTINCTCOUNT('Transaction'[Merchant_ID]),
 FILTER(ALL('Transaction'), 'Transaction'[Cohort_Date] = CohortPeriod)
 )

Cohort_Active_Count, how many are still active. This counts how many merchants from a given cohort were active in month t+N, and it is the numerator of everything that follows.

Cohort_Active_Count =
 VAR SelectedCohort = SELECTEDVALUE('Transaction'[Cohort_Date])
 VAR CohortMonthIndex = SELECTEDVALUE('Transaction'[Months_Since_Cohort])
 VAR CohortMonthDate = EDATE(SelectedCohort, CohortMonthIndex)
 RETURN
 CALCULATE(
 DISTINCTCOUNT('Transaction'[Merchant_ID]),
 FILTER(
 ALL('Transaction'),
 'Transaction'[Cohort_Date] = SelectedCohort &&
 'Transaction'[Period_Month] = CohortMonthDate)
 )

Cohort_Retention_Rate. The headline metric. Format it as a percentage in the visuals.

Cohort_Retention_Rate =
DIVIDE([Cohort_Active_Count], [Cohort_Size], 0)

Performance note. Keep Cohort_Date and Months_Since_Cohort as calculated columns or ETL output rather than measures, and consider reducing a very large transaction table into a merchant-by-month summary table. On a few million rows that is the difference between a report that responds and one that times out.

Cohort_Revenue_Period, revenue in the period.

Cohort_Revenue_Period =
VAR SelectedCohort = SELECTEDVALUE('Transaction'[Cohort_Date])
VAR CohortMonthIndex = SELECTEDVALUE('Transaction'[Months_Since_Cohort])
VAR CohortMonthDate = EDATE(SelectedCohort, CohortMonthIndex)
RETURN
CALCULATE(
 SUM('Transaction'[Net_Revenue]),
 FILTER(
 ALL('Transaction'),
 'Transaction'[Cohort_Date] = SelectedCohort &&
 'Transaction'[Period_Month] = CohortMonthDate
 )
)

Cohort_Cumulative_Revenue, revenue to date.

Cohort_Cumulative_Revenue =
VAR SelectedCohort = SELECTEDVALUE('Transaction'[Cohort_Date])
VAR CohortMonthIndex = SELECTEDVALUE('Transaction'[Months_Since_Cohort])
RETURN
CALCULATE(
 SUM('Transaction'[Net_Revenue]),
 FILTER(
 ALL('Transaction'),
 'Transaction'[Cohort_Date] = SelectedCohort &&
 DATEDIFF('Transaction'[Cohort_Date], 'Transaction'[Period_Month], MONTH) <= CohortMonthIndex
 )
)

Cohort_Average_LTV. The measure you compare against acquisition cost, so it is the one that turns this report into a budget decision.

Cohort_Average_LTV =
DIVIDE(
 [Cohort_Cumulative_Revenue],
 [Cohort_Size],
 0
)

Additional KPIs: overall merchant health

The cohort matrix answers how each intake is doing. These three answer how the business is doing this month.

Active merchants.

Active_Merchants =
CALCULATE(
 DISTINCTCOUNT('Transaction'[Merchant_ID]),
 'Transaction'[Period_Month] = MAX('Transaction'[Period_Month])
)

New merchants.

New_Merchants =
VAR CurrentMonth = MAX('Transaction'[Period_Month])
VAR CurrentActive =
 CALCULATETABLE(
 VALUES('Transaction'[Merchant_ID]),
 'Transaction'[Period_Month] = CurrentMonth
 )
VAR PreviouslyActive =
 CALCULATETABLE(
 VALUES('Transaction'[Merchant_ID]),
 'Transaction'[Period_Month] < CurrentMonth
 )
RETURN
COUNTROWS(EXCEPT(CurrentActive, PreviouslyActive))

Churned merchants.

Churned_Merchants =
VAR PreviousMonthActive =
 CALCULATETABLE(
 VALUES('Transaction'[Merchant_ID]),
 DATEADD('Transaction'[Period_Month], -1, MONTH),
 'Transaction'[Transaction_Count] > 0
 )
VAR CurrentActive =
 CALCULATETABLE(
 VALUES('Transaction'[Merchant_ID]),
 'Transaction'[Transaction_Count] > 0
 )
RETURN
COUNTROWS(EXCEPT(PreviousMonthActive, CurrentActive))

Dashboard design and visualisation

On what separates a dashboard that reports from one that changes a decision, there is a separate piece here: marketing dashboards that decide, not just report. For this model specifically, three pages do the work.

Executive summary. KPI cards for active merchants, new merchants, churn rate and average lifetime value, each with its comparison period visible. A number without a comparison cannot be called good or bad.

Cohort heatmap. Cohort month down the side, Months_Since_Cohort across the top, retention rate in the cells, conditional formatting on the colour. This single visual is what makes the drop month visible at a glance, which is the entire point of the exercise.

Segment analysis. The same heatmap sliced by product type and by category. Where two segments diverge, you have found something actionable; where they move together, the cause is external and no onboarding change will fix it.

Two design decisions are worth making deliberately rather than by default. First, put the cohort size next to the retention percentage in every cell, or as a tooltip. A cohort of eleven merchants showing 45% retention is noise, and without the denominator on screen it reads exactly like a cohort of nine hundred showing the same figure. Second, leave incomplete cohorts visibly blank rather than letting them render as zero. The current month has no month-three number yet, and a chart that draws that as a collapse is the fastest way to have the whole report disbelieved in its first review meeting.

On formatting: use a diverging colour scale anchored on your own target rather than on the range in the data. Anchored on the data, a bad quarter recolours the whole matrix and last month looks fine because everything around it got worse. Anchored on the target, the colour means the same thing every time somebody opens it.

A worked example: cohort analysis over 50,000 merchants

At this scale the transaction table runs to millions of rows and the naive model stops responding. The fix is to reduce the grain before the report ever queries it: group to one row per merchant per month in Power Query, and point the cohort measures at that summary rather than at raw transactions.

-- Building a summary table in Power Query M
let
 Source = Transaction,
 Grouped = Table.Group(
 Source, 
 {"Merchant_ID", "Period_Month", "Product_Type", "Category_Code"},
 {
 {"Transaction_Count", each List.Sum([Transaction_Count]), type number},
 {"Net_Revenue", each List.Sum([Net_Revenue]), type number},
 {"Gross_Revenue", each List.Sum([Gross_Revenue]), type number}
 }
 )
in
 Grouped

-- Alternative: the same summary table in DAX
Merchant_Month_Summary = 
SUMMARIZE(
 'Transaction',
 'Transaction'[Merchant_ID],
 'Transaction'[Period_Month],
 'Transaction'[Product_Type],
 "Transaction_Count", SUM('Transaction'[Transaction_Count]),
 "Net_Revenue", SUM('Transaction'[Net_Revenue])
)

This is the single largest performance change available, and it costs nothing analytically, because every measure in this article works at merchant-month grain anyway.

What that reduction buys, concretely: 36 months across roughly 52,000 merchants is about 2.3 million transaction rows, and the same period at merchant-month grain is closer to 900,000. More importantly the summary is dense rather than sparse, so the compression the engine applies to a repeated merchant ID actually works, and the model file shrinks by more than the row count suggests.

Two things to decide before you build it. Whether a merchant with zero transactions in a month gets a row at all: keeping the row makes the retention measures simpler and the table larger, dropping it makes Cohort_Active_Count depend on absence rather than on a value, which is harder to reason about but usually the right trade at this scale. And whether the summary refreshes fully or incrementally: a full refresh is simpler and stops being viable somewhere around the two-year mark on most hardware, at which point incremental refresh on Period_Month is the change to make.

Common mistakes and how to fix them

• Dates that are not the first of the month. The most common failure by a wide margin. Cohorts split across two buckets and every retention figure reads low.
• Duplicate merchant IDs. One merchant with several IDs appears as several merchants, all of them looking like they churned.
• Months_Since_Cohort stored as text. The visual sorts 0, 1, 10, 11, 2 and the heatmap becomes unreadable without anyone noticing it is wrong.
• Counting a month with zero transactions as active. Decide explicitly what active means and apply the same definition in every measure.
• Reading a cohort that is not complete yet. Last month’s cohort has no month-3 figure. Showing it as a drop rather than as absent is the fastest way to lose trust in the whole report.

Performance optimisation

Beyond the summary table, most of the remaining gain is at the source rather than in DAX.

-- Single-column indexes
CREATE INDEX idx_merchant_id ON Transaction(Merchant_ID);
CREATE INDEX idx_period ON Transaction(Period_Month);

-- Composite index (the important one)
CREATE INDEX idx_merchant_period ON Transaction(Merchant_ID, Period_Month);

-- For product type segmentation
CREATE INDEX idx_product_type ON Transaction(Product_Type);

-- Performance check
SELECT 
 tablename, 
 indexname, 
 indexdef 
FROM pg_indexes 
WHERE tablename = 'transaction';

The composite index on merchant and period is the one that matters, because every cohort query filters on exactly that pair. Alongside it: keep only the columns the model uses, set the data types explicitly rather than letting them be inferred, and prefer calculated columns over measures for anything constant per merchant.

Advanced techniques

Once the base model is trustworthy, a churn risk score is the most useful thing to add, because it turns a backward-looking report into an early warning.

Churn_Risk_Score = 
VAR CurrentMonthTx = [Transaction_Count]
VAR PrevMonthTx = CALCULATE([Transaction_Count], DATEADD('Transaction'[Period_Month], -1, MONTH))
VAR CohortAvgTx = CALCULATE(
 AVERAGE('Transaction'[Transaction_Count]),
 FILTER(ALL('Transaction'), 'Transaction'[Cohort_Date] = SELECTEDVALUE('Transaction'[Cohort_Date]))
)
VAR MonthsSinceCohort = SELECTEDVALUE('Transaction'[Months_Since_Cohort])

// Risk factors
VAR LowVolumeRisk = IF(CurrentMonthTx < CohortAvgTx * 0.5, 30, 0)
VAR DeclineRisk = IF(CurrentMonthTx < PrevMonthTx, 25, 0)
VAR EarlyStageRisk = IF(MonthsSinceCohort <= 3 && CurrentMonthTx < 5, 20, 0)
VAR InactivityRisk = IF(CurrentMonthTx = 0, 50, 0)

RETURN
 LowVolumeRisk + DeclineRisk + EarlyStageRisk + InactivityRisk

It compares a merchant’s current activity both to their own previous month and to their cohort’s average, which is what stops a seasonal dip across the whole book from being read as individual churn risk.

A score is only useful if something happens when it fires, so decide the threshold and the response together. In practice three bands work better than a continuous number: watch, contact, and escalate. A continuous score gets sorted descending once, admired, and never acted on.

The second addition worth making is a value segmentation, so that retention work can be prioritised by what it is worth rather than by count.

LTV_Segment = 
VAR LTV = [Cohort_Average_LTV]
RETURN
 SWITCH(
 TRUE(),
 LTV >= 100000, "Platinum",
 LTV >= 50000, "Gold",
 LTV >= 10000, "Silver",
 "Bronze"
 )

Testing and validation

Five checks, all of which have caught a real error at some point.

1. Cohort zero: on rows where Months_Since_Cohort = 0, Cohort_Active_Count must equal Cohort_Size.
2. Random merchant check: pick a merchant, take MIN(Period_Month) from the raw data, and confirm it matches Cohort_Date in the model.
3. Unique counts: compare DISTINCTCOUNT(Merchant_ID) against a count from outside Power BI.
4. Time zones: convert everything to UTC in ETL. A transaction at 23:40 local on the last day of the month otherwise lands in the wrong cohort.
5. Null and zero revenue rows: decide an outlier trimming rule before computing lifetime value, and write it down, because a single large merchant can move an entire cohort’s average.

Real-world figures and benchmarks

A typical retention curve in this sector falls fast and then flattens: 100% at month 0, roughly two thirds by month 1, around half by month 3, a little under 40% by month 6, and near 28% by month 12. Your own numbers will differ, and the shape usually will not.

For a sense of scale, a project of this kind might span 36 months, some 52,000 merchants and around 2.3 million transaction rows, with roughly 1,500 new merchants a month.

The one thing to take from the curve: the first three months are where the loss concentrates, so onboarding investment aimed at that window generally returns more than anything spent later.

Conclusion and an action plan

Cohort analysis is not a reporting exercise for a payment business, it is the basis on which growth decisions get made. Working through this guide:

1. Prepare the ETL. Normalise Period_Month in Power Query and compute Net_Revenue there.
2. Build the model. Date table, the two calculated columns, then the measures.
3. Build and test the dashboard. Executive, heatmap and segment pages, then run the five sanity checks before anyone sees it.
4. Then improve. Add segmentation, the churn risk score and whatever KPIs the first read makes you want.

Thresholds worth setting. First-month retention above 80%, or the onboarding process needs looking at. A twelve-month lifetime value to acquisition cost ratio above 3, as the minimum for sustainable growth. Monthly churn under 15%. Month-three retention above 45%, and an intervention programme for anything below it.

Cohort analysis is not a one-off. Run it monthly and it changes what you do about onboarding; run it once and it is a slide. For the first 90 days a weekly review is worth the time, because early patterns are what you can still act on, after which a monthly rhythm is enough.

If you would like this built end to end on your own data, write to us, and if you want to see what else we do on the analytics side, start here.