Product Mix Analysis

[pookyman]

Hello, I'm trying to craft an analysis of the product mix of customers using a dataset I have.

I want to know how many customers have bought just Product A, just Product B, just Product C, just Product A + B, just product A + C, and Products A + B + C, and how that trends over each quarter.

The dataset I have has the initial purchase dates for each of the products for each customer.

Some more description on how I would like the analysis to go:

If a customer buys Product A in FY14Q1, I would expect in FY14Q1, it would be +1 for Product A. However, if in FY14Q2, the same customer buys Product B, I want to see a -1 for Product A in FY14Q2 and a +1 for Product A + B in FY14Q2.

In addition, if another customer buys Product B in FY14Q3, I want to see a +1 for Product B in FY14Q3. Then the customer buys Product C in FY14Q4, -1 for Product B, +1 for Product B+C. Finally, if it buys Product A in FY15Q1, -1 for Product B+C, +1 A+B+C

If a customer buys A+B in the same quarter, I want a +1 for A+B and nothing for the individual Product A and individual Product B

The total sum on the summary page should equal the total number of customers on the data page.

Any help would be much appreciated. Please see the attachment here - Product Mix.xlsx

Thank you

[Richard Buttrey]

Hi

A Pivot table I suggest is going to be your best option and certainly the most efficient. However your data first need to be normalised to a simple 2D layout. Hence you should change it so that it has columns for

Account Name
Purchase Year
Purchase Qtr
Product Name
Just A values being Y/N
Just B "
Just C "
A & B "
A & C "
A & B & C "


The Y/N values in the columns will be formulae

You also seem to want to know the variance from the same Qtr in the previous year. This will probably need three more columns with formulae to evaluate the change from the previous Yr/Qtr but if you can re-arrange the data as above first then it will be easier to see how best to achieve this penultimate requirement.

Similarly with your last requirement. It's not clear whether a customer buys more than one of the same product in the same period. If so then clearly another column for the quantity will be necessary since the above suggests you only want to know the FACT that a product has been bought irrespective of whether more than one is bought.

As I say the key to this is the normalised table.