Conjoint Analysis Using MS Excel for New Product Development

As a product manager of a SaaS company that is planning to create a new cloud storage app, you know that there are already a few great options available on the market. So how can you make sure that your app stands out from the crowd?

One way is to use conjoint analysis. Conjoint analysis is a market research technique that can help you understand how customers value the different attributes of a product or service. By asking respondents to choose between hypothetical products that vary in their attributes, you can identify the most important attributes to customers, the relative importance of those attributes, and how much customers are willing to pay for different levels of each attribute.

Here are three specific problems that conjoint analysis can help you solve when you’re building a cloud storage app:

1. What are the most important attributes in a cloud storage app? This is a critical question to answer, as it will help you prioritize your development efforts and make sure that you include the features that are most important to your target customers. Some of the key attributes that you might consider include:

   1. Storage capacity
   2. Security & Privacy features
   3. Price
   4. User interface
   5. Version Control
   6. Real-time Collaboration
   7. Sync capabilities
   8. Integration with other products
   9. Customer support

2. How do customers trade off different attributes while buying a cloud subscription? Once you know the most important attributes to customers, you need to understand how they trade off those attributes. For example, are customers willing to pay more for a higher storage capacity if it means sacrificing security features? Or are they more concerned about having a user-friendly interface? By understanding how customers make these trade-offs, you can optimize your product offering to meet their needs.

3. How can I test the new app concept before even launching the app? Conjoint analysis can also be used to test new app concepts before they are launched. This can help you reduce the risk of product failure by giving you feedback on how customers perceive your app before you spend a lot of time and money developing it.

Now we would use conjoint analysis to make better decisions about our product development, pricing, and marketing. This analysis is broken into 2 parts –

1. Conjoint Design
2. Conjoint Analysis

I am going to perform this analysis in MS-Excel as it is widely available with every PM.

Conjoint Design

Step 1 – Select the attributes and levels that are relevant for the product

For writing this blog, I have selected the following attributes:

1. Price
2. Brand
3. Storage
4. Large File Transfer Limit
5. Recovery Period for deleted files

Then, I went online to see what other cloud storage providers are offering and from their top products in the market, I selected the levels for my attribute.

1. OneDrive – Rs. 6200 per year, gives 2 TB storage, 200 GB large file transfer and 30 days recovery period for deleted files.
2. Google Drive – Rs. 6500 per year, gives 2 TB storage, Unlimited large file transfer and 25 days recovery period for deleted files.
3. Dropbox – Rs. 9000 per year, gives 5 TB storage, 250 GB large file transfer and 180 days recovery period for deleted files.

Step 2 – Create product bundles

Since we have 5 attributes, and each have 3 levels (except Storage -which has 2 levels), the number of possible products would be – 3*3*2*3*3 = 162 products.

Tips:

1. Do not use too many attributes for conjoint analysis. Select only relevant attributes.
2. You can use focused group discussions to select the attributes.
3. Select attribute levels similar to the existing products.
4. Levels should have distinctive utility for the end users.

I used orthogonal design to create product bundles and the software gave me these 15 product profiles.

Now, we ask the respondents to rate these hypothetical products. You can also do this via FGDs, Questionnaire surveys. Once done, calculate the rating for each product and add it in the excel table.

Now, I have the ratings against each of the product profiles.

Conjoint Analysis

Step 3 – Run dummy variable regression

A dummy variable is a variable created to assign numerical value to levels of categorical variables. Each dummy variable represents one category of the explanatory variable and is coded with 1 if the case falls in that category and with 0 if not.

Lets take Google Drive product as the baseline –

Google Drive – Rs. 6500 per year, gives 2 TB storage, Unlimited large file transfer and 25 days recovery period for deleted files.

Now, everything else becomes 0 and only the base selected level becomes 1. This would give us this table:

Now we construct the model as follows:

Now we will run dummy variable regression using the above table.

We don’t check the p-value significance in case of dummy variable regression. The beta coefficient takes care of the magnitude of utility of these attributes.

From the above regression, we have the beta value for below attribute levels –

• βPrice_6200 = 1.7
• βPrice_9000 = -2
• βBrand_Dropbox = 0.4
• βBrand_Microsoft = 0.2
• βStorage_5TB = 1.1
• βLFT_200 = -0.8
• βLFT250 = 0
• βRecovery_30 = -0.5
• βRecovery_180 = -0.1

Step 4 – Calculate the part worth diagram for each attribute level

Now, to calculate the marginal utility of each attribute, we can plot the part worths:

1. βDropbox is 0.4, βGoogle is 0 and βMicrosoft is 0.2

1. βPrice_6200 is 1.7, βPrice_9000 is -2 and βPrice_6500 is 0. (We will add the + lowest β to each value. We did not do this earlier because the lowest β in previous value is 0, which won’t make any difference.)

So the updated values will become βPrice_6200 is 3.7, βPrice_9000 is 0 and βPrice_6500 is 2.

Interpretation:

• End Users prefer Google over Dropbox and Microsoft for cloud storage.
• The utility decreases as price increases.

Same way we can calculate the part worth for each attribute and plot graph to understand the importance of each attribute level.

Step 5 – Calculate the importance of each attribute

The attribute’s importance is normalized to ascertain its importance relative to other attributes. To calculate this, we would find the range for each attribute.

Range = Max β – Min β value

• Range of Brand: 0.4 - 0 = 0.4
• Range of Price: 1.7 -(-2) = 3.7
• Range of Storage: 1.1-0 = 1.1
• Range of LFT: 0 + 0.8 = 0.8
• Range of Recovery Period: 0 + 0.5 = 0.5

Total = 6.5

Now, we can calculate the importance of each attribute.

• Importance of Brand = (0.4/6.5) * 100 = 6%
• Importance of Price: 57%
• Importance of Storage: 17%
• Importance of LFT: 12%
• Importance of Recovery Period: 8%

So, as a product manager you now know that which attribute matters most to the customers and now the utility changes for customers when you increase or decrease the values.

If you have any comments, feedback, or requests, please feel free to connect with me on Twitter at @HighOnDataPro. And if you liked this post, don’t forget to share it with your network!