How to calculate the lost, new or existing customer based on their sales by year or month

[Sajid_Mahmood]

I want to create a bar chart or pivot table to find out the customers that have not purchased during the period selected by user to flag out as LOST CUSTOMER, NEW CUSTOMER OR EXISTING CUSTOMER. Can anyone help me out to solve this problem and advise the solution.

Thanks

Regards

Sajid Mahmood

[fernandotoledo]

You can load your sales table as follow:

FLAG_ACTIVE:
LOAD DISTINCT
 MONTHNAME(SALES_DATE) AS PERIOD,
 CUSTOMER,
 IF(INMONTH(SALES_DATE,TODAY(),0),1,
 IF(INMONTH(SALES_DATE,TODAY(),-1),2,0) AS FLAG
RESIDENT SALES
;

In your charts, the result of SUM(FLAG) means

0 -> your customer had a sale but dont in the last two months = long time lost client

2 -> your customer had a sale last month but not this mont = lost client

1 -> your customer had a sale this month but dont in the last month = new client or lost client reactivated

3 -> Your client is buying regularly the last two months = active client

You can use more months and improve the logic, but this is an idea!

best regards

Fernando