Associating $ to each product when they are bundled into a single SKU

[jparkerrandall]

Hey fellow solvers,

A quick one to gather some thoughts on how others might approach the following problem. Lets say a company has the following products:

Newspaper A (NPA)

Newspaper B (NPB)

Newspaper C (NPC)

Newspaper D (NPD)

Newspaper E (NPE)

Newspaper F (NPF)

etc

Now the company only sells their products in bundles:

SKU A = NPA + NPB

SKU B = NPA + NPB + NPC

SKU C = NPA + NPB + NPC + NPD

SKU D = NPA + NPB + NPC + NPE

SKU E = NPD + NPE + NPF

etc

Now someone in the company wants to understand how much $ comes from NPA, so we need to create some kind of attribution model to split out how much $ comes from each product.

Now you might argue that if all customers pay the same for each bundle, there are some observations that you can make. For example, we can figure out NPC as it would be the difference between SKU B and SKU C. The same idea can get you NPD, NPE, NPF. (This is my current methodology)

But what if not all customers pay the same rate. For example, some customers get a discount as they are buying many thousands of newspaper bundles, other may have just negotiated very strong rates. As such, using the above approach, our attribution model gets strongly influenced by the number of customers in each bucket.

I've thought about using Linear Regression (and other Tools in the Predictive suite) to train a model based on the papers a customer purchases e.g. NPA / NPB to predict the $ amount a customer would pay for each bundle. I was then going to pass through all possible bundles into the scoring tool (if there are 6 newspapers there should be 2^6=64 potential 'theoretical' bundle combinations). And then get back to each product by seeing the impact to the $ if I include or remove a product.

But, I wondered if anyone thought I was missing a trick, should use a different approach or just give up!

Thanks in advance.

[DanM · Accepted solution]

@jparkerrandall,

Looking at your request, I am not seeing how Alteryx can help you come to the conclusion you want from the standpoint of being able to predict a value without understanding your bundling structure and discounting. In order to reach the goal you want you would have to understand both of those variables. If you don't have that information, really the only way to accomplish this task would be to understand what percentage the NPA makes up of each bundle and apply it to the pricing for each varied cost of the bundle. The Predictive tools like the Linear Regression will never match the total package cost without understanding the bundling or what each newspaper is worth within the bundle. Once you understand these other variables or decide to go with a percentage base concept, you can then apply that logic in a workflow to streamline the process.

DanM

[mmenth]

Hey @jparkerrandall,

Is it oversimplifying to use a weighted average? You could first calculate an average of the cost of each sku, then use weights in your subtraction to get the true value of NPA. 

For instance assume that on average Sku B costs $10 and Sku A costs $7. Your math right now would say the added value of NPA is $3. However, let's say 1500 people currently buy Sku B while only 500 buy Sku A. Then we can weight our formula as follows:

[(Sku B avg cost)*(Number of subscribers to B) - (Sku A avg cost)*(Number of subscribers to A) ] / (Number of subscribers to A + Number of subscribers to B)

=[($10)*1500 - ($7)*500] / (1500+500)

=($15000 - $3500) / 2000

= $5.75

So the real value of adding NPC is $5.75, not $3.

This may be oversimplifying, and I may not be understanding the problem fully, but I would suggest playing around with formulas like this before diving into regression.

Best,

mmenth