Effective Link Building Service: In The Trenches: How To Excel At Excel For SEM Applications, Part 5: Master Pivot Tables

**How To Excel At Excel For SEM Applications, Part 5: Master Pivot
Tables**

**by Josh Dreller**

In the previous posts of this Excel series for Search Engine
Marketers (Part 1
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-1-19840>
, Part 2
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-2-20453>
Part 3
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-3-21435>
, Part 4
<http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-4-22119>
), I've shared various tips and tricks for the tool. However, today's
post is just going to cover pivot tables, as I feel they are such an
important piece for online marketers to not just grasp, but master.
Excel itself can be intimidating and pivot tables are doubly so. I
hope this will be the definitive guide to pivot tables for anyone in
this industry who has yet to feel one hundred percent comfortable with
this crucial Excel feature.

By the time you finish this post, you will be a Pivot Table champ (or
your money back! LOL.)

The first thing to understand is why you would need to put your data
in a pivot table. Well, not all data needs to be pivoted. However,
when you have a table of data that you're trying to analyze, pivot
tables allow you to quickly extract meaningful information by easily
changing columns, rows, and filters to get those useful insights.

Here's an example Google Adwords Placement/Keyword Performance Report
for a fictitious company called MapWiseTech:

Ten questions you might want the data to answer could include:

What keywords get the most impressions?
Which engines get the most clicks?
Which campaigns generated the most conversions?

Which ad groups had the highest click-thru Rates?
What was my average position for my top spending terms?
Which keywords spent more than $100?
Which keywords in my General Terms campaign spent the most yet
converting the least?
Which ad group had the lowest Cost Per Conversion?
How did Search perform vs. Content?
How did different match types affect my performance?

These are all very common questions that any paid search professional
has to answer in order to optimize their accounts. For those of you
who aren't consistently using pivot tables to collect this data, how
long would you say it would take you to answer these questions for
your accounts? Let me tell you, with pivot tables, these ten questions
will take you less than five minutes to answer. That's right. Less
than five minutes.

Are you ready to be a pivot table convert now?

Building a pivot table

Converting a regular data table into a pivot table is much easier
than you think. Simply highlight your table, then go to the INSERT
ribbon and choose Pivot Table. A Create PivotTable dialogue box will
open to confirm your data source and where you want the Pivot Table
generated. The default option is in a New Worksheet, but you can
choose the Existing Worksheet option and pick a cell to begin the
insertion.

That's it. You now have a pivot table from that source data. From
here, you just have to choose your columns, rows, and values from the
Field List. Notice you now have two extra ribbons for PivotTable
tools, Options and Design as well.

Drag/Drop fields to build reports you want to see

The first question was, What Keywords get the most impressions?.
This is child's play in a pivot table. Drag the Placement/Keyword
field into the Row Labels box and the Impressions field into the
Values box. Then, drag the Ad Distribution field into the Report
Filter box. The pivot table is built. The last thing to do is to
choose Search Only from the report filter (so you see keywords, not
placements) and click on the first impression value listed and click
the ZA down arrow to sort from largest to smallest.

I know you can say that you could have done the same thing by
filtering the source table and sorting the impression column. However,
let's go to the next level. What if you wanted to find out which ad
groups had the most impressions? An account with even just 10-30
campaigns might easily have 100-200 ad groups. You could tell me that
you could go back to Adwords and pull an ad group report and then
filter and sort. But that would take time. With the current pivot
table, just drag the Ad Group field into the Row Labels box and you
have the data you need.

It's important that you understand there is always a lot of
experimentation in pivot tables. Even for advanced users, trying out
new ways to drag and drop fields into the various row, column, filter,
and value boxes provide infinite ways to look at the data. It's
important for you not to get frustrated while experimenting with your
pivot table. It will take time to learn, but you may actually uncover
interesting ways of looking at the data that you've may have never
considered. So, go ahead, drag and drop fields until you find the data
you need.

Collapse/Expand fields

When you have multiple fields in the Row Labels box, they will be
grouped in the order which they appear. So, in the example below, I
now have both the Ad Groups and Keywords showing. If I just want to
see everything at the ad group level, I just select one of the ad
group cells and click Collapse Entire Field from the Options ribbon.

Normally, I may have a Row Label configuration such as: Engine ->
Campaign -> Ad Group -> Keyword. By using Collapse and Expand, you can
quickly go in and out of granularity. Each time you do, the Grand
Totals will change based on what's selected.

Why you should use the most granular data you can find

Understand that Pivot Tables will aggregate values if there's a
common connection between the data you're looking at. For example, the
pivot table knows that these 100 keywords are all in the Branded
Terms ad group. So, even though the source table is at the keyword
level, they all have Branded Terms listed as the ad group. The pivot
table will recognize that and aggregate them together. Therefore, a
best practice is always to get the most granular data into a pivot
table as it will be able to calculate the higher level groups as long
as these connections exist. You can even pull the source data at the
day level and group them into weeks, months, and years so you can see
that information quickly in your pivot table.

Calculated Fields

There's an inherent problem when you have calculated metrics already
in a source table. For SEM, the two big ones are CPC and Click Thru
Rate. You can't sum up these metrics together in a pivot table, right?
So, you have to use Excel's CalculatedFields feature.

HOW TO: Options ribbon -> Formulas -> Calculated Fields. A dialogue
box opens up where you can name a new field and then create the
formula to generate this new calculated field. In the example below,
I've created a new CTR field with the formula Clicks/Impressions. You
can type the formula in or just double click each field to add it to
the formula box. Now you've got a CTR field that's being generated at
every level of the pivot table.

