8 Replies Latest reply on Mar 22, 2016 2:02 PM by Brian Winkle

# Charting "existing" customers over time

I posted about this yesterday but haven't been able to totally solve the problem. I want to track my number of "existing" customers over time. That is, customers that, at every date charted, made their first purchase more than two weeks prior.

My formula looks something like: CNTD(IF DATEDIFF( 'week', { FIXED [Customer Id] : MIN( [Purchase Time] ) }, [Purchase Time]) > 2 THEN [Customer Id] END)

The results do not look correct.

• ###### 1. Re: Charting "existing" customers over time

Your formula looks right if you are only counting "existing customers" if they actually made a purchase within each date interval you are charting. For example, if you are showing the count of existing customers by month, you would only be counting customers that actually made a purchase in each month (that was at least two weeks after their initial purchase).

Please provide some more detail on "the results do not look correct" & we might better be able to help.

-Steve

• ###### 2. Re: Charting "existing" customers over time

I think that's my issue, Steve. My results look like they're leaving out a fair number of our customers. I'd like to really just exclude customers that made their first purchase within two weeks of the charted date.

• ###### 3. Re: Charting "existing" customers over time

You probably need to solve this on the data side (maybe someone out there has a brighter idea about solving it with the existing data source). I've solved similar problems by creating a 2nd table with just dates, e.g.:

Report Day

1/1/2015

1/2/2015

1/3/2015

etc.

And doing a full outer join with your transactional data so that you have records for every Report Day, even when customers don't actually make a purchase on that day. Then you can use Tableau to determine whether to count a customer as an active customer based on their initial purchase date & the report day.

The resulting tables can get very large of course, but you will have a way to reconstruct historical customer counts.

-Steve

• ###### 4. Re: Charting "existing" customers over time

I'd certainly rather not have to manipulate the datasource here if I don't have to. But I will if I do.

• ###### 5. Re: Charting "existing" customers over time

I'd really like to be able to do this for several fields. Customers over time, employees over time, etc. Is there really not a good way to do this?

• ###### 6. Re: Charting "existing" customers over time

As long as you are working with a data source that just includes Purchase Date, I don't see an easy way (or any way really) to count existing customers.

I think you need to start with your rules for what an existing customer is - is it a customer who made a purchase _any time_ in the past, or do customers eventually drop off? Do you have a concept of "active customer" - customers who have purchased within the last 12 months, for example.

Then build your data source to fit - if you want to graph historical dates, you either have to build the date table with a join that I described above, or build out a data warehouse that snapshots customers.

The date table approach is not difficult - it just means spending some time on the data side instead of on the Tableau side - probably necessary if you want to start viewing historicals.

-Steve

• ###### 7. Re: Charting "existing" customers over time

Okay, I've got the date table set up and am joining that against our purchases table. How would I count the number of customers now? A running total of COUNTD(customer id) won't do it.

• ###### 8. Re: Charting "existing" customers over time

I'm presuming it'll be an LOD function, but I'm unsure how the date table makes this easier.