Store Billing Data Analysis

Schleiden

New Member
Joined
Sep 7, 2017
Messages
1
Hello Everyone!

I'm about to join a company that sells retail products through its stores. Hence, they should have data available on their customers shopping quite easily.

For Example:
The billing data shows that a customer buys soap, shampoo and potatoes; another buys shampoo, soap and tomatoes; and yet another buys shampoo, body-wash and a hammer. From this data we could say that there is a high possibility that a customer buys a shampoo and a body cleansing product each time; furthermore the probability that that cleansing product is a bar of soap is 66% and that it may be a body-wash is 33%.

Ofcourse the data above is simplistic, but this is exactly what I aim to do with the products that this company sells. I know that I can further add a complication like seasonality to this.

My question is, How do I measure the co-variance for this?

For stocks this is easy, you can measure a stock keeping the market as a standard. But in this case I need to form product pairs and then see their commonalities.

I expect the data will be under the following columns:
Bill no. > Date > Product name > Type > Brand > Sub-brand > Price > Discount

Ofcourse there could be more data that is given, however for this process i believe this is all the data that I'll need. I plan to show this data on an interactive graph with slicers that allow a user to choose the brands, sub-brands, products in a set (not just a pair). If no Product set is chosen, then any one product can be chosen and graph should give the range of products with the highest correlation in purchases.

Any advice on how to proceed with this analysis would be much appreciated.

Thanks.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,641
Members
416,932
Latest member
mm07

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top