Excel has been a popular topic lately. One popular feature of Excel is the answer to many woes marketers face.
With thousands of rows of conversion, click, keyword, and device type data, how can search marketing pros explore and summarize this data into a few concise lines?
Answer: Pivot tables.
Pivot tables can help organize massive amounts of data and pull out only what is needed.
Problem is, many digital marketers can't get a tight grip on this core skill. Where do the numbers go in this table to get the right answers? Guess and check is a total time drain.
This post will break down the basics of pivot tables. Here's what we'll cover:
- Pivot tables in plain English.
- Visual breakdown of pivot tables.
- Common examples of pivot tables in search marketing.
Pivot Tables in Plain English
Pivot tables help answer questions by organizing your data. Ultimately marketers are trying to answer specific questions, like "what keywords get the most clicks?"
Some thought narrows the focus to specific devices like computers. And exploration reveals that clicks might vary by city. That question can be answered in a pivot table.
Here's a quick four-step question outline that helps translate marketing questions into pivot tables:
|Question||Pivot Table Area||
|What am I trying to measure?||ROWS*||Keywords|
|How am I measuring it?||VALUES*||Clicks|
|Anything to focus on, or exclude?||FILTERS||Only Computers|
|Once I have this information, how would I like it broken out?||COLUMNS||Is it different by City?|
In PPC the building blocks are keywords and ads. For pivot tables the building blocks are VALUES and ROWS. It isn't necessary to add FILTERS or COLUMNS, but they're powerful when analyzing data and diving deeper.
Visually Break Down the Layout
Pictures say a thousand words. Let's start with the logical way the pivot table box is laid out.
In the field list area ROWS and VALUES on the bottom and FILTERS and COLUMNS on top. This aligns visually with the table itself, providing a cue of where the data should go.
Visualizing the logic behind the pivot table helps organize what goes where. As data is added and removed the table dynamics changes. Very useful, but potentially confusing.
How does the table react to the data moving?
The impact of changes are straightforward:
- ROWS make the table taller.
- COLUMNS make the table wider.
- VALUES also make the table wider.
- FILTERS make the table shorter and/or slimmer.
Common Data for Pivot Tables (Search Marketer Edition)
Now let's look at some common scenarios search marketers could put into each field to create a pivot table. (This should keep you from ending up with a table that's one row tall and 58 columns wide.)
|FIELD||Ex. #1||Ex. #2||Ex. #3||Ex. #4|
|ROWS||Keywords||Date||Quality Score||Ad Group
|FILTERS||Match Type||No Tablets||Brand Campaign||AdID Clicks > 10|
* Use Calculated Fields for Accuracy
When you take them apart, pivot tables aren't nearly as complicated as they seem. The incredible boost they'll give to your insights will have your colleagues clamoring to learn how to use them.
Digital marketers who master pivot tables, big data visualization, and other Excel beauties gain insights that help you save time, create better messaging, better targeting, and better ROI.
To Be Continued...
We've started with fundamentals of pivot tables, but will dive deeper over time for you experienced users. In coming months we can focus on calculated fields, techniques such as filter/hide, visualizing with pivot charts, sorting, and data types.
Please use the comments to share questions you have and any pivot table topics would you like me to cover. If you have the question, others do as well.
ClickZ Live San Francisco (Aug 11-14) will bring together the industry's leading online marketing practitioners to deliver 4 days of educational sessions and training workshops. From Data-Driven Marketing to Social, Mobile, Display, Search and Email, the comprehensive agenda will help you maximize your marketing efforts and ROI. Register today!