CRM customer migration table implementation

[felcar2013]

hi

do you have any experience creating in qlikview a dynamic "customer migration table"? (customer relationship management)

With pivot tables i got  it, but i cannot get the new customers (neu kunden, null values). I loaded twice my table, set different names for time and customer segment, but it contains 100 million records and it is not so handy, even if the data comes from qvds.

it looks like the following figure. On the left side, we have the "ABC customer" field with values : A-Kunden, B-Kunden, etc. and the time period (2012/06) The columns are exactly the same, but the difference is the period of time. What would be a good approach to display the table as it is shown in qlikview?

it is important to count customers that were A in 2012 / 06 and that went to other categories in 2012 / 12 or stayed in the same category

thanks

[#2]

Hi Felcar

Have you made sure that

- the "suppress when value is null" box in the dimensions tab is unchecked and;

- the "suppress zero values" and  "suppress missing" boxes are unchecked  in the presentation tab?

Otherwise could you give me an outline of your data model (structure of the columns) please?

Many thanks,

Erica

[felcar2013]

hi Erica

thanks for your answer. the boxes are unchecked. This is my table structure. The original table has the fields

Customer
contact_id
abc
segment
programcountry
month

from this table i created two, and renamed the fields but not the contact_id.

the formats are showed in the data extract. this data belongs to the second table (which is identical to the first one, but with 1 instead of 2).

i did an outer join of both of them and got this final pivot table:

this should show the customer migration. If i select Nov-2012 (row, month1) and Dez-2012 (column, month2), and corresponding segments (abc1, abc2) i see how many customers that were A in Nov-2012 stayed in the segment A in Dez-2012 and how many went to other segments, this is, migrated.

the problem is, i need to count the nulls (which i do not get like this), this is, customers that did not exist in Nov-2012 but who appear in Dez-2012 (same with lost customers). Do you know, if there is a way to get the nulls or probably another way to get all in one table doing something else?

[#4]

Hi Felcar.

Wow, what a data structure, I had to think about it for a while!

Why this is happening:

When you select your data, you are only selecting those with a segment in your chosen month 1 and chosen month2 . So there are effectively "no nulls".

IE when you make your selections the resulting data looks like this:

Month1	Segment1	Month2	Segment2
June	a	Dec	a
June	b	Dec	c
June	c	Dec	d
June	d	Dec	f

There also won't be any nulls in the whole table, as a result of the join (customers with only one month/segment would have just one line - month 1&2 would be the same and segment 1 & 2 would be the same)

To work out the customers that "did not exist" in june, you either need to

1) write some clever set analysis in your expression in the pivot table that coutn distincts everyone in the entire table who had a month <= latest of the two  months

2) load in a dummy "null" segment for customers not active in that month.

[#5 · Accepted solution]

1) Something like: If counting the number of customers returns nothing, then count all the customers whose month1 is less than the month 2 selected.

if(count(cust_no) = 0, count({1<Month1={"<=$(Month2)"}>} distinct cust_no)

You would need to play around withthe dimension too as this would effectively unselect the first month in your chart.

2) you need to "fill in the blanks" for all customers who have months missing or were not around at the start, so you end up with a dataset like this (new rows = bold)

Customer	month	Segment
12345	Jan	<null>
12345	February	<null>
12345	March	<null>
12345	April	<null>
12345	May	a
12345	June	a
12345	July	b
12345	August	<null>
12345	September	<null>

To do this you could outer join a full list of months onto your customer / month / segment table.

In the table, you now have a "null segment" that will be included in month1 / 2

Let me know if this works,

Regards,

Erica

[felcar2013]

hi Erica

thanks, i was thinking something similar with set analysis, but the dummy "null" segment is something also interesting. My application has already more than 60 million records per table and the outer join almost 1 billion, so i was afraid that with set analysis on the user interface, it would be slower.

i try it and let you know if it worked

thanks again

felipe

[felcar2013]

hi Erica

working with all nulls in the table was simplier, i got all segments including lost and new customers

many thanks

felipe

[#8]

Haha yes well it's always best to script anything complicated, especially with 1 billion records! That's impressive by the way.

Glad it helped,

Erica