Chad Summerhill's blog

PPC Storytelling: How to Make an Excel Bubble Chart for PPC

The Excel bubble chart is often overlooked by PPC advertisers, because it can be tricky to set up. However, when used properly, a bubble chart lets you clearly present and compare categorical data (e.g. campaign, ad groups, device, etc.) – represented by the number of bubbles – as well as quantitative data (e.

g. cost, conversions, CTR, etc.) – represented by the size of the bubble and its location on the XY axis.In this article, I will show you step-by-step how to create the powerful Excel bubble chart for use with PPC data and even give you access to a free download of the unlocked Excel spreadsheet (Click to download excel bubble chart template).Why Bubble Charts Are Useful for PPCAs PPC managers, we understand our campaigns, but it can be difficult to quickly and effectively descr... > Read more

September 17, 2013     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 8

Re-Creating AdWords Campaign Performance Charts in Excel, Part 5: Dynamic Controls

This is the final post in my series on Re-Creating the AdWords Dashboard in Excel. For the rest of the series, see below:Re-Creating AdWords Campaign Performance Charts in Excel, Part 1: The DataRe-Creating AdWords Campaign Performance Charts in Excel, Part 2: The TransformationRe-Creating AdWords Campaign Performance Charts in Excel, Part 3: Making the ScorecardRe-Creating AdWords Campaign Performance Charts in Excel, Part 4: Making the ChartsIn part four of this series, you learned how to build the charts for the AdWords dashboard.

In this article, I will show you how to make the dashboard dynamic by teaching you how to:Add a dynamic campaign filter.Add a dynamic date-range picker using a Data Validation list.Building the Campaign FilterAs you may recall we are using the DTP framework as... > Read more

May 19, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 3

Re-Creating AdWords Campaign Performance Charts in Excel, Part 4: Making the Charts

In part three of this series, you learned how to build our dashboard’s scorecard. In this article, I will show you:How to build the charts.How to copy and reuse your charts to save time when building multiple charts.Building the Charts The AdWords Campaign Performance report only has two charts and they allow you to choose from many different available metrics for comparison.

Our dashboard isn’t really limited by space, so we will be creating a chart for each of our KPI’s.ImpressionsClicksCostConversionsCTRCRCPACPCThe best way to do this is to create and format one chart for one KPI, and then we will copy the chart several times for each of the other KPI’s. This technique will save you lots of time, because you will only have to format your chart once.Follow these steps:1. Ins... > Read more

May 11, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 1

Re-Creating AdWords Campaign Performance Charts in Excel, Part 3: Making the Scorecard

In part two of this series, you learned how to transform the data we will be using to create our dashboard. In this article, I will show you:How to build the scorecard.How to use conditional formatting.How to use the camera tool.Presentation (the “P” in “DTP”)Again, we are using the DTP framework created by the smart folks at Juice Analytics.

The presentation layer for our exercise consists of all the pieces of our dashboard:Dynamic charts and scorecardsDynamic controls for date ranges and campaign filteringBuilding dynamic dashboards in Excel can be a challenge, but if you’ve already completed the steps from the first two posts in this series you’ve done the hard part. Now we are ready for the fun stuff.The Dashboard TabFirst, we need to create a new worksheet and rename it ... > Read more

May 03, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 1

Re-Creating AdWords Campaign Performance Charts in Excel, Part 2: The Transformation

In Part 1 of this series, you learned how to prepare the data we will be using to create our dashboard. In this article, I will show you:How to set up an Excel pivot table.How to use calculated metrics in a pivot table.How to use Named Ranges in Excel.How to use the OFFSET formula in Excel.Transformation (The “T” in “DTP”)We are going to use an Excel pivot table for our "transformation" layer in our DTP framework (worksheet).

You should already have your data prepared in the "current" worksheet, so you will need to:1. If you haven’t already, format your data as a table in Excel by highlighting your entire data set in the "current" worksheet and click on "Format as Table" from the "Home" menu.2. Now select any cell within your table and navigate to the "Insert" menu and insert a "... > Read more

April 26, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 2

Re-Creating AdWords Campaign Performance Charts in Excel, Part 1: The Data

