How To Use Your 2020 Data To Better Understand, Support and Delight Your Customers

Last week we discussed building the ultimate data collection process to place your business in the best possible position to grow. The more you can learn about your business, your customers and your targeted market, the better prepared you will be for the newly emerging algorithms, artificial intelligence support and insight.

For now, let’s look at the data we do have. Let’s set it up to answer questions that will allow us to better understand what is happening in our business and what we can do better to improve and gain market share.

Start with asking your ERP system to create the following spreadsheet for up to the last five years. It will be a big file, but full of data to help you and your team better understand what is actually happening in business.

I suggest the following 10 headers for each line item invoiced:

• Invoice Date
• Customer 
• Qty shipped
• Vendor
• Vendor Product Number
• Description
• Finish (if you can)
• Margin (if you can’t pull this information directly, you can create a simple formula with information below)
• Total line cost
• Total line sale
• Showroom salesperson

Please note here, only ask for the data if you are confident that the data in each of the cells is correct. If you had any past data integrity problems or consistent mis-entry issues, do not include those topics or time periods in your spreadsheet. As the saying goes; Garbage in, garbage out.

Once you have this data, make sure each cell in a column is formatted uniformly. If they are not, the data will be off when combined for a table or report.

Find your best and brightest spreadsheet expert and ask them to set up a pivot table and pivot chart with this data. If you cannot find such a person, ask your accountant, myself or YouTube. Click here for a simple overview of pivot tables to get you started.

From this data you will be able to ask the following questions:

• What am I actually selling by: 
• Vendor
• Finish
• Product category
• Are my customers:
• Purchasing everything they need from me?
• At what margin?
• What product categories that I sell that they are not buying from me? 
• What are my salespeople selling:
• By brand
• By function
• By finish
• What are they not selling?
• What are my margins by:
• Brand
• Customer
• Vendor
• Salesperson
• Product category

Notice that I have included questions related to product categories even though that is not one of the spreadsheet headers. We do not have exact information here, but we know that most DPH brands are primarily focused on one product category. Some companies focus on cabinet hardware, others on plumbing fixtures and others on kitchen sinks. It is not a perfect metric, but it will offer you added insight into what your customers are buying/not buying. This filter will help you identify which product categories and vendors are reaching your margin goals, opportunities to sell more product categories to your good accounts, and if your salespeople are remembering to sell all the products you offer.

Once you have these answers, and any other questions you deem important to your business, you can focus on the opportunities you see that offer the largest upside and are the easiest to address.

After you set this up, you can run the data output and create your pivot table reports at the end of each month. You will now have actual, black and white data to allow you and your team see what is actually going on and plot a course to improve sales, margins and team performance. Not to mention getting the right products on display and building a more dynamic inventory.