Customer loyalty based on months since last activity

[QlikNewbie20]

Hi Qlik Experts.

I'm very new to Qliksense and have been struggling to categorize customers into loyalty segments based on activity in previous months.

I would like to select a month and year from filter pane and display the below:

Fields in Table: CustomerID, Date (DD/MM/YYYY)

New Customers- New Customer ID's within last 13 months

Retained customers - Existing Customer ID's that are active in last 13 months and previous 13 month period before that

Lapsed Customers - Customer ID's with activity between 13-18 months ago but have not returned

Returning Customer - Customer ID's that were previously lapsed but have now returned

Lost Customer - Customer ID's with last activity between 18-24 months ago who have not returned

Unfortunately due to access rights I cannot import an app so I would appreciate any help on the script to be pasted here. Thanks.

[dplr-rn]

i think it may need to be a multi step process. share some sample data and should be able to give you full logic .

but essentially you create multiple steps for each category and use that to load a single category column in a new table.

roughly (havent tested but should give you a starting point)

Category:
load distinct
CustID
,'New' as Category

where Min>addmonths(today(),-13);
load CustID
, Min(Date) as Min
resident Customer
group by CustID;

//Lapsed
Concatenate(Category)
load distinct
CustID
,'Lapsed' as Category
//last order between 13 and 18 months ago
where Max<=addmonths(today(),-13) and Max>=addmonths(today(),-18); 
load CustID
, Max(Date) as Max
resident Customer
group by CustID;

//retained
Concatenate(Category)
load CustID
,'Retained' as Category
//All Order in last 26 months
// latest order in last 13 months and at least 1 order in 13 months before that

where Max>addmonths(today(),-13) and Min <addmonths(today(),-13) 
;
load CustID
, Max(Date) as Max
,Min(Date) as Min
group by CustID
;
load *
resident Customer
where Date>addmonths(today(),-26)

[Martijn_W · Accepted solution]

Customers:
LOAD 'Customer-'&floor(Rand()*5000) as Customer,
Date(Today() - Floor(Rand()*720)) as PastDates
AutoGenerate (5000);

Customer2:
NoConcatenate load
Customer,
if( PastDates >= addmonths(today(),-13),1,0) as Last13Months,
if( PastDates >= addmonths(today(),-12) and PastDates < addmonths(today(),-1) ,1,0) as Between1_12Months,
if( PastDates >= addmonths(today(),-26) and PastDates < addmonths(today(),-13) ,1,0) as Between26_13Months,
if( PastDates >= addmonths(today(),-18) and PastDates < addmonths(today(),-13) ,1,0) as Between18_13Months,
if( PastDates >= addmonths(today(),-24) and PastDates < addmonths(today(),-18) ,1,0) as Between24_18Months,
if( PastDates >= addmonths(today(),-1),1,0) as LastMonth,
if( PastDates < addmonths(today(),-24), 1,0) as Olderthan24months
resident Customers;

Drop Table Customers;

Customers:
load
Customer,
if(sum(Last13Months) >=1 and sum(Between26_13Months) >=1, 'Retained Customer',
if(sum(Last13Months) >=1 and sum(Between26_13Months) =0 and sum(Olderthan24months) =0, 'New Customer',
if(sum(Between18_13Months) >=1 and sum(Last13Months) =0, 'Lapsed Customer',
if(sum(Between18_13Months) >=1 and sum(LastMonth) >=1 and sum(Between1_12Months) =0, 'Returning Customer',
if(sum(Between24_18Months) >=1 and sum(Last13Months) =0 and sum(Between18_13Months) =0, 'Lost Customer'))))) AS LoyaltyBracket
resident Customer2
group by Customer;

Drop table Customer2;

You then left join this table on the original Table, to fit your requirement as I understand it. You would be able to filter on Date and get order data with customer ID's and Loyalty Brackets.

[QlikNewbie20]

Hi dilipprajinth.

Thanks for quick reply.

I seem to get an error in the last bit of script. I assumed to  rewrite as below but I then get an error saying "Date" field not found

load CustID

resident Customer

where Date>addmonths(today(),-26)

 Any suggestions why the Date field cannot be found?

Thanks

[QlikNewbie20]

Hi Martijn

Thanks for the quick reply. I'm curious to know where is the script does it reference the date field? The field that I use is called TransDate so I'm not sure where to insert that.

Thanks

[dplr-rn]

@QlikNewbie20  glad that martijn's code worked but please mark his answer as the solution not your own.

the whole point of this community is to give credit to people who help others with their time

[QlikNewbie20]

Hi. Sorry, I clicked wrong button. Updated now. Thanks guys.