Matrix of product lift
jonathan.o'grady.0 Jul 22, 2015 10:36 AMHello 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

tableau LOD example.twbx 26.7 KB

tableau example.xlsx 11.7 KB