A Simple Solution To The Adwords Close Variant Change

Beating Google By Using Google, A Solution To The Close Variant Change

Every year it seems like Google pushes a major update to the Adwords platform. In 2013, the change was enhanced campaigns, a move meant to force advertisers into placing ads on Mobile devices. In doing so, Google took away PPC marketers ability to control targeting for campaigns by device. This year's change is to remove a keyword targeting feature known as close variants. Close variants allowed advertisers to control whether or not they wanted phrase and exact match keywords to show for misspellings, plurals, or slight variations.

At Three Ventures, we’ve noticed a trend here. Google is no longer a search engine company. Google is an advertising company with a search engine. So what do PPC marketers do when changes to the Adwords platform are meant to better Google's bottom line, not the accounts we manage? We default to spending “more time” on negative keywords. Apparently, becoming more sophisticated with using them (Follow up comment on Rand's tweet)...

We completely disagree with the idea of being more "Sophisticated".

Every Paid Search Marketer's Go To When A Major Adwords Update Limits Targeting Abilities

Think about the enhanced campaign change in 2013. What was your strategy? We bet it involved monitoring search term to build out negative keyword lists. Our reasoning is due to the different behaviors by device (Shorter v. longer queries). It's no surprise that many PPC managers went the same route for their strategy given this year's major Adwords update. The strategy of monitoring search term data to build out negative keywords works and drives ROI. However, that's not the part we disagree with. We disagree with the process for executing the strategy. To see search term data, managers have to use the Adwords interface. A lot of time is spent looking up search terms for only one campaign, adgroup, or keyword at a time. We haven't even mentioned technical difficulties like page loads and slow internet. So ask yourself this, are you really going to do this for every account you manage? Are you really going to be able to execute this strategy and not get bogged down? The answer is no. The process for this strategy takes time, a lot of time. This is time we don't have! Time we should be spending communicating with our bosses or clients. So here's the million dollar question, is there a better way to monitor search term data outside of the interface? Yes. Integrating with the Adwords API allows you to monitor search term data. It also removes the limitations cause by using the interface. The best part is you don't need to be a programmer, or have worked with the API before. With our strategy for using the Adwords API below, we have saved hours each week in management time and have seen ROI increase up to 35% for phrase and exact match keywords.

The Adwords API? I'm Not A Programmer, I'm A Paid Search Manager!

Right now, you're probably saying "I've never worked with an API. I'm not a programmer. I don't have a budget to hire anyone, Etc.". All of these become irrelevant when we use a well developed third party tool to access the Adwords API. The specific tool we use is an Excel application that’s very straight forward. It also has one click automation! So the next time you have to review search term data for an account, you can refresh the data with a single click. This only leaves one thing for you to do, analyze search query data to build out negative keywords. However, because the specific tool that we use integrates with Excel, we can use some simple formatting rules to automate a majority of manual analysis.

Super Metrics Data Grabber - Three Ventures Recommended Adwords API Tool

First, Three Ventures does not have any affiliation with Super Metrics. We are very happy customers of theirs, and in turn, are writing about an strategy we developed using it. Let’s talk a little bit about the tool. Super Metrics Data Grabber is an excel application. The tool operates out of three tabs in an excel file (Modules, settings, and Adwords). The first tab is where you select the API you want to interact with (It integrates with other APIs too!). The Second tab is for general settings, which we won’t be using. The third tab is for the API you’re interacting with, Adwords in this case. It is used to define what data you want to access via the API. Unlike the Adwords interface, Super Metrics Data Grabber can pull search term data for any grouping of accounts, campaigns, adgroups, or keywords, all in one report (Tab in this case).

• You build multiple reports for any grouping of search query data by campaign, adgroup, or keyword.
• Each report that you build is generated in a tab, and includes the data you selected to include in it.
• In the configuration tab, you can update all tabs with the click of a button.
• You can then save the excel file. It will house all tabs you have built. And continue to function as programmed upon reopening.

Before we can get started with the “How To” below, you’ll need these to get started:

• $39 a month for a license.
• An Adwords ID (Preferably an MCC to access multiple accounts).
• Excel for mac or windows.

It's literally that cheap and simple. we'll need to discuss a plan for two things: account strategy and excel conditional formatting. Also, thanks to Tracy, we found that Super Metrics' tool for Google Docs also works!

