What a Master Data Sheet Is and Why Your Business Might Need One
As a Product Data Specialist and the Lead on the Customer Success Team here at Venzee, a common problem I see clients struggle with is product data management. At Venzee, we work to eliminate inefficiencies resulting from manual work in the digital supply chain. Venzee streamlines the way product information gets from manufacturers to retailers by turning product information from the source into consumer-ready product content and distributing it out to any and all desired retailer channels.
What do I mean by product content? In the world of retail, product content refers to everything you see in a product listing, including images, pricing, descriptions, lists of features, and more. As product listings get more complex to meet consumer demand for more information about the products they shop, meeting retailer requirements for uploading this content becomes more challenging for vendors and manufacturers. This is the challenge Venzee helps our users solve.
Brands need product data that’s clean, complete, and consistent.
At Venzee, preparing product content for distribution to retailers begins with collecting product content, or product data, from a vendor, manufacturer, supplier, or brand. Too often at this step, I find that clients are working with data that’s inconsistently formatted, incomplete, and disorganized across multiple spreadsheet files. In order to automatically transform original product content to content that meets retailer requirements, the source data must first be standardized. Once the product data is clean, consistent, and complete, the user can take advantage of automated workflows for data transformation and distribution that Venzee provides. Many users achieve clean product data by using a PIM (Product Information Management) software, but an alternative to investing in software that manages your product information, the cost for which ranges considerably, is to build and maintain a master data sheet. This method is especially beneficial to SMBs who don’t have the budget to invest in a PIM or vendors who manage a smaller number of SKUs.
Product submissions and the case for a master data sheet
Whenever vendors submit products to retailers, they must match that retailer’s specific formatting and attribute requirements. Not having a single source of truth for their product information becomes a major hurdle when preparing products to submit and leads to all sorts of problems downstream. Errors are harder to correct, submission processes take up more time than they should, and managing the mess eats into the time products could be live in front of customers on a retailer’s site, and this problem is amplified when vendors submit to multiple retail channels.
When I work with vendors who want to upload their products to retail marketplaces like Amazon, Wayfair, and Overstock, they don’t always have a product data file ready to pull the accurate information they need for product uploads and submissions. Too often, they need to dig through endless email chains, reconcile multiple versions of undated spreadsheets, and reach out to different departments to request separately managed data points, only to end up with inaccurate or incomplete information. Maintaining a master data sheet helps solve such problems. For these organizations, learning how to create and sustainably manage a master data sheet might be one of the most profitable lessons they could learn as their business grows.
When implemented well, master data management procedures will streamline any process that involves retrieving product data and, thus, prevent delays in getting products online and ready to sell. Though such a process change requires thought and resources to start, keeping a master data sheet means up-to-date product data can be easily accessed by anyone who needs it in an organization.
Consumer demand for content is growing and retailers are responding accordingly
Across ecommerce, providing rich product information for every SKU is essential for earning, enticing, and keeping online shoppers who--now more than ever--demand photos, bullet points, and detailed product specifications before making a buying decision. With the understanding that meeting consumer demand for information is crucial to survival, online retail giants (like Bed Bath & Beyond and Home Depot) are demanding more and more product data from their suppliers. This demand translates into increasingly complicated submission templates and more and more attribute requirements for vendors. The number of product data attributes required when adding new products to various ecommerce websites or retailer sites ranges anywhere from five to 80+ attributes, all of which must be completed with the retailer’s end consumer in mind. This may include a specific number of bulleted points, romance descriptions, key search terms, and categorizations. There may also be further content and formatting requirements for a given attribute, that restrict character counts, accepted characters, possible multiple choice values, etc. To top it off, these requirements may change on a weekly basis as retailers adapt requirements based on consumer feedback. Retail is transforming and retailers must be able to change rapidly to compete in the space, and so must vendors, which starts with changing how they’re managing product information.
Brands need to maintain centralized product data
The ability to syndicate enriched product data to multiple endpoints relies on a vendor’s knowledge of their own products and how well they communicate or transfer that data to their partners. Any business in the retail industry looking to scale will recognize the need to optimize data management procedures, which includes maintaining an impeccable master data sheet. In principle, a master data sheet is a spreadsheet that is a supplier’s single source of truth for all available product data relating to existing SKUs. A master data sheet is typically composed of information that’s been compiled from multiple files and/or sources. In the master data sheet spreadsheet, each body row contains one unique product or SKU. In the header, one product attribute or characteristic is assigned per column, so a SKU’s attribute data stretches horizontally across all columns. The resultant file contains values for price, dimensions, color, materials, and many more characteristics for every product, or SKU.
For vendors who don’t have a master data sheet from which to pull the required information, the process of getting required product data across to their retailer partners becomes a much more difficult process. Learning how to collect and maintain this data strategically can help tremendously in delivering content for high-converting, customer-facing product pages.
To keep up with multichannel, complex networks between vendors and retailers, a master data sheet must follow several guidelines:
- The master data sheet must be produced and updated with more flexibility relative to the top-down, linear processes of traditional data input in response to constantly changing retailer requirements.
- The master data sheet must include data points needed to satisfy all requirements across your endpoints or retail partners.
- The master data sheet must consist of clean, typo-free, standardized data that is easy to synthesize and convert uniformly to multiple different forms as required.
Benefits of a master data sheet:
- Reduce time wasted on tracking down information from different sources and reconciling conflicts in data resulting from poor version control
- Improve data accuracy and completeness
- Ease the product submission and upload process, decreasing time-to-market
- Help your business scale as improved data management procedures free up time for more productive activities
Building a master data sheet
Common problems with product data
1. Not keeping enough data points for each product
I have encountered many cases in which I am unable to meet a retailer’s requirements for even basic product information such as shipping method, dimensions, color, and retail price because the client’s data sheet lacks the information for those attributes. Although the client usually has the knowledge either memorized or stored separately, they have never thought to flesh out their basic price list by adding the information they will need across all their ecommerce endpoints. Having this compiled information on hand for each product upload saves much more time and prevents many more errors than filling in every separate form from scratch.
Even if data exists for these basic product attributes, clients still may omit basic data attributes simply because their organization has not created the columns to start recording it in the master data sheet. For example, some vendors selling items with multiple components or shipping cartons will not separately list the dimensions for each piece, even though their retailer partners require this information. So, instead of referring to one spreadsheet for the information they need, vendors find themselves scrolling through a separate sheet with shipping data, another with retailer-dependent prices, and yet another for product romance copies and different bullets.
The more data you record in your data sheet, the better for avoiding these extra steps. This way, you know all the information you need is in one organized place, and you have many options for converting your data for different criteria on different platforms.
2. Messy data
These days, most ecommerce platforms will accept bulk product submissions in the form of a formatted spreadsheet, with designated custom columns and regulated values that are read and validated by their software. Because this process is automated, consistency of the values within each column of your data sheet is essential for correct interpretation by the platform you are working with. Any differences in syntax, however small they seem, will be read as an entirely different value, which may increase the frequency of errors and stretch the time it takes to get products live on-site. Fixing the problem from the root involves making sure data is initially entered into the central source using a uniform protocol, rather than trying to fix multiple outputs from an error-filled file. This will make data much easier to work with when transforming and distributing to different destinations.
3. Storing multiple data points as one
Most retailers will request data in a form that designates separate columns for the smallest units of information. For example, rather than providing a single column for “Item Dimensions” to be recorded together, retailer setup sheets will primarily use separate columns:
- Item Height
- Item Length
- Item Width
If your source data sheet has dimensions combined in one cell for every SKU, you would need to split up the information in order to provide the specific bit of information for each column in the submission. But if you store these data points separately in the source data sheet, it would eliminate the work of splitting up the components for every submission. For any retailer that also requests the dimensions listed together in one cell, the separate columns can also be easily combined using a formula.
Similarly, for shipping carton dimensions:
- Shipping Height
- Shipping Width
- Shipping Depth
- Shipping Box 1 Height
- Shipping Box 1 Width
- Shipping Box 1 Depth
- Shipping Box 2 Height
- Shipping Box 2 Width
- Shipping Box 2 Depth
This goes not only for dimensions but for materials, which are often separated into:
- Material 1
- Material 2
- Material 3
- Frame Material
- Leg Material
- Top Material
- Upholstery Material
Some retailers also mandate multiple feature bullets, each in their own column, which will be much easier to provide if they are separated already in your master data, rather than copying and pasting parts of a long paragraph of text contained in one cell.
3 tips for building a master data sheet
For those who just want the basics of building and managing a central spreadsheet from scratch, here are some tips:
1. Include as many attributes as possible, both basic and those used only in certain cases
You may want to start off by gathering information for attributes that are generally required across all retailers, for example:
PC, model number, product dimensions, shipping dimensions, color, ship type, cost, retail, country of origin, product title, longer romance copy and/or multiple feature bullet points, materials, product type or category…
To get a sense of which additional attributes to include, you should answer the question: What fields are required by my retailer partners? These will be the fields that you frequently need in your product submissions; thus, either recording these systematically or adding additional columns into your existing basic data sheet will save you the most time.
One way to do this is to take the following steps:
- Review previous submissions or setup sheets for each of your retailer partners and note what requirements exist for each.
- Create a consolidated list of requirements across all endpoints.
- Add these as new columns in your master spreadsheet.
- Fill in these columns for each product.
You can also identify additional columns that are valuable to record during the process of preparing a submission:
- As you fill in a product upload sheet for a retailer, any time you find yourself needing to find information from outside your data sheet, add it as a new column in your data sheet.
- When you fill in a product upload sheet for another retailer, do the same, while evaluating different forms of storing this information that may satisfy both sets of requirements.
2. Check for standardization and consistency
To ensure that your data is standardized and free of errors, follow these steps:
- Proofread for typos. Eliminating errors in your source file will also prevent them from trickling down to every document created from here.
- Check for consistent formatting throughout. Do some price values include a dollar sign while others do not? Does your product description column include some values done in HTML format while others are in plain text?
- Maintain standardized content so multiple values within the dataset are easy to sort and convert in bulk. Consider the example below: the Material column is essentially listing the same information for all 3 SKUs, with each value being a slight variation. However, most retailers request Materials with limited multiple choice values, so these inconsistent values create extra work down the line when needing to change each variation (100% Polyester, polyester frame, and Poly ester) to the same value (“Polyester” option from the dropdown). Maybe not so difficult when considering a list of 3 SKUs, but when the catalog reaches 100, 1000, 10000 SKUs, the time saved by standardizing such values becomes obvious. This principle applies for the Product Type and Country of Origin columns as well; choose one standardized value for every attribute.
3. Split up data into the smallest chunks of information possible in each column
Instead of spending time extracting parts of data contained in one cell, splitting up the information into the smallest units possible will adhere much better to most retailer requirements. Small chunks allow you to be more flexible in the ways you can combine and convert information, and can be handy for synthesizing formula-based titles, dimensions descriptions, and extra bullet points.
One example can be seen below for dimensions. The first column contains general dimension data combined in a single cell. A better way to record this data is to split up each product dimension into its own column: length, width, and height. So 9”x2”x3” will read 9, 2, and 3 respectively in each column. 5 in by 5 in, 2 in thick will change to 5, 5, and 2 respectively. 5 round will convert to a length, width, and height of 5. Notice that when each discrete value is split up in its own cell, there is no need for the extra text and punctuation surrounding the numbers.
This principle can also be applied to products with several different dimensions (i.e. folded dimensions and extended dimensions should be separated into columns), materials lists, feature descriptions, etc.
For many vendors, rehauling their data process is an immense project that doesn’t happen overnight, but the long-term benefits are clear. No company aiming for ecommerce success can achieve significant expansion without a way to organize product data in a central repository. Investing the effort now in order to improve data quality and update data management procedures will have great impact on your organization. Among the clients I have worked with, those who have invested in improving the way they manage product information have made their product upload process significantly smoother and turned what was one of their most tedious and time-consuming processes into a much less daunting task.