Connect with us


How to Pivot Paid Search Data in Excel



Learning how to use Pivot tables in your PPC reporting helps you find insights in a quick and easy manner. I create Pivot tables to look at how our campaigns are performing across different marketing platforms for brand, non-brand, and specific client goals. Let’s dive into how you can feel confident with your data and start pivoting your way into data analysis.

The Hurdle

The biggest hurdle in creating a pivot table and getting your data organized first so you can perform the magic that is the pivot table. By having a consistent naming convention across your campaigns in your accounts it helps you break down your data in a pinch. Brittany Sager has a great blog on “How To Create a Great PPC Campaign Structure” which stresses the importance of organizing your campaigns for performance but also for when someone is covering your account or to pull reports.

For this blog, my naming convention consists of Platform, Location, and Program. Example below:

Google_Nashville_Rad Tech

This campaign expresses what I need to know very quickly by showing me where the data came from, where my campaign physically targets, and what type of keywords are in the campaign. Now let’s jump to the fun stuff, playing around with the data and see what insights we can pull for this account.

The How-To

To begin, we are going to pull campaign level data in Google and in Bing and I only care about capturing these metrics at this time: Cost, Conversions, Impressions, and Clicks.


After I have pulled both reports for my preferred time frame, I combined my Google and Bing data making sure my columns for each platform match up.


Now I am going to insert a few columns after the campaign column because I want my data to be more granular. Because my naming convention uses brackets I can use the Text to Column in excel to break my campaign name into new segments of data.

See also  How Learning Python Can Improve Your PPC Campaigns

Series of steps below:

Insert a few columns to allow the data a place to go. If you don’t the data could overlap your metrics.
Text-to-column-previewUse the preview in Text to Columns to make sure the data is breaking apart the way you need it to.
post-text-to-columnText to Column Completed

Make sure to add column headers for your new data segments so the Pivot Table will know what your data is and how to manipulate the information in your chart. In my data, I have used: Channel, Location, and Program.

Now we can get to do the actual pivot! You select all of the data you want to use and go to the Insert tab in Excel and select Pivot Table.  When you select this a window will pop up with your range and you select OK.


Once this happens a new worksheet will appear in Excel and the Pivot Table will be open with the Pivot Table Fields. It will be empty when you begin with, but once you select your fields, the data will pop up immediately.

empty-pivot-tableEmpty Pivot waiting for some values

We will start off easy and look at how the channel overall is performing for cost, clicks, impressions, and conversions.

You will select the Channel name and it will move underneath the Rows section.


Now select the metrics we want to see and those will go underneath the values section of the Pivot Table Field List.


If you want to see more granular information, we can select Program and drag this to the Rows section. Once we do that we can see how each program has been performing in each channel. If I don’t like this set up I can switch the options or “pivot” my data the other direction by dragging Program to appear before Channel in the Rows section of the PivotTable Fields


Here I can compare side by side how the programs are doing in one channel versus the other channel. I can also move Program once again to the Filters section so I can look at my data in a different way.

Using Programs as a Filter

For this example, the client was requesting information about our nursing programs and how they were performing in Google and Bing. With the filter I can select both General and AND Nursing and the numbers in the chart will be the sum total of both programs.

Filter-data-pivot-tableNursing Programs by Channel

Now you may have noticed that I didn’t download and rate type of metrics and that is because I wanted to show you have to create your own fields in the Pivot Table. We are going to add Click Thru Rate which is Clicks divided by Impressions. Go to the Field, Items, and Sets and hit Calculated Field.


Once you are in there you can rename Field1 as CTR and select Clicks and hit Insert Field. Do the same thing with Impressions but make sure to add the backslash for the division symbol and hit OK.


Now we can format the value as a percentage and the cost as a currency to make this table a bit prettier.


The Why

Ther are many ways to use pivot tables but today I covered how you can move the data around quickly bouncing between channels, locations, programs, and even campaign type if you want to compare Search and Display. I have used Pivot Tables to look at A/B tests for ad copy to see which Headline performs better on based on my client’s metrics. Once you are comfortable with playing around with Pivot tables you will find new ways to look at any report you pull from Google and Bing and show the information in a clear and simple format for your client. If you are interested in learning how else to use Excel in digital marketing here is our Complete Guide to Using Excel for PPC.

See also  How 500 Marketers are Leveraging Instagram Shopping Tools [Data]


How SEO Works in Digital Marketing




Search engine optimization (SEO) is an integral part of digital marketing.

SEO helps with brand discoverability. When done right, SEO can create the most consistent and by far the highest-quality traffic source which doesn’t require on-going maintenance.

Yet, SEO is usually the most isolated part of the marketing. Whether it is an in-house team or a third-party service that’s delivering your SEO campaigns, it usually exists on its own without really communicating goals, progress or results to the whole company.

This creates silos that can lead to poor results and even reputation crises.

How does SEO work in digital marketing and how can a business make it work better?

What is SEO?

