4 Replies Latest reply: Nov 15, 2013 4:56 AM by Annika Palm

# User Churn Chart

Hi everyone!

I'm new to the QlikView Community, so forgive me if this subject has been covered. I can't seem to find the answer I'm looking for.

I'm trying to calculate a churn report by taking the amount of unique users that have logged on to our site during a 30 day period, and finding the amount of those that have logged on again in the next 30 day period. The catch is that I want to calculate this every week using WeekStart. For e.g.:

5 unique users in the WeekStart-60 to WeekStart-30 range (a.k.a. "A"), 4 of those are still in the WeekStart-30 to WeekStart range (a.k.a. "B"), therefore churn would equal (A-B)/A or (5-4)/5 = 20%.

Here's some test log in data to give you an idea of how the log file is structured:

User_IDDate_Logged_On
101/01/13
201/01/13
301/01/13
401/01/13

5

01/01/13
201/02/13
301/02/13
101/02/13
501/02/13
201/02/13

I hope this make sense, it's been driving me crazy looking at ARRG() functions, Set Analysis, etc. Not sure what to use or how to use it.

• ###### Re: User Churn Chart

See attached qvw

• ###### Re: User Churn Chart

Hi Gysbert,

Thanks very much for the reply. Unfortunately I don't have the paid version of QlikView yet. I'm still waiting on approval and a budget to get it, which could take months. Could you please explain how you did it? Maybe with examples of the code?

Sorry if it's a hassle, really appreciate it.

Regards,

• ###### Re: User Churn Chart

I think there might be an issue with the suggested approach, because it does check that group B is a subset of A, instead it just compares the distinct count of users in both periods.

For example, if you replace your second last line with:

7, 01/02/13

the distinct count won't change and the result is the same, -20%, while user 7 has not appeared in previous period yet and should be disregarded.

I think you can potentially create a frontend only solution, but it will be probably very complex and resource extensive.

If possible, try creating a solution in the script, e.g. you can check for User appearance in the requested periods in script already.

Maybe like this (you can probably optimize this a lot):

SET DateFormat='DD/MM/YY';

T1:

User_ID, Date_Logged_On

1, 01/01/13

2, 01/01/13

3, 01/01/13

4, 01/01/13

5, 01/01/13

2, 01/02/13

3, 01/02/13

1, 01/02/13

5, 01/02/13

2, 01/02/13

];

Let vStart  = num(makedate(2013,1,1));

Let vEnd     = num(makedate(2013,3,1));

Churn:

Date(weekstart(\$(vStart),iterno()-1)-30) as Date30,

Date(weekstart(\$(vStart),iterno()-1)-60) as Date60

AutoGenerate 1

while weekstart(\$(vStart),iterno()-1) <= weekstart(\$(vEnd));

JOIN LOAD distinct User_ID Resident T1;

Let vNumDates = NoOfRows('Churn');

for i = 0 to vNumDates-1

TMP1:

peek('User_ID',\$(i),'Churn') as User_ID,

if(count(Date_Logged_On),1,0) as Flag30

Resident T1

where User_ID = peek('User_ID',\$(i),'Churn')

and Date_Logged_On <= peek('ChurnRateDate',\$(i),'Churn')

and Date_Logged_On > peek('Date30',\$(i),'Churn');

TMP2:

peek('User_ID',\$(i),'Churn') as User_ID,

if(count(Date_Logged_On),1,0) as Flag60

resident T1

where User_ID = peek('User_ID',\$(i),'Churn')

and Date_Logged_On <= peek('Date30',\$(i),'Churn')

and Date_Logged_On > peek('Date60',\$(i),'Churn');

next i

Left Join (Churn) LOAD * Resident TMP1;

Left Join (Churn) LOAD * Resident TMP2;

drop table TMP1;

drop table TMP2;

Then, in the frontend, just create a straight table chart with dimension ChurnRateDate and as expression:

= (count({<Flag30 = {1}, Flag60={1}>} User_ID) - count({<Flag60={1}>} User_ID))

/ count({<Flag60={1}>} User_ID)

Hope this helps,

Stefan

• ###### Re: Re: User Churn Chart

Hi! I have a smiliar problem so i tag along here .

I want to look at month for when a customer "churned". I have used your example, swuehl, and changed it so that i can se customer that has not bought anything in the past 12 moths. My problem is that if a customer bought at two different dates, that customer will have 2 churn dates. i only want to established when that customer first (or maybe last(?)) churned according to my definiton when looking at a time period.

My problem is if i look at Orgno 1 you can se that it had activity 2012-01-04 and 2012-09-20 so when i look at a period of time this orgno "churns" at both 2013-01 and 2013-09 but since that customer has come back in a 12 month period the churn should only be at 2013-09. how can i solve this?