topic Re: Customer loyalty based on months since last activity in QlikView

Customer loyalty based on months since last activity

QlikNewbie20 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Customer loyalty based on months since last activity

dplr-rn — Thu, 29 Oct 2020 21:27:46 GMT

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)

Re: Customer loyalty based on months since last activity

Martijn_W — Thu, 29 Oct 2020 21:42:04 GMT

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.

Re: Customer loyalty based on months since last activity

QlikNewbie20 — Fri, 30 Oct 2020 05:15:50 GMT

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

Re: Customer loyalty based on months since last activity

QlikNewbie20 — Fri, 30 Oct 2020 05:19:19 GMT

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

Re: Customer loyalty based on months since last activity

dplr-rn — Fri, 30 Oct 2020 13:39:18 GMT

@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

Re: Customer loyalty based on months since last activity

QlikNewbie20 — Fri, 30 Oct 2020 15:11:24 GMT

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