SEO is a collection of tactics (content, technical, link building, even website security and usability) that ensures that your website is easy to understand for search engines.

Basically, it ensures a machine knows that your page will be easy to find to a human being who is looking to solve a related problem.

Search engine traffic is one of the highest-quality traffic for many reasons:

  • Unlike PPC (paid) traffic, it doesn’t require an ongoing investment to keep coming
  • Unlike social media traffic,  it doesn’t require an ongoing work to keep coming
  • Unlike social media traffic, you are not interrupting people’s browsing. Instead you give them what they were actually searching for.

In other words, it is consistent and it converts well. No other digital marketing tactic beats that.

Apart from driving direct traffic, search engine optimization helps build brand awareness by increasing your brand’s organic findability.


Keep Your Whole Team Aware of Why SEO is Important

The great thing about today is that everyone understands the value of ranking high on Google! Sadly, however, many folks only know that they “need SEO” without having really understood what that means. 

See also  5 Tips for Managing PPC During the Coronavirus Outbreak

SEO these days is too hard for a digital marketer to do alone. Many SEOs find themselves in situations where an executive will simply come down and go “Why are we not ranking well for ‘dingwobble’?” 

Keep working hard with teams for them to understand how they contribute to the SEO process:

  • Product Marketers who are responsible for the business, personas and messaging understand that SEO is critical to driving the bottom line revenue numbers they are looking at. Part of the persona developing process should be the development of the “digital persona” – what websites and search terms are these people looking for? This helps the product marketer when it comes time to develop messaging, as that is going to be critical for developing the content, so the right search terms better be there!
  • Field Marketers responsible for the campaigns need to know how SEO fits within their campaign, how it in fact is core to our demand generation, and how to make sure to keep the campaigns integrated.
  • Marketing Communications is creating the content, so SEO should very well be top of mind for them, as the content itself will be critical in impacting how successful SEO will be.
  • But that’s not all! Often, other groups are creating content (Press Releases, Blog Posts, Presentations, etc.) that also end up on the web and impact SEO. Whether it’s Corporate Communications, Investor Relations or even Legal teams, working with them is critical.
  • IT manages the infrastructure and can be very critical to the technical aspects of SEO.
  • Sales and customer support teams are at the forefront of marketing talking to your future and current customers, so they need to be involved in the SEO strategy. Creating relevant content goes beyond keywords. It needs to address real problems and answer actual people’s questions, and your client-facing teams will be your best source of inspiration here.  
  • Executives also care! While they can’t often influence the day-to-day of SEO, they will care a lot about the bottom line, to which SEO contributes.
See also  How to Create a Pivot Table in Excel: A Step-by-Step Tutorial

Educating all of these people about SEO helps empower them, as well as position yourself, the SEO, as the subject matter expert who is not just someone back-office who gives very little visibility into the black box of SEO, but someone who is actively educating and contributing to the organization’s success.

Review and discuss common KPIs early and often to make sure everyone knows what victory looks like to the team.

Additionally, SEO should be a solid part of any project launch as it impacts every stage of product positioning. From choosing a business name to choosing a website builder, your initial efforts should be driven by SEO best practices.

What is the key to SEO success in a constantly changing environment?

As a practitioner of SEO, I believe that you need to look to ensure you are looking at both developing yourself in both depth and breadth of knowledge. A key danger in the name of being informed or being a part of the SEO community is spending all your time debating tactics and practices rather than testing them. 

Additionally, SEOs as with all employees need to look outside their field to stretch and learn how to be more well rounded. This could mean learning to code, or educating yourself in some other area of the business you work for.  This will expose you to ideas others may not have.

As a manager of people, success is really about diversity of expertise. Who you hire and the kind of people you hire will be far more valuable than much of what people invest in with regards to SEO programs. You have to have people who can roll with the punches and develop a skill for self-management and personal growth. 


Finally, I think knowing what your real goals are in having an SEO program are the key to long term success. The reality is you may get more traffic, but if that traffic is not from qualified leads and generates real revenue then the benefit may be very little. Having well defined goals and metrics will also help you avoid chasing algorithm changes and focus on the big picture.

See also  10 Awesome Paid SEO Tools That Are Worth the Money


SEO is the most essential long-term digital marketing strategy but to make it really effective, you need a knowledge team that is well-integrated into the company’s life. Good luck!

Ann Smarty

Ann Smarty is the brand NINJA at Internet Marketing Ninjas as well as the founder of numerous startups including MyBlogGuest, MyBlogU, ViralContentBee, TwChat and many more.

Ann Smarty has been an online marketing consultant for 10 years providing high-quality digital marketing consulting through her services and courses (both free and paid).

Ann Smarty’s content marketing ideas have been featured in NYtimes, Mashable, Entrepreneur, Search Engine Land and many more. She is known for her indepth tool reviews, innovative content marketing advice and actionable digital marketing ideas.

Source: Ann Smarty

Continue Reading

Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address