Interesting things you will learn in this how-to:How to prepare data for year-over-year analysis in ExcelHow to use the Excel INDEX-MATCH formulaHow to use the Excel ISNA formulaA few weeks ago I showed you how to compare date ranges in AdWords, and then a few days later I came across an article on Search Engine Journal about using the new AdWords dashboard located on the "Home" tab.

 In the article the author mentioned how, in the new version of the Home tab, you couldn’t do date range comparison like in the previous version. See the image below:So, I thought it might be interesting to try and recreate this chart and summary in Excel. The finished dashboard will look something like this:For this exercise I will be using a variation of the Juice Analytics DTP Framework.Data (the... > Read more

April 18, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 4

AdWords Negative Keyword Lists: A Negative User Experience

When you have to read a help file to understand how to use a feature, doesn’t it bug you? Don’t you wish that things were a little more intuitive and easy to use? For whatever reason, the AdWords team seems to be masters at releasing very useful features (sometimes awesome features like Broad Match Modifiers) and then making them a pain to use.

You may remember my concerns about  ACE for ads a few weeks back. Again, ACE for ads is great, but no fun to use. I hate to be negative about negative keywords, which I love so much (see my posts on search query mining), but AdWords made me feel stupid when I created my first "negative keyword list." I hate it when software makes me feel stupid. Creating the list was very straightforward. Simply: Navigate to the Control panel and library C... > Read more

February 09, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 6

Analyzing Your AdWords Geographic Report Using Tableau

One of my PPC New Year’s resolutions was to start taking advantage of geo-targeting more in AdWords. Analyzing your AdWords Geographic Report is a good place to start with developing your strategy for setting up geo-targeted campaigns.  Everyone knows about the power of Excel for PPC data analysis, but not too many people are taking advantage of the power of data visualization using Tableau.

Download Tableau Public Tableau is an easy-to-use business intelligence and data visualization software. You can download a copy of Tableau Public for testing. Tableau is like a visual pivot table and really shines at displaying multi-dimensional views of data. Don’t be intimidated, Tableau has lots of training material on their website, so it won’t take you long to start slicing and dicing ... > Read more

January 25, 2011     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 1

AdWords Campaign Experiments Adds Ads: Why I'm Disappointed

Was anyone else a little confused and let down when Google launched AdWords Campaign Experiments back in August without the ability to test ads? Instead of the obvious new ad testing features, Google focused on split testing across ad groups and campaigns for changes to keywords and ad groups.For example: The first test I ran was a keyword reduction test.

I paused low impressions, low Quality Score keywords, etc. for an entire campaign. ACE worked great for this type of test.But why not start with the ads? Ads are the single most tested element of anyone’s PPC campaign, and yet they were mysteriously missing from the initial release.So, Google has now announced the ability to use ACE with your text and display ads. Better late than never—right?You can watch the video below to learn how... > Read more

December 20, 2010     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 4

Advanced Search Query Mining Part 5: Acting on Your Insights

This is the last post in my series on Advanced Search Query Mining. Here is a list of the previous posts in this series just in case you missed one. Part 1: The Power of Search Queries Part 2: Getting the Right Data Part 3: Preparing Your Data for Analysis Part 4: Mining Your Data for Insights In my previous post on search query mining I showed you my technique for creating an ad-group-level negative candidates list and a keyword expansion list from your search queries.

In this post I’m going to show you a method for acting on those insights. I will also include a link in the conclusion of this post to a free Excel download that has all of the formulas I’ve used in this series. Acting on your Negative Candidates There are several factors that could be impacting the performance o... > Read more

December 08, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 0

Advanced Search Query Mining Part 4: Mining Your Data for Insights

In the last post in this series, I showed you how to prepare your search query data for analysis. We had some specific questions that needed to be answered and that shaped how we transformed our data. These questions included the following: What search queries have high impressions but no clicks?  What search queries have resulted in a conversion?  What search queries have a below-average CTR for the ad group?  What search queries have an above-average cost/conversion? Do I have a problem with ad poaching and duplication?  For the most part, these questions are focused around search queries that may need to be added as negative keywords and search queries that need to be a part of a keyword expansion strategy.

I like to start my query mining analysis with a quick surv... > Read more

December 01, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 0

Advanced Search Query Mining Part 3: Preparing Your Data

In part 2 of this series, we gathered all of the data we would need for our search query mining exercise into Excel, as seen below. Now, we must take the time to prepare our data for analysis. This will include creating derived fields to bring information to the surface, flagging and deleting noise, converting counts to proportions, etc.

 We are going to use the power of Excel to our advantage and push our data to its limits to extract value. Here are some of the questions our data will need to be able to answer easily: What search queries have high impressions but no clicks? (might be a good negative candidate) What search queries have resulted in a conversion? (promote these to exact match keywords in your account). What search queries have a below average CTR for the ad ... > Read more

November 24, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 6

Advanced Search Query Mining Part 2: Getting the Right Data

As with any PPC analysis, you must get the right data to answer your questions. Here are some of the questions our data will need to be able to answer easily: What search queries have high impressions but no clicks? What search queries have resulted in a conversion?  What search queries have a below average CTR for the ad-group? What search queries have an above average Cost/Conv? What search queries are duplicates of existing exact match keywords?  In order to answer questions about a search query’s performance we need the Search Query Report, for questions about comparison metrics we will need an Ad-Group Report, and for questions about duplication we will need a Keyword Account Structure Report.

This tutorial does require a basic understanding of how to use the AdWord... > Read more

November 17, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 2

Advanced Search Query Mining Part 1: The Power of Search Queries

If you are bidding on broad match keywords and ignoring your search queries, you are definitely wasting money, by not managing your negative keywords, missing out on profitable long-tail keyword opportunities, and possibly missing new emerging search trends in your market. Keywords are not search queries Keywords are not search queries, although search queries can be keywords.

 Keywords are assumptions about the words we think our customer will use when using a search engine, while search queries are the reality. If you are only using exact match keywords in your PPC campaigns, then your keywords will match your customers' search queries exactly every time a search is matched to your ad. However, if you are taking advantage of broad and phrase match, oftentimes one keyword ca... > Read more

November 10, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 5

Google's Broad Match Modifier: Who Should Use It, Why, and How

I’m sure by now that most of you have heard of Google AdWords’ new feature, Broad Match Modifiers (BMM). In short, by adding a simple “+” before a word in your broad match keyword, Google requires that word (or a close variation) to appear in the user’s search query. If you’re not excited, you should be.

This is exactly the type of feature that we advertisers have been asking for and can benefit greatly from. BMM gives us more visibility and control over how we spend our money. (Check out Alan Mitchell’s blog post on using Modified Broad Match and its effect on CTR and CPC if you still need to get pumped up about trying out this new feature.) So who should use this exciting new feature? Anyone who has been afraid to use Enhanced Broad Match (EBM) in the past because it’s ... > Read more

October 27, 2010     |   Written by: Chad Summerhill   |   Posted In: AdWords Tips   |   Comments: 6

PPC Ad Testing to Maximize Conversions

The goal of PPC ad testing is to increase your click-through rate (CTR) and/or your conversion rate (CVR). Focusing on CTR should result in more traffic to your site, but it could be at the expense of your CVR. On the other hand, focusing on conversion rate should improve the quality of your traffic, but could hurt your CTR and reduce the number of potential customers visiting your site.

As advertisers we are asked to maximize both CTR and CVR, which often requires totally different approaches. If you want to maximize the total number of conversions on your site, you will need to look at a different metric which considers both the CTR and the CVR. This metric is called Impression-to-Conversion or I2C (Conversions/Impressions). I recently created a free Excel download for determining statis... > Read more

September 15, 2010     |   Written by: Chad Summerhill   |   Posted In: Paid Search Marketing   |   Comments: 2

Using Enhanced CPC and Third-Party Bid Management

I was reading Google’s post about Enhanced CPC (they're offering a free webinar about the feature next week) when one sentence in particular struck me as very interesting: “Enhanced CPC is compatible with advanced campaign settings and third party bid management systems.” For a while now, I have been using a proprietary, in-house bid management system in conjunction with Google’s Conversion Optimizer to produce excellent results.

I can do this because my company has built our own web analytics data warehouse. Our solution is unique in that we have embraced Google Conversion Optimizer and modified our bid management system to change CPA bids at the ad-group level. This “best-of-both-worlds” bid management approach is available to anyone using third-party bid management software.... > Read more

September 01, 2010     |   Written by: Chad Summerhill   |   Posted In:   |   Comments: 5
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.