You are currently viewing The Simple Guide to Using Pivot Tables to Understand Marketing Data

The Simple Guide to Using Pivot Tables to Understand Marketing Data

Does the word โ€œpivot tableโ€ make you want to scream?

You arenโ€™t alone.

But, if you arenโ€™t using pivot tables, you could be missing out on valuable insights about the effectiveness of your SEO, social media, or content marketing campaigns.

Data is the most powerful resource on the planet right now โ€” some are even calling it the โ€œnew oil.โ€ But, are you making the most of your marketing data?

If you already have a good grasp of the basics of Excel or Google Sheets, then you have a head start.

But Iโ€™m here to take you beyond the basic functions like SUM, AVG, or adding new rows.

You should be using Microsoft Excel to analyze and organizeย data from your marketing efforts.

Why? Because itโ€™s one of the most underutilized marketing assets out there.

pivot tables for analyzing data in marketing infographic

Itโ€™s safe to say that you probably know how to input numbers, words, dollar amounts, and other figures into the rows and columns of an Excel spreadsheet.

If thatโ€™s all youโ€™re using Excel for, youโ€™re not utilizing the software to its full potential.

Why?

Itโ€™s tough to analyze large data sets without the help of visual aids.

This is where you can use a pivot table to make your life easier.

What is a Pivot Table, Anyway?

A pivot table is an interactive table that allows you to sort and display data based on filters.

For example, say you have a massive Excel document of all your customer complaints about your large e-commerce business. Itโ€™s a lot of data โ€” but it doesnโ€™t actually tell you anything.

A pivot table could display averages about complaints by category, location, date purchased, and any other fields that you have.

But thatโ€™s just the basics. These fairly simple tables can do a lot more.

You can also use them to compare similar data and figures from different perspectives.

Despite their reputation, pivot tables are not as complicated as you might think. At their core, pivot tables help:

  • Summarize large data sets
  • Analyze large data sets
  • Explore data insights
  • Present those insights in an easy-to-understand format.

These are all crucial elements in your marketing efforts.

How can you tell if your efforts are successful or profitable without crunching the numbers? You canโ€™t.

Thatโ€™s where pivot tables come into play.

How to Create a Pivot Tableย 

Pivot tables are fantastic tools for analyzing large amounts of data.

Iโ€™ll show you how to create them โ€” and how to analyze your marketing data effectively.

Step 1: Find Your Source Data

Before you can make a pivot table, you need to get all your information organized in an Excel spreadsheet.

So, the first step is to figure out what the source of your data is.

Hereโ€™s a breakdown of how companies are spending their digital marketingย budgets.

pivot table guide how CMOs spend marketing budget 2020

Each of those channels can then be broken down into more categories โ€” digital ad spend might also include social, content marketing, and other digital efforts.

Itโ€™s a lot of data โ€” so making sure itโ€™s organized is the first step.

You might also have your data on another platform or software thatโ€™s not in Excel.

If youโ€™ve been manually inputting the information into an Excel spreadsheet, chances are itโ€™s already somewhat organized.

However, if your data is somewhere else, like an online marketing tool, you need to get those numbers on a new spreadsheet.

There are a couple of ways you can make this happen.

The first is by manually typing everything. I do not recommend this.

Sure, if youโ€™ve been manually putting figures into an Excel sheet daily or weekly over a long period of time, itโ€™s not the worst idea.

With that said, however, manually typing a yearโ€™s worth of data into Excel is not practical (or even realistic in some instances).

Depending on how much data you have, this process could take hours, weeks, or even months. Thatโ€™s definitely not an efficient use of your time.

If possible, I recommend importing your data.

Step 2: Import Data Into Excel

To save some time and to maximize productivity, you can import data to an Excel spreadsheet. Hereโ€™s how:

Open a new Excel spreadsheet, then select the โ€œDataโ€ tab at the top.

Select the import option that fits your data. Your data is probably in a CSV or HTML file format, but all of the choices are a possibility depending on the platform or software youโ€™re using.

For example, if you are uploading data from a CSV file, youโ€™d select โ€œFrom Text.โ€

upload data for pivot table

You can also select โ€œFrom other sourcesโ€ if you need to upload data from an SQL server or other sources.

Step 3: Clean Up Your Imported Dataย 

While Excel is extremely useful, itโ€™s not always flawless.

Itโ€™s possible that your import process didnโ€™t go 100-percent perfect.

Take some time to go through your information and fine-tune any incorrect fields.