Naming trick: You cannot duplicate names in a pivot table. Since
CTR already exists, my trick is to just add a single invisible
space after each name. That way, it fools the system into thinking
it's a unique name.

Filtering

If you know how to use filters already in Excel, you can apply the
same techniques to pivot tables. Click the little box with the filter
icon in any row or report filter cell and you can filter the following
four ways:

Report Filter. This is for filtering the entire pivot table, not just
individual columns. You can drag/drop any field from the Field List
into the Report Filter box. Using a combination of the Report Filters
and the filters below can really help you zoom in on data. For
example, you could use the report filter to choose a certain month and
engine and then the Standard Filter to pick just those values you want
to analyze.
Standard Filter. Every value in that group will appear in the lower
box. You can chose to Select All or individually check the boxes next
to the values you want to see. A quick tip - If you want to exclude
only a few values, use Select All and uncheck the ones you don't want.
If you only want to see a few of the values, click Select All until
all of the boxes become unchecked. Then it's easier to select those
few.
Label Filter. This is for filtering text. You choose the standard
excel options such as contains, begins with, etc.

Value Filter. This is for filtering numbers. Once again, you have the
standard Excel options such as greater than, Top 10, etc

Formatting the table

You can format each field as you would with any column in Excel. The
easiest way to choose the column and click Field Settings in the right
click menu or the Options ribbon. From there you can choose whether
you want the data to be summed, Counted, Averaged, etc. You can change
the number format to percentage, dollar, include decimal levels or
commas, as well. You can also explore the Design ribbon for setting a
color scheme, banded rows/columns, when you want subtotals to appear,
etc. Basically, all of the same style options you have for regular
tables in Excel can be applied to pivot tables.

Pivot Charts

Pivot charts are just charts based on pivot tables. If you begin with
the source data, you can choose to insert a PivotChart instead of a
PivotTable. All this does is build a pivot table next to your chart.
Pivot around the table to affect the chart. As well, you can simply
create a chart from an existing pivot table (as you would with any
data table in Excel).

Quick Tip: Select any cell in a pivot table and click F11. It will
instantly create a column chart for you.

Miscellaneous Pivot Table stuff

You can make multiple pivot tables from the same source table.
When the source table changes, you can import that new data into the
pivot by choosing Refresh from the Options ribbon.
Blanks may appear in rows as data. Easily filter them out by
clicking the down arrow on any header and un-checking the box next to
that value.
Move row labels up or down with Right Click -> Move. This can be
helpful if you want to a row to appear at the top even if it's not
alphabetically going to happen if you sort. You can also just grab the
row labels themselves and put them in the order you'd like (thanks for
that, Sean!)

If you pivot a table into data you want to copy and paste, make sure
to Paste Special -> Values or the entire pivot will be inserted.
You can make multiple pivot charts from the same pivot table.
However, every time you manipulate the data, the chart will change.
The best practice is to make a new pivot table to be the source data
for each pivot chart.

Final Pivot Table trick: the Average Position calculated field

Unlike CTR or CPC, you can't create a simple formula for the Average
Position calculated field. Since each keyword has its own Average
Position value, if you were to simple create an average to see it at a
higher level such as the Ad Group or Campaign level, your numbers
would be skewed. Why? Because you have to take into account the weight
that each keyword has on it's grouping level. In a simple example, if
you had two keywords, one with Avg Pos of 1 and the other at 3, a
simple formula would tell you their average position overall was 2.
However, what if I was to tell you the first keyword had 100
impressions and the second keyword had 1,000,000? The average position
for that group would be more like 2.99 than 2, right? So, how do you
do get around this in a pivot table?

The actual formula is (keyword 1 Avg Pos x impressions)+(keyword 2
Avg Pos x impression) etc / Total Impressions. So, to start, you have
to create a new column in the source table called AvgPosXImpsFormula.
Then, create the formula of Avg Position x Impressions. Drag that
formula down for the entire report. Then, copy and paste values that
same column so you have a column of just the values, not the formulas.
I call this column just AvgPosXImps. Then, when you convert this
source table into a pivot table, you can create a calculated field
with AvgPosxImps/Impressions. That will create the proper metric for
any level you want to see the average position (i.e. campaign level,
engine level, ad group level, etc).

I'll be wrapping up this Excel series in my next post with some final
tips/tricks and a Online Marketer Excel Skills test20 questions to
see just how good you and your crew are at the tool.

Opinions expressed in the article are those of the author, and not
necessarily Search Engine Land.

Josh Dreller <http://searchengineland.com/author/josh-dreller/>

is the Director of Media Technology for Fuor Digital
<http://www.fuor.net>
, an agency concentrated in the research, planning, buying and
stewardship of digital media marketing campaigns. Josh can be reached
at jdreller@fuor.net <mailto:jdreller@fuor.net>
.

<div><a href="http://www.superbhosting.net/"><img
src="http://www.searchmarketingnow.com/_images/superb.gif"
border="0"></a></div>
You are receiving this email because you subscribed to a Search Engine
Land, Search Marketing Now or Search Marketing Expo newsletter. To update
your subscriptions or unsubscribe, use your newsletter preferences page at
http://third.thirddoormedia.com/lists/?p=preferences&id=2&uid=f9779113b77d03598707df46ae032f9b.
Or send your written request to: 279 Newtown Tpke., Redding, CT
06896</font>

Effective Link Building Service

Friday, July 24, 2009

In The Trenches: How To Excel At Excel For SEM Applications, Part 5: Master Pivot Tables

0 comments:

Post a Comment

Followers

Blog Archive

About Me