4 Replies Latest reply on Jun 20, 2012 1:23 AM by haskebrukx

# How to count repeat purchases

I'm trying to make a workbook showing how many repeat purchases (from existing customers) our company gets each day. I got a dataset with all the order and customer information.

Simplified the data is like this:

DateOrder-idAmountCustomer e-mail
01-01-2012100150peter@company.com
02-01-2012100260anne@company.com
03-01-2012100350peter@company.com
10-01-20121004100john@company.com
17-01-2012100550peter@company.com

Is it possible to have a calculated field that counts the number of times a particular value (e-mailadress in the example above) was already present in the dataset? My purpose if to be able to show that on 03-01-2012 and 17-01-2012 we got repeat purchases (a 2nd and 3rd order from a customer). Is that possible with a calculated field?

The data would then have to look like this:

DateOrder-idAmountCustomer e-mail
Repeat purchase (calculated)
01-01-2012100150peter@company.com1
02-01-2012100260anne@company.com1
03-01-2012100350peter@company.com2
10-01-20121004100john@company.com1
17-01-2012100550peter@company.com3
• ###### 1. Re: How to count repeat purchases

You could do a running sum of number of records set to run along customer email. Attached shows this.

1 of 1 people found this helpful
• ###### 2. Re: How to count repeat purchases

Thanks a lot! I got one additional question though. Is it possible to change the formula so that the customer e-mail isn't a required field in the workbook?

I'm trying to make a graph that shows repeating purchases over time, similar to the graph below (colors show order count):

• ###### 3. Re: How to count repeat purchases

Are colors the customers? How are you going to identify an individual customer?

• ###### 4. Re: How to count repeat purchases

Indiividual customers aren't in the graph.The graph shows total orders over time, with the colors showing the repeat purchase count.

I do not want individual customers in the graph, and the customer e-mail is only need to determine whether an order is from a new customer or from a customer who has ordered from us in the past.

So for example on a particular month we had 50.000 orders. 30.000 of those are from new customers (shown in blue), 5000 from customers for which it was the second time they ordered from us (in orange) and 5000 3rd time (in green) etc.