1 2 Previous Next 19 Replies Latest reply on Aug 10, 2015 10:43 AM by jonathan.o'grady.0

    Matrix of product lift

    jonathan.o'grady.0

      Hello tableau fans!

       

       

      I've wasted a number of hours today trying to set up a difficult visualisation, and out of desperation I'm throwing myself at your mercy! All help much appreciated!

       

       

      I would like to calculate the likelihood that a product will be included in a sale, given that another product has been selected. What the retail trade call basket lift.

       

       

      I have a number of sales receipts, each of which has a unique ID.

       

       

      Each receipt will contain various items, which can be grouped by category, subcategory et cetera.

       

       

      Ultimately, the maths is simple. I need to count the number of times product A, say "bakery" was in a receipt versus the total number of receipts (call this bakery%).

       

       

      I then need to calculate the number of times "fresh food" was contained in a receipt, call this food%.

       

       

      Finally, I need to calculate the number of times both bakery and fresh food were contained in a receipt, call this bakery & food #

       

       

      I bring all of this together as:

       

       

      bakery & food #

      /

      total number of receipts*bakery%*food%

       

       

      This measures the independence of bakery and fresh food sales. A reading >1 indicates that there is a complementary relationship between the products.

       

       

      I have run into various problems:

       

       

      Calculating the denominator requires a level of detail fix on the number of receipts. However, the total number of receipts is also required leading to a level of detail aggregation issue.

       

       

      Once this is solved, what I would like to achieve is a matrix where I can select a number of product lines, and compare these across the range, for example:

       

       

      Uplift for bakery & food

      uplift for bakery & drinks

      uplift for bakery, food & drinks et cetera

       

       

      I would then like to drop in the day of the week onto the row shelf to see if the relationship differs on a Monday versus Friday for example.

       

       

      I attach a workbook extract, together with an Excel file which hopefully describes some of the above calculations in more detail.

       

       

      I am, as usual, at my wits end as to how to proceed so your ever helpful input much appreciated.

       

       

      Best wishes,

       

       

      Jonathan

        • 1. Re: Matrix of product lift
          jonathan.o'grady.0

          Swaroop,

           

           

          Thank you for your kind reply.

           

           

          Would definitely getting closer, but we are not there yet.

           

           

          Your calculation for "Receipts2" seems to be picking up subcategories.

           

           

          In the data, a single receipt may contain various line items within one category.

           

           

          We need the result to return 1 for each valid receipt.

           

           

          Using: {FIXED [Client Name]:COUNTD([ID])} seems to return all lines, not just all receipts.

           

           

          I tried changing this to:TOTAL(COUNTD([ID])) but I get the attribute issue.

           

           

          I must be doing something silly, any ideas?

           

           

          Best wishes,

           

           

          Jonathan

          • 2. Re: Matrix of product lift
            Dana Withers

            Hi Jonathan,

             

            Can I just throw an idea out there that unfortunately I've not really had time to test (and probably will not have enough time to take properly forward today)... Currently you're going through great lengths to calculate at ID level - presumably to make a basis to ultimately add up to the level of your grid as per excel.

             

            What if you change the shape of your data? The basis of your matrix in excel is that you can set categories on the rows and the columns. What if you connect to your datasource and take the same table in twice linking to itself on ID? Then use countD(ID) as your "number of records"/count sort of thing... you can do that using fixed LoD with both first categories for example. Then you have  setup that allows you to more easily use those numbers to total up and divide by a fixed countd of id to give you a total number of receipts.

             

            I've taken the data from your workbook into an excel file and then reconnected - both the start workbook and the excel file are attached, but don't go further than the initial setup.

            Sorry not wildly helpful if I've not worked out any of your calculations yet, but I hope it gives you a new approach that might give some fresh ideas...

             

            Dana

            • 3. Re: Matrix of product lift
              jonathan.o'grady.0

              Dana,

               

               

              Thank you very much for considering my problem. I must admit, I'm getting to the "pulling my hair out stage", so all input gratefully appreciated!

               

               

              I'm obviously working of the much larger database. When I tried to link to the data twice, tableau told me that I can't have two connections in the level of detail query.

               

               

              What am I doing wrong?

               

               

              Best wishes,

               

               

              Jonathan

              • 4. Re: Matrix of product lift
                Dana Withers

                Hi Jonathan,

                 

                I'm not sure why you would not be able to connect to the same table twice?

                Oh hang on... Are you making a duplicate of your connection and trying a blend or are you using your data connection screen to drag in the same table twice?

                I think you cannot use a blend in a table calc, but you should be able to amend your data connection to drag in the same table twice and link it there. That's what I've done in my starter sheet.

                 

                Hope that helps,

                 

                Dana

                • 5. Re: Matrix of product lift
                  Dana Withers

                  Hi Jonathan,

                   

                  I did have a chance to try again Workbook and Excel sheet attached again with changes.

                  However I am going crosseyed now so I'm not sure I did get it completely right.

                  I also think there was a slight mistake in the calculation in your example: F6 was calculated with a value from Product B, which I vaguely assume can't be correct.

                   

                  I did get a calculation going and working I think.

                  I copied over your sample data into another sheet so that it would match the same format as your data from the workbook. That way I could apply the same calculations on your sample data and verify the answers and then use those calculations on your actual data in the workbook.

                  I've no idea what this calculates and if the number are correct, but the sample numbers do seem to match correctly.

                   

                  What do you think?

                   

                  Dana

                  • 6. Re: Matrix of product lift
                    jonathan.o'grady.0

                    Dana,

                     

                     

                    This is just fantastic! I really can't thank you enough for the time and intelligence you have dedicated to my annoying problem.

                     

                     

                    The real trick seems to be exporting the database data, reorganising it in Excel, and re-importing it. It's a bit of a fiddle, but it gets the result. Thank you.

                     

                     

                    Can't wait to give it a test drive on the original data and see how it looks!

                     

                     

                    Best wishes,

                     

                     

                    Jonathan

                    • 7. Re: Matrix of product lift
                      Dana Withers

                      Hi Jonathan,

                       

                      Glad it seems to work!

                      (If it does work, could you please mark this question as answered?)

                       

                      What do you mean with exporting database data and putting it in excel? Why do you need to do that? I thought that your sample data would be as it is in the database, but if it is not, perhaps there is a way we can make this work with the database-shape of data? (I think I've lost my English there sorry)

                       

                      Thanks !

                       

                      Dana

                      • 8. Re: Matrix of product lift
                        jonathan.o'grady.0

                        Dana,

                         

                         

                        I suppose I'm just being a bit dim, but one I mean is: how do you end up with the "first thing bought" and "second thing bought"?

                         

                         

                        Surely you have to shape the data in tableau, and then export it and then reconnect?

                         

                         

                        Best wishes,

                         

                         

                        Jonathan

                        • 9. Re: Matrix of product lift
                          jonathan.o'grady.0

                          Dana,

                           

                           

                          Looking through your working again, I notice you kindly attached an Excel file with the flat data pivoted.

                           

                           

                          However, in the tableau document I notice that you have joined two worksheets: product 1 and product choice 2.

                           

                           

                          Although I don't see any pages in the excel file with these names.

                           

                           

                          You are obviously using some technique here that I am unfamiliar with hence my confusion.

                           

                           

                          Maybe you could point me to a webpage that would demonstrate the steps you took to set up these two tables?

                           

                           

                          I'll be working on this all weekend, so no problem if you get to this a little later.

                           

                           

                          Very much obliged to you.

                           

                           

                          Best wishes,

                           

                           

                          Jonathan

                          • 10. Re: Matrix of product lift
                            Jonathan Drummey

                            I think there might be an alternative route that doesn't require such a large cross product, I've been playing around with this lately in posts like Counting Pairwise Similar Votes in Tableau | Drawing with Numbers. I'll take a look tomorrow morning and get back to you.

                            • 11. Re: Matrix of product lift
                              Dana Withers

                              Hi Jonathan,

                               

                              I've not done anything mysterious really: I've connected the sample dataset from your excel calculation from sheet Example Data Pivoted (tableau example datasource). I've connected the "real data" from the sheet "Flat Data" (that's Flat Data+ datasource).

                              Both times what I've done is to drag over the sheet, give it a "first buy" name (if you double click on the name of a table/sheet you've dragged over, you can rename it in the connect to data screen), then I've dragged over the exact same sheet and given that a "second buy" name. Both tables are exactly the same sheet.

                              Unfortunately I cannot help you further as I'm off on holiday for the next week without any access to internet

                               

                              I'm very curious to see what Jonathan Drummey comes up with though because if this can be done without cross product you'd probably be better off as your data would be much smaller.

                               

                              I'll see in a week - good luck!

                               

                              Dana

                              • 12. Re: Matrix of product lift
                                Jonathan Drummey

                                Have a great holiday!

                                 

                                I'm not sure how much of a cross product I'll be able to avoid, I'm going for the smallest one possible.

                                • 13. Re: Matrix of product lift
                                  jonathan.o'grady.0

                                  Dana/Jonathan,

                                   

                                   

                                  Thank you both for looking into this problem. Glad to hear I'm not the only one interested in being able to count across a matrix.

                                   

                                   

                                  I have attempted to perform the analysis as per Dana's suggestion and I'm delighted to attach a sneak peak at my results comparing relationships by month by venu-for encouragement/inspiration purposes.

                                   

                                   

                                  Trouble is this data is not directly linked to my database as I had to extract the relevant data.

                                   

                                   

                                  Thank you all very much for looking into this. I'm sure a lot of users will find it of benefit.

                                   

                                   

                                  Best wishes,

                                   

                                   

                                  Jonathan

                                  • 14. Re: Matrix of product lift
                                    Jonathan Drummey

                                    Hi Jonathan,

                                     

                                    A few questions:

                                     

                                    1. What is your database? SQL Server, Oracle, etc.?

                                    2. How many receipts would you be analyzing in one view? (as a rough order of magnitude)

                                    3. How many products would you want to display in one view? (also as a rough order of magnitude)

                                     

                                    That will help guide which solution may be the most appropriate.

                                     

                                    Jonathan

                                    1 2 Previous Next