Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
samuel_lin
Creator
Creator

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

Hello Qlik Geeks,

I have a data set like this:

     

CustomerIDOrder DateProduct
12310/1/16A
12310/1/16B
12310/3/16C
12310/5/16D
12310/10/16A
45610/1/16A
45610/2/16A
45610/3/16B
45610/5/16C
45610/5/16D
45610/10/16C
45610/11/16C

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

11 Replies
sunny_talwar

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

samuel_lin
Creator
Creator
Author

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

      

CustomerIDOrder DateProductRevenue
12310/1/16A350
12310/1/16B250
12310/3/16C100
12310/5/16D400
12310/10/16A
45610/1/16A
45610/2/16A
45610/3/16B
45610/5/16C
45610/5/16D
45610/10/16C
45610/11/16C

(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)

      

DimensionCustomer CountTotal RevenueRevenue from ProductA
1100
2200
3 to 10250
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....

      

DimensionCustomer CountTotal RevenueRevenue from ProductA
30100
30 to 60200
60 to 90250
90 to 180300
….

(3)

      

DimensionCustomer CountTotal RevenueRevenue from ProductA
Recent100
Stopped ProductA and continued200
Stopped ProductA and Lost250

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

Capture.PNG

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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