@AlanMorte Follow-up to my question "Can this be replicated in the Supermetric add-on in Google Docs?" - yes pic.twitter.com/I5uKA6hLC8

— Tracy Nixon (@tracy_nixon) November 17, 2014

Before We Get Started With The Super Metrics Data Grabber How To…

We recommend planning out your strategy for every account you manage. Here are some of the questions we have our team ask themselves when planning:

• Who are we doing this for?
• What are the account goals?
• What are the KPIs we need analyze search term performance?
• How often will we be reviewing search term data?

Using the questions from above, we can establish the type of client who owns the account, lead generation or ecommerce driven. This helps us identify their account goals. Using these goals, we pick the Key Performance Indicators (KPIs) that we will use to determine the success, or lack there of, of a search term. Once we know the KPIs needed to analyze search term performance, we then work our way into planning how we can use excel to automate some of our analysis. Here are some questions we ask when planning this out:

• Should we create a column that inputs a value of 'Review' if the cost of a search query is greater than the conversion value from it?
• Should we then highlight your review column with either green or red values depending on whether or not it should be reviewed?
• Should we highlight values for cost per conversion that are higher than the account goals?

These are just some of the main questions that we ask. You will have to tailor these to each account you manage. If you come up with some logic that you think would benefit others, please leave it in the comments below! :)

Beating Google By Using Google - The Super Metrics Data Grabber How To

Now that you have your plan for account strategy and excel automation in place, let's get started. Remember that you will have to repeat this process, including the planning, for each account that you manage. First, go download the tool. Open it with excel, you should see an image like below show up.

From here, login with your Google Account by selecting the Google Adwords Module. Managers use your MCC ID to get access to all of your client accounts. You should see an image like below show up.

Notice that there are three segments in the image above once you login. The first segment is where you select the accounts that you want to pull data from. The second segment is where you choose what data you want to access. The third is where you select your date, report filters, and formatting type. All of which we discuss below. In the first segment, "Select Accounts", select the account for the client you're setting this up for. In the second segment, you will have to select your dimensions and metrics. For the analysis of search terms that triggered an ad, the dimensions that you will need are "Match Type" and "Matched Search Term”. Use “Match Type” as your “segmenting dimension”. When choosing your metrics, revert to your answer from the question earlier "What are the KPIs we need analyze search term performance?". For ecommerce clients, we like to use clicks, impressions, cost per conversion, cost, and total conversion value. For lead generation clients, we like to use clicks, impressions, cost per conversion, cost, and total conversion value from a Google Adwords Goal value. If your client doesn't calculate a goal conversion value in Google Analytics, learn how to properly calculate lead generation goal values in Google Analytics here.. You're setup should look something like below.

In the third segment, you will have to pick your report options. Under 'dates', we'll recommend selecting a relative date. A relative date is “last week”, or “last month”, not a specific date range like 10/10/14 - 11/10/14. By selecting a relative date, you will never have to update the date setting when you perform one click updates, it will automatically update the dates within the relative date for you. Use your answer from the question earlier, "How often will we be reviewing this account?", to choose the appropriate relative date. Your selection should look something like below.

Still in the third segment of 'Report Options', you will need to select your filters. Filters are used to include any grouping of campaigns, adgroups or keywords, that you want to see search term data for. For example, if you wanted view search query data for a client's who sells widgets by color, you would use a filter to segment the campaigns by color. Let’s say you wanted to see data for only yellow widgets campaigns, your filter would look something like: "Campaign name contains yellow widgets" In doing so, you would obtain all of the data you specified, from the account selected, from campaigns that have "Yellow Widgets" in their name. If you don’t already, make sure that campaign names for your clients use the appropriate prefix and suffixes to group campaigns. Your filter should look something like below.

Finishing up in the third segment under 'Formatting', select raw data report. Since we are going to apply conditional formatting, we need the raw data. Your setup should look something like below.

Review all of your selections. If all are correct, select the red button 'Run The Report'. From here a new tab should be generated with your report. It should look something like below.

Awesome, look at all that data you have! From here, build out the remainder of the reporting you will need for this specific accounts strategy. After you have built all of the reports you need, save the file using a format like “Client Name - Relative Date - Search Term Review”. Once complete, let's move into automating the analysis of search term data with conditional formatting.

