How to Use Pivot Tables
by Leah DeKok •
It’s about to get real nerdy. Like most digital marketers and paid media managers, I get extremely excited about three things… #1 Data, #2 Excel and #3 Anything that helps create efficiency. Lucky for you, today we are going to talk about a program in Excel that encompasses all three… Pivot Tables.
What is a pivot table? For those individuals who are not familiar, let us start with the basics. A pivot table is a feature in Excel (going back to 1986) that summarizes and slices large amounts of data, creating customized tables based on your data set. It allows you to summarize multiple columns or rows of data without actually making any changes to the original data set or worrying about creating any formulas. Pivot tables allow you to quickly and easily sum, count or average, all while sorting and filtering. They also help to eliminate user error. Sounds magical right?
An Excel pivot table can help to answer basic questions like:
- How much did I spend last week?
- What products generated the most revenue?
- What was my return on ad spend (ROAS) last month?
- How have my sales varied week over week or month to month?
- How many products were sold within a specific price range?
The possibilities are endless. We will walk through how to create an excel pivot table, the best uses for digital marketers and a few basic tips and tricks.
Creating a Pivot Table
Before you begin, your data set must be complete. This means there can be no blank rows or columns. You will also want to make sure each column has an accurate header. Once the data set is ready, you can start by using the Recommended Pivot Table or creating one from scratch.
According to Microsoft, a Recommended Pivot Table “determines a meaningful layout by matching the data with the most suitable areas in the PivotTable.” This can help give you a good framework to build upon and test different variations. To use this feature, follow these steps:
- Highlight your entire data set
- Go to Insert > Tables > Recommended PivotTable
- Select the table that most closely represents what you are looking for, keeping in mind any of the fields can later be edited
If you prefer to start from scratch or have something particular in mind for your pivot chart, below are the steps to set up a table manually:
- Highlight your entire data set
- Go to Insert > Tables > PivotTable
- Click OK
The table will then be created on a new tab. From there, select which items you want to be your rows and drag/drop that item into your ‘rows’ field. You will then do the same for any values and filters you would like to see. Play around with different layouts and options when you are ready to summarize values. Once you have decided on the best layout, you can apply filters, sort and fully customize the excel table.
Use #1 = Analyzing Data
The most basic and common use of pivot tables is for analyzing large sets of data. Pivot tables offer a flexible way for you to extract significance from a large data set. This is also something digital marketers likely do on a daily basis. Below are a few basic examples of how a digital marketer may utilize a pivot table for different levels of data analysis:
Campaign Data – Exporting campaign level data for different engines, segments or timeframes is useful but can result in hundreds upon hundreds of rows making it difficult to gauge performance. Using a pivot table will allow you to summarize the data by the campaign, day, engine, etc. (dependent on what level of data you pull) while easily sorting and filtering your campaigns. Using the filter can also allow you to look at only Brand vs. NonBrand or different countries (all dependent on your campaign naming structure) and the options are endless.
Keywords – Keyword level data in paid search can also get extremely messy, as keywords are often launched across multiple campaigns for different locations and match types. Removing the [Exact] or +Broad Match Modifier (BMM) qualifiers will allow you to see performance as a whole for each keyword. Easily create pivot tables showing your top keywords by any metric you choose (clicks, impressions, revenue, ROAS). For some of my weekly reporting, I set up tables that will automatically list the top 25 keywords by clicks and revenue, providing a quick reference of performance at that level.
Ad Copy – Ad messaging plays a big role in PPC performance and is something that should be constantly tested and updated, whether it be the headline or description of an ad. Analyzing headline performance (H1 or H2) for your ad copy is extremely easy when pivoting the data. Drag and drop your headline into the row of your pivot table field and you will have a quick snapshot of all ads that contain the same headline. If you are wanting to view performance for the entire ad, insert an additional row into your original data set and concatenate (combine the contents of several cells) your H1, H2 and description. Another option is to add an extra column into the original data set to categorize the type of ad that is running. For example, I may categorize all of the ads based on the call-to-action (shop, learn more, see how) or the unique selling proposition (organic, low cost, durable). Once categorized, it allows me to pivot that data and see performance for each of those CTAs or USPs. Any learnings can then be applied across the entire account, and even other marketing channels.
Quality Score – Now that additional quality score metrics are available for both current and historical performance, you can create pivot tables to get the total count (rather than sum) of keywords ranking Above Average, Average and Below Average for each qualifier. This will allow you even more insight into why quality scores may be low or high and help you to monitor changes over time. To do this simply add the quality score indicator (for example: ad relevance) into both the rows field and the values area. If you have a high count of keywords that are below average for any of the indicators, optimizations should likely be made to either your ad copy or landing pages.
Use #2 = Merging Data
Another use is to marry or merge pivot table data from separate sources. For example, we regularly have to pull data from multiple engines (Google, Bing, Facebook, Pinterest, Analytics, etc). Pivot tables make it easy to drop all of your data into excel and display that data within one clean excel table, removing those duplicate rows.
Many clients need to do this on a regular basis. For example, they may want to look at front end metrics from Google Adwords (such as spend, clicks and impressions) while using backend data from Google Analytics (leads, conversions and revenue). Below is a step by step example of how to marry or merge the frontend and backend pivot table data from two different sources:
- Copy your frontend data into an excel spreadsheet
- Example: columns could be campaign, clicks, impressions, spend
- Create additional columns for your backend data and ad the necessary headers
- Example: conversions and revenue
- Since your initial data source does not have the backend data available, add zeros into each of new columns for the rows
- Copy your backend data below what has already been added
- Example: campaign name, conversions, revenue
- Fill in zeros for the frontend metrics of your backend data dump
Once you have confirmed that all cells now have a value, you can select the entire data set and choose to insert a pivot table. This will allow you to look at that campaign level data, while only showing 1 summarized line for each campaign (rather than 1 line with frontend and 1 line with backend data). This is a basic example but can be very useful if your data is segmented many different ways.
Use #3 = Recurring Reporting
Reporting can take up a lot of time for digital marketers and organizing or cleaning-up data is not everyone’s idea of a good time. If you do any type of reoccurring reporting, pivot tables can help to provide efficiency and eliminate user error. When setting up your pivot table, rather than selecting the data set, select an entire Excel spreadsheet as the data source. If set for an entire worksheet, once you add the new rows to the bottom and refresh, the pivot will automatically update your table to reflect the new rows of data.
This is extremely beneficial whether you are doing weekly, monthly or quarterly reporting. Having these pivot tables setup will allow you to continue to build upon your last report, by dropping in the latest week or month and simply clicking the Data tab > Refresh All.
To add even more efficiency, refresh the active pivot table with a keyboard shortcut of ALT + F5 or refresh all pivot tables with a keyboard shortcut using CTRL + ALT + F5.
Other Tips and Tricks
Number Formatting – Unfortunately the formatting of the source of the data may not always transfer to your pivot table. By right-clicking within the column of data in your pivot table, you can select ‘Number Format’ and make the necessary changes. Another option is to click on your item within the values area and select “Value Field Settings”. You then have the option to update the number format.
Calculated Fields – Rather than setting up multiple formulas, adding a calculated field is useful when trying to monitor things like CTR (click-through rate), CPC (cost per click) and ROAS (return on ad spend). To create one, select the pivot table, click on the Analyze tab and select Fields, Items & Sets > Calculated Field. You can then setup a calculated field using any of the metrics from your original data set.
Group Numbers into Ranges – Right click on the row, a grouping menu will pop up and you can select a ‘starting at’ and ‘ending at’ point. This type of option is useful for metrics like time of day. You may have hourly data available but want a general idea of when your campaigns perform the best. Consolidating the numbers into 3-4 different groups (night, early morning, midday, afternoon) may provide more learnings.
Adding Data Bars or Color Scales – Data bars and color scales can be added directly to a pivot table, this great way to provide a visual along with the data. Highlight the range in your table where you would like to add the data bars then go to the home tab, select Conditional Formatting and then choose either Data Bars or Color Scales.
Deleting Your Source Data – While this is not something I regularly do, if you need to reduce the size of your workbook this is a good option. Once the pivot table is created, right click on the sheet tab and select delete from the menu. The pivot table contains a cache of the data and will continue to work.
Style – Select the Design tab at the top of your document and scroll through the different colors and options or even create your own.
PivotCharts – Another newer feature is pivot charts. These work similarly to pivot tables, allow you to easily slice and filter data. The main difference is that these create visual graphs or charts, along with the table. These can be created in the same way as the pivot table, by using the recommended or creating manually. The advantage of pivot charts (vs. standard charts) is that you can more easily modify them.
Pivot tables are one of the most powerful tools in Excel and there is no better tool for data analysis due to the ease of use and the wide variety of applications. Hopefully this article helped you to expand your knowledge and find a new use for pivot tables in your day to day.
