Pivot Tables: The Unsung Hero of PPC Performance Evaluation

analyzing PPC perfromance by location with pivot tables

Analyzing your PPC campaigns by location reveals actionable insights. It helps you spend your digital advertising budget more efficiently. Once you discover where your ads are enticing people to convert, you can nurture those efforts. While, on the other hand, you can reduce your spend in places where your ads aren’t the right fit for the audience.

This blog post shows you how to extract location data from Google Ads into an Excel sheet, transform that data into a pivot table, and then manipulate that table to pull out performance insights.

Pivot tables are potent and complex. I hope that this careful, step-by-step example of PPC analysis—complete with (one might even say replete with) screenshots to guide you—will start to demystify pivot tables.

Exporting Location Performance Data from Google Ads to Excel

We’re going to extract our data via this path: Locations > User location report > Campaign view > Download

Let’s begin by setting the date range to examine. In the example here, I’m going to look quite far back; I want to take a deep dive into the historical performance of this account.

 
 

a_Setting_Date_Range_-_Pivot_Table_PPC_Performance_Blog

Navigate to: Locations > User location report

b Location User location report - Pivot Table PPC Perf

I’m selecting the “User location report” because I’m interested in where users actually were when they interacted with my ads.

v_User_location_report_def_-_Pivot_Table_PPC_Blog

The “Geographic report” factors in user interest in addition to user location.

w_Geographic_report_def_-_Pivot_Table_PPC_Blog

Pick the report that best suits your product or service. For example, a tour company specializing in treks up Mount Kilimanjaro would benefit from knowing when people are interested in Tanzania.

Select “Campaign view,” not “Location view.”

If you select “Location view,” you won’t be able to segment out how each campaign did in each location; you’ll only be able to see total performance, for all campaigns, by location.

c_Select_Campaign_View_-_Pivot_Table_PPC_Performance_Blog

Set your columns for the metrics you want to pull into the pivot table.

d_Modify_columns_-_Pivot_Table_PPC_Performance_Blog

Make sure to include “Location type.”

e Include Location type column - Pivot Table PPC Blog

With your desired columns in place, download your data.

f Click download - Pivot Table PPC Blog

I recommend downloading it as a .xlsx file; a .csv file does not support saving advanced Excel elements (like pivot tables). I’ve had my stomach drop one too many times upon opening a file and finding all my work gone.

g Save as an xlsx - Pivot Table PPC Blog

The resulting Excel should look something like this.

h Resulting table - Pivot Table PPC Blog

Setting Up the Pivot Table

Navigate to: Insert > PivotTable

i Insert Pivot Table - Pivot Table PPC Blog

Select the data that you’ve just pulled into the Excel sheet, including column headings.

Choose to place the pivot table on a new worksheet. (I find this keeps your work cleaner, and keeping all your raw data in a separate sheet is convenient in case you need to refer to it or create a new table.)

Click “OK.”

j Data Place OK - Pivot Table PPC Blog

Populate your pivot table fields. You do this by checking the boxes that correspond to the columns you’ve pulled into Excel.

k Populate fields - Pivot Table PPC Blog

You can then drag the enabled fields between the 4 pivot table areas: Filters, Columns, Rows, Values. Shifting the fields from one area to another adjusts the pivot table for numerous avenues of analysis.

l_4_areas_in_green_-_Pivot_Table_PPC_Blog

Here, I’m going to analyze how campaigns performed by country (and vice versa) looking at impressions, clicks, conversions, cost, and cost/conv.

Using a Pivot Table to Identify Performance Insights

Pivot tables can be a bit overwhelming. So let me begin with some navigation tips; then we can get to analyzing.

Expanding and Collapsing Your Locations and Campaigns

In the screenshot below, campaigns are nested within locations in column A. Being able to expand and collapse your rows is key to identifying general trends and then drilling down for granular performance analysis.

Navigate this path to open and close the nesting: Hover over the column you want to change > Right-click > Expand/Collapse > Collapse Entire Field

m_Expanding_Collapsing_-_Pivot_Table_PPC_Blog

Sorting Your Metrics

There are many ways to sort within the nesting of a pivot table. This means there are many possible insights to be gained, but getting your sorting technique down can be finicky.

Where you click within the table to sort is crucial. The process to sort each time will be the same. Paying close attention to the cell you select, navigate this path to sort: Hover over a cell of the level/metric you want to sort by > Right-click > Sort > Sort Largest to Smallest

