1 2 Previous Next 18 Replies Latest reply on Oct 23, 2014 4:41 AM by matthieu.degroot

    Group customers based on order behaviour




      I'm busy with analyzing the shopping behavior of our customers. This post was already a great help in finding out how to group customers based on the amount of orders placed per customer:

      Group customers based on number of orders


      However I would like to take it a step further and make customer groups based on combinations of types of products they bought. We have a shop where people can buy a sample of the product before they place a full order. This sample is a unique product named 'sample' and a final order is called 'final'. What i am trying to find out is how many people order 0, 1, 2, or more samples before they place an final order.


      Example of data:

      DateCustomerProduct Type


      In this example the result would be:

      Group 1 (0 sample): 1

      Group 2 (1 sample): 2

      Group 3 (2 sample): 1

      Group 4 (3 or more): 0


      Could someone help me out and point me in the right direction? 


      Thanks in advance!

        • 1. Re: Group customers based on order behaviour
          Matt Lutton

          Hi Matthieu de Groot: if you can mock up a sample packaged workbook using this sample data, someone will be more likely to jump in and help--I'll certainly take a look if you can post a TWBX that demonstrates the issue you're trying to resolve.  Cheers

          • 2. Re: Group customers based on order behaviour

            You're totally right :-) Here is a mock up of the data.

            • 3. Re: Group customers based on order behaviour
              Matt Lutton

              Well, here is one way to get you started on a distribution of how many Samples were ordered.  I just got off a screenshare with Joe Mako, who reminded me of a simple way to get the results you've asked for in your mock up.


              Your scenario did not include an example where a Sample order is placed AFTER a Final order, so if that is another layer of complexity, we'll need to go a different route, and we'll need sample data that represents that scenario (plus any others that may exist in your actual data).


              But to get a simple 0, 1, 2 distribution of how many IDs had a Sample order, you can use the #3 Table Calc technique shown here:




              The method used seems several fairly complex, as we're using a few techniques many folks do not know exist -- notably partitioning a Table Calc by a Discrete Measure pill (which is not typically possible in the Advanced Settings of a Table Calculation) via unchecking the "Ignore in Table Calculations" right click option on a Discrete Measure pill in a view.


              Its also a very simple solution, once the concepts are in place.  I'll do my best to write out my understanding/explanation.


              We use this calc to identify how many users had sample orders:

              SUM(IF [Order]=="Sample" then 1 else 0 end)


              From there, its just a matter of setting up the view with ID on the detail shelf, and in this case, I used a simple Table Calc of:

              SIZE() -- with a compute using on ID (in Tableau terms this means "Addressing" on ID and Partition on the discrete measure since we unchecked "Ignore in Table Calculations")


              To calculate the size of the partition in the view (the ends up giving us the # of IDs who placed Sample Orders in this example)  The partition in the final view is set to be the Discrete AGG(Sample Flag) pill due to the unchecking of "Ignore in Table Calculations".


              Ignore in TC.png


              By unchecking that option, we are telling Tableau to partition our Table Calc by the discrete measure.  We set the "Compute Using" (or Addressing) of the Table Calc to be on ID, which is on the Detail shelf so it can be used in the Table Calc, but not shown in the view.


              The "First" filter is an often used optimization technique used to ensure only one mark is returned per partition in a Table Calculation result. The calculation for that filter is also a simple Table Calculation of:

              FIRST()  -- ALSO with a compute using on ID


              I'm happy to go over this approach with you in a bit more depth.  Just let me know how I can help.  I just did a screenshare with another user, who reminded me of this approach, so I'm happy to pass it on where I can.


              The same approach is shown by Jonathan Drummey in this workbook, see #11 "How many of X did how much of Y": http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/TheNextNTableCalculations/Introduction


              Version 8.2 workbook attached.  One limitation of this approach is that we cannot have a "Group 4" as your mockup includes.  It would appear when a user had 3 or more samples automatically, but Tableau won't draw that unless the data supports it.


              There are likely several other approaches to the same solution -- this one is quick and easy....  as long as you understand a bit about Table Calculations and the concepts or partitioning and addressing in Tableau.  Try rebuilding this example from scratch yourself, and if you want to do a screenshare, I'm happy to go through it with you.


              I hope this helps, and does not add more confusion.  Cheers!  The final view looks something like:

              Final View.png

              Many thanks to other users like Jonathan and Joshua, who both write great blogs on Tableau -- and, of course, thanks to Joe Mako, for taking the time to work with me, and teach me one-on-one via screenshares.

              1 of 1 people found this helpful
              • 4. Re: Group customers based on order behaviour

                Thank you Matthew for the extended explaining. Great help!

                I can follow most parts and am able to recreate it in my own dataset. However i noticed that my data is more complex, than in my sample workbook. There seems to be a fair amount of customers that order 1 or more samples, but do not do a final order. It is however not possible to see this with this method. Which is in line with what you said about when someone orders another sample, after the final order. 

                This group that doesn't order a final order is very relevant for us, because it might tell us that people didn't like our (sample) product. Besides identifying this non-final group, taking into account a final order is also relevant for us because another thing we want to measure is the time between the (first) sample and the final order. With this we could create a forecast, based on our historical samples!

                Here is an update of the sample workbook. I updated the data with users that only order a sample and where indeed someone orders another sample, after having bought a final. My questions: how can a sample or a chain of samples be accounted to the first next final order? And how can this non-final ordering group be identified? And (how) can this be structured in such a way that it would also be possible to measure the time between the (first) sample and the final order?

                • 5. Re: Group customers based on order behaviour
                  Matt Lutton

                  OK, that's a lot of info... let me try and wrap my head around it.  I would encourage anyone else to jump in here and throw out ideas, as I'm not quite sure how to proceed next.


                  Matthieu de Groot: Everything I built previously worked because we weren't concerned with Samples that occurred after a Final order.  Now, we just have a Count of how many users ordered a sample, but that doesn't give you what you need.


                  Can you explain what you mean by: how can a sample or a chain of samples be accounted to the first next final order?


                  I'm hopeful some other amazing Tableau person will jump in to help us out, as I'm not sure what the next logical steps would be.

                  • 6. Re: Group customers based on order behaviour

                    What i meant with "how can a sample or a chain of samples be accounted to the first next final order?" is maybe best described in an example:

                    In our shop we sell shoes, but our customers do not come to our shop to buy just one hat, but let's say they want to buy at least a hundred. So what we do is we sell sample products (1 shoe), so the customers can see/feel the quality before they place a final order of at least a 100 shoes.


                    Customer ID 'Zack@me.com' in our dataset ordered a Sample (shoe) on 01-01-01, now he also placed a Final order (so at least 100 shoes) on 01-02-01. 'Joey@ma.com' however ordered a sample hat on 01-02-01, but never ordered a final batch of at least 100. 'Donna@me.com' ordered a sample on 01-03-01 and on 01-04-01 and then later placed a final order on 01-05-01. We want to link the 'sample' orders with the 'final' order on a later date within the same account (ID). Or see if the account has only placed an sample and no final.


                    Does this make it more clear or even less?

                    • 7. Re: Group customers based on order behaviour
                      Matt Lutton

                      Yes, I understand the business "logic" behind what you're after, but I'm not sure what kind of visualization you're wanting to create with this sample data.  Mock-ups can be very helpful in communicating that.


                      If you are posting here to try and get ideas on visualizations you might build, that's another story, and may be best left to someone who has experience solving this type of problem in Tableau.  I'm just trying to learn from your problem, but I'm not sure what kind of viz you're after.



                      • 8. Re: Group customers based on order behaviour

                        Basically the same visualization as before, with the inclusion of the 'Sample, but no final order' group. But maybe it's better to not focus on the visualization for the moment..


                        The bigger problem is that i can't seem to figure out how i could make certain customer groups based on conditions.

                        1. This is my data:




                        2. I want to create three user groups:

                        Group 1


                        Sample = 0


                        Finalorder = 1

                        Group 2


                        Sample > 1


                        Final = 1

                        Group 3


                        Sample > 1


                        Final = 0

                        It would look in the end something like this:


                        3. Question: How to write these conditions?

                        With how my data is structured I cannot seem to figure out how i could bundle the rows with the same [ID] and than place them in a group, based on conditions..

                        • 9. Re: Group customers based on order behaviour
                          Matt Lutton

                          Hi Matthieu de Groot:  I am inquiring with Joe Mako again to see how we might solve the problem, given the additional conditions you've added to the data since our first go-round.


                          Assuming his solution makes sense to me, I will be sure to try and post something that makes sense for you.  If needed, you and I can do a screenshare session to go over any potential solution.  But I wanted to let you know that I have not given up on you completely!

                          • 10. Re: Group customers based on order behaviour
                            Matt Lutton

                            Let me know if the attached helps you out.  If you have questions, I am happy to discuss this with you in more detail -- or I'm sure Joe would as well.  He pointed me toward this solution.  The 'workout 2' sheet matches your mock up and the 'Groups' sheet shows a count per grouping.


                            I hope this helps.  Cheers!

                            • 11. Re: Group customers based on order behaviour

                              I applied it to my dataset and it seems to work. Thank you very much Matt Lutton and Joe Mako for taking the time for helping me! The 'Count per Groupings' and then compute using on ID is just what i needed!


                              A few new questions arise, but i will probably ask them later in a new topic, since they are not specifically related to creating these customer groups. Which this topic is about. ;-)




                              Again thank you very much for taking the time and helping me!

                              • 12. Re: Group customers based on order behaviour
                                Matt Lutton

                                No problem.  As mentioned, I'm happy to speak with you over the phone or over a screenshare if you want to go into more depth on how this works.


                                The main commonality between both approaches (the first example and the second), is that we are able to Partition a Table Calculation by a Discrete Measure/Aggregate, by unchecking "Ignore in Table Calculations" on the Discrete Measure pill.


                                The threads I linked to previously -- the "Slicing by Aggregate" and "How Many of X did how much of Y" both discuss this technique a bit.  The WINDOW_SUM just counts the number of records within each partition.


                                I had overlooked the simplicity of the second solution -- which really is just an extension of the first solution.


                                I hope this helps -- again, happy to discuss further if you want to at any time.  This was a good reminder of how Table Calcs can make some things easier to accomplish in Tableau -- but we must be aware of functions in the tool, like the ability to partition Table Calcs by a discrete measure via unchecking the "Ignore in Table Calculations" option.

                                • 13. Re: Group customers based on order behaviour

                                  Is it true that with this WINDOW_SUM approach it is not possible to express these groups in percentage of the total count?

                                  • 14. Re: Group customers based on order behaviour
                                    Matt Lutton

                                    It might become difficult, but I doubt it is impossible.  What exactly are you looking for?

                                    1 2 Previous Next