Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a database with the revenues of every customer, I would like to know what I consider a super loyal customer: The customer that has bought us once or more evry month last 3 months.
The Data base has the folling structure:
Salesid custaccount date
1 A 31/06/2013
2 B 30/07/2013
3 A 15/07/2013
4 B 31/08/2013
5 A 15/09/2013
Superloyal Cutomer: A with 3 sales once as minimum every months last 90 days
*Date= Today
Thanks guys
Hi Eduard,
May be this will work?
//Raw Data
TEMP_Fact:
LOAD * // Create Month Field
,MonthName(date) as Month
INLINE [
Salesid, custaccount, date
1, A, 30/06/2013
2, B, 30/07/2013
3, A, 15/07/2013
4, B, 31/08/2013
5, A, 15/09/2013
6, B, 24/04/2013
7, A, 30/07/2013
8, C, 23/03/2013
8, C, 23/03/2013
9, C, 25/06/2013
10, C, 23/07/2013
11, C, 23/08/2013
];
//// Calculate No of Months and Sales in last 90 days
LEFT JOIN (TEMP_Fact)
LOAD
custaccount
,count(distinct Month) as NoOfMonths
,count(custaccount) as [Sales (last 90 days)]
RESIDENT TEMP_Fact
//Last 90 days only
Where num(date)>=num(today())-90
group BY custaccount ;
Fact:
NoConcatenate
Load Salesid, custaccount, date, Month,
// Sales for the last 90 days
if((num(date)>=num(today())-90),[Sales (last 90 days)],0) as [Sales (last 90 days)],
if(NoOfMonths>=3,'Super Loyal Customer',null()) as LoyalCustomerFlag
Resident TEMP_Fact;
drop table TEMP_Fact;
Result: