SEO News
Microsoft

Pivot Table Basics For Search Marketers

john-gagnon
by , Comments

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.

Pivot Table Questions

Here's a quick four-step question outline that helps translate marketing questions into pivot tables:

Question Pivot Table Area

Example

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.

Pivot Table Visual Breakdown

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?

Pivot Table Visual Breakdown

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
Ad Title
VALUES Conversions
CPA*
Clicks CTR*
CVR*
CTR*
FILTERS Match Type No Tablets Brand Campaign AdID Clicks > 10
COLUMNS   Campaign   Campaign with
most Clicks

* 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.


SES LondonOptimising Digital Marketing Campaigns with Search, Social and Analytics
At SES London (9-11 Feb) you'll get an overview of the latest tools, tips, and tactics in Paid, Owned, Earned, Integrated Media and Business Intelligence to streamline your marketing campaigns in 2015. Register by 31 October to take advantage of Early Bird Rates.

Recommend this story

comments powered by Disqus