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.
PPC (pay-per-click) marketing is a form of online advertising in which advertisers accrue costs when users click their ads. Advertisers bid on keywords and audience types, which a search engine matches to user search queries and predefined lists, and then displays ads.
PPC is used for all types of campaign goals, including:
PPC is all about relevance. Users are searching for specific products, services, and information at any given time. Advertisers have the ability to show a targeted ad at the exact moment this search is occurring. For example, if a user searches for “blue running shoes,” an advertiser can show an ad speaking to “blue running shoes.”
Through both targeting settings and account structure, advertisers can run successful PPC campaigns as long as relevance is paramount.
Run on Google, Search Partner sites, and Display Network sites, AdWords is the largest pay-per-click platform. AdWords was launched in October 2000 and has gone through several iterations over the last 15 years. AdWords is geared toward the entire spectrum of companies from small business to Fortune 500.
Similar to AdWords, Bing Ads is a pay-per-click platform showing ads on the Bing and Yahoo networks. The platform also utilizes Search Partners. Bing Ads is primarily keyword based advertising. As of 2014, Bing Ads has an audience of 553 million unique searchers worldwide*.
*Data taken from advertise.bingads.microsoft.com
Advertisers begin by choosing keyword themes and creating individual campaigns. For example, a PPC professional may create a campaign with the theme “Coffee Tables”. Within this campaign are themed subcategories, called ad groups. These ad groups may include:
Oval Coffee Tables
Long Coffee Tables
Round Coffee Tables
Each ad group then contains themed keyword variations. For example, the “Oval Coffee Tables” ad group may contain these keywords:
Oval coffee tables
Coffee tables oval
Oval coffee tables on sale
Every keyword must be assigned a match type, which defines the queries for which ads will show. There are seven keyword match types:
Exact – Query must be typed in exactly
Exact (Close Variant) – Query must be typed in exactly, but can include misspellings or other variants
Phrase– Query must be typed in correct order, even if there are additional terms before or after the query
Phrase (Close Variant) – Query must be typed in correct order, even if there are additional terms before or after the query. Query can include misspellings or other variants
Broad – Query can be typed in any order and will potentially show ads for similar searches
Modified Broad – Query can be typed in any order, but must include terms that contain a plus sign
Broad (Session-Based) – A form of broad match that takes into account other queries from that user’s search session
Here is a table of the match types, keywords, and potential search queries.
Along with the positive terms, negative keywords can be added to help eradicate unqualified traffic. For example, someone who searches for “free coffee table” isn’t looking to buy. By adding “free” as a negative keyword, the advertiser’s ad will not show when a query containing this term is typed. For a company selling high end products, “bargain” or “cheap” related terms may make good negative keywords.
Audiences are groups of users segmented in a variety of ways. Most often audiences are used in remarketing. Audiences can be created based upon specific pageviews, time spent on site, pages per visit, and more. Similar to keywords, audiences are bid upon based on relevance. For example, advertisers may bid more to remarket to shopping cart abandoners vs. homepage viewers.
Once ad groups are created and the keywords chosen, ads can be written. Ads should include the targeted keyword theme, any value ads, and a call to action.
AdWords text ad structure and character limits are as follows:
Headline – Up to 25 characters (including spaces)
Description Line 1 – Up to 35 characters (including spaces)
Description Line 2 – Up to 35 characters (including spaces)
Display URL – Up to 35 characters (including spaces)
Bing Ads text ad structure is similar, but description lines 1 and 2 combined can be up to 71 characters.
Ads cannot contain excessive capitalization or punctuation. Additionally, the root display URL must match the root destination URL. For example, a display URL of www.abccompany.com is required to lead to a page on the www.abccompany.com domain.
Every ad group should contain at least two ads for testing purposes. Here is an example of an “oval coffee tables” ad:
Upon clicking, visitors should be taken to a page that continues the ad messaging. This is called the landing page, and it should contain a selection of oval coffee tables with messaging around free shipping.
Mobile specific ads should be written for users conducting searches on smartphones. Mobile ads contain the same requirements, but should be written in a more concise manner. Mobile sites are generally condensed, easy to use versions of the desktop site. Mobile ads should follow the same practice.
Here is an example of a mobile version of the “oval coffee tables” ad.
The messaging makes it clear that the ad is meant for a mobile user. Additionally, the combined character count of descriptions lines one and two is less than sixty characters. Sometimes when the character count is over sixty, the ad will be truncated. To ensure this truncation doesn’t happen, write mobile ads with no more than sixty characters between description lines one and two.
When creating a mobile specific ad you’ll need to select “Mobile” as your device preference.
Product Listing Ads are square units used in eCommerce PPC campaigns that contain product titles, images, and prices.
PLAs utilize Google product feeds, and must be connected to a Google Merchant Center account. Bing Ads contains a similar feature called Product Ads that also requires the use of a Bing Merchant Center account.
Advertisers can run Display Network campaigns that utilize image ads. These ad units show within and around the content of millions of sites across the Display Network. There are 19 total sizes for image ads.
Search Network – This is the most common targeting option. The Search Network consists of google.com and Google’s Search Partners such as aol.com, amazon.com, and many more. The Search Network is primarily keyword based advertising. In other words, searchers type in queries for which ads are shown.
Display Network – This network consists of millions of sites that agree to show Google text, image, and video ads. These ads are shown within the site’s content and don’t utilize traditional keyword based targeting, but rather audiences and demographics. For example, a user may visit a blog that speaks to the history of coffee tables. Even though the user isn’t necessarily in a buying mode, the content is relevant to coffee tables. The user may or may not click the ad, but is ultimately now aware of the brand.
Search Network with Display Select – This targeting option is a combination of both networks. Campaigns are setup as you would a regular Search Network campaign, however, ads can selectively show on the Display Network. The caveat is that Google determines when and where ads may perform best, taking control away from the advertiser. The preferred option is to break out campaigns by network, but Search with Display Select is worth testing.
Shopping: Product Listing Ads – PLAs are shown on Google and Bing. After submitting a product feed to Google Merchant Center, Shopping campaigns can be created in AdWords. Advertisers create product groups to which they can bid on various feed attributes. These attributes include:
Shopping campaigns do not contain keywords. Both search engines match user queries to the product they deem most relevant. Thus, it is important to ensure all products have accurate information as well as clear titles and descriptions.
Ads can be shown across all devices, including:
Desktops/laptops and tablets are considered similar enough by the search engines that the same bid is applied to these platforms. Mobile devices can have a bid modifier. For example, if the bid is $1.00 and the mobile bid modifier is set to -50%, the bid on mobile devices becomes $0.50. A bid modifier of 150% would set the mobile bid at $1.50.
PPC targeting is extremely granular, going down to the zip code level. Advertisers have many options to ensure that their ads show only in desired locations. The example below showcases a campaign only targeting Iowa.
Bid modifiers can be setup by location. For example, advertisers targeting all 50 states can set separate modifiers by location.
Advertisers have the ability to run ads only at desired times. Whereas an eCommerce campaign may run ads 24/7, a brick and mortar store may only show ads during business hours. Ad scheduling allows easy management of when ads will show.
Also, just like device and location, bid modifiers can be set for both days and hours. For example, weekends may drive more revenue so bids could be 20% higher on Saturdays and Sundays. Or, poor quality traffic comes in from midnight to 4 AM so the modifier might be set at -80%.
Each individual campaign is allowed a daily budget. Budgets should be created in accordance with account goals.
There are two options for which ads are delivered: standard and accelerated. The standard delivery method shows ads evenly throughout the day. This option is good for advertisers who may have budget restrictions and want to ensure their ads show throughout the day. Depending on the budget concerns, ads will not show at all times. Accelerated delivery method shows ads until the budget is depleted. This option is best for advertisers who may not have budget restrictions and want to ensure their ads show for every query.
There are four options for which your ads will be delivered by Google:
Optimize for clicks – Delivery is based upon ads expected to produce higher click volume
Optimize for conversions – Delivery is based upon ads expected to produce higher conversion volume
Rotate evenly – Ads will be rotated evenly for 90 days and then the better performing ads will be seen more
Rotate indefinitely – Ads will always be rotating evenly
Advertisers have the ability to create conversion goals in order to gauge account performance. Both platforms provide code snippets that can be placed on key pages— generally order confirmation or thank you pages. Advertisers are able to determine whether ad clicks are turning into conversions.
AdWords allows many types of conversion tracking, including:
AdWords accounts can be linked to Google Analytics accounts to provide information on post click behavior. It is highly recommended that these accounts be linked in order to get a more complete picture of the conversion funnel. In order to link the accounts, the AdWords ID number must be entered in the “Admin” section of Google Analytics.
Once added, the connection can be confirmed by navigating to “Account Settings” and then “Linked accounts.” The “View details” section will show the link.
The account can also be linked to Webmaster Tools, which will show how ads and the organic listings perform.
In order to run Google Shopping campaigns, or PLAs, the Google Merchant Center account must be connected to AdWords. Similar to Google Analytics, the AdWords ID must be entered into the “Settings” section of the Merchant Center account.
Once connected, Merchant Center account information will show within the “Tools” section in AdWords.
Setting up Google remarketing code is a fairly simple process. Within the Shared Library is the remarketing tag.
The code needs to be copied and placed on every page of the site before the closing </body> tag. The code can then be validated by going back into the Shared Library.
Ad extensions are additional links and details that show supplementary information about your business to enhance the basic PPC ads. Certain ad extensions are manual choices, which you can control. Search engines may also automatically generate some ad extensions. The main advantage of ad extensions is that they help improve the click-thru-rate (CTR) of the ad headline because the ads are larger in size and therefore more prominent on the search engine results pages (SERPs). There are many ad extensions.
Sitelink extensions are additional links to help searchers navigate deeper into your website. Sitelink extensions are manual additions, and can be added within the interface or in the editor, both in Google and Bing. The actual sitelinks should be relevant to the search term.
Sitelinks can also include descriptions, known as enhanced sitelinks.
Location extensions are great for brick and mortar businesses. Location extensions show the business address and are available in Google and Bing. Google requires that a Google My Business account is setup and connected to AdWords.
Call extensions are available in both Google and Bing and give advertisers two possibilities. On mobile devices, call extensions supplement ads with the ability to click-to-call, giving mobile searchers an easy way to call the business.
On desktop ads, call extensions allow a phone number to show within the vicinity of an ad. Again, users have the option to call the number instead of clicking the ad.
App extensions or application extensions is a feature in Google. This extension works great for businesses looking to promote application downloads and engagement.
Consumer ratings are extra annotations that promote business ratings based on various customer surveys. This extension is only found in Google and is automatically populated. Google pulls these ratings from trusted sources and specifies that businesses must have at least 30 unique reviews in order to show. Consumer rating extensions are determined only for certain businesses and industries based on Google’s discretion.
Seller Ratings are similar to consumer ratings in that ratings are automatically pulled by Google based on trusted website reviews. Google will show these ratings when a business has had at least 30, 4 plus star reviews over a 12-month period.
This extension also takes into account the overall business process. Businesses that successfully roll out rating and review extensions create processes whereby they ask customers for feedback on a regular basis. Search engines also have processes to identify fake reviews as well. Part of this process involves a natural flow of ratings. For example if a business were to suddenly get fifty 5-star ratings in single a month, it would indicate to the search engines the potential for fraudulent reviews.
Social extensions populate based on your business Google Plus account. They show how many followers your business has on Google Plus.
Working in PPC rocks. The search engines want to maximize revenue. As account managers, we are in charge of this spend. The search engines are incentivized to keep the people managing budgets happy, while providing tools to justify increasing spend. Here is a preview of various tools that can support an effective PPC campaign.
Change history can be found in both Google AdWords and Bing Ads. The change history catalogs past updates, from bid changes to sitelink additions. The data includes the alias or login email that made the modification and the date and time of the change. The change history can be exported.
The Keyword Planner is a tool delivered through Google AdWords, which is used to discover and plan your campaigns, keywords, and ad groups. The tool also provides performance data approximations. You can use the data in the Keyword Planner to estimate starting bids and budgets for your PPC accounts.
The Display Planner is a tool used when architecting Display Network campaigns. This tool lists websites, keywords, topics, and interests that your target audience is likely to utilize. The Display Planner recommends thousands of fresh websites, apps, and video channels.
The Ad Preview and Diagnostics tool helps establish why your ad might not be displaying for an individual search term. This tool can be used in the AdWords Interface. Enter the location and language and the tool will run in the background and populate results.
Labels are like Post-It notes and built-in documentation for campaigns, ad groups, keywords, and ads. Labels can be used for anything, from ad creation dates to top performing keywords. Labels are especially useful in accounts with multiple account managers. Each account manager can filter based on labels to see their campaigns under management.
Automated rules are unique to AdWords. These rules are set using any number of performance criteria and can run on a schedule. The rules are meant to make account management less tedious, but should never fully replace the human touch. It is also worthwhile to set some type of performance threshold or safety rule to account for performance degradation.
The AdWords shared library allows advertisers to manage changes across multiple campaigns.
Ads in the shared library can be used throughout several campaigns. This way if you want to adjust a few items you avoid using the AdWords Editor or making many updates in the interface.
Audiences can be added to Display, Remarketing, and Remarketing Lists for Search Ads campaigns.
Bid strategies are a form of automated bidding where control is handed to the search engine based on predetermined goals. Bid goals are setup within the shared library and the search engine changes the auction bids through algorithms. There are several different strategies that can be employed at the campaign and ad group level.
Enhanced CPC – A bidding feature where your max bid is spontaneously raised for you if Google believes that the click will convert. Your maximum bid using this bid strategy can be up to 30% higher when your ad is competing for a spot on the SERP. If Google does not think that your ad will convert then your bid is decreased in the auction. The last part of the Enhanced CPC bidding feature is that your bid will stay at or below the maximum bid you set for certain auctions. Google’s algorithms evaluate the data and adjust bids.
Target Search Page Location – This flexible bidding strategy changes bids so your ads can be consistently shown either at the top of the page or on the first page of the SERPs. This strategy is great when your goal is to maximize the number of people who see your ads.
Target CPA – This strategy sets bids to maximize conversions at your target cost-per-acquisition. This strategy works well when wanting to keep costs down while growing conversions.
Maximize Clicks – A flexible bid strategy that will set bids to help you get as many clicks as possible while maintaining spend. This strategy is useful when click volume is the primary goal.
Target Return on Ad Spend (ROAS) – Some businesses, particularly eCommerce, place higher value on certain conversions over others. The target ROAS is a strategy that sets bids to maximize conversions within a target ROAS goal
Typically, daily budgets are setup for each campaign, but sometimes you want these funds to shift between campaigns depending on what’s working. The shared budget feature saves the time spent managing and monitoring individual campaign budgets. Using a shared budget, AdWords will adjust the budget. There is one daily amount for the entire account or a group of campaigns within the account.
Negative keywords can be managed through the shared library, saving time adding negative keywords to multiple campaigns. Most account managers have certain lists of adult terms or industry exclusions that are standard for an account. Maintaining the lists in the shared library saves time. The lists can be added account wide or to selected campaigns in the account.
Much like negative keywords, in Display campaigns certain websites convert poorly. Adding a list of campaign placement exclusions will allow the list to be shared across multiple Display campaigns.
Search query reports are extremely useful and are one of the top optimization techniques. SQRs can be run in both Google and Bing and can be used to find irrelevant search queries to be added as negative keywords. This report can also be used to unearth new ideas for keyword expansion. It is suggested that SQRs be run at least twice a month.
Placement reports show the websites within the Display network where your ad was shown. You can adjust bids for certain websites or exclude websites that are producing non-converting traffic. It is best practice to run a placement report at least twice a month.
Found in AdWords, this report is used to determine what companies are competing against your business in the search auctions. The Auctions Insights Report is a great place to look at your impression share relative to the competition, and then determine if you should increase bids and or budget to become more competitive in the auction. Another useful feature of this report is determining if you are competing against businesses in other industries. This could mean you need to add negative keywords to your campaigns or reconsider some of the keywords on which you are bidding.
When running reports in the search engines you always have the option to further segment your data. You can segment by device, time or even Google +1 annotations. There are many different options to choose from. These can be located on many of the tabs in AdWords. Some segments will only apply to certain sub-sets of data, and other segments can be found once you download the report from the interface.
Filters can be created and saved in AdWords and Bing Ads. They are especially useful when reviewing large campaigns and trying to break them down into more digestible pieces for analysis. You can filter based on all types of performance data and then make bid changes to the filtered group or other actions based on your goals.
Columns are another cross advertising platform feature that shows pre-determined metrics. Columns can be edited on all tabs within the engine interfaces. You can add data such as assisted conversations, competitive impression share metrics, conversion data calculations, and more. Columns act as a great way to customize the interface and view for your goals and analysis needs.
The dimensions tab is one of the most powerful locations within the AdWords interface. Data can be segmented by all metrics and features available, including geography, time, or device. Some clients have daily conversions that fluctuate enough that you always want to look at conversions on a weekly or monthly basis, whereas for other accounts looking at daily numbers is sufficient. The dimensions tab allows for easy to see segments of data.
The Display Network is a great way to drive a larger volume of traffic than Search. Average cost per clicks tend to be less expensive on the Display Network, but the the traffic isn’t always as qualified. It’s important to test all of the Display Network targeting options to ensure quality traffic is coming in.
Using keywords on the Display Network is called contextual targeting. These keywords match your ads to websites with the same themes. For instance, the Display keyword “shoes” will match to any website that Google deems is related to shoes. These keywords aren’t used as literally as Search keywords, and they’re all considered broad match. Keywords in an ad group act more like a theme.
Display keywords can be used alone, or you can layer them with any other targeting method to decrease scope and increase quality.
Placements are the sites on which your Display ads are shown. You can opt to let AdWords choose placements based on your targeting methods, or you can choose to manually select placements for your ads to appear. Manually selected placements are called “Managed Placements.”
One common way to manage Display campaigns is to pull regular placement reports, in which you identify both great and poor performing sites. You add the sites with great performance to your “managed placements” to ensure you’re capturing a high share of impressions on that site, and you exclude the poor performing placements.
Remember, depending on your targeting methods, the placement might not be that important. If you’re targeting the user through interests or remarketing, the placement is just where that user visits. Of course, some sites will still perform better than others, but keep in mind which targeting method you’re using when evaluating placement performances.
Display Topics are themes you can select, which Google will then use to find sites that match that theme to display your ads. Topics work in much the same way as Display keywords, except that you select the theme instead of creating a theme with your own keywords. You also have keyword level bidding with Display keywords, and only Topic level bidding with Topic targeting. Placement reports for Topic targeting should evaluate if the site actually matched the intended theme for the placement.
A way to determine which Topics to target is by deciding which themes match the services or products you are marketing. However, if you have less of a direct response goal and more of a branding or impression goal, you could also select Topics that you believe your targeted audience would be interested in.
Interests are very similar to Topics. In fact, they are the same themes. However, the key difference is that Topics target websites and Interests target users. Google gleans user interest based on browsing history or self-selected interests if they’re logged in to their Google account. This allows your ads to appear on whatever site someone with your targeted interests is on, even if that site isn’t related.
Gender targeting works similarly to interest targeting. It targets the gender of the user based on information Google has gleaned from their browsing history or their self-selected gender if they’re logged into Google. If you are marketing a service/product that has different performance by gender, this option is a great one to test.
Age targeting works the exact same way as Gender targeting, except it targets the user’s age instead of gender. With both of these demographic targeting methods, you’ll want to exercise caution. There are many situations, such as a family computer, in which Google could be wrong about their gleaned demographics.
Rounding out the demographic targeting options is the newest addition, “Parental Status”. This option targets users based on if they have children, which can be a huge win if you’re marketing a product or service to parents, like selling baby furniture.
Display text ads are written just like Search text ads, but these tend to have lower CTRs than other Display ad types. This format isn’t as noticeable, but can garner better direct response rates since clicks are usually more deliberate and result after reading the ad.
Image ads are the more traditional Display ads, since they are unique to this network. You can create your own image ads through your own software or in the AdWords interface with the Ad Gallery.
For effective image ads, call-to-actions should be explicit. Including prices, promotions, and exclusives is effective as well. Testing different engaging images and ad sizes will be important when using image ads.
The Ad Gallery allows you to create image ads, dynamic ads, engagement ads, and video ads in any Display campaign. It offers customizable ad styles and industry-standard ad templates. It’s an easy and free way to get effective image ads for your Display campaigns.
The theory of remarketing is that users who have visited your site are both more likely to convert upon visiting again and less likely to click your ad a second time if they aren’t already considering converting.
The remarketing code lives under the “Shared Library.” You’ll place that code across all pages of a site, and then set up remarketing lists to target based on what page(s) users did or did not visit, or based on the dates they did or did not visit a page or set of pages.
You can create combinations of remarketing lists. For instance, if you have a subscription-based service that needs renewal every 30 days, you could create one list for visitors of your “thank you” page that lasts 30 days and another that lasts 60 days. You could target the one that lasts 60 days while blocking the 30 days one. This would target people who have visited the “thank you” page 30-60 days after that conversion, and you could use ad copy like “time to renew your subscription.”
Another classic example of a custom combination is targeting people who have visited the cart of an eCommerce site, while excluding those who have already purchased an item. This strategy allows you to target people who came close to buying, but didn’t. They are often persuaded into purchasing with an ad that gives them a bit of a discount or free shipping.
Ad copy for the Display Network will be written similarly to ad copy for the Search Network. It needs to be compelling, have a call-to-action, and specifically lets users know what your product/service is. The more related to your landing page, the better your Display quality score will be.
You must have a Google Merchant Center to utilize Dynamic Remarketing. You link this feature with your Merchant Center data feed, and it can use product images and information to customize ads with products users have viewed.
You can customize dynamic ads with the style elements of your brand, or simply upload your logo and auto-optimize layouts through AdWords.
Remarketing Lists for Search Ads (RLSA) work like Display remarketing in that it targets previous site visitors based on one or more pages they did or did not visit. You can create custom combinations here as well. However, we are targeting users through Search instead of Display.
The implications here are that you could use more broad keywords and higher CPCs if you know the audience is more qualified. For instance, an eCommerce site might want to bid on “Christmas Gifts” if users have already been to their site and added an item to their cart.
To set-up RLSAs, you simply need to navigate to the “audiences” tab in your Search campaigns. Here, you can manage remarketing audiences, both positive and negative, and their bids.
Dynamic Search Ads use your website to dynamically create ad headlines and landing pages to target relevant searches automatically. So, if you have a section of your website dedicated to purple shoes and someone searches about that, they’ll be served a purple shoes ad that takes them to the most relevant page.
Theoretically, advertisers with a ton of different pages, such as large eCommerce sites, could use this ad type to fill in the gaps where they haven’t specifically target with keywords.
You’d avoid cross-contamination of search queries by placing all non-low search volume keywords currently enabled in your account as negative keywords to your Dynamic ads.
You would select this campaign type in the settings in order to create. You’ll need to ensure you create a dynamic ad type as well. You can write your own ad copy and allow AdWords to create a dynamic headline and landing page.
You can focus on your targets so you can write targeted ad copy and bid/budget appropriately. You can do this based on categories, URLs, page titles, or page content. For example, you could set a target for all URLs with “purple-shoes” in the string. That would allow you to know all searches and ads will be about purple shoes, so you could write ad copy and bid accordingly.
You’ll want to exclude pages on your site that you don’t want used for ads. For example, any pages that include the words “out of stock”. You would use the “+ Exclusions” button under the dynamic ad targets sections of the auto targets tab.
This guide gives you a great start in the world of PPC. It touches on everything you’ll need to start or get into soon after launching your PPC accounts. However, the unofficial motto of the PPC world is “always be testing.” Make sure that you test different features and strategies for your account. Every account is unique, and will have its own reactions to different features and strategies. Of course common practices exist because they’re considered to work the best for most accounts, but you’ll never know until you test.
Of course, also keep coming back to PPC Hero to find more advanced and detailed case studies and how-to guides!
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.