In PPC it never does harm to stay on top of your knowledge. Here are some functions and tips to help you get the best out of Excel and save time.
VLOOKUP helps you amalgamate your analysis tasks and practical work, associating huge amounts of data from multiple spreadsheets.
By associating Keywords and Ad Groups with corresponding master entries, VLOOKUP helps you create large numbers of unique destination URLs in much less time than if you had to do it manually.
You can also use VLOOKUP to analyze maximum CPC bids in comparison to average CPC at keyword level across multiple accounts. You’ll be able to determine which average CPC are getting inflated by bidding practices elsewhere, by downloading a keyword report from each account.
Used with VLOOKUP, Concatenate takes correlated keywords alongside their corresponding product categories, search queries and locations to create fully functional Destination URLs.
Concatenate can also help you create ad copy. Name your Ad Groups smartly and with a little tweaking you can Concatenate your ad copy around the name. For example, call your Ad Group “Beach Ball”, then Concatenate your copy to “Buy a new Beach Ball now!” The same formula can be applied to the rest of your products in moments.
Find and Replace
As with any kind of work you need to make the most of your time. Make a generic template from your keyword research, then find and replace keywords for other words meaning the same or similar within your parameters.
You could also concatenate a “+” before keywords that are a broad match, and find and replace every space with “+” for modified Broad Match variants.
This method can also be applied to Ad Copy, by finding and replacing the match type designation.
Word count Formulas and LEN
Excel’s character counting formula, LEN, not only helps you when working with Ad Copy character limits, it can help you analyze the effectiveness of long-tail keywords.
If – for example – any keyword of three words is going to be added as a Modified Broad Keyword, this Formula will find out which keywords meet those criteria. Once identified, use the above listed processes to finish it off.
Text to Columns
A useful function for Product Listing Ads.
Because product targets won’t download from AdWords interface and allow you to quickly re-upload them for bid changes, Text to Columns downloads the product target in to one singular cell. When the Editor requires they be split out in to two, Text to Columns helps you split cells, by either defining a specific character to denote where to split the cell, or setting a fixed width at which to split it.
It also helps you complete other tasks such as split UTM tags on Destination URLs.
Pivot Tables are vital in PPC. They help analyze and dissect data in hundreds of different ways. You can look at Ad Copy Performance, Quality Score, Locations, Devices… in fact anything that comes in spreadsheet form, you can Pivot Table for valuable insights.
By taking the time to brush up on your Excel skills you can save yourself a great deal of time and effort in the future. Not every function is useful all the time, but when they come in handy you will be glad you knew them!
Image credit – Afrank99 https://en.wikipedia.org/wiki/Action_Replay#/media/File:Action_Replay_Amiga500.jpg (Under CC BY-SA 2.5 http://creativecommons.org/licenses/by-sa/2.5/, unmodified)