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.
The Download
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
- Campaign
- 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:
- Campaign
- Ad group
- Headline 1
- Headline 2
- Description
- Path 1
- Path 2
- Final URL
- Label
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.
Conclusion
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.
