topic Cross Pollination First Order vs Second Order in QlikView

Cross Pollination First Order vs Second Order

Anonymous — Tue, 11 Aug 2015 12:24:17 GMT

Hi,

I have a challenging question (I hope).

I want to check the share of recurring customers how purchase from a new product category on their second purchase.

I have attached some dummy data, which could be read the following way.

Customer A purchased Clothes both on her first and second order, which would mean "No Cross Pollination"

Customer B purchases Electronics both on her first and order, which would mean "No Cross Pollination"

Customer C purchases Books the first time and then Clothes, which would mean "Cross Pollination"

Customer D purchases Clothes the first time and then Books, which would mean "Cross Pollination".

This would mean a result of 50% (2 out of 4 customers) Cross Pollination.

But how would I get this number in QlikView? To make it more complex I would of course have 100000+ customers and 10+categories and you could purchase from several categories already the first time.

Kind regards

Niklas

Re: Cross Pollination First Order vs Second Order

Gysbert_Wassenaar — Tue, 11 Aug 2015 12:29:07 GMT

This should give you that first 'uncomplicated' number:

=count({<Customer={"=count(distinct Category)>1"}>}DISTINCT Customer)/count(DISTINCT TOTAL Customer)

Re: Cross Pollination First Order vs Second Order

Anonymous — Tue, 11 Aug 2015 12:41:09 GMT

Hi,

I agree that this is the "uncomplicated" number showing share of customers who are purchasing from several categories, but it does not solve the question wether they have switched categories between first and second order.

Neither does it handle the situation when first and second orders are the same, but order three is in a new category.

As a general overview of multi-category purchase share it is still fine.

Kind regards

Niklas

Re: Cross Pollination First Order vs Second Order

Anonymous — Tue, 11 Aug 2015 12:51:58 GMT

You Need to provide a flag during script

with this script you get at least that second (or third or later on) that custome rboght a product from other category

you may adapt it to your reequirements

TEST:
LOAD * INLINE [
Customer, Category, Order Rank, Order Id
A, Clothes, 1, 1
B, Electronics, 1, 2
C, Books, 1, 3
D, Clothes, 1, 4
A, Clothes, 2, 5
B, Electronics, 2, 6
C, Clothes, 2, 7
D, Books, 2, 8
];

TEST1:
NoConcatenate load *
resident TEST
order by Customer, [Order Rank];

TEST2:
NoConcatenate load
*,
if (Customer=previous(Customer) and Category<>previous(Category),1,0) as ChangeFlag
Resident TEST1;

drop tables TEST, TEST1;