Count #Orders for specific products, Determine segmentation by order timing

[samuel_lin]

Hello Qlik Geeks,

I have a data set like this:

CustomerID	Order Date	Product
123	10/1/16	A
123	10/1/16	B
123	10/3/16	C
123	10/5/16	D
123	10/10/16	A
456	10/1/16	A
456	10/2/16	A
456	10/3/16	B
456	10/5/16	C
456	10/5/16	D
456	10/10/16	C
456	10/11/16	C

I want to do something either in the backend script load or front end to:

(1) Segment customers by the number of times placing orders for specific or a group of product; or count the number of times a customer has ordered a specific product

(2) Determine the last date that a certain customer has ordered product A (or any other product)

(3) Segment customers into three segments:

     (a)customers who have recently (within the last 6months) bought productA

     (b)customers who have placed at least an order of productA 6+months ago and continue to buy other products other than A recently (within 6months)

     (c)customers who have placed at least an order of productA 6+months ago and stop buying.

I have tried to create segmentation from load script but the outcome doesn't seem accurate, please help and let me know your thoughts!

Thank you,

Samuel

[sunny_talwar]

Would you be able to provide your requirement in form of numbers?

[samuel_lin]

Hi Sunny, than you for your reply. I have seen your response to numerous qlik community posts and am very thankful to get your attention to mine.

not exactly sure what you meant by form of numbers, but let me try:

first of all, let me add one more column, Revenue, associated with each order

CustomerID	Order Date	Product	Revenue
123	10/1/16	A	350
123	10/1/16	B	250
123	10/3/16	C	100
123	10/5/16	D	400
123	10/10/16	A
456	10/1/16	A
456	10/2/16	A
456	10/3/16	B
456	10/5/16	C
456	10/5/16	D
456	10/10/16	C
456	10/11/16	C

(matches with the original post (1) to (3); using productA as the specific product)

(1) ideally, I think I would like to have a dimension that can count customer who have order product A once, twice, 3~10 times, 10+ times... any thought about calculating revenue from a specific product (e.g. productA)

Dimension	Customer Count	Total Revenue	Revenue from ProductA
1	100
2	200
3 to 10	250
10+	300

(2) I would like to have a dimension that can count number of customers who last ordered product A, within 30days, 30 to 60 days, 60 to 90 days, 90 to 180 days, 180+ days....

Dimension	Customer Count	Total Revenue	Revenue from ProductA
30	100
30 to 60	200
60 to 90	250
90 to 180	300
….

(3)

Dimension	Customer Count	Total Revenue	Revenue from ProductA
Recent	100
Stopped ProductA and continued	200
Stopped ProductA and Lost	250

Please let me know if this is what you were asking about?

Thanks!!

[sunny_talwar]

While I work on the 3 req, check if 1 and 2 make sense

[sunny_talwar]

Actually how do you define your dimension for the third req. How to determine if dimension is recent or Stopped ProductA and continued or Stopped ProductA and Lost?

[samuel_lin]

Hi Sunny,

I am still in the process of obtaining Qlik commercial license, do you mind posting the codes that I can try on my end?

Thank you very much!!

[samuel_lin]

I am just posting sample data here, but basically for the third requirement, I want to have dimension on a specific product

(a) placed an order for productA within 6months

(b) placed an order for productA 6+months ago, stopped buying productA but still buying other products

(c) places an order for productA 6+months ago, stopped buying completely

Please let me know if this is clear.

[sunny_talwar]

Sure...

Req 1(B)

Dimensions

1) CustomerID

2) =Aggr(

  If(Count({<Product = {'A'}>} Product) <= 2, Count({<Product = {'A'}>} Product),

  If(Count({<Product = {'A'}>} Product) <= 10, Dual('3 to 10', 3), Dual('10+', 4)))

, CustomerID)

Expressions

=Count(DISTINCT CustomerID)

=Sum(Revenue)

=Sum({<Product = {'A'}>}Revenue)

Req 2(B)

1) CustomerID

2) =Aggr(

  If(Today(1) - Max({<Product = {'A'}>} [Order Date]) <= 30, Dual(30, 1),

  If(Today(1) - Max({<Product = {'A'}>} [Order Date]) <= 60, Dual('30 to 60', 2),

  If(Today(1) - Max({<Product = {'A'}>} [Order Date]) <= 90, Dual('60 to 90', 3),

  If(Today(1) - Max({<Product = {'A'}>} [Order Date]) <= 1800, Dual('90 to 180', 4)))))

, CustomerID)

Expressions

=Count(DISTINCT CustomerID)

=Sum(Revenue)

=Sum({<Product = {'A'}>}Revenue)

Part 1 for both Req charts don't have CustomerID as a dimension as I was not sure if that was needed or not

[samuel_lin]

Thank you so much for this, Sunny. Since from experience it usually takes a lot longer to do if's in the frontend UI, I am applying similar logic to the backend load script like this:

Load If(Today(1) - Max({<[product] = {'productA', 'productB}>} [order date]) <= 185, [EmailAddress]) as [EmailAddress],

'Recent' as Segment

Resident [Order_History];

Concatenate

Load If((Today(1) - Max({<[product] = {'productA', 'productB}>} [order date]) > 185) and (Today(1) - Max([order date]) <= 185, [EmailAddress]) as [EmailAddress],

'Ordered and Continued w/out ProductA and ProductB' as Segment

Resident [Order_History];

Concatenate

Load If((Today(1) - Max({<[product] = {'productA', 'productB}>} [order date]) > 185) and (Today(1) - Max([order date]) > 185, [EmailAddress]) as [EmailAddress],

'Ordered and Stopped buying completely' as Segment

Resident [Order_History];

It doesn't seem to work yet in load script, perhaps that "{}" isn't allowed in loadscript? Please let me know your feedback.

Thank you!

[sunny_talwar]

Set analysis isn't available in script and aggregations require Group By statements. To apply set analysis like filters you can either use if or where clauses to get this done. This won't be very straight forward and will need to be worked on to get it int he script