Calculating client retention and churn

Question & Answer
, , , , , , , , ,
1

I’m trying to calculate client retention. I have a few goals:

  1. KPI] one absolute for total # of clients retained
  2. [KPI] one % for # of clients retained/total # of clients
  3. Period window
    3a. churn (number of clients at start, number of SAME clients at end)
    3b. net churn (churn + (new clients - churn of new clients))

We consider that a client is retained if they’ve transacted in the last 3 months (or 90 days). My approach to calculating retention is pretty straightforward. I’m taking this data from an orders table on Quicksight which contains the order date, client id, client name, etc. Here’s the step-by-step method:

  1. Last Order Date: maxOver(orderDate, [clientID], PRE_AGG)
  2. Days Since Last Order: min(dateDiff(lastOrderDate, now(), 'DD'), [corporateId])
  3. clientsRetained: ifelse(daysSinceLastOrder<=90, distinct_count(clientID), 0)

Everything works well up until step 3, where the output isn’t correct and shows up as 0, despite many clients being retained. I’d like to be able to get the number of retained clients overall and also to focus on this: number of clients at start, number of SAME clients at end, which i believe would be called churn.

Any help to answer the 3 objectives I have would be of great help. Thank you!

2

Hello @rohit_SB !

Could you share a table view of some of the data that those calculated fields are returning? That would make it easier to be able to back track from your third calculated field what might be happening.

My hunch is that it might have something to do with the date calculations.

1 Like
3

Hey @duncan , thank you for the response. Sure thing, I could try to create a new analysis on arena but that would take me a day. would that be okay?

4

Hello @rohit_SB !

Totally up to you! You can also copy and paste the calculated fields here if that’s faster.

5

Hello @rohit_SB !

Are you still working on this or were you able to find a solution?

6

Hi Duncan, yes I’ll be posting an update shortly! Sorry for the delay, it’s been a hectic past few days

7

HI @duncan Retention
Here’s a copy of my analysis on the arena.
I’ve managed to create a cohort analysis of sorts that shows the # of orders and the % of orders. This was from the AWS QS “How to do a cohort analysis” video on youtube.
The next step would be to:

  1. Generate a KPI that shows the retention as an absolute number in a specified period.
  2. Generate a retention curve, that shows how the # of orders for a specific cohort goes from 100% to 60 to 50 or whatever the number may be.
1 Like
8

Hello @rohit_SB !

So I changed the thinking here by using a filter rather than an ifelse statement and made changes to the last order date calculated field to get your first KPI.

Last order date = minOver(dateDiff({Last Order Date}, now(), 'DD'), [{Client ID}], PRE_AGG)

Screenshot 2024-04-15 105634
Screenshot 2024-04-15 1056341122×766 31.8 KB

Screenshot 2024-04-15 105621
Screenshot 2024-04-15 105621236×541 17.1 KB

2 Likes
9

Could you explain why you’re using a minOver function with the last order date field? I calculated the last order date field using this: maxOver(orderDate, [clientID], PRE_AGG), which leads me to the last order date for each client. How does the second formula you wrote (minOver(dateDiff({Last Order Date}, now(), 'DD'), [{Client ID}], PRE_AGG)) tie in with the existing formula?

10

Thanks a lot for your help @duncan! To make things easier, I’ll put up a new post describing exactly what I need to do, do you think that’ll help streamline things a bit?