topic Re: User Churn Chart in QlikView

User Churn Chart

Wed, 10 Apr 2013 06:07:08 GMT

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_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

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.

Thanks in advance for any help you can give me!

Adam

Re: User Churn Chart

Gysbert_Wassenaar — Wed, 10 Apr 2013 14:52:39 GMT

See attached qvw

Re: User Churn Chart

Wed, 10 Apr 2013 23:53:04 GMT

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,

Adam

Re: User Churn Chart

swuehl — Thu, 11 Apr 2013 13:19:16 GMT

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:

LOAD * INLINE [

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:

LOAD Date(weekstart($(vStart),iterno()-1)) as ChurnRateDate,

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:

LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

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:

LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

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

Anonymous — Fri, 15 Nov 2013 09:56:08 GMT

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?

Thanks in advance!