Building An Automated PPC Spend Tracker In Google Sheets
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
Search ads are no longer just about headlines, description lines and display URLs. Ad Extensions now also play a huge part in the appearance and success of our ads.
Take a look at this ad from Walmart I saw the other day. Notice how more than 50% of the space taken up by this ad comes from extensions of various types.
I recently audited a huge account that spends millions of dollars per month across hundreds of campaigns and millions of keywords. This account is set up flawlessly. It has a fantastic structure, great ads with good testing plans, excellent use of negative keywords and a sophisticated back end tracking system. However, when I dug into the account I noticed one glaring problem:
Every single campaign contained the same four Sitelinks.
This is the PPC equivalent of skipping leg day (see the PPC Hero character if you don’t know what I’m talking about). We work hard to set up our accounts, but we forget to apply this diligence to extensions. In almost all well-run accounts I audit, my top recommendation involves ad extensions.
The guys over at Bing recently put together an eye tracking case study that looked at mobile users.
The study showed that mobile users were much more attracted to ads on the results page when multiple types of ad extensions were present. The data suggests the rise in click-through rate was as much as 15-30%. With mobile expected to outgrow computer searches by 2017, this is a huge wake up call for us to be ahead of the game with our mobile extension strategies.
In October Google announced extensions would play a part in ad rank calculations. The quality score of your ad extensions will be taken into account when your ad goes into the auction.
The main reason Google took this action was so their algorithm could decide the optimum combination of extensions in any given auction. With so many ad extensions now available, they needed a way to determine which combinations would yield the highest possible CTR. There were no radical changes to the fundamentals of quality score – the auction is still mainly about your CTR compared with the expected CTR of a given keyword. A higher than expected CTR will lead to cheaper CPCs and higher average positions.
Given that Google’s algorithm now helps you get as high of a CTR as possible by setting optimal extension combinations, it makes sense to focus on them and get them in good order.
The following are the list of what I call active AdWords ad extensions. These are the extensions we can control from the “Ad Extensions” tab as shown here:
Sitelinks are extremely important for almost all AdWords accounts. To illustrate, take a look at this example from one of our clients. The first thing we did for this client was add sitelinks as they weren’t using them prior to hiring us. Without making any other changes, our week over week click through rate increased 64%!
The implications were huge. The higher CTR led to higher quality scores, cheaper clicks and better average positions. We were able to generate nearly 30% more clicks within the existing budget than previously.
The above data looks at the overall boost in CTR on the ad as a whole. However, I also want to know whether people click on specific sitelinks, or if the simple inclusion of sitelinks makes users more likely to click the headline.
To understand direct clicks on sitelinks we need to go into our Ad Extensions tab and segment the data by “This Extension vs. Other”. Here’s the breakdown for a couple of Hero Conf 2014’s sitelinks. Notice our overall CTR was higher on the “Speakers” sitelink, but when we break it down, people were much more likely to click on “Schedule”.
From here we can download this report and use an Excel Pivot Table to show the overall impact of direct clicks on all of our sitelinks:
Only 0.6% of our total conversions came from direct clicks on our sitelinks. This is typically the case across lead generation accounts. As the thing you want people to do is normally limited to one action, having 4-6 links to other pages is fairly meaningless. However, the overall lift to clicks on ad headlines is still highly significant; even using bad sitelinks (assuming their relevance) is worth it when the alternative is not utilizing sitelinks at all.
One piece of advice that works well is to create new landing pages using a tool like Unbounce. Find the top six benefits of your product that you currently don’t mention in your ad and turn those into sitelink headlines with associated landing pages. As long as your landing pages are at least 80% different to one another (new headlines, copy, images, videos etc.) you won’t fall foul of Google’s sitelink policy. This enables you to write benefit driven sitelinks that lead to high-converting landing pages.
Sitelinks are more important for eCommerce accounts. They work as a way for customers to pre-select exactly where they want to go on your site. They also help overall clicks by showing users the type of products they will find on your site – “oh they have boat shoes? I should look at this site!”
In this example, 14% of overall sales came through direct sitelink clicks. 23% of our clicks were directly on sitelinks. Notice that both our Cost-per-Acquisition (CPA) and Return on Ad Spend (ROAS) are stronger for direct sitelink clicks than for headline clicks. This is because we test our sitelinks a lot.
There’s a simple way to test sitelinks – adding a load of them to your accounts, letting Google optimize for the best combination and leaving it at that.
It’s actually not a bad strategy, and it’s certainly better than letting a set of boring, untested sitelinks run forever in your campaigns.
However, we’ve found a better way— one that helps you learn more about your customers at the same time: Sitelink category testing.
By this, we mean developing with logical groups for your sitelinks to show together and then sequentially testing those groups against one another. Testing anything in a PPC account sequentially (one after the other) does risk a huge amount of interference from outside or seasonal factors, but is the only way it works at the moment.
Here’s an example of a set of sitelink groups we tested recently for an eCommerce account:
The reason we don’t just throw all 20 sitelinks (excluding Mobile for now) into the mixer is that our ads are better when there is logic to the organization of their appearance. If our sitelinks showed “Nike”, “Clothes”, “Catalogue” and “Volleyball” at the same time, the ad would lack direction and focus. This organizational method also allows us to learn about the strongest type of message to feed back into our ad testing (for example, if Brand sitelinks work really well, our takeaway is to mention brands in our ad copy).
Take a look at the 23% conversion rate jump we saw from changing one company-focused sitelinks to a product-led sitelink:
Note: This wasn’t just for direct sitelink clicks either. This was an overall lift to performance (when sitelinks were showing) that came as a result of a more coherent ad.
With extended sitelinks, high quality keywords now have the ability to show a full 70-character description underneath each sitelink.
Extended sitelinks are set up the same way as regular sitelinks. When you add or edit a sitelink you should see the optional Description field as shown below:
AdWords will automatically decide whether or not to show this description based upon the quality of your keyword. A sitelink that contains a description will not be prevented from showing when not extended, so don’t worry about creating both extended and un-extended versions of each sitelink.
Unfortunately, there are no stats available in the AdWords interface to specifically illustrate the impact of extended sitelinks. This makes it hard to make broad conclusions. The best indicator we have is the change in CTR after adding descriptions to our sitelinks in brand campaigns, which for us, was somewhere between 8-10%. For non-branded campaigns, we’ve not been able to record any noticeable difference from applying descriptions.
Our advice is to make sure you have extended sitelinks set up for brand campaigns, and wherever you have high quality score keywords. Eventually you’ll want to roll them out for all campaigns, but for now this remains a low priority.
Here’s the official take from AdWords via Twitter:
Just like with ads, you should always be running mobile-preferred versions of sitelinks when bidding on mobile traffic. There are a couple of good reasons to do this. First, mobile and non-mobile users react differently to different messaging. Take a look at this example from an account:
Sitelink 3 is the best for mobile devices, but the absolute worst on computers. Spinning this sitelink out for mobile devices and pausing the all-device version will improve our overall CTR by showing this sitelink more prevalently on mobile devices
We’ve found that writing mobile optimized sitelinks improves our mobile device CTRs by roughly 15%.
One neat trick that works really well is setting up a “Mobile Site” and “Desktop Site” sitelink as mobile-preferred. Not only does this convince users they are going to get an optimized experience, but also lets them self select the experience they want. Smaller iPhone screens might go for the mobile site, whereas Galaxy S5 users might be ok with the desktop experience. Typically, “Mobile Site” sitelinks have the best click-through rates of any sitelinks in our accounts.
Location Extensions allow you to attach your business address to ads.
Note: If you’re running both call and location extensions with different phone numbers in your campaigns, the phone number here will be pulled from your call extension and not from the location extension.
The easiest way to get location extensions up and running is through a Google My Business account. From there you can add relevant business info, such as address and opening hours.
Google have said they are phasing out the old manual way of creating location extensions, so we suggest you switch over ASAP.
For businesses with multiple store locations, you can manage bulk uploads using the Google My Business Locations interface.
Once you have location extensions running, your ads will also qualify to appear to users searching on Google Maps as long as you are targeting Google Search Partners in your Network settings.
What’s tricky about location extensions is measuring their value to PPC. If someone sees our ad, remembers the address and decides to head over to our store, the technology isn’t yet in place to attribute an accurate value to these visits.
We can, however, measure the following:
For CTR we’ve seen an approximate 10% boost when adding location extensions. It seems that the mere presence of a location extension convinces more people to click through on your ads – most likely due to an increase in ad real estate and perceived legitimacy of a business with a physical location.
To see your location extension stats, first go into the Ad Extensions menu and navigate to Location Extensions. From here, segment the data by click type:
You now have your total stats for when location extensions were visible, plus a list of the specific interactions that occurred. The value of “Get direction” or “Get location details” is, however, much harder to determine at present.
Navigating to the Free Clicks report within the Dimensions tab also shows you the number of users who used your location extensions to start GPS navigation. This is one of the most concrete measurements for additional in-store visits you can draw from your location extension efforts.
Setting up location extensions also gives you the option to target your campaigns based on a fixed radius from your store. Within your campaign settings click to edit locations and select “Advanced Search”. From here select “Location groups” and then location extensions.
For businesses with multiple stores and locations this is a nice simple way to get your ads in front of relevant customers. You could even use it to set up campaigns with different ad messaging depending on distance from your stores. Nearby customers might see ads that talk about in-store deals, while those further out could be told about free shipping and other online savings.
Call extensions allow you to add a customizable phone number to your ads. Unless you’re particularly attached to your phone number, you’ll want to enable Google call forwarding. Doing so allows Google to track call stats for you and is the only way to get a complete picture of the traffic your ads are sending.
Tip: Use call scheduling to only show a phone number on your ads during open hours. Customers who leave an answer machine message have been found to only convert at 20% of the level as people who get through to a real person. Viewing call details from the dimensions tab will show you the percentage of calls that are currently received or missed.
On mobile devices call extensions give the option to show only your phone number. You can see in this example how the headline gets pushed down into the description and my new headline becomes a link to a phone number:
This can be really effective for local services like plumbing, dry cleaning, and lawn care. However, in the example above you can see how not to go about it. All I wanted was to see a list of prices for my local dry cleaner before driving over. What I got was an ad that not only didn’t help me get to the website, but also forced me to call while the place was closed! If you go the call-only route, make sure someone is there to pick up the phone.
We conducted several case studies into the value of “Phone number only” vs. “Headline and Phone number”:
Our conclusion was that there’s no “best practice” other than to test.
The call details option in the dimensions tab brings up a list of all calls that have been tracked through a Google forwarding number. This report allows you to see:
You can also see mobile click-to-call data from within your campaign menu using the “click type” segment. This data won’t align exactly with your call details data as it’s only looking at the number of clicks on the phone number, rather than the number of people who end up making the call.
You can see in my data below that the conversion rate for users who end up calling is much higher than the rest of my traffic:
Tracking conversions from phone calls allows you to make improved bid and budget optimizations. However, you will need to know the duration of a phone call that typically represents a conversion. Simply guessing at 60 seconds isn’t a great way to go about it. Instead, speak with your sales team, or start noting the duration of calls that led to sales versus those that didn’t.
Call conversion tracking, which was previously found in the call extension menu, is now in with your other conversion settings in Tools > Conversions.
For more information on call tracking metrics see this awesome post by Susan Waldes of 3Q Digital.
Review Extensions allow you to incorporate a third-party endorsement of your company or product into your ad. The review typically appears below your description lines and above your sitelinks:
The benefits of showing these reviews outweigh the fact that you must link to a site other than your own from your ad. For those of you worried, clicks to the review are free, and extremely rare. Check out this Better Business Bureau review, which received a grand total of 0 direct clicks out 2,144 total:
For instance, Hanapin campaigns currently using Review Extensions are seeing an average 11% increase in CTR for top of page impressions. Our most compelling review extension saw a 26% uptick in CTR.
We also consistently see improved conversion rates when using review extensions. In our most extreme case the difference between conversion rates in non-branded campaigns was 27%.
To set up review extensions, you first need to find a review of your business that meets AdWords’ criteria:
For larger well-known brands this is normally not a problem. However, these criteria can be surprisingly difficult to meet for small and medium-sized businesses. If you’re struggling to find reviews, try local newspapers, ratings agencies like the Better Business Bureau, Google Trusted Stores or from anywhere Google gets seller ratings. Unfortunately for you, the more legitimate the review source, the better CTR and C/R improvements seem to be.
The real shame is that we (quite rightly) can’t use user reviews, such as on Amazon, for our extensions. Allowing such reviews would leave the whole thing open to exploitation – “Sam Owen is the best PPC guy ever” – John Smith, Amazon.com.
App Extensions allow you to direct visitors to your company’s app directly from a PPC ad.
These are pretty straightforward to set up, as you can quickly look up and add your apps for both iOS and Android.
However, our data shows minimal interaction with these extensions. Google seems to have changed direction on Apps, which will now be pushed through App Promotion Ads rather than with extensions. Google also announced a whole host of additional functionality coming for app ads, including deep linking and better conversion tracking.
Advertisers on Bing can make use of Rich Ads In Search to showcase ads on brand queries. This can come in the form of expandable videos or a company logo that runs alongside existing ads.
To get started with RAIS, contact your account representative. If eligible, they will send you a spreadsheet where you can list all branded search terms you want toshow RAIS for.
From results we’ve seen, RAIS success varies by industry, but can increase branded CTRs by as much as 55%.
Let’s take a look at those ad extensions we can’t directly control in the AdWords interface…
Seller Ratings are an example of a passive ad extension. This means Google chooses exactly when, where, and how to show them, and you’re pretty much left out of the conversation. You do at least retain the right to opt-out of seller ratings if you desire.
To include seller ratings in your ad, you must meet 2 requirements:
To see if you qualify for seller ratings change yoururl.com in the following to your own URL:
Here’s what that shows for Amazon.com:
Notice that this also tells you where Google is currently picking up reviews for your business.
If you’re struggling to get reviews, try signing up for review services like Trust Pilot or Viewpoints.com. Using follow-up emails to ask users to leave reviews after they’ve received your product or interacted with your customer service team is a good way to get the ball rolling.
It’s tricky to get exact stats for an account – there’s no direct data for us to compare within AdWords. In several cases we’ve worked with our clients to help increase their number of reviews, and from these instances we’ve seen an immediate increase in CTR with Seller Ratings. Across these clients, the average lift was 14%, with higher ratings leading to stronger improvements.
Consumer Ratings make use of information collected by Google consumer surveys to show ratings of different aspects of your business. They appear in your ad as a score out of ten, and are an attempt from Google to add a layer of trustworthy social feedback to ads.
Google will only show Consumer Ratings if your scores are good and your click-through rate improves when they are shown. This is controlled entirely by Google right now. In theory, Consumer Ratings should therefore only be able to improve your ads, but if you notice something strange going on with your traffic you may opt-out.
Consumer Ratings seem to be reserved for the elite at the moment (the kind of large businesses that are likely to get 100+ survey responses), but you can submit a request if you are desperate to get involved. Check out the official AdWords Help section for Consumer Ratings to learn more.
Remember the good old days when we could control whether we showed Google+ followers in our ads? Pepperidge Farm PPC Hero remembers. However, it seems Google became frustrated with the lack of attention Social Extensions were getting from account managers and decided to take it out of our hands.
In order to set up Google+ endorsements you need to:
Luckily, unlike with Seller or Consumer Ratings, we can pull data from our ads showing +1 annotations via the segment menu in AdWords:
When segmenting by +1s, two variations appear – Personal and Basic. Basic refers to ads that state the number of +1 followers a page has. If a user is logged in and people in their Google+ circles have +1d a page, the ad will say “John Smith +153 others +1d this page” – this counts in the “Personal” segment. I have never seen a single impression in any of our accounts fall under the personal segment.
In order to judge if +1s help our ads, we pulled data from 20 different Hanapin accounts. We then took a quick performance snapshot of each campaign for ads showing top of page with basic +1s and with no +1s showing.
In 87% of campaigns, click-through rate was higher when +1s were shown.
With larger brands (who have lots of Google+ followers) we saw the difference showing +1 annotations as high as 35%. For smaller brands with fewer followers, this number closer to 5%.
Our advice: Make sure you have your Google+ account linked and make the investment to increase your follower numbers.
Unfortunately, Google makes us sign NDAs (Non Disclosure Agreements), so we can’t talk about the specifics of many Ad Extension betas. However, there are a number of betas in the public domain, which have been shown on other blogs, that we can highlight.
AdWords announced image extensions on their official blog a while ago. These allow you to incorporate highly relevant images to go along with your quality keywords (normally brand terms). Given how difficult it would be for Google to perform quality assurance on millions of images, we wouldn’t be surprised if these never made it out of beta. Check out Matt Umbro’s assessment of image extensions so far.
These extensions allow you to start collecting names and/or email addresses directly from your ad – normally in exchange for discounts, coupons or future news updates.
Form extensions are similar to communication extensions in appearance but allow for deep linking into your website. In the example below, an insurance company is using a form extension to begin the quote process by requesting a zip code.
Live chat extensions were mentioned on a few blogs last year as people spotted them out in the wild. They allow for a direct click through to speak with a live representative.
Smart annotations are currently being rolled out to help showcase additional information about products or websites. They are created automatically by Google based on information on your landing pages – sometimes from the body of the page, as with the laptop features here, and sometimes from the footer or page navigation. There seems to be a wide range in the quality of the annotation selection process.
These are essentially an expanded list of sitelinks to help connect users to a deep page of your site.
If the above proves anything, it’s that Google are constantly testing and tinkering with Ad Extensions. If you see a beta you want to be part of, contact your rep, but don’t stress if you can’t get on board. Most of these tests don’t make it out of beta. Google typically won’t push anything live that doesn’t offer at least a 10% improvement to click-through rates.
Here’s a quick recap that Certified Knowledge put together for Ad Extensions:
Finally, here’s my PPC Masters presentation that contains much of the above information:
Browse By Category
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
Learn 3 easy ways to quickly test out variants on low-traffic landing pages to improve performance.
Get a client services view on 10 traits of successful PPC account managers and see which are on your list!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Hero Conf Los Angeles is one short month away. And now we get to share the good stuff. Join us at Hero Conf Los Angeles, April 18-20, for 10 of the most fun and unique networking opportunities in the industry.
In light of the March Madness season it was thought that we could have some fun and review some of the previous PPC Hero articles on the best ways to utilize Excel in order to more efficiently and effectively manage PPC accounts.