1 2 Previous Next 17 Replies Latest reply on Sep 2, 2018 9:55 PM by Hunter Morgan

# Calculating YOY customer retention

I'm trying to calculate the percentage of customers who have a transaction in 2012 and 2013 (or any arbitrary years, but this is a starting point).  It's easy for me to end up with something like this:

...but I'm having a hard time figuring out the next step.  Shawn, in the image above, would count as a retained customer from 2012 while the rest of them would not.  I have 2 tables (customers and transactions) that are easily linked by a customer ID field.

Can someone point me in the right direction for this?  And if it's really easy, how would I go about expanding it so that I could choose a starting year and a finishing year and calculate the retention between the two of them?

Thanks!

• ###### 1. Re: Calculating YOY customer retention

This could be one approach:

2 of 2 people found this helpful
• ###### 2. Re: Calculating YOY customer retention

As an FYI, there's some slightly complex stuff going on in that solution.  If you need a breakdown, let me know and I'll do my best.

For the denominator of retention percentage I used most recent year but that can be switched.

If you want to have a "Start" and "End" year selector the logic isn't that much more to add, I just didn't put it in because I'm not sure what the rules are.  e.i.  do they have to have transactions for every year in between or is it strictly having a sale in each year defines as a retained customer.

2 of 2 people found this helpful
• ###### 3. Re: Calculating YOY customer retention

Thanks very much for setting that up.  I was able to replicate your calculations and fields in my data set, and it is working the same way that yours is.  I'm still going through the calculations to understand exactly how they're set up, so I don't quite understand this next bit yet:

This is from the data in your workbook:

Can you explain why only Amy Melvin and Amy Shea are marked as retained?  It seems like Amy Harris and Amy Zimmerman should be marked as well (the year parameter was set to 2012 here).  Thanks again!

• ###### 4. Re: Calculating YOY customer retention

There seems to be an issue with either the year function or the fact Im using ATTR.

Change Customer Retention calc to:

if datepart('year',min([Order Date])) = [Year Selector] then

if sum([Sales]) > 0 and lookup(sum([Sales]),-1)>0 then 1 else 0  end else 0 end

4 of 4 people found this helpful
• ###### 5. Re: Calculating YOY customer retention

Beautiful, that fixed it.  I also replaced the ATTR function in the 'Total Customers for Selected Year' field.

Thanks so much!

• ###### 6. Re: Calculating YOY customer retention

What is the final formula you ended up with? I'm trying to replicate and getting some errors. Thanks for your help!

• ###### 7. Re: Calculating YOY customer retention

this is super helpful - thank you! I think I've been able to figure out my original question.  But, I was curious if you have been able to use a similar approach not only for retention, but also being able to identify the number of retained customers increasing, decreasing, or sustaining the amount of their orders from the previous year.

In the example below

For 2012: Bob, Pete, and Sally are all retained donors, and bob and pete downgraded in 2012, sally sustained her level.

For 2013: Bob and Pete are retained donors, Sally is not. Bob sustained, Pete Increased.

thanks, again! hugely helpful.

• ###### 8. Re: Calculating YOY customer retention

I am also wondering what the final calculation was for Total Students for Selected Year (as I can't take out the ATTR without getting calc errors). Becky, did you figure it out?

• ###### 9. Re: Calculating YOY customer retention

Wow this was a while ago.  To answer the main question, I swapped the attr() for min().

The approach I originally provided was messy so I rebuilt with better logic.

I changed the logic to look forward instead of backwards (since that seems to make more sense to the original question)

I created a large number of small calcs so that you can walk through the logic (rather than more confusing larger calcs).

I'll try to make a solution with LOD calcs instead of Table Calcs if I get time as this would be cleaner IMO.

I also added a "running sum of retained" in the visual so you can see the customer count incrementing as you scroll down where a customer meets the condition (selected 2012 year has activity as well as the following year 2013).

1 of 1 people found this helpful
• ###### 10. Re: Calculating YOY customer retention

Here is the updated workbook with the LOD approach as well.

(Make sure if you use filters to add them to context)

3 of 3 people found this helpful
• ###### 11. Re: Calculating YOY customer retention

This is great, thank you!

I'll be digging into it more throughout the week and let you know if I get lost elsewhere.

• ###### 12. Re: Calculating YOY customer retention

Thank you so much for this workbook Aaron! I've been really struggling with doing a donor retention analysis and this helped immensely!

Zareen

• ###### 13. Re: Calculating YOY customer retention

Hi Aaron,

I've used the LOD method you described to calculate the retention rate with a year selector.

Can you please confirm if you use the following logic?

Year Selector = the year base year for the calculation [say 2014]

Customer Retained ? = Yes if Amount >0 in 2013 and 2014, else 0

The calculation used is as follows;

if

{fixed [Constituent ID (Gift Data)]:

max(

if(if year([Gift Date])=[Year Selector] then [Amount] else 0 end)>0

then 1 else 0 END

)

}>0

and

{fixed [Constituent ID (Gift Data)]:

max(

if(if year([Gift Date])-1=[Year Selector] then [Amount] else 0 end)>0

then 1 else 0 END

)

}>0

then 1 else 0 end

Thanks,

David

• ###### 14. Re: Calculating YOY customer retention

HI Aaron,

Do you have a way to compute this for fiscal year starting in July? The [year selector] limits me to calendar year, and I'm looking to track retention across a fiscal year July 1-June 30.

Other than this small issue, the LOD and Table Calc options both work well.

-Ashley

1 2 Previous Next