No public Twitter messages.
Archive for the ‘Google AdWords’ Category
4 ASAP Utility functions that will make you love Excel even more…
As a senior production manager at PPC Associates, I spend a great deal of my day working in Microsoft Excel, whether that be manipulating data for reporting or prepping an upload sheet to load into AdWords Editor (Google AdWords’ offline editing program). When you’re dealing with thousands of rows of data that needs to be formatted for a client or uploaded into an account, accuracy and efficiency become vital.
In Excel, there are literally thousands of tools, formulas and shortcuts that can be used to your advantage when managing your PPC campaigns; some have already have been explained by my colleague, Laura Rodnitzky. However, within Excel, ASAP Utilities (which can be downloaded for free, here), is an add-in that works as a supplement to your preexisting tools and formulas and can enhance your Excel capabilities. Here are some of the ASAP Utilities’ most helpful tools for managing your AdWords data:
1. Advanced character remove or replace
This utility allows you to remove or replace advanced characters, some of which Excel does not allow you to change. In Excel, when using the Find/Replace function, the question mark is used to signify all data within a cell, which prohibits you from being able to isolate a question mark and deleting it. However, with ASAP Utilities, you can remove or replace a question mark.
a. In Excel, highlight the cells that contain the question marks.
b. In the “Text” section of ASAP Utilities, click “10. Advanced character remove or replace.”
c. Select the appropriate symbol – in this example, the question mark – then fill in the “Replace each selected character with:” box with a symbol that is not represented in the URL string. Replacing the question mark will allow us to then use the Find/Replace function in the next step. For this example, we chose the dollar sign. Then click “OK” and “Close.”
d. The question mark within the urls should now be a dollar sign. Use the Find/Replace function to find “$*” and replace with nothing. In Excel, * represents all data that is left in the cell (similar to the ?). This will allow you to delete all tracking, along with the dollar sign.
2. Performing Calculations on Selected Data
This utility allows you to perform a specific calculation on a selected range or data. It is most commonly used for calculating new bids. Instead of setting up a new formula juxtaposed with the original bid (Max CPC/CPM) to perform the calculation, this utility lets you update the original data. Note that this can also be done quickly in AdWords Editor, but if the keywords/ad groups can’t be isolated easily in AWE, this is the fastest way to update your bids.
a. In Excel, select the data to apply the calculation to.
b. In the “Formulas” section of ASAP Utilities, click “2. Apply formula/calculation to selected cells…”.
c. Create the formula, and then select “OK.”
3. Delete leading, trailing or excessive spaces
This utility allows you to delete any extra spaces before or after text, as well as extra spaces between tokens (excluding single spaces). The Excel Trim Function, which is located under Excel Tools and Functions, can also be used to eliminate unwanted spaces. However, depending on the format of the data, the Trim Function does not always delete these spaces, whereas the ASAP Utility will work on all types of data. This utility is most commonly used for deleting unwanted extra spaces in keywords, ad groups or ads before uploading into AdWords Editor.
a. In Excel, highlight the cells that contain extra spaces.
b. In the “Text” section, click “9. Delete leading, trailing and excessive spaces”.
4. Insert before and/or after each cell in your selection
This utility allows you to prepend or append data to a cell or range of data, which can be helpful when creating keywords with a broad match modified match type where a “+” must be prepended to each token in the keyword. The simple Find/Replace Excel function allows you to insert a “+” for all tokens by simply finding a space and replacing with a space and “+”. However, we can’t add a “+” at the beginning of a cell, in front of the first token, because Excel will then view the data as a formula, and will show an error. Similar to Excel’s Concatenate Function, we can prepend a “+” to the first token without receiving a formula error. However with ASAP Utilities you can prepend data to the cell itself, instead of creating a new cell to add in the “+”.
a. Select the cells that you want to prepend or append data to.
b. In the “Text” section, click “1. Insert before and/or after each cell in your selection” and fill in the “add before” and/or “add after”. In this example the “+” is added to “add before”. Make sure to check the “Example” section to verify the data looks accurate, and then push “OK”.
There are hundreds of other utilities within ASAP, all of which can be used to make your work in Excel a more efficient and user-friendly experience. I highly encourage anyone who uses Excel to manage their PPC campaigns to take advantage of this free utility and to start exploring all of its possibilities.
Melissa Bregar is a Senior Production Manager for PPC Associates, a digital marketing firm with offices in the Bay Area and downtown Chicago.
Adwords are surprisingly complicated – a side effect of having LOTS of user options and the TON of data google collects and crunches to optimize user experience (ie, the likelihood of internet searchers to quickly find relevant ads).
Infographic by pulpmedia: Awesomely Accessible Lesson in the Basics of Adwords
1) What you pay (CostPerClick) depends on:
* [how much you bid]
* [Quality Score of your ad] – this itself is way complicated – see below
* [Adrank of advertiser in lower rank position]
2) Two Tactics to optimize your Adwords:
2.1) Test Your Ad Copy:
2.1.1) Always have at least 2 ad variations; eg, “Blue Shoes for Sale” and “For Sale: Blue Shoes”. Google will test out your ads’ performance and help you use the higher performing ad copy, so you want at least 2 at a time for testing purposes.
2.1.2) Analyze the data. Pulpmedia suggests experimenting with different metrics: One ad might have slightly higher CTR (click through rate) but significantly lower CVR (conversion rate).
2.2) 3 Easy Bidding Strategies:
2.2.1) Pause Keywords for 30 days when Impressions > 200 and CTR < 1%
2.2.2) Pause Text Ads for 30 dyas when Impressions > 200 and CTR < 1%
2.2.3) Raise max CPC to first page bid estimate for 30 days when Average Position > 1.8 and Quality Factor < 4.
Infographic by Pulpmedia Online Marketing
Quality Scores: Not Simple
There simply isn’t a way to dumb-down Quality Scores: they comprise a set of relationships between many variables, each of which is more or less complex in its own right. That said, PPC Hero makes a valiant, effective effort in his/her(?) Ultimate Guide to Adwords Quality Score.
This guide provides counsel on no less than seven types of Quality Scores. Below is my distillation of this opus.
1. Account-Level QS* is based on how well your entire account – all keywords and ads – have performed in the past.
* High CTR
* High QS for your keywords
* Older accounts have more data, so will have higher QS than newer ones
* Some people argue that you should Delete low QS keywords, others say to Pause them
* PPC Hero advises you to consider the search volume you get with these words before deleting – if you add them back in after deletion, Google counts them as “duplicates”
2. Ad Group QS is the average ad score within a single ad group.
Contributing Metric: Each individual Ad’s QSs
Actionable: Find Ads with the lowest CTR in the group and rewrite them
3. Keyword-Level QS is based on how well your ads do in search queries that are an exact match to your keyword. It is a number from 1-10, 10 being the best.
* At first Keyword QS is based on the keyword’s performance history on Google.com
* After your keyword has appeared enough times in searches (ie, after it achieves “a significant number of impressions in your account”) it will be based on CTR for queries that were an exact match.
* If your impression share is low (impression share = [Number of times your ad appeared/ Number of times your ad was eligible to appear]), increase your bid or daily budget amount
* Broaden your match types or add broad match keywords. Eg, in Advanced Options select “broad match” instead of “exact match”, or change your keyword from “Patio Furniture” to “Outdoor Furniture”
Contributing Metrics: CTR
Actionable: If you have low CTR for a lot of ads, DKI (Dynamic Keyword Insertion) shows the exact query of the user within your ad, typically increasing CTR.
Contributing Metric: Good, relevant content
Writing PPC ads can be fun – using your brain to shoehorn great messaging into a limited number of characters is like doing puzzles. The parts of the copy-creation process that get really tedious are the repetitive acts – checking character count obsessively, combing the text to make sure your capitalization preferences are followed, appending the same word over and over.
There’s good news, though – Excel has some handy features you can use to streamline those mind-numbing steps. Here are my four favorite ad copy time-savers (they work for keywords, too):
1. Length Formula
If you’re writing or customizing a significant number of text ads, create an extra column to the right of any ad text component to automatically measure length (i.e. the number of characters, including spaces, in the cell). Type in =LEN(cell), where cell is simply the cell whose characters you want to count. This feature makes it easy to see if you’re hitting the character count limits for Headline, Description Line 1, Description Line 2, or Display URL in AdWords. You can easily copy the formula down multiple rows and use conditional formatting (see #2) to quickly flag any cells over the limit.
2. Conditional Formatting
It would be impossible to go into all the conditional formatting options in such a short blog post, so I’ll stick to the two main uses for ad text and keyword builds. First off, we can use the “Greater Than…” option to flag any ad text lines that exceed the specified limits. Go to “Conditional Formatting” > “Highlight Cells Rules” > “Greater Than….” and enter your max limit in the dialog box that appears.
The screenshot below shows that the last two headlines exceed the 25-character max. As I modify the text of the headlines, both the length count and the conditional formatting will automatically update.
You can also use the “Less Than…” option if you want to identify ad text components that are significantly below the character limit. For example, you may want to flag any Description Line with fewer than 20 characters – perhaps there is additional, relevant text that can be added to use up more of the real estate and enhance the message you are trying to deliver.
Another useful conditional formatting tool is “Duplicate Values…” which is also found in the “Highlight Cells Rules” section. You can use the “Duplicate Values…” option to flag repeated keywords in keyword builds, duplicate ad text, etc. In the screenshot below, the two headlines flagged in green are duplicates.
3. Capitalization Functions
Another useful feature for ad text is the “proper” function, which capitalizes the first letter of each word in a cell. Type in =proper(cell) to use this function.
There are a couple things to consider when using “proper.” First of all, the output appears in the cell where you place the formula, and the output itself is a formula. In order to manipulate it, you’ll need to copy and paste as special (values). You may want to paste it back into the cell with the original text, so you maintain the headers (“Description Line 1” in the example above). The other thing to keep in mind is that acronyms (such as PPC) will be modified so that only the first letter is capitalized. You’ll need to go back and fix any acronyms – fortunately a simple find/replace can do the trick.
There are additional capitalization functions that may be useful, depending on your preferences for keywords, ad group names, and ad text. The “lower” function makes every word lowercase, and the “upper” function makes all letters capitalized.
The last feature I’ll hit on today is “concatenate,” which allows you to join the contents of two or more cells and/or cells plus text. For ads, “concatenate” is a great tool for appending tracking parameters to destination URLs, or for adding text to existing ads, among other things. For example, if you want to run an ad test with the word “Free” added to the beginning of every headline, the concatenate function would let you do this easily in Excel.
Note that, like the “proper” and “lower” functions, the output is placed in another cell and needs to be copied and pasted as values in order to manipulate the text.
Another great use for “concatenate” is to append the plus sign before broad match modifier keywords – however, it will only append the sign to the first token in a keyword. For the remaining tokens, simply use find/replace to find spaces and replace with space and plus sign.
There are several more functions and tools in Excel that will make your life as a search engine marketer much easier, but these are some of my favorites for ad text creation (and they can be handy for keyword builds, too).
Do you have any to add? Leave a comment!