counting returning customers

[nicolai_moller]

Hi

I have a list of customers and each customers orderDates. I'm trying to count the number of customers who have made orders the following years.

Of the 4 customers who have made orders in 2012, 2 of 4 have also ordered in 2013 and 1 of 4 have one in 2014.

How can this be made?

Thanks

CustomerID	OrderDate
1	12-12-2012
2	01-01-2012
2	05-05-2013
3	07-07-2014
4	06-06-2015
5	04-04-2012
5	04-04-2012
6	08-08-2013
7	01-01-2012
7	01-01-2013
7	01-01-2014

[jpenuliar]

You can add a Year field to your table and present in a pivot table

[anbu1984 · Accepted solution]

Load *,Year(OrderDate) As Yr;

Load CustomerID,Date#(OrderDate,'MM-DD-YYYY') As OrderDate Inline [

CustomerID,OrderDate

1,12-12-2012

2,01-01-2012

2,05-05-2013

3,07-07-2014

4,06-06-2015

5,04-04-2012

5,04-04-2012

6,08-08-2013

7,01-01-2012

7,01-01-2013

7,01-01-2014 ];

='Returing customers in 2013: ' &Count(DISTINCT {<CustomerID=P({<Yr={'<2013'}>})>*<CustomerID=P({<Yr={2013}>})>}CustomerID)

='Returing customers in 2014: ' &Count(DISTINCT {<CustomerID=P({<Yr={'<2013'}>})>*<CustomerID=P({<Yr={2014}>})>}CustomerID)

[sudeepkm]

As Jonathan suggested you can use Year as dimension in a pivot table like below.

here you can find number of orders by year and again you can go to the second dimension to find who all have ordered in a particular year.

[Ralf-Narfeldt]

If you want to do it in the script, you can add this resident load after you load the Orders table (you may have named it differently).

It will create a linked table with for each customerID:

ReturnedTimeSpan: diff between first and last order year

ReturnedYears: number of distinct years the customer has ordered

I'm sure you can do more according to what you really want with this as starting point.

Returning:

Load

  CustomerID,

  Max(Year(OrderDate)) - Min(Year(OrderDate)) As ReturnedTimeSpan,

  Count(DISTINCT Year(OrderDate)) As ReturnedYears

  RESIDENT Orders GROUP BY CustomerID;