Understanding average customer return rate
- Thread starter mgao77
- Start date
Tarun Yadav
New Member
Can you please elaborate your requirement here. Do you want the show the average period in which your customers return from the month of first purchase to the current month or from 1st month itself.
If you want to the check the return rate from 1st month mentioned by you in your excel file then simply divide 17 (total period) by total transactions
Regards,
Tarun Yadav
Chihiro
Excel Ninja
I'm assuming that this is to assist in business plan.
In this kind of study. You'd want to look at few other things.
1. Is customer new or returning?
2. What is the rate of new customer becoming returning?
3. What's the attrition rate of existing customers?
Also, since it looks like purchase price isn't fixed amount, you'd want to look at how much each customer spends per transaction, instead of simply looking at how often they make transaction.
So first step is to analyze if the business model is good at bringing in new customer. Then you want to look at how many of those customer become repeat customer.
In general, about 6~7% of new customer can reasonably expected to become returning customer in a good business model.
Then finally, analyze if any of existing customer base is becoming unsatisfied and seeking business else where and why.
Marketing strategy for the first part, customer retention for 2nd and 3rd, as well as Win-Back for 3rd part.
In this kind of study. You'd want to look at few other things.
1. Is customer new or returning?
2. What is the rate of new customer becoming returning?
3. What's the attrition rate of existing customers?
Also, since it looks like purchase price isn't fixed amount, you'd want to look at how much each customer spends per transaction, instead of simply looking at how often they make transaction.
So first step is to analyze if the business model is good at bringing in new customer. Then you want to look at how many of those customer become repeat customer.
In general, about 6~7% of new customer can reasonably expected to become returning customer in a good business model.
Then finally, analyze if any of existing customer base is becoming unsatisfied and seeking business else where and why.
Marketing strategy for the first part, customer retention for 2nd and 3rd, as well as Win-Back for 3rd part.
This file here highlights only the returning customers. What we want to understand, is, on average, how often (in months) are the customers using the service. The statement we're really looking for is
"On average our customer use the service X amount of times"
For example, So if the customer used the service on month 1, month 3 and finally, month 6, he uses the service every 3 months.
I'm sure its simple and i'm overlooking something. Is it as simple as dividing the number of transactions by 17 months?
"On average our customer use the service X amount of times"
For example, So if the customer used the service on month 1, month 3 and finally, month 6, he uses the service every 3 months.
I'm sure its simple and i'm overlooking something. Is it as simple as dividing the number of transactions by 17 months?
Chihiro
Excel Ninja
Wait, from your description, you want frequency and not the avg. number of transaction.
Calculation will be as following.
="Duration in months" / "# of transaction"
Frequency is defined as, rate at which something occurs or is repeated over particular period of time or in a given sample.
FYI - In your example, customer only had 2 months between 1st and 2nd visit...
Calculation will be as following.
="Duration in months" / "# of transaction"
Frequency is defined as, rate at which something occurs or is repeated over particular period of time or in a given sample.
FYI - In your example, customer only had 2 months between 1st and 2nd visit...
Chihiro
Excel Ninja
Sorry, if I confused with explanation above. I realized I reversed Frequency with Wave Length (i.e. avg. duration) 
To elaborate on relationship between frequency (of transaction/month) & avg. duration (in month) between transaction.
Frequency = # of transaction observed in sample duration / sample duration
x =3/17 = 0.176... per month
yx=z
Where x = Frequency, y = avg. duration, z = 1 month
So, y=z/x derives Avg. Duration.
y=1/(3/17), which can be simplified to 17/3 = 5.67 Month
To elaborate on relationship between frequency (of transaction/month) & avg. duration (in month) between transaction.
Frequency = # of transaction observed in sample duration / sample duration
x =3/17 = 0.176... per month
yx=z
Where x = Frequency, y = avg. duration, z = 1 month
So, y=z/x derives Avg. Duration.
y=1/(3/17), which can be simplified to 17/3 = 5.67 Month
eibi
Active Member
Hi mgao77,
I propose an array formula to calculate the average you're looking for.
See attached; I think it's getting at what you want...
You have a little bit of ambiguity in your request, which makes it difficult to understand exactly what you want.
Suppose, for example, that a client comes twice: in Month 1 and Month 12.
Do you want to say that this client returns once every ~12 months?
Or do you want to say that the client came twice within a 12 month period (which would be every ~6 months)?
There are three kinds of lies, you know...
I propose an array formula to calculate the average you're looking for.
See attached; I think it's getting at what you want...
You have a little bit of ambiguity in your request, which makes it difficult to understand exactly what you want.
Suppose, for example, that a client comes twice: in Month 1 and Month 12.
Do you want to say that this client returns once every ~12 months?
Or do you want to say that the client came twice within a 12 month period (which would be every ~6 months)?
There are three kinds of lies, you know...
Chihiro
Excel Ninja
@eibi
I was thinking something similar using INDEX. But decided against it, when I saw Customer ID# 200. Which had 2 transaction recorded in 17 month, but in consecutive months (which returns 1 or 2 depending on how you treat starting month).
Although, frequency solution is just as bad... suggesting 8.5 months between visit from the sample duration of 17 months.
Either case, it's not useful info.
Following might offer more meaningful data... though it's hard to say. What do you think?
1) Only calculate for customer that returned more than "x" times in 17 months sample. Say 4 times?
2) Take sample from longer duration. Ex. 36 months
I was thinking something similar using INDEX. But decided against it, when I saw Customer ID# 200. Which had 2 transaction recorded in 17 month, but in consecutive months (which returns 1 or 2 depending on how you treat starting month).
Although, frequency solution is just as bad... suggesting 8.5 months between visit from the sample duration of 17 months.
Either case, it's not useful info.
Following might offer more meaningful data... though it's hard to say. What do you think?
1) Only calculate for customer that returned more than "x" times in 17 months sample. Say 4 times?
2) Take sample from longer duration. Ex. 36 months