1. Resources
  2. Articles

Article · 8 min read · Published September 2024

Customer retention and CLV dashboard in Tableau and Power BI

Who stayed, who left, and what each customer is worth, with retention, churn, and lifetime value on one page.

A customer retention dashboard shows who is still buying, who left, and what each customer is worth. A revenue or finance leader reads it for the period: retention, churn, repeat purchase, and customer lifetime value.

The metrics

Use one definition for each number, and use it on every card. These are the six.

Customer retention rate = (customers at end of period - new customers acquired in period) / customers at start of period
Churn rate = customers lost in period / customers at start of period
Repeat purchase rate = customers with 2+ purchases / customers with 1+ purchase
Average order value = revenue / orders
Purchase frequency = orders / unique customers
Customer lifetime value = average order value x purchase frequency per year x gross margin x expected years as a customer

Customer lifetime value here is the simple margin-based version. With order data and no subscriptions, customers at the start of the period are the customers who bought in the prior period of the same length, and retained customers are the ones who bought in both. Expected years is a number you set for the plan. Average order value and purchase frequency come from orders. Gross margin comes from the order, as an average, or from the margin you already use in finance.

Retention rate matters when the period’s growth is new customers and the base is shrinking. Churn rate matters when you need the count of who left, beside the share who stayed. Repeat purchase rate matters when the second order is the target for the quarter.

Average order value matters when revenue moved and the order count did not. Purchase frequency matters when a retained customer is buying less often than last year. Customer lifetime value matters when a budget sets a ceiling on what you will spend to acquire or keep someone. Set that value next to acquisition cost on a marketing ROI dashboard.

These numbers are made up, so the formulas have something to land on. Start with 1,000 customers. Acquire 200. End with 900.

Customers still there from the starting base are 900 minus 200, which is 700. Retention is 700 / 1,000, or 70 percent. Customers lost are 300. Churn is 300 / 1,000, or 30 percent.

Customers average 4 orders a year at $50 an order. Gross margin is 50 percent, and the plan expects 3 years. Customer lifetime value is 50 x 4 x 0.50 x 3, which is $300.

Of the 900 customers who bought this period, 360 ordered at least twice, so the repeat purchase rate is 40 percent.

The cohort retention table

A cohort table groups customers by the month of their first purchase. Rows are that month. Columns are months since the first purchase. The cell is the share of the group still active.

Read across a row to see one group age. Read down a column to compare groups at the same age. Each column shows every group at the same age. The company-wide retention rate can’t do that, because it mixes every tenure in the period.

The figure is an example. January is 100 percent at the start, 60 percent in month 1, and 45 percent in month 2. February is 100 percent, 55 percent, and 40 percent. March is 100 percent, 50 percent, and 38 percent.

The drop from the start to month 1 is the large one. Month 2 loses fewer of the people who were still active. That is a flattening curve. Most of the loss has already happened, and later months move less.

A flattening curve tells you where to spend. If month 1 is where the row falls, the work is the second purchase, timed off the first. If a row keeps falling at the same pace in later columns, the offer is losing people who already came back, and a win-back at month 1 will not fix it.

An example cohort

Share still active, by months since first purchase.

CohortStartMonth 1Month 2
Jan100%60%45%
Feb100%55%40%
Mar100%50%38%
An example cohort table. Rows are acquisition month. Columns are the start, month 1, and month 2. Cells are the share still active. The numbers are made up.

RFM segments

Score recency, frequency, and monetary value from 1 to 5, with 5 as the best fifth of customers on that measure. Recency is days since the last order. Frequency is the number of orders. Monetary value is revenue.

A customer can be recent and still low frequency. The segment uses all three scores together.

Five segments are enough for the page:

  • Champions. High recency, high frequency, high monetary value. Keep the offer they already buy. A discount teaches them to wait.
  • Loyal. High frequency and solid revenue, with recency a step behind champions. Prompt the next order on their usual gap.
  • New. One purchase, and it was recent. The action is the second order, on the timing the cohort shows in month 1.
  • At risk. Frequency and revenue used to look like loyal, and recency has gone quiet. One direct offer before they go quiet for good.
  • Hibernating. Low recency and low frequency. One reactivation. If they stay quiet, stop spending against that segment.

