Analysing repeat customer orders

spaceace

New Member
Joined
Feb 23, 2005
Messages
3
Hi

I have a set of data in excel which consists of three columns:

Order Reference, Order Timestamp, CustomerID

This list of data tells me all customers who ordered a specific product from inventory.

What I need to provide as an output is the number of distinct customers who ordered in a specific month (Nov 2004) - which I can do - but then I also need to output the number of distinct customers who ordered in Nov 2004 who have ordered again since Nov 2004.

Its this second bit that I have no clue how to do. Can anybody give me some pointers in terms of where to start?

Thanks in advance to anybody who can shed some light on this.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
this formula:
paste it as a matrix formula (ctrl-shift-enter instead of just enter to confirm it)

=INDEX(C2:C9,INDEX(N(MATCH(N(IF(B2:B8,"<"&F2,B2:B8)),N(IF(B2:B8,">"&F2-1,B2:B8)))),ROW()-1,1),1)

it lists all distinct customers that have bought something before the time in F2, and also after F2. You have to drag it down in order to see all matches.
When there are no matches left, it returns 0.
Map2
ABCDEFGH
1ReferenceTimeStampCustomerIDdatematches
23726911-10-20041
33766422
43768836
53769445
63787650
73799960
83827410
9382752#VERW!
10383066#VERW!
11383335#VERW!
Blad1
OCCUR.</FONT></CENTER>
 

spaceace

New Member
Joined
Feb 23, 2005
Messages
3
Hi

I am not quite sure I understand the answer as I do not understand what the values in column F represent.

I have approximately 1000 rows of data and wish to look at all 1000 rows and establish how many orders < 30/11/2004 and how many of those customer IDs ordered again after 30/11/2004.

Where is this shown in the example post above?

Also I made a small mistake - my dates are in the format dd/mm/yyyy in column B which I think perhaps makes a difference?

Thanks & Regards
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
the actual date formatting doesnt make a difference, because indirectly the timestamp is used. cell F2 represents the date you want to use as starting point.
the values in column H in this example show the customers that have ordered BEFORE 1-10-2004, and have ordered AFTER 1-10-2004 again.
if you want to change the date, you only have to change cell F2.
If you want to show the same things for the products (ie. all products ordered before 1-10-2004 and after 1-10-2004 again, change the formula a little:

=INDEX(A2:A9,INDEX(N(MATCH(N(IF(B2:B8,"<"&F2,B2:B8)),N(IF(B2:B8,">"&F2-1,B2:B8)))),ROW()-1,1),1)
 

spaceace

New Member
Joined
Feb 23, 2005
Messages
3
OK now I understand. Column F is a list of all the people who have ordered before and after the date specified, right?

Then I presume I just do a formula to count how many non zero rows are in that column?

If that is correct then thanks very much for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top