10 Replies Latest reply on Mar 2, 2017 3:18 PM by David White

# Percent of repeat orders in three months of first order

I am trying to find the early renewal of customer, which is defined by if a customer orders within three months of their first order.

I have got the customers in monthly cohorts which are defined by the month of first purchase.

{ FIXED [Customer Name] : MIN([Order Date])}

Then COUNTD([Customer Name]) gets the number of customers in that cohort.

I can get repeat purchases by this:

IIF([Order Date]>[Min Orderdate],[Order Date],NULL)

So what I need to do is find distinct customers that repeat ordered within 3 months of there first order in a calculated field.I only want to count them once even if they order more then once in that 3 month period.

Once I have that number need to divide total customers in that cohort by the number that made a repeat purchase in that three month time period times 100 and this will give me the early renewal of that cohort. I am having trouble with the repeat purchase within three months, can anyone help me on this, I have enclosed a superstore sample to work on.

• ###### 1. Re: Percent of repeat orders in three months of first order

wow. this was a good one. love the use case though and definitely possible

you were on the right track with the fixed lod on customer name but you need to go one level deeper with another lod on the order id. the true and false below identifies the group of customers that made at least one purchase within 3 months of the first. Calculation 1 is the first purchase date for each customer.

• ###### 2. Re: Percent of repeat orders in three months of first order

Good work that does do the trick for finding a repeat order in three months, but how would you divide the total count of customers in a monthly cohort by the count produced by this filter per monthly cohort?

• ###### 3. Re: Percent of repeat orders in three months of first order

you would do something along the lines of now dividing that calculation i posted by the total of customers as a second calculation. perhaps a second calculation like:

{countd([Customer Name]}

the above calculation could be a fixed, include, or exclude depending on what level you need the count of customers at.

• ###### 4. Re: Percent of repeat orders in three months of first order

I tried this

SUM({ FIXED [Min Order Date]:COUNTD([Customer Name])})/SUM( { FIXED [Calculation5],[Min Order Date] : COUNTD([Customer Name)})*100

Calculation 5 is the calculation you provided above and I have it on the filter shelve set to true but all the values become 100. Do you have any insight?

• ###### 6. Re: Percent of repeat orders in three months of first order

That calculation is a Boolean value how are you suppose to divide an int by a Boolean?

• ###### 7. Re: Percent of repeat orders in three months of first order

I attached the solution for you. we needed to build out 3 items here. first was the calculation above that gives use the t/f for customers that purchased within 3 months of their first order. i did find that a customer was being counted for each month that they purchased so i added one additional piece to the above calculation that only returns a value for the month of the first purchase. the second piece we needed is the total count of customers. lastly, we needed to divide the number of customers from the Boolean calculation by the total count of customers.

2 of 2 people found this helpful
• ###### 8. Re: Percent of repeat orders in three months of first order

Thank you Chris, this works great I actually got it working last night very similarly to your solution but yours seems more elegant. The only difference is I did:

IF [Purchased within 3 Months] = true

then [customer Name]

END

Then Just did a count on that Calculated field and divide that by COUNTD([Customer Name])

Thank you for all your help!!

• ###### 9. Re: Percent of repeat orders in three months of first order

Hi,

I posted this and then pulled it down to double check my math, but I do believe the Total Customers is calculating all orders within the given month/year rather than just first time orders. I found this post very useful for my needs and made one addition to have a variable selection on how many months this looks for the repeat purchase, but I'm not able to figure out how to limit the Total Customers to just those making first purchases within the month. Otherwise, I believe the % Customers renewal rate is being understated according to LL's original cohort specifications.

Any thoughts on how to resolve this?

• ###### 10. Re: Percent of repeat orders in three months of first order

Hi,

I think I may have gotten to this by changing Total Customers to:

{fixed DATEPART('month', [First Purchase Date]),DATEPART('year', [First Purchase Date]): COUNTD([Customer Name])}

Would love confirmation by those more advanced than me though.

David