6 Replies Latest reply on Aug 8, 2018 4:43 PM by Thomas Tobin

# Repeat Customer on Customer ID & Date

Hi,

Firstly sorry to trouble you with a question which I am sure will be simple to many.  I have been trawling through the many questions on repeat customer reporting and none seems to provide the answer I need.

I am using Tableau v10.5.1.  I have data with purchases listed in rows.  Each purchase has an item ID, customer ID and purchase date.

I need to report the count of purchases in a particular date range (e.g. most recent month) and, also, the count of those purchases made by customers who had purchased anything in the 12 months prior to that purchase.

The second part is causing  me the problems.  I think I need to use an LOD calculation to work this out but can't nail it.  The aim (I think) would be to be to add a calculation ("Repeat") to each row telling me if a purchase had been made by that customer ID in the 12 months before that purchase date.  The Excel formula (in cell D2) would be:

=IF(COUNTIFS(A:A,A2,C:C,">="&EDATE(C2,-12),C:C,"<"&C2)>0,1,0).

Data example and desired output below.  Workbook attached.  Thanks in advance,

Tom

Data

 A B C D 1 CUSTID ItemID Purchase_Date Repeat 2 A123 CCF22 1-Aug-18 1 3 A123 CCUE8 1-Apr-18 1 4 A123 PPI99 4-Apr-17 0 5 A440 CCUE8 4-Jan-18 0 6 B204 D9IOD 29-Jul-18 0 7 B204 CX88U 1-Apr-17 0 8 C440 CCF22 30-Jun-18 0 9 D333 CCUE8 1-May-18 0

Desired Output (for period 1-Jan-2018 to 31-Aug-2018):

 ItemID Count of Sales Sum of Repeat CCF22 2 1 CCUE8 3 1 D9IOD 1 0 Grand Total 6 3
• ###### 1. Re: Repeat Customer on Customer ID & Date

Quick Question.

12 months prior to that purchase

This mean the anchor date is "last" or "first"purchase date in that period ?

Shin

-------------------------------

I may misunderstood.

The period range is the check period and the anchor is always the last month?

Shin

• ###### 2. Re: Repeat Customer on Customer ID & Date

Shin-san,

Thank you for your reply and I am honored to be talking to Tableau royalty!

Ideally the anchor date would be the purchase date for each specific purchase (so that the 12 month period is different for each purchase).  Is this possible?

Regards,

Tom

• ###### 3. Re: Repeat Customer on Customer ID & Date

Hi Tom,

Including some speculation but here is something.

Thanks,

Shin

• ###### 4. Re: Repeat Customer on Customer ID & Date

Thank you very much.

I'll need to have a good, long look at this to work out precisely what it is doing and whether it solves the problem!

Tom

• ###### 5. Re: Repeat Customer on Customer ID & Date

Shin

• ###### 6. Re: Repeat Customer on Customer ID & Date

Your solution worked well for the example workbook I attached but didn't translate when I transferred the method to my workbook (not sure why, sorry).

In retrospect I think was being too ambitious in trying to anchor qualifying period to each purchase date.

I ended up using a static period based on the date params, using a calculation to determine if each purchase (line) fell into that period, using a fixed lod calculation to find whether each customer had an entry which qualified then simply summing this last field to give a total for repeat purchases.

Tom