Cross-Sell Analytics
My discussion with a friend led to a story about how a store used analytics to boost revenue by placing beer close to diapers. A retail grocery store did the study. The findings were that men between 30–40 years of age, shopping between 5 pm and 7 pm on Fridays, who purchased diapers were most likely to also have a beer in their carts which motivated the store to move beer close to diapers.
This is cross-selling analytics, i.e. understanding what other product a customer who bought product A also bought.
So what is cross-selling?
Business adobe defined this as encouraging customers to purchase products or services in addition to the original items they intended to purchase. Often the cross-sold items are complementary to one another, so customers have more of a reason to buy both.
By definition above, a cross-sell analysis aims to identify which products customers can be encouraged to purchase in addition to their initial purchase.
How do we carry out this analysis?
Let’s go into PowerBI to see this
The model is a pretty straightforward one
You can download the data I used if you need to follow it here. Let’s get back on track.
Remember, cross-sell analytics is to help us identify what products can be recommended to the customer based on initial purchase, i.e. we have to compare the products in the inventory. Hence we need two separate product tables. My product table is just a single-column table, so creating another table is pretty straightforward.
You can also load in two separate product tables; it doesn’t matter how it’s done. Now our model looks like this.
You want to create a relationship between the comparison product table and the sales table.
Let me leave that as a mystery so you can discover what would happen if you did but do that after creating your measures so you can see the before and after.
Model ready. All that is left is to start writing the DAX measures. First, we need to calculate the number of customers that bought a product.
Then, customers who bought more than 1 product, i.e. product A and any other product.
Let’s break down this DAX together.
Line 2–3 creates a virtual table listing customers who bought a product. i.e. for product A, list customers who bought product A. The product columns will add context.
Lines 5–13 also create a virtual table of customers who bought certain products. By default, DAX measures use the relationship established in the Model page unless you override it. The ALL function here is used to override the relationship between the products and sales table, i.e. we are telling PowerBI not to use the products — sales table relationship but use the connection specified in the TREATAS function.
Lines 15–19 are pretty straightforward.
Lines 21–31 are where we specify what to return. This is where the magic happens.
Remember, Lines 2–13 created two virtual tables. Consider this example if we are trying to determine how many customers bought products A and B. We would need a list of customers that bought A and another list of customers that bought B and then compare both lists to identify customers on both lists. Customers on both lists are the ones that purchased both products.
A similar thing is what the INTERSECT function here does. It compares both virtual tables and then creates another virtual table with a list of customers on both tables.
If you understand SQL Queries, think of it like an INNER JOIN. If you don’t, see the Venn diagram below.
Yellow is for customers who bought product A, while blue is for customers who bought product B. Green is for customers who bought both products. The INTERSECT function gives you the green, i.e. list of customers who bought both products.
The INTERSECT function is then wrapped in COUNTROWS because we are interested in the number of customers and not a list of customers.
Since we have everything in place, let’s use the measures in PowerBI.
I’m a fan of percentages as it is way easier to picture
You have done the table; how do you now run your analytics? Let’s go into PowerBI one last time.
You can see you don’t need to start worrying about python codes to do this. PowerBI is my personal nuclear weapon. It can do and undo.
You can view the full report here.
See you next time. Adios
Looking for more in-depth analysis and data-driven insights? I’m thrilled to be contributing to the fantastic TA Insight HUB blog! (www.tainsighthub.com) or on Twitter here and linkedIn here
Catch up on everything data from me and other experts over there. Let’s keep the data conversation going!
