Data Harmonization for CPG Insights

Data harmonization combines data from various file formats and naming conventions into a single, comprehensive data view. For CPGs, smart analytics start with data harmonization – and customization.

For a true view of your business performance, you should harmonize your data – from ecommerce to brick-and-mortar and specialty channels.

Our friends at TABS Analytics show us how their QuickTABS and Account Insights tools enable prompt, visual insights for CPG categories, brands, and companies.

Watch to Learn:

• Why ecommerce and in-store data integration is critical to understanding business
• How to answer important category and brand questions for more effective strategies
• TABS Analytics simplifies and improves CPG analytics – which translates to your competitive advantage

Ecommerce Integration Example: QuickTABS

PET CARE CATEGORY

It’s a great time to be in the pet care business; pandemic-induced pet adoptions skyrocketed and we’re all staying home more, spending that time with our pets!

Total US xAOC – the class of trade comprised by food, drug, mass, club, dollar stores, and military commissaries – account for just 1/3 of the total sales that we track. Ecommerce spans a significant share of the category with +20% of total sales.

Ecommerce is a major player in this category, and the channel is growing in importance. Its strong, double-digit growth is driving the total category into double-digit dollar returns versus a year ago.

(video time: 5:20)

In our example of data harmonization in the pet category, we’ve included 26 market breaks – including etailers like Amazon, Chewy, Target, Instacart, and a US ecommerce market total. If you work with ecommerce vendors (1010data, ClearCut Analytics, One Click Retail) you can harmonize your data with our responsive systems and knowledge.

QuickTABS DEMO

NOTE: The brands in the demo have been masked with generic terms.

(video time: 6:52)

QuickTABS is an excel-based CPG analytics tool. Once it’s downloaded, you can use it anytime – anywhere. It’s a pivot table… on steroids. QuickTABS includes a number of prepopulated reports, each with its own charting capabilities.

Adam Gray of TABS Analytics walks us through the QuickTABS interface.

Let’s take the current view (image above) – $ Sales – 52 Week.

Immediately, you see the value of ecommerce data integration. The ecomm data has been harmonized with xAOC, vet channel data, and pet data.

So, this is a lot of information…

Now what?

Let’s ask (and answer) these questions:

1. How big is the category in dollar sales?
2. What does the split look like between ecommerce and brick-and-mortar?
3. How do growth rates compare across channels?
4. Which channel is contributing the greatest share of growth on an absolute dollar basis?

This chart (below) will provide answers:

• The current ecommerce dollar share is 21.5%… which is more interesting since last year’s ecomm dollar share was 15.6%
• Sales between brick-and-mortar and ecommerce is split 80/20 in the latest year
• The column $ % Chg YAG shows that ecommerce contributes nearly 60% total growth – or 3x its fair share
• The smallest share of category business goes to the pet specialty channel (PetCo, PetSmart)
• US xAOC is the largest channel in the category, and the 2nd largest contributor after ecommerce
• The vet channel is the second largest channel, with a 1/4 of category sales (who knew that vets sell more pet treats and treatments than the pet channel itself!)

Now, share your insights! Simply click the Graph Summary button in the toolbar to automatically create data visualizations (like the charts below).

With a clear sense of annual category size and channel breakdown, you’re ready to review the trends across latest year, quarter, and month.

Now, let’s change the view to the Dollar Sales Momentum Report 🌟

(video time: 11:27)

With this report, channel insights visually indicate where sales are accelerating or decelerating over time (chart below).

Sales Momentum Chart • Dollar Percent Change vs. YAG

From this visual view, you realize that ecommerce is the growth leader far and away across periods. Even though growth slightly declined in the last 4-weeks, its still up 54% (or nearly 3x category growth in the US).

Another great discovery: The vet channel is growing steadily year-over-year.

How Will Your Company Use These Insights?

⚙️ Sales: Make the most of the category growth channels (especially ecommerce). Concentrate on which accounts are driving growth and where opportunities exist.

⚙️ Trade Finance: Drive efficient expenditure to support growth in the right channels.

⚙️ Marketing: Devote marketing strategy and budget to channels with momentum. Dig into what specific items and product trends are advancing to inform new product development.

⚙️ C-Suite: Check that the team is aware of and understands what can happen to take advantage of market trends.

Let’s keep digging into the harmonized information!

🌟 Where is the online growth is coming from — Amazon, Walmart, Target? How do specialty websites play into category growth?

(video time: 14:39)

Amazon is the top etailer. As you can see, Amazon is broken into 5 different segments:

1. Direct
2. Market
3. Subscription 1P
4. Subscription 3P
5. Fresh Orders

Add these 5 Amazon segment shares up… they are +40% of the pet care ecommerce business.

