As a finance executive with 15 years of experience in partnering with departments and divisions, Devon Coleman has used financial analysis/modeling, negotiation, communication, and software development skills to help companies and divisions achieve various business goals. Devon has held high-profile positions in finance and software development at Disney and CUNA Mutual Group. He has an MBA in Finance and International Business from UW-Madison and a BS in Finance and Accounting from UNC-Greensboro. 

Given Devon’s extensive experience, we asked for his input on master product hierarchies, a vital consideration in today’s complex data landscape. In this blog, Devon explains what they are, why they matter – and the dangers of NOT using them in your organization. Here are his insights: 

Q: What exactly is a master product hierarchy?   

A: A master product hierarchy is a system whereby an organization “defines” each of its products/services and how they relate (or roll up into) other products/services. By establishing such a system, your organization can more effectively use corporate data (mining, analyzing, consolidating) across a variety of existing applications. 

So what does it mean to “define” products? Here’s a quick example of an auto insurance contract. That “product” (the contract) rolls up into another product, say, a “collision policy.” That collision policy rolls up into the parent category of “auto insurance,” which rolls up into “personal insurance” (along with products like auto policies, home policies, boat policies, plane policies, and so on). “Personal insurance” then rolls up into “total insurance products” (along with business insurance, liability insurance, warranties); and so on and so forth. As such, a master product hierarchy clearly establishes each product and how it relates to other products. 

Q: Why is it so important to define products? 

A:  Defining each product and family of products, and how they relate to other products, yields critical meta data that helps data analysts reveal key insights in a way that other systems cannot do alone. For example, in keeping with our auto insurance example, it would help answer questions such as: 

• Is collision more effective as part of a bundle, or standalone?
• How does our auto insurance contract directly compare to a competitor’s contract?
• For which geographic areas is this product most profitable -- and how does the product offering need to change accordingly with say, different insurance riders? 

Additionally, a master product hierarchy creates a common denominator among a number of other existing systems. Here are some common ones, just to name a few: 

• CPM systems (Workday)
• ERP systems (SAP, Oracle)
• BI systems (MicroStrategy)
• Multi-dimensional applications (HOST, Anaplan, Essbase)
• Home grown analytical relational databases (MS Sequel Server, PostgresSQL) 

While these applications are valuable tools, the data that’s pulled from them lacks the common intersections and relational data that a master product hierarchy provides. (Simply put, they may not provide an apples-to-apples comparison.) This creates problems in linking a product sales unit in one system to revenues, costs and profitability of that same product in another system. 

To demonstrate, let’s dive deeper into our insurance example above and explore how it relates to profitability in specific geographic areas. Let’s say your company is interested in determining profitability of an insurance policy in California. With a data analytics tools, you may add up the policies in that region to determine revenue; then come up with a blended average of your costs and apply it to everybody (in every state) to refine that number. In actuality, this is NOT a true measure of profitability – because that policy contains a rider in California that makes it more expensive and less profitable then that same policy in, for example, Boston. Such inaccuracies have much larger implications, as companies use that data to drive sales efforts, promotions and marketing initiatives. With a master product hierarchy, on the other hand, you could quickly take into account the geographic costs and implications of that rider, versus looking at a blended national average. 

Q: What are the main concerns in NOT using a master product hierarchy? 

• Flawed aggregations and consolidations. As we learned from the insurance example, when a master product hierarchy is not in place, departments and divisions have different definitions on what a product or product roll-up entails – this causes issues when outside manipulations or adjustments are needed within a service or business line. In such cases, reconciliations or variance analyses are cumbersome as the product nuances between departments/divisions need to be considered to ensure apples-to-apples comparisons. 

• Tedious and time-consuming processes. Without a product hierarchy, data mining becomes extremely tedious and time-consuming. Data analysts have to construct and map elaborate tables to align data from Sales, ERP, Finance, and other systems, then bring that data into a BI/statistical or CPM system to crunch the numbers. This not only puts an enormous strain on your team (who is doing much of this work “on the side”), but it creates more room for errors and risk. 

• Insubstantial comparative analyses. Comparisons across systems become futile if those systems do not share a common definition of product and hierarchy. Think about a basic sales volume analysis: how can a sales volume analysis be used to determine sales-related cost or revenue per sale unit if sales, cost, and revenue do not have the same definition of products consolidating into a business line? 

Q: When is the best time to implement a master product hierarchy? 

A: Well, this question is probably best answered by explaining when the WORST time is. Often times, the need for a CPM or ERP system (learn more about Corporate Performance Management tools) is what triggers the need for a master product hierarchy. If that hierarchy doesn’t exist, there becomes a sudden and immediate rush to create one. I’ve seen it happen many times: organizations quickly scramble to build the two systems in tandem – which diminishes the effectiveness of both. 

For one, this scenario doesn’t give your company enough time to establish the correct processes and resources to manage your hierarchy. Additionally, it explodes the timelines of your original initiative (that is, the CPM build), adding anywhere from two weeks to months to a project’s timeline. This “fire drill” scenario increases your costs as the original project is interrupted and drawn out – and in some cases, it means the hierarchy tables map well to the system that it’s built alongside of; but cannot be leveraged by other existing systems. All of these factors undermine the effectiveness of your master product hierarchy – and translate to time and money that is not as well spent.  All in all, the best time to integrate a master product hierarchy is when you recognize the need for more accurate meta data; or when you’re first starting to recognize the need to upgrade to a CPM or ERP system. 

Q: Can you address some of the big-picture costs of not using a hierarchy? 

Again, I’ll use an example here to explain. Let’s take a company that is collecting data by measuring online clicks. They use those clicks to drive SEO efforts and costs. At the end of the day, all those clicks lead to a product.  But those clicks are measured in, for instance, Google Analytics – while your related revenue is measured Sales Force. Without a common product definition across two systems, it’s difficult and time-consuming to tie those clicks to revenue. That “matching” of data takes considerable time and manual effort – and means you’re slow to apply those learnings or trends in the greater marketplace. This opens the door for your competitors, who may be able to respond quickly and efficiently. 

Q: What advice would you provide to companies who are thinking about implementing a master product hierarchy? 

A: Starting high level, I’d note the following: In today’s data-driven world, companies increasingly look to their data to drive key decisions. While a master product hierarchy isn’t the be-all, end-all solution to your needs, it truly does improve the value of that data – and in turn, the decisions that result from it. 

As for specifics? Here are a few key take-aways to keep in mind: 

• Avoid the data fire drill. Again, I’ll emphasize the importance of taking time to build it right. You should recognize the stand-alone value for a master product hierarchy – and avoid making it an add-on to another system build. 

• Dedicate a resource. As you build your master product hierarchy, have a permanent, dedicated resource (a DBA) to manage and oversee it. Your hierarchy is not a “set it and forget it” system: once your tables are set up, they require ongoing maintenance and development. Additionally, keep your DBA actively involved in any discussions around new systems or analytical processes that your company is considering, as those decisions may impact your hierarchy. 

• Involve multiple departments. Particularly in the creation process, it’s critical to get input from cross-functional teams. Key leaders from those teams should provide insight on how product and meta data are used across the company in operational, management reporting, and regulatory reporting environments, as this will impact product your “definitions.” 

Is your organization experiencing inefficiencies with multiple data sources? Do you want to mine your data to yield the most valuable results in the corporate marketplace? Talk to Devon or one of 8020 Consulting’s expert consultants. Call us at (855) 367 8020 to set up a consultation.