Set Up Account-Wide PPC Promos In Less Than 5 Minutes
January 4, 2017
We all need that friend to lean on from time to time, and there is no difference when it comes to a highly active ecommerce PPC account. A plethora of deals and promotions come and go throughout the year, especially around the holidays. During these hectic times, it is easy to spread account initiatives too thin.
In this post, I’ll discuss not only how to avoid promotion neglect, but also how to get your in-demand management time back. I will show you how a promo template can be that amazing friend for your ecommerce PPC account (and you) to lean on.
The template setup does take some time depending on how savvy you are in Excel, but don’t fret! PPC Hero boasts some of the best PPC Excel posts in the game. By using this awesome insight and a couple of my own tips, you will be good to go on all aspects of the template setup.
First, you need to download all of your active ads. You’ll want to make sure you have “All but removed” selected in the Campaign and Ad Groups tabs on the AdWords interface. Then, go to your “Ads” tab and make sure it is set on “All enabled.” Select only “Search campaigns,” and then download the ad report.
Condensing The Data
Once you open the downloaded report, you will have way more information than what is needed. First, delete every column except
- Ad group
- Final URL
As a note, if you want to recycle any copy from your existing ads you will need to also keep the existing columns (i.e. Headline 1, Headline 2, Description, etc.). Also, delete the “Total” rows at the bottom of the data. Now, you should remove as much excess information as possible. Highlight all of the data and remove duplicates. To do this, simply select the data in Excel, navigate to the “Data” tab and hit “Remove Duplicates.”
You will most likely still have some overlap after removing duplicates. Follow these quick steps to weed out the last bit of excess:
- Copy and paste the “campaign” and “ad group” columns into a new sheet.
- On the new sheet, add a filter and sort the ad groups in ascending order, followed by the campaigns in ascending order.
- On the same new sheet, you will remove duplicates again. You should now be down to a list of all your desired ad groups (with no duplicates).
- Add a column for “Final URL” and run a VLOOKUP to find the correct URL.
- This is the trickiest part of the process. However, thanks to an awesome tutorial from Hanapin pro Rachael Law, VLOOKUPs have never been easier:
- But if that doesn’t help, here is the VLOOKUP for this specific request in English: =VLOOKUP(the ad group you need the URL for, the columns that the ad group and final URLs are in (downloaded data), the number of the column the URL is in (relative to where the “table_array” range starts), false)
We use “false” at the end to let Excel know that we only want exact matches on the URLs. This formula should populate a URL. Then, drag the formula down through the rest of the ad groups. Copy the data and use “Paste Values” to get rid of any existing formulas.
Customization – The Easy Part
The VLOOKUP will help you repopulate the final URLs after removing duplicates and filtering. Once you have the accurate campaign, ad group, and final URLs populated, duplicate the entire set (so there is two of everything) and filter again in ascending order. I use two versions of the promotion as a standard, but you can either skip or add to this step depending on how many versions of the promotion you want to create. For the sake of this post, I will continue as if we are using two versions. At this point, your template structure should look similar to this:
Once you make it to this step, the hard part is over! You now will need to add the necessary column headings to create an Expanded Text Ad. I find it best to have them in this order, starting in Column A:
- Ad group
- Headline 1
- Headline 2
- Path 1
- Path 2
- Final URL
It is also wise to add in character counts after the headlines, descriptions, and paths to ensure you follow the appropriate character limits. This can be done by using the formula “=LEN(cell you want the character count for).”
Your template structure should now look like this:
At this point, your promo template can be as general or as custom as you would like. I typically will use the same Headline 1, Path 1, Path 2, and obviously the same Final URL, across all promotions. I then color code the cells that will change with each new promotion. Here’s an example:
With this setup, I would know to always change anything with color. Typically, I will use the same description for each promotion, and always the same label, which is why they are one color. There are two colors for “Headline 2” to remind me that I will create two versions. Here is what the final, filled-in template would look like:
AdWords Editor And Interface Automation
At this point, the template is now ready to upload in AdWords Editor or the AdWords Interface. As a note, the character counts should automatically not import with the rest of your ads, if you choose to use Editor (recommended). Also, you may be wondering why I snuck in a “Label” column. The answer is because labels are the key to making this process easy and quick! If you aren’t using labels in your accounts, then start today as they make life much easier. They allow you to pause ads in bulk, enable in bulk, and set up automation in bulk. To make these bulk promo decisions go to the “Ads” tab in the AdWords interface and filter by label:
From there you can find the promo label and select all of the ads in the promotion. Then, you can set an automated rule to enable ads when the sale begins and pause ads when the sale ends. This is especially beneficial during the holidays where you can be proactive and set up promos ahead of time. It also helps with managing promotions when you are away from the office, on a holiday, vacation, etc.
Setting up a promo template can take some time and could be a bit of a challenge depending on your skill level. However, even the most novice PPC advertiser can follow these steps and put a highly efficient system in place to create and run account-wide promotions with little effort. After your template is in place, simply transfer the promotion ad copy into your template and upload into AdWords Editor, all in just five minutes. Give it a try and get some much-desired management time back, while consistently keeping up with your account’s busy promotion schedule.
Browse By Category
3 Easy Ways to Optimize Landing Pages With Low Traffic
Learn 3 easy ways to quickly test out variants on low-traffic landing pages to improve performance.
10 Traits Of A Successful PPC Account Manager: A Client Services View
Get a client services view on 10 traits of successful PPC account managers and see which are on your list!
A Pool Party, Lucky Strike, and Hundreds of PPC Geeks
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.
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
March Madness Continues: What Excel Tool/Function Rules Them All?
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.
5 Second Survey: Do You Trust Your Metric Sources?
Work Smarter! Analyze Data More Efficiently with Google Sheets
In this webinar recording, Hanapin Senior Account Analyst Rachael Law will cover how to get your data into Sheets, as well as some useful formulas to help you manipulate your data for key insights.