Finding the repeat buyers of an e-commerce order list

[BrianEcom]

Hey guys. I have a customer order list for my e-commerce website and I am trying to find out my repeat buyers as well as my biggest buyers.

Here is an example of how it looks like:

Email	Product Bought	Total That Customer Spent
customer1@email.com	shoes	$5
customer2@email.com	hats	$3

<tbody>
</tbody>

The only way to find out how many times this customer bought is to count the number of times their emails show up in the email field.

Is there an easy way to find out who my repeat buyers are as well as sort by the highest amount of total spend?

 

[Eric W]

Welcome to the Board!

You can do that with formulas like this:

	A	B	C	D	E	F	G	H	I
1	Email	Product Bought	Total That Customer Spent		Biggest Spenders			Most Frequent
2	customer1@email.com	shoes	$5		customer4@email.com	$106		customer1@email.com	6
3	customer2@email.com	hats	$3		customer1@email.com	$70		customer5@email.com	4
4	customer3@email.com	shirts	$9		customer2@email.com	$40		customer2@email.com	4
5	customer2@email.com	shoes	$8		customer5@email.com	$36		customer3@email.com	3
6	customer2@email.com	belts	$10		customer3@email.com	$32		customer4@email.com	2
7	customer5@email.com	belts	$10
8	customer5@email.com	belts	$3
9	customer1@email.com	shirts	$17
10	customer1@email.com	ties	$8
11	customer3@email.com	belts	$6
12	customer4@email.com	shirts	$95
13	customer5@email.com	belts	$4
14	customer5@email.com	shirts	$19
15	customer2@email.com	belts	$19
16	customer4@email.com	shoes	$11
17	customer1@email.com	shirts	$4
18	customer3@email.com	belts	$17
19	customer1@email.com	hats	$17
20	customer1@email.com	pants	$19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =IF(H2="","",COUNTIF($A$2:$A$20,H2)) F2 =IF(E2="","",SUMIF($A$2:$A$20,E2,$C$2:$C$20)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula H2 {=IFERROR(INDEX($A$2:$A$20,MOD(LARGE(IF(MATCH($A$2:$A$20,$A$2:$A$20,0)=ROW($A$2:$A$20)-ROW($A$2)+1,COUNTIF($A$2:$A$20,$A$2:$A$20)+(ROW($A$2:$A$20)-ROW($A$2)+1)/10000),ROWS($H$2:$H2)),1)*10000),"")} E2 {=IFERROR(INDEX($A$2:$A$20,MOD(LARGE(IF(MATCH($A$2:$A$20,$A$2:$A$20,0)=ROW($A$2:$A$20)-ROW($A$2)+1,INT(SUMIF($A$2:$A$20,$A$2:$A$20,$C$2:$C$20))+(ROW($A$2:$A$20)-ROW($A$2)+1)/10000),ROWS($E$2:$E2)),1)*10000),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The problem is that if your order list is really large, this type of formula tends to crash Excel. If that happens, I'd recommend an on demand VBA macro that scans your list and creates the output lists. Let me know if that's of interest.

 

[DRSteele]

I would like to suggest that a Pivot Table is ideal. I was able to construct a PT the same as the output in your columns E to I in about 30 seconds.

 

[BrianEcom]

Thats awesome! What are the steps involved in this pivot table? I dont have much experience creating them.

 

[DRSteele]

Well, if you lack experience with PTs, you could watch these videos and others from the ExcelIsFun channel on youtube. Then, it will be easier to understand the steps for this particular task.

https://www.youtube.com/watch?v=e-yuYNgsHAk
https://www.youtube.com/watch?v=Y2cIAIJAEWA

 

[markmzz]

​Look at the table below (3 Pivot Tables - DRSteele's suggestion):

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Email	Product Bought	Total That Customer Spent
2	customer1@email.com	shoes	5		Total That Customer Spent			Count of Email				Values
3	customer2@email.com	hats	3		Email	Total		Email	Total		Product Bought	Count of Email	Total Spent
4	customer3@email.com	shirts	9		customer4@email.com	106		customer1@email.com	6		belts	7	69
5	customer2@email.com	shoes	8		customer1@email.com	70		customer5@email.com	4		shirts	5	144
6	customer2@email.com	belts	10		customer2@email.com	40		customer2@email.com	4		shoes	3	24
7	customer5@email.com	belts	10		customer5@email.com	36		customer3@email.com	3		hats	2	20
8	customer5@email.com	belts	3		customer3@email.com	32		customer4@email.com	2		ties	1	8
9	customer1@email.com	shirts	17		Total Geral	284		Total Geral	19		pants	1	19
10	customer1@email.com	ties	8								Total Geral	19	284
11	customer3@email.com	belts	6	**	*************************	*****	**	**********************	*****	**	****************	*************	***********
12	customer4@email.com	shirts	95
13	customer5@email.com	belts	4
14	customer5@email.com	shirts	19
15	customer2@email.com	belts	19
16	customer4@email.com	shoes	11
17	customer1@email.com	shirts	4
18	customer3@email.com	belts	17
19	customer1@email.com	hats	17
20	customer1@email.com	pants	19
***	*********************	********	*********

<tbody>
</tbody>

Markmzz