• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula for calculating repeat customers over 3 & 6 months

G

Gilly

New Member
Hi Guys,
My first post..Need your help with a formula please. I am the operations manager for a small company and my boss is anxious to identify our levels of repeat customer retention.
I have prepared a 12 month sales performance pivot table, month by customer, with a count of their orders by month. I then pasted the pivot into a normal sheet to custom format and sort it.
Where I am struggling is to derive an either/or conditional formula to identify the repeat customers over 3 and 6 months (see D283 and G285 on attached sheet). I was using SUMPRODUCT, but this layout is producing a count for ANY single order placed in the last 3 months, not repeat orders. I also tried COUNTIF(S) but I must have done that wrong as it was only counting customers who had placed orders each and every month within the 3 month period.
I need it to look up and down the 3 columns (for the 3 month repeat) and count only those customers where they have placed orders in 2 or more months. This is irrespective of how many orders they have placed in any given month.
To describe this on the sheet for Jan, Feb, and Mar it needs to work out orders thus:
Jan and Feb
Jan and Mar
Feb and Mar
and return the result in one box.
it does not matter how many orders a customer places in one month.
Many thanks
Gilly
 

Attachments

  • Customer Sample.xlsx
    26.3 KB · Views: 23
Hi Gilly, and welcome to the forum! :awesome:

Can you elaborate on what you'd like the final output to look like? Looking at just a small portion of your data
upload_2016-2-9_10-2-27.png

Would we say that customers 1 and 2 are repeat customers for 6 months, and customer 3 is only a repeat for 4 months? Or, do they have to fit into the Jan-Mar group, and then the Apr-Jun group? I guess I'm really asking, what are the buckets they need to fall in.

Or perhaps...
In col O, put this formula:
=COUNTIF(B2:D2,">0")>0

Then, to count how many repeat customers you have, in some cell, have:
=COUNTIF(O:O,TRUE)

which would give an answer of 143 for the Jan-Mar time frame.
 
I'd recommend going the route Luke took and use helper columns.

But I think you wanted to count only if there's 2 or more month wtih purchase in 3 month period. So helper column formula will become.
=COUNTIF(B2:D2,">0")>1

It can be done in single cell if needed, but only way I can think of will produce long formula just for 3 month calculation. You can imagine how annoying it can become for 6 month.

In D283:
=COUNTIFS(B2:B278,0,C2:C278,">0",D2:D278,">0")+COUNTIFS(B2:B278,">0",C2:C278,0,D2:D278,">0")+COUNTIFS(B2:B278,">0",C2:C278,">0",D2:D278,0)+COUNTIFS(B2:B278,">0",C2:C278,">0",D2:D278,">0")
=71
 
Thanks Chihiro, you're right, should have changed my helper column to be TRUE only if 2 (or 3) months had orders. :)
 
Guys, thanks for your help so far. Apologies for the length of the customer rows. I could have perhaps trimmed down slightly but this is true data anonymised. At the totals columns at the foot of the table you'll see I've completed rows D283:M283 for rolling 3 month repeat customers and G285:M285 for 6 months. The formula is dragged across and runs month to month in 3 month chunks so to speak, so Jan-Mar, then Feb-Apr, Mar-Jun etc
I think your answer Luke arrives at what I've already got using SUMPRODUCT.
Chihiro, your formula works and I can see how you've arrived at it but I would have absolutely no idea how to make it less painful on the eye. I have no problem with using helper columns but until such time as someone can improve on what you've provided I'll run with that.
Your time and help is much appreciated. :)
 
Ah, I see those now.

So, if a repeat customer is defined as someone who bought in all 3 months of the rolling time frame (Jan, Feb, and Mar), then this formula will do it:
=SUMPRODUCT((B2:B278>0)*(C2:C278>0)*(D2:D278>0))

If you put that in D287, you could copy to the right to see how it changes with the rolling time frame.

Six month view would be similar, just with additional arrays.
 
Were it that easy Luke, the repeat customer customer I am trying to calculate is someone who buys more than once over the 3 month period but not every month so it has to be 2 or 3 purchases - 1 purchase doesn't cut it nor does buying every month. Chahiro's formula works but it's hellishly long - for me anyway.
:)
 
Hi Narayan,
This gives exactly the same result and I've evaluated the formula in the spreadsheet and get bombarded with innumerable True Falses but quite honestly I have no idea how it works but it does, thanks.
How would the formula look for the 6 month for repeat customers where again we are looking for every customer who has purchased from us in 2 or more months over the 6 month period
 
MMULT is of the dark side! :eek:

NO, it's pretty powerful actually, I just never seem to be able to wrap my mind around how it works, or be able to use it effectively.
 
You must log in or register to reply here.
Back
Top