Determine repeat customers vs. new customers based on purchase date

**mktgdude** · 05-02-2013, 02:19 AM

I've got a table of customers and purchase dates, and I'm trying to figure out in any given month, whether that customer is a new (registered but not purchased) or repeat customer (purchased in this month or any prior month.) I'm really having a hard time trying to figure out how how to run the iterative calc.

RegMth CustomerName OrderDate New or Repeat?
Jan Customer A n/a
Feb Customer B 20-Feb
March Customer C 4-Mar
March Customer B 11-Mar
March Customer C 24-Mar
March Customer D 12-Mar

Can someone please help advise?

repeat visitor analysis.xlsx

**Lemice** · 05-02-2013, 02:43 AM

Hello,

You can try this formula on D2

=IF(COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2)>1,"Repeat","New")

And drag them down. They will return "Repeat" if that customer is found more than one time in a month.

**mktgdude** · 05-02-2013, 03:57 AM

Thanks Lem! Very elegant and effective. I did have to modify a bit as I needed to do the repeat check for the year, not per month. So i checked on the number one, and modified the formula to check line by line as you go along: =IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)>1,"Repeat","New"). To get the year and month view I could then throw into a pivot table.

The issues is that in order to make this work, i had to sort by order date. If i wasn't able to do this, is there a way to do this iteratively using an array or vlookup?

thanks again!!

**Lemice** · 05-02-2013, 04:10 AM

In case you want to do an Array check, this formula might be what you are looking for

=IF(COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2)=1,"New",IF($C2=SMALL(IF(($A$2:$A$7=$A2)*($B$2:$B$7=$B2),$C$2:$C$7),1),"New","Repeat"))

Use it on D2 and drag it down.

Note that this is an Array formula, so when you edit / enter it, you will need to hold Ctrl-Shift and hit Enter. If you see the formula being wrapped inside a { } then you did it right.

This formula will flag "New" if a customer only appears once in a month. In case a customer appears more than once, it will flag "New" to the most recent date of the order, and "Repeat" to the rest.

Note that in case that same customer has two orders in the most recent date, both row will be flagged as "New"