Automating Adwords Search Query Data Analysis With Excel

To help save time, let’s apply some of our conditional formatting to make our manual analysis easier. Since the whole theme of this post is to save you time, don’t go any further until you read this quick paragraph on how to apply the same formatting to all of your tabs at once in excel. Let’s work smarter, not harder! (Golf Clap) :) Now that you've read how to format all of your tabs at once, let’s revisit some of our questions from earlier.

• Should we create a column that inputs a value of 'Review' if the cost of a search query is greater than the conversion value from it?

Yes! Let's do that by going to the first available column to the right of the report. Click on the column header In the formula builder bar, enter the formula '=IF(V:V>U:U,"Okay","Review")' where 'V:V' is your column for total conversion value and 'U:U' is the column for your cost. Note that your columns for each metric may have a different letter than mine. So if your column for total conversion value is “W”, you would use ‘W:W’ instead of ‘U:U’. Hit enter, and apply to all of the rows in the column. See the example below for reference.

In english, the formula says if total conversion value is greater than cost, input the cell value of good, else input review. For those of you excel gurus, you can create formulas with similar logic that highlights the value if the ROI is below a certain percent or goal. Please do leave your suggestions in the comments below. Now that you have a review row, let's look at our question on formatting it's values.

• Should you then highlight your review column with either green or red values depending on whether or not it should be reviewed?

Yes, let's do that by selecting the whole column that you just created. Next click on conditional formatting. Next, click on 'highlight cell rules', then 'equals to'. A small formatting box will pop up. Select if cell value equals 'Review', format with light red fill and dark red text. You should see the cell values with review turn red like below.

Repeat the same process for cell values that equal 'Good', except this time format with light green fill and dark green text. Your review column should now look something like below.

Awesome! We just took out most of the leg work of reviewing search queries with a negative ROI. Now every time you generate a report, the formatting will remain and all you will have to do is review the search queries labeled with 'review’, which are highlighted red. But what about applying similar logic to cost per conversion data? This leads us into our last question.

• Should we highlight values for cost per conversion that are higher than the account goals?

Yes, just because we may be getting a positive ROI doesn't mean that we're meeting our cost per conversion goals. To get started, select the whole column for cost per conversion, and click on conditional formatting. First, click on 'highlight cell rules', then click 'greater than'. Select if cell value is greater than X (Where X = Cost Per Conversion Goal), format with light yellow fill and dark yellow text. Your report should look something like below.

Now you should see all search queries with a cost per conversion higher than your goal highlighted yellow. PRO TIP: Take notes on consistent search queries that are producing a positive ROI, but are not within your cost per conversion goals, then take the appropriate steps of communicating with your client or boss. Feel free to add your own logic using conditions as desired and please add them as recommendations in the comments :) . Your final report should now look something like below.

And if you followed our advice from earlier about applying all of the formatting to your tabs at once, all of your reports should be formatted! Save the excel file, and repeat for all accounts that you manage. Now the next time Monday or the start of the month rolls around, guess what? One click updates all of your reports!

Simply perform your manual analysis of the automated conditions and implement new negative keywords as needed. For those of you who also manage Bing Ads? There's a module for that.

Beating Google By Using Google - Does It Really Work?

The data that your about to see if for the first month after the close variant change (October 2014). It is compared to October 2013's data. In October for each year there were 22K+ clicks and 491K+ impressions for both exact and phrase match keywords. The results are impressive.

In summary:

• ROI Increase: 25.32%
• Cost Per Conversion Decrease: 5.33%
• Cost Decrease: 14.45%
• Conversion Rate Increase: 17.85%

A Simple Solution To The Adwords Close Variant Change - In Review

First off, for those of you who were at State of Search in Dallas and watched this session, thank you! Alan, and the rest of the team at Three Ventures, spent a lot of time developing this strategy to the close variant change and hope you found it of great value. If you have any comments, suggestions, or recommendations, please feel free to leave them in the comments below. Did you like our solution to the Google Adwords close variant change? Work with Three Ventures on your next project by contacting us.

Beating Google By Using Google - A Solution To The Adwords Close Variant Changefrom Alan Morte