2 Replies Latest reply on Dec 19, 2016 7:24 PM by Yuri Fal

# Customer Loyalty

I need to count the number of Customers who fall into the following categories based on a time frame of one year (12 months).

Customers who spent at least 1000/month 3 months in a row

Customers spent at least 1000/month 6 months in a row

''        "                                9 months

''                                                      12 months

I need to Count them uniquely - i.e. if someone is in the 12 months, they would not fall into the 3 month category.

I have attached a sample workbook from superstore.

• ###### 1. Re: Customer Loyalty

It seems like you could potentially use Yuri Fal's advice on cohort bins to do the analysis you're looking for:

• ###### 2. Re: Customer Loyalty

Diego, thank you for the ping.

The problem as you explained it

is different than what you've got

in my workbook mentioned above.

What you're trying to calculate is similar

to a moving window distinct count of "uniques".

One would approach this problem in such a way:

1) For each Customer / Year-Month combination

count a number of distinct adjacent months ("in a row")

when SUM([Sales]) for a Customer per Year-Month

is higher than the level (set by a Parameter).

This is done by the [YF : Customer-month #] Table Calculation.

2) For each Customer / Year-Month combination

calculate the MAX() of any value from the step 1 calculation

where all adjacent months are within 12-month moving window.

This is done by the [YF : 12m (Bin)] Nested Table Calculation.

3) Count # of Customers in a particular Bin calculated in the step 2.

This is done using a RANK() technique (frequently mentioned in the Forum).