For example, columns that are supposed to be a currency, such as USD, may appear as regular numbers.

Thatโ€™s an easy fix.

Just highlight the cells and select your currency under the โ€œNumberโ€ section on the โ€œHomeโ€ tab.

pivot table data options

There could be some other minor import problems, but itโ€™s nothing you canโ€™t sort out quickly.

If youโ€™re struggling, Microsoft has a troubleshooting guideย to walk you through some common issues.

Itโ€™s important to get all your data organized before you attempt to create a pivot table.

For the most part, you may just need to delete some empty rows, columns, or blank cells.

Step 4: Create a Pivot Tableย ย 

Now that youโ€™ve imported all your information into Excel, you can create a pivot table to organize and compare the data.

Right now, your spreadsheet contains raw data.

To take things a step further, you can create a pivot table to analyze the information.

Hereโ€™s an example containing some data about a hypothetical sales team.

Select the Data You Want to Analyzeย 

create a pivot table select data

This raw data is great information, but what can you do with it? Not much.

You donโ€™t know the total order amount for each salesperson, and you donโ€™t know the total for all the orders.

If you want to analyze your marketing campaign, these are important pieces of information.

First, narrow down the data.

You donโ€™t need to select the entire spreadsheet to create a pivot table.

For example, the screenshot above may contain additional columns for the date of each sale or the customerโ€™s home address.

However, thatโ€™s not necessary to compare the sales team and their monthly order amounts.

Just go with the important information.

Highlight all of the cells containing the data, including the headers for each column.

Choose โ€œPivot Tableโ€ from the โ€œInsertโ€ Tab

add pivot table in excel

This will create the table.

ย Select the Data You Want to Add to Your Tableย 

add pivot table data

By default, your pivot table will open in a new worksheet tab.

I recommend leaving it that way.

It can get messy and tough to read if you put your pivot table on the existing worksheet with all your data.

If you need to refer to your datasheet, itโ€™s easy to switch back and forth between tabs.

Since youโ€™ve previously highlighted the information in the spreadsheet, the โ€œSelect a table or rangeโ€ option is already marked.

Open the New Worksheet Tab

pivot table fields

The new worksheet will automatically be labeled โ€œSheet 2,โ€ but you can rename this to anything that helps you stay organized.

Notice the top right section of the screen.

The pivot table fields section contains all the headers we highlighted back in the first step.

Remember how I said to only select the important information from your data source?

Well, now you have the option to narrow those choices down even more.

Choose the Fields for Your Pivot Tableย 

pivot table fields

For this example, weโ€™ll select the โ€œSalespersonโ€ and โ€œOrder Amountโ€ fields.

Drag the Fields to the Desired Area

drag fields to pivot table

You can put a selected field into one of four areas.

  • Filters
  • Columns
  • Rows
  • Values

For this example, I put the โ€œSalespersonโ€ field under โ€œRowsโ€ and the โ€œSum of Order Amountโ€ under the โ€œValuesโ€ section.

Change the Value Field

pivot table field setting screenshot

If you want to summarize the value field by something other than the sum, you have some options.

Just click the value field settings, and you can adjust the calculation.

For this example, the only other relevant option would probably be an average.

However, Iโ€™ll leave it as the sum for now.

View Your New Pivot Table

pivot table row labels screenshot

Take a minute to assess what weโ€™ve accomplished here.

Remember how our data looked back in the first step? If not, take a second and scroll back up.

Itโ€™s much easier to analyze the information now.

Each salesperson is only listed once, and their total sales are calculated for you.

You can even see the grand total from your entire sales force.

We turned the raw data from the initial spreadsheet into an organized pivot table.

Pivot the Data

pivot table pivot screenshot

Letโ€™s say you want to see the total order amounts each month.

You donโ€™t need to start from scratch.

This information is easily attainable with a few simple clicks.

Just uncheck the salesperson box and click on the month field instead.

Make sure the โ€œMonthโ€ field appears in the โ€œRowsโ€ section on the bottom right of your screen.

Thatโ€™s it.

The data is calculated for you in seconds.

Continue to mix and match which boxes you want checked off depending on the information youโ€™re trying to analyze.

You can also take advantage of other analysis toolsย while youโ€™re evaluating the data.

Step 5: Analyze Your Results

Now that youโ€™re an expert in creating pivot tables, itโ€™s time to apply that information to your business.

What should you be looking for?