The names are labels for the scores. Change the cut points if your quintiles bunch everyone into one bucket. Do not add a sixth segment until someone owns the action for the five you have.

The dashboard layout

The top of the page is four cards: retention rate, churn, customer lifetime value, and repeat purchase rate. Each card shows the selected period and the prior period. In the example those cards read 70 percent, 30 percent, $300, and 40 percent.

Below the cards:

  • The cohort table, acquisition month by months since first purchase.
  • RFM segments, as a bar of customers or revenue, so the five names are comparable.
  • Revenue by segment for the same period, so a large champion group and a large hibernating group are not treated as the same kind of money.

Leave these off this page: one row per order, a product grid, a ranking of customer names, and a second lifetime value that uses a different margin. Order lines belong in the tool that posts them. A second lifetime value will be argued instead of used.

Period is the filter above the cards, and it moves the cohort, the segments, and the revenue together. A chart that ignores it does not belong here. This is the kind of dashboard we build in our business intelligence work: a few numbers a revenue leader will open before the meeting.

A retention dashboard

Round numbers, made up, to show the cards.

Retention exampleExample data
An example retention dashboard with retention rate, churn, customer lifetime value, and repeat purchase rate. The numbers are made up.

What a journey dashboard shows

The useful customer-journey view on this page is the time from the first purchase to the second, and the drop-off between purchases after that. Both are already in the cohort. Month 1 is the share who made the second step. Later columns are the drop between purchases as the group ages.

A journey dashboard that earns a place next to this one is a distribution of days from first order to second order, for the same customers as the cohort row. If month 1 retains 60 percent and the second order usually lands on day 40, the prompt goes out before day 40, while that month is still open.

Stop there. Page views, email opens, and a map of every touch before the first order answer a different meeting. They dilute the retention page. When someone asks for the best dashboard for customer journey analysis in this work, the honest answer is the cohort plus the days to the next order.

Building it in Power BI

Assume an Orders table with OrderID, CustomerID, OrderDate, Revenue, and GrossMargin. Assume a date table related to Orders on OrderDate. Filter the page with the date table.

The date table sets the period. Retention compares the customers who bought in that period with the customers who bought in the prior period of the same length. DIVIDE returns blank when a denominator is zero.

Customer Count = DISTINCTCOUNT ( Orders[CustomerID] )
Order Count = DISTINCTCOUNT ( Orders[OrderID] )
Average Order Value = DIVIDE ( SUM ( Orders[Revenue] ), [Order Count] )
Purchase Frequency = DIVIDE ( [Order Count], [Customer Count] )
Gross Margin % = DIVIDE ( SUM ( Orders[GrossMargin] ), SUM ( Orders[Revenue] ) )
Customer Lifetime Value =
VAR ExpectedYears = 3
RETURN
    [Average Order Value] * [Purchase Frequency] * [Gross Margin %] * ExpectedYears
Repeat Purchase Rate =
VAR CustomerOrders =
    ADDCOLUMNS (
        VALUES ( Orders[CustomerID] ),
        "@Orders", CALCULATE ( DISTINCTCOUNT ( Orders[OrderID] ) )
    )
RETURN
    DIVIDE (
        COUNTROWS ( FILTER ( CustomerOrders, [@Orders] >= 2 ) ),
        COUNTROWS ( CustomerOrders )
    )
Customer Retention Rate =
VAR PeriodStart = MIN ( 'Date'[Date] )
VAR PeriodEnd = MAX ( 'Date'[Date] )
VAR PeriodDays = DATEDIFF ( PeriodStart, PeriodEnd, DAY ) + 1
VAR PriorCustomers =
    CALCULATETABLE (
        VALUES ( Orders[CustomerID] ),
        REMOVEFILTERS ( 'Date' ),
        Orders[OrderDate] >= PeriodStart - PeriodDays,
        Orders[OrderDate] < PeriodStart
    )
VAR CurrentCustomers = VALUES ( Orders[CustomerID] )
RETURN
    DIVIDE (
        COUNTROWS ( INTERSECT ( PriorCustomers, CurrentCustomers ) ),
        COUNTROWS ( PriorCustomers )
    )
Churn Rate =
VAR Retention = [Customer Retention Rate]
RETURN
    IF ( NOT ISBLANK ( Retention ), 1 - Retention )

Every measure follows the date filter on the page. Purchase frequency is per year, and lifetime value uses it that way, only when the page is filtered to one year. ExpectedYears is the planning number from the formula; change it in the measure or move it to a what-if parameter.

Put retention, churn, lifetime value, and repeat purchase rate on cards. Use a matrix for the cohort: cohort month on the rows, months since first purchase on the columns, and the share still active in the values. A bar is enough for the five RFM segments.

Building it in Tableau

Use the same Orders columns and definitions. Create two date parameters, Period start and Period end. On sheets that use the retention, churn, or repeat purchase calcs, don’t filter OrderDate: the parameters set the window, and the FIXED calcs need every order to see the prior period. Other sheets can filter OrderDate from the same parameters.

First purchase date: { FIXED [CustomerID] : MIN([OrderDate]) }
Cohort month: DATETRUNC('month', [First purchase date])
Customers: COUNTD([CustomerID])
Order count: COUNTD([OrderID])
Average order value: SUM([Revenue]) / COUNTD([OrderID])
Purchase frequency: COUNTD([OrderID]) / COUNTD([CustomerID])
Period days: DATEDIFF('day', [Period start], [Period end]) + 1
Bought in period: { FIXED [CustomerID] : MAX(IF [OrderDate] >= [Period start] AND [OrderDate] <= [Period end] THEN 1 ELSE 0 END) }
Bought in prior period: { FIXED [CustomerID] : MAX(IF [OrderDate] >= DATEADD('day', -[Period days], [Period start]) AND [OrderDate] < [Period start] THEN 1 ELSE 0 END) }
Customer retention rate: COUNTD(IF [Bought in prior period] = 1 AND [Bought in period] = 1 THEN [CustomerID] END) / COUNTD(IF [Bought in prior period] = 1 THEN [CustomerID] END)
Churn rate: 1 - [Customer retention rate]
Repeat purchase rate: COUNTD(IF { FIXED [CustomerID] : COUNTD(IF [OrderDate] >= [Period start] AND [OrderDate] <= [Period end] THEN [OrderID] END) } >= 2 THEN [CustomerID] END) / COUNTD(IF [Bought in period] = 1 THEN [CustomerID] END)

Cohort month is the acquisition month on the rows. Months since first purchase is the column: DATEDIFF(‘month’, [First purchase date], [OrderDate]). The syntax differs from the Power BI measures. The numbers should match: customers who bought in both periods, divided by the customers who bought in the prior one.

Linas Sutkaitis

01 FAQ

Frequently asked questions

What should a customer retention dashboard show?

Retention rate, churn, customer lifetime value, and repeat purchase rate for the selected period, each with the prior period. Under the cards, a cohort table, an RFM segment view, and revenue by segment. The same formula should sit behind every visual.

How do you calculate customer lifetime value?

The simple margin-based version is average order value times purchase frequency per year times gross margin times the years you expect the customer to stay. Average order value is revenue divided by orders. Purchase frequency is orders divided by unique customers. You set the years. The orders table does not calculate that span on its own.

How do you calculate retention rate?

Subtract the new customers acquired in the period from the customers at the end, then divide by the customers at the start. With order data, that works out to the customers who bought in both the prior period and this one, divided by the customers who bought in the prior period. Churn is 1 minus retention.

How do you build a retention dashboard in Power BI?

Use an Orders table with OrderID, CustomerID, OrderDate, Revenue, and GrossMargin, and a date table related on OrderDate that sets the period. Write measures with DIVIDE for customers, orders, average order value, purchase frequency, repeat purchase rate, and retention against the prior period of the same length. Cards hold the rates. A matrix holds the cohort.

What does a cohort analysis show?

Each row is an acquisition month. Each column is a count of months since the first purchase. The cell is the share of that group still active. A curve that flattens means most of the people who were going to leave did so in the early months.