topic Customer retention in QlikView

Customer retention

boris_nechaev — Tue, 06 Sep 2011 18:28:09 GMT

Hello QlikView community!

I have a task that I don’t quite know how to approach. Any help would be much appreciated.

I have two tables to work with. First is the customer information with relevant fields being customer ID (customer_id) and account creation date (account_created_date). The second table stores customer sessions, i.e. customer visits to the system. Fields we need here are customer_id and session_date.

The task is to figure out customer retention. Specifically, for each customer I have to calculate number of days in which he/she had a session within first 14/30/60 days after account creation. I know how to then aggregate this information, what I don’t know is how to obtain it.

Thanks in advance!

Customer retention

swuehl — Tue, 06 Sep 2011 23:43:59 GMT

Hi Boris,

maybe something like this:

= count( if( session_date >= account_created_date and session_date <= account_created_date+14, session_date))

to count all session dates within the first 14 days (replace 14 by 30/60 to retrieve the other buckets then).

Create a straight table with dimension customer_id, then use above expression(s). Your sessions should already be correctly linked to the customer via customer_id. session_date and account_created_date must also be recognized as Date type by QlikView, please have a look into date format and Date() and Date#() functions for that.

Does this answer your question? I assumed you already loaded the tables into QlikView and the tables are linked together.

Regards,

Stefan