n_Sorting_Yr_Metrics_-_Pivot_Table_PPC_Blog

In the screenshot above, I am sorting locations by the total impressions they received from any campaigns. Excel is letting me sort this way because I right-clicked on cell B4, which is on the location level of nesting and under the total of impressions metric.

You can also sort by how the campaigns performed within a location. Starting this same sorting procedure by right-clicking on B5 would have sorted the campaigns nested within Singapore by impressions (and it would have the same effect on all the other campaigns within all the other locations below).

Warning: Don’t sort on column headings.

o_Before_sorting_column_headings_-_Pivot_Table_PPC_Blog

Sorting by any of the metric headings (here, any of row 3 cells B, C, D, E, or F) results in the rearrangement of your columns by the totals of the various metrics in each column. That is, the structure of your pivot table will likely change, as your metric columns are moved left or right to be in order from largest to smallest total. (You can also see the results of this change in the reordering of your fields within the areas in the panel on the right.)

p_After_sorting_column_headings_-_Pivot_Table_PPC_Blog

See how C3 is now occupied by “Sum of Cost/conv” rather than “Sum of Clicks”; D3, E3, and F3 have been similarly affected.

Analyzing by Location

This pivot table lets us look at this account from (at least) two perspectives: how well our campaigns did in a particular country, and how various countries contributed to the performance of a single campaign. I’ll begin with the former.

Here I’ve collapsed the campaign level and then sorted by conversions to reveal which locations drove the most conversions.

q_Sort_by_Conv_-_Pivot_Table_PPC_Blog

From this, I can see that I want to nurture my efforts towards the United States, the Philippines, Indonesia, Singapore, and Iraq.

What campaigns in particular, though, should I focus on?

To answer this, I’ll expand the nesting to reveal the campaigns under each location and then sort by conversions by right-clicking on D5.

r_To_answer_this_-_Pivot_Table_PPC_Blog

Now I can see that Campaign C has driven the most conversions in the United States and that Campaign A has driven the most conversions in the other 4 locations.

s_US_sort_by_Conv_-_Pivot_Table_PPC_Blog

I could also sort by cost/conv to see that while Campaign C has driven the most conversions in the US (107.8), Campaign E has driven about one-fifth (39.1) of all conversions (206.5) in the US for about half (5.69) the cost/conv of Campaign C (11.72). So I’ll want to investigate Campaign C to see what’s going right there and do more of it.

Let’s also identify where there’s fat to be trimmed. When I sort by cost in the US, I see that Campaign H has spent the third-most of any campaign (727.78) for only 4.6 conversions; this campaign might not be worth running at that level of return.

Before I just pause the campaign altogether, though, I should dig into the ad groups and keywords within the campaign to see whether there’s a clear poor performer that can be carefully excised without axing the whole campaign.

See this blog post for how to pinpoint PPC performance changes. It shows you how to take a scalpel, rather than a sledgehammer, to poor performers.

Analyzing by Campaign

Time to switch it around.

t_Move_location_below_campaign_-_Pivot_Table_PPC_Blog

Go to the panel on the right, to the “Rows” area, and move “Location” below “Campaign.” This will reverse the nesting in column A: locations will now be nested under each campaign.

u_Reverse_the_nesting_-_Pivot_Table_PPC_Blog

Collapsing the locations reveals the big picture of performance by campaign.

x_Big_picture_-_Pivot_Table_PPC_Blog

Campaign C has driven the most conversions and at a cost/conv of just 11.72; on the other hand, Campaign A has spent thousands more for nearly the same number of conversions. Let’s investigate.

z_Highlighted_locations_-_Pivot_Table_PPC_Blog

Here I’ve sorted by cost and then highlighted locations that have not converted in the nearly year-and-a-half that we’re looking at. It might be time to consider excluding these locations from our targeting.

We can also switch back to looking at locations overall and do this same evaluation across all campaigns.

za_Locations_highlights_-_Pivot_Table_PPC_Blog

I hope you’ve gotten the flavor of the numerous avenues of analysis that pivot tables open up.

For more help you PPC efforts, check these out:

Happy analyzing!

About The Author

Ryan Norman is a Digital Advertising Account Manager for DOM. Ryan earned a BA in English from Wheeling Jesuit University. His blog posts cover PPC strategy and how to improve your writing. When he's not helping DOM's clientele grow confidently online, Ryan enjoys backpacking and philosophy.

View Ryan's full bio.
Get a free, no-obligation consultation with a digital marketing expert