All your data should help you scale and measure the results of your marketing campaign. For example, you might ask questions like:

  • Is it effective, or is it burning a hole in your companyโ€™s checking account?
  • All your data should come back to the user experience.
  • Where do your customers hear about you?
  • Whatโ€™s driving sales?
  • Which marketing efforts are translating to high-conversion rates?

Try to use the results from your data to maximize your conversion rate optimization.

One of the best ways to analyze your marketing data is with visual aids.

Take a look at this example from Chandoo. These graphics were made with a pivot table and slicers.

pivot table example

These make it much easier to understand data trends, right?

If youโ€™re presenting the information in your pivot table to your sales force, marketing team, or accounting department, you can use visuals like these to convey your message.

Sure, the pivot table is great. Weโ€™ve already established that.

But you can take the information from your table one step further if youโ€™re giving a presentation.

How? With a pivot chart.

Step 6: Create a Pivot Chart

To keep things simple, Iโ€™ll continue with the same hypothetical data from the example we used earlier.

Click any Cell in Your Pivot Table

pivot table create a pivot chart

It doesnโ€™t matter if itโ€™s a word, number, total, or header.

Just make sure a cell within the table is highlighted.

Go to the โ€œAnalyzeโ€ tab in the Top Ribbon

create a pivot chart

Then click on the PivotChart icon.

Choose a Chart From the Listย 

pivot table create a chart example

Iโ€™m selecting a clustered column for this data set. Itโ€™s a good visual representation of the information over three months.

When youโ€™re doing this, feel free to choose any option.

With that in mind, itโ€™s best to keep it simple, especially if youโ€™re giving a presentation.

Analyze the Chart

pivot table guide how to create a pivot chart

Itโ€™s a great visual aid to measure performance and compare it to your marketing strategy.

Letโ€™s dive a little deeper into this information.

Michael Brennan had an outstanding month of January.

In fact, it was the highest-performing month out of every sales team member in the data set.

What caused him to drop off so drastically over the next two months?

It could be based on several factors, but letโ€™s play out a couple of reasonable scenarios.

It could be a marketing issue. Compare this information to what changed in his region over these months.

Did you scale back your marketing efforts? Did you change your strategy?

You need to create an effective marketing strategyย and stick to the plan.

If you changed something on your e-commerce website, it could impact your data as well.

Hereโ€™s a video where I explain how to optimize your e-commerce product pages.

Maybe Michael lost motivation, and his numbers dropped as a result.

Motivated employees tend to work harder, which results in a higher customer satisfaction rate and better productivity.

If Michael has already met his maximum commission quota for the quarter, he wonโ€™t be as motivated to continue selling your product.

To fix this problem, you might give your employees uncapped commissions or find other ways to keep your staff motivated.

You need to motivate your personnel to keep your team tightly aligned.

Outside of financial motivation, here are the top reasonsย employees stay motivated, according to KMI Learning.

guide to pivot tables how to keep employees engaged infographic

What can we learn from this?

Sure, the numbers we used to create the pivot table, and pivot chart was hypothetical.

But they still speak volumes to scenarios you may encounter while analyzing your own data.

Itโ€™s not always a lack of advertising or error in the marketing department that impacts your sales numbers.

There are other external factors to take into consideration while you examine these figures.

Hereโ€™s something else to consider about your marketing data.

You need to have clear goals set, so you know what to measure and analyze.

Write down your marketing goals.

These are the steps you need to take to analyze your marketing data effectively.

  1. Set your goals and objects.
  2. Manage your data (using a pivot table).
  3. Analyze and report (with a pivot chart).

When you break it down into these three simple steps, itโ€™s really not that difficult.

Conclusion

Data is an essential component of a successful marketing campaign.

And itโ€™s underutilized by most businesses.

How can you effectively analyze your marketing data?

First, you need to get all your information organized in Microsoft Excel.

From here, you can easily create a pivot table that will turn your raw data into something you can analyze to evaluate your marketing campaign.

Then take this evaluation one step further with a pivot chart. Itโ€™s a simple process that only takes a few extra clicks.

Pivot charts arenโ€™t as complicated as they sound.

Now that youโ€™re familiar with making them and using them as a marketing tool, you can get started analyzing your data to better understand the effectiveness of your marketing plan.

Youโ€™ll have it done in minutes. Then, use these Excel hacks to take your marketing even further.

What data will you use to create your first pivot table? What might you learn about your marketing efforts?ย 

The post The Simple Guide to Using Pivot Tables to Understand Marketing Data appeared first on Neil Patel.