Now that you see the key online retailers in this channel, it’s time to analyze manufacturer and brand growth.

(video time: 16:16)

The best manufacturer – outside of the ‘remaining manufacturer’ line – is Best Pet Co with 12% of category dollar sales (see chart below).

🌟 What is Best Pet Co’s biggest brand? How are the brands are trending across periods?

Click back into the Sales Momentum view for the answers:

Sales Momentum Chart • Dollar Percent Change vs. YAG

Right away, you recognize that…

• DAISY represents Best Pet Co as its #1 brand – making up 6.9% of dollar share and almost 60% of the manufacturer’s total sales
• Some brands are decelerating across periods (look at FLUFFY and BOWIE)
• Some brands are picking up momentum across periods (look at SPARKY and PENNY)

(video time: 20:45)

This tool enables so much exploration. It simply integrates your ecommerce business with brick-and-mortar or specialty channels data. Analyze your growth drivers, productivity, and item rankings. QuickTABS harmonizes multiple sales channels from different data sources provides a straightforward, efficient way to track your whole business in one place.

(video time: 21:30)

If you aren’t already, watch the data harmonization insights in action:

Ecommerce Integration Example: Account Insights

The CPG Account Insights tool is a custom data dashboard that integrates retailer-specific POS data, EDI feeds, shipment, financial forecasts, and financial data. This system is a custom data integration strategy that provides extensive insights for CPGs.

Within the tool, your entire organization can see one view – for performance insights at the total level, weekly level, channel level, by category, and by item performance.

Account Insights delivers harmonized data from standard and non-traditional sources and the ability to view account and channel-level detail across accounts.

ACCOUNT INSIGHTS DEMO

NOTE: Data and brands has been masked for the demo. The data is not completely representative of the beauty business, it’s representative of how we can capture data within the system.

The Account Insights tool has the ability to provide row level security. That means if your WalMart team should only see WalMart data, that can be enabled. We know that everyone doesn’t need access to every accounts, so the tool is set up to provide that security within different levels by users within your organization.

For CPGs, the benefit of this tool is that granular level of data.

You can leverage all of the data available from retailer portals; even if its only available in a flat spreadsheet, we can take that information and harmonize it! If you’re buying Target data from Nielsen, you’re seeing the complete, single view of Target. But, within Account Insights, you’re able to look at Target brick-and-mortar, ecommerce, and shipped from fulfillment center and what’s shipped to store. Plus, look at trends by state and region.

(video time: 29:49)

Through the Account Insights CPG reporting platform you can:

• Create reports which automatically update every time the data is refreshed
• Review business performance by channel, brand, subsegment
• Align customer hierarchy from your shipment data to the hierarchy you’re using for POS data
• View information by channel, customer, units, dollars, time periods
  • Custom time period reviews are a great way to look at quarter-over-quarter performance and/or planogram-over-planogram performance
• Learn about the top items at a specific retailer
• See what’s driving performance and growth

Annette Gray of TABS Analytics walks us through the Account Insights interface.

(video time: 33:25)

What’s Driving Performance?

First, look at distribution and focus on brick and mortar so you can see the store count. This is the Momentum & Decomposition report.

You see all individual brick-and-mortar retailers – in this example (image above) – you can learn the key drivers for the overall business.

You can see individual retailers in one view – Ulta, Target, Walgreen, CVS – or focus on one – let’s click into Target.

I can see all key metrics to know what’s happening over the last 62-, 24-, 12-, and 4-weeks. Looking at the chart, you quickly realize:

• Store count was flat
• Pricing was up
• Units per door per week were down
• I see these are consistent trends

Now, you can go deeper into the data hierarchy to see what categories are driving those metrics.

(video time: 34:43)

The tools works very quickly. It drills down into key insights so everyone at your company can learn the drivers to business from the same lens. Its not dependent on what the WalMart team says is the skincare SKU. All of the data – from all of your retailers – is harmonized in one single source.

(video time: 35:40)

Now, let’s check out the Ship to Sales report.

Say you want to look at and identify new items.

Track shipment to sales on particular items or categories of products.

Choose the timing to see what actual performance is and how much volume has been shipped in by category down to a specific SKU down to how much has been sold through at any particular timeframe.

Other reports available from Account Insights:

• Base vs. Incremental – Calculate baselines
• Weekly Sales Detail – See difference between a retailer’s in-store and ecommerce performance
• Interactive Map – Review individual trends for a brand or retailer (by state, by region)

Thanks to Account Insights, CPGs can take the guesswork out of POS data. With all your data in one place, everyone within your organization can finally be on the same page. Cross-customer analysis and benchmarking will become commonplace.