As digital marketers of our clients whether internal or external, or for your own company, there are times when we need to make mass edits to the campaigns' keywords and ad copies because editing them one by one is simply too tedious. This is when equipping yourself with useful excel skills becomes important, as it could help you to automate some of these campaign editing. These are the 3 excel skills that you need to help you speed up your campaign mass editing process.
1. Concatenate and Pivot Table Functions for Ad Copies Reporting
When your download your AdWords report for the performance of your ad copies, they are normally categorised by each ad copy performance for each ad group. When you have thousands of ad groups and thousands of ad copies, with about 50 variations of ad copies for all your ad groups, what is an efficient way of consolidating say, top 10 performing ad copies for your client?
Use the Concatenate function, which basically connects a string of text. For stringing the text in each cell, depending on the version of Microsoft Excel you are using, you could either use ; or ,. The syntax for Concatenate is =CONCATENATE(text1; text2; text3) or =CONCATENATE(text1, text2, text3).
Start by downloading the report for your ad copies, insert a column and Concatenate the headlines, description and paths for all the ad copes in your excel report. This essentially generates a unique entry for each variation of ad copy and allows you to identify easily which ad copy you are looking at. Then select the marketing metrics you want, with the concatenated text and insert a pivot table. Remember to label your table headers.
Drag the metrics to the Values quadrant and drag the header for your concatenated text to the Rows quadrant. For metrics like CTR, CVR, CPA, click the drop down menu of the items in the Values quadrant and go to Value Field Settings > Summarise Value Field by Average to obtain the average of the CTR, CVR and CPA of the variation of ad copy. Doing a pivot table automatically collates the metrics for each variation of the ad copy which you concatenated.
To select the top 10 performing ad copy based on a metric, click on the drop down menu on the top left corner of your pivot table, then Value Filters > Top 10... > toggle the number of ad copies you want to shortlist and basing on which metric. With this, you can then easily list down the top performing ad copies for your client.
2. Find & Replace and Concatenate Functions for Mass Editing Ad Copies and Keywords
The Find and Replace function, which you could easily launch by using the keyboard shortcut of CTRL + F, is extremely useful when you want to make mass edits to a large number of Expanded Text Ads (ETAs) and keywords.
For instance, you would like to edit a particular section of your generic ad copy, but it is used in 3,000 ad groups, which means that you need to edit each of these 3,000 ad copies. Instead of manually editing your ad copy 1 by 1, simply use Find & Replace and mass edit your ad copies. This works well for editing sections of your landing page URL too.
You could also use this excel skill for mass editing keywords. For instance, you would like to edit broad match modifier keywords (+generic) to a broad keyword (generic). By using the Find and Replace function, you could select the cells you want to find and replace, find + and replace with a blank. For editing broad match modifier keywords (+generic) to exact keywords ([generic]), you could use Find and Replace function to make the keyword a broad form (generic), then insert 2 columns in your excel sheet for the brackets as follows, then use the concatenate function:
The Concatenate function basically connects a string of text, so it is useful when you are mass editing keywords to exact keywords. For stringing the text in each cell, depending on the version of Microsoft Excel you are using, you could either use ; or ,.
Note that because of the introduction of the new Expanded Text Ads (ETAs), we can no longer edit standard text ads. So this is only for ETAs.
3. LEN Function for Ad Copy Character Count
The LEN function with the syntax of =LEN (text) counts the number of characters in the cell you assigned the formula to, which is useful in ensuring you are keeping to the character count limits of Google AdWords' ETAs when you are crafting your ad copies in mass volume to upload to AdWords Editor.
For simplicity, this is the character count limits of ETA for each corresponding field:
Note that for Mandarin characters, it counts as two characters in AdWords. So when you are optimising your AdWords camapign for the Mandarin-speaking markets, be sure to follow the character count as above.
As a brief explanation, headlines 1 and 2 are the header sentences of your ad copy, while the 80-character description is the body of the ad text. Paths 1 and 2 are optional, and are to be used as part of your display URL in conjunction with your main URL. Optimising your path URL gives your customers an indication of what your landing page is about. Normally, a path URL which is relevant to the search of the customer would land a better CTR.
Essentially, the 3 Excel Skills that could help you boost your AdWords optimisation are:
- Pivot Table and Concatenate Functions for Reporting
- Find & Replace and Concatenate Functions for Mass Editing Ad Copies and Keywords
- Len Function for Mass Crafting Ad Copies
Hope that these excel skills could help you to save time in mass editing your campaigns so that you could spend your time to better optimise them.
For other articles like this, you can go to my Linkedin Profile https://www.linkedin.com/in/bryanong1988/
or these articles:
About the Author: Bryan Ong
Bryan is currently a digital marketing manager focusing in Singapore and global (US, Australia, Dubai) markets and has proven in his career to overachieve targets greatly while maintaining CPA efficiency. His specialities are SEM, SEO, Facebook Marketing and Content Marketing. He is also awarded a government scholarship, is a published author of a motivational book and a TEDxSingapore speaker.