Anyone who has created or managed a PPC campaign will appreciate the value of Excel.
The thing is, there is an enormous amount of data to log and manage, especially in larger campaigns. And while Google Ads creates some stunningly useful reports, they can’t perform all the leg work for us.
Sorting through screeds of data while maintaining its integrity, and still making sense of the mind-bending amount of information requires concentration, skill, and a whole lot of time.
Thankfully, Excel has proven itself a valuable friend to PPC managers everywhere and is the data consolidation solution you’ve been looking for.
So, because we are great guys and want to make your life easier, we’ve curated some PPC Excel tips to help you manage your time and data better.
What is PPC?
Pay-per-click (PPC) advertising has proven to be one of the most effective ways to get instant traffic, leads, or sales from online searches.
The system is genius. You choose the words or phrases that you think your audience are entering into search engines, and you craft your ads in Google Ads. Users see your ad, they click on your ad, you pay a nominal fee, and you make a sale.
Well, that’s the theory anyway.
The problem is that Google Ads is probably one of the deepest rabbit holes you will ever encounter in your journey through the digital marketing landscape. The features are incredible and, when used properly, can work some weird and exciting magic for your business.
However, when attempted by overenthusiastic dilettantes, a PPC campaign can haemorrhage your entire marketing budget in under an hour. Or give you no results at all.
The point here is clear, we need to make sure all our checks and balances are in place when we’re setting up and managing our PPC campaigns. Enter, Excel.
PPC Excel Tips and Formulas
Excel can do an awful lot of really clever things, most of which we’ll never use. When this smarty-pants program is applied to our PPC needs though, we tend to do a small happy dance. Here’s why.
Keyboard Shortcuts
You are probably aware of many of the Excel keyboard shortcuts as they apply in MS Word as well. CTRL+ C to copy and CTRL + V to paste, for example, are commonly used. There are more, though, lots more! These are called shortcuts because the small, repeated time savings add up to a whole lot of man-hours.
Currency and Percentage
Here’s one you may not know. You’re sorting through data that needs to be displayed in various formats i.e., currency or percentage. Highlight the cells you want to format and use CTRL + SHIFT + 4 to format into currency and CTRL + SHIFT + 5 to format to percentage.
(This is easy to remember because the shift function of 4 is $ and 5 is %. Clever stuff!)
Copy Formulas
If you’re working with several formulas, then a super-fast way of repeating them in a column is to click on the cell containing the formula, and then double click on the small + on the bottom right of the cell. This automatically applies that same formula down the column.
Adding Rows
Yes, you can use the SUM function at the top of your spreadsheet and select the data you want to add. But you can also click on the first empty cell beneath the data and use ALT + = which will create the SUM formula and select the rows immediately above.
Advanced Excel for PPC
We’ve dipped our virtual toes in Excel for PPC, let’s go a little deeper.
When it comes to creating client reports and analysing data, you’re going to find yourself in filtering and sorting territory.
Sort Range
The success of PPC ads can be measured by using different metrics. Some are interested in cost per click, others in click-through rate. The ability to sort and view several metrics at a time is useful. To do this you will:
- Highlight the range of cells containing the data you are analysing.
- Click on data > sort range.
- Your range should have headers, so be sure to check that little box.
- You can then select the metrics that you are interested in and view them in ascending or descending order.
Let’s take an example.
I have run several ads in a campaign, and I want the following information:
- Which ad received the most interest.
- Which ad received the best click-through rate?
- Which ad was the most expensive?
I would sort my data by displaying the following information:
- Campaign name
- Ad name
- Total impressions
- CTR
- Total spend
With this information in a table, I can quickly sort each column to show the lowest or highest result. Alternatively, I can sort my information in order of importance that may indicate that a certain ad had a lot of impressions, but a low click-through rate and – oh no – we need to amend the copy!
Excel Tools for Creating PPC Campaigns
Small PPC campaigns are fairly quick to set up, but the larger ones can involve a lot of repetition. Here’s where Excel can save you even more time. (And unnecessary fiddling about with words and things.)
LEN Function
When you’re creating copy for ads in Google you know that you have a limited number of characters per line.
Figure 1 Source Google Support
These character limits are not negotiable, so we need to know that we’re getting our message across succinctly within these parameters.
In an Excel sheet, you can create columns based on the fields in the image above, with each row being a new ad.
Next to each column you will want to add another column for the text length which is where you will pop your formula. Once you’ve entered your ad copy, click on the empty Text Length cell to the right and select Formula > Insert function > LEN. In the field, you will select the cell containing your headlines and then click OK. This will display a number which is the total character count of the cell, remembering that characters include spaces.
Clever stuff, right?
CONCAT Function
The CONCAT function merges the information from two or more cells together. When you’re setting up an ad campaign and you want to include specific variables, this function is going to be your best friend.
For instance, you may want to run the same ad copy for multiple ads but highlight different products from your catalogue. Instead of cutting and appending a new product into each cell, you call on CONCAT.
- Your spreadsheet would have the copy that you want as standard in one column, and the catalogue variables in another.
- Click onto a clear cell to the right of your data, select Formulas > Text > CONCAT.
- In the first field, you can either enter free text or select a cell, so in this instance, you would select the ad copy that you want to repeat.
- In the second field, you would click on the space bar, otherwise the data sources will simply run into each other.
- The third field will be a cell reference containing one of the variables.
- When you accept this function, you will have successfully merged the two pieces of data together into a single cell. Double click on the + at the bottom right of your formula cell and it will populate this function to the entire row.
PPC Optimisation Techniques
Google does some things really well – okay, most things. But it seems to have lost the plot when it comes to certain functions, like setting up keyword lists using the modified broad match.
Yes, you can add that essential little + to the beginning of each word manually when setting up your campaign, but when you’re managing hundreds of keywords this gets more than a little boring.
Let’s just make sure we’re all on the same page.
Modified Broad Match Keywords
Modified broad match types are being phased out by Google but while they’re active we should use them, right?
WordStream confirms their use saying, “With modified broad match keywords, you choose specific keywords that are required for your ad to show, through the use of a plus sign. In other words, your ads will only show for queries that contain all of the words you precede with a plus sign in your keyword or phrase.”
Adding a ‘+’ before your mandatory keywords gives you a measure of control over the synonyms or related searches that Google may offer.
Changing match types for hundreds of keywords into modified broad is going to be tedious…without Excel to help.
The Find and Replace function is one way to add a space and a + sign to these phrases. You would do this by highlighting the cell range containing the keywords that you want to change, click on Find & Select > Replace.
In the Find field, you’ll enter a space, and in the replace field you’ll enter a space and + sign.
You’ll then have a + in front of all the words in your cell range.
These can then be downloaded into a suitable file format and imported or simply pasted into the relevant places in your ad campaign.
Don’t Go it Alone
Setting up PPC ads is not for newbies. They can be the most amazingly life-altering business decisions, but they need to be done right.
Using these PPC Excel tips will go a long way towards helping you make sense of that dizzying landscape of raw data. But if you’re still a little concerned about diving into the murky waters of online advertising, please feel free to have a chat with one of our specialists.
We perform these functions every day. And we’d love to do it for you.