10 Simple Useful Excel Tips Every PPC Expert Should Know
A PPC expert knows how to take advantages of every possible resource.
There are several PPC tools available- some are paid and some are free. Along with having assisted with these tools PPC professionals also bring out benefits from other nonconventional options.
Microsoft Excel tops that list. Though there is no such direct relationship with Adwords or other PPC platforms but for its allied features, MS Excel has similar importance in PPC Campaign management.
PPC is a field where huge calculation goes on in keyword research, ad copy creation and data crunching.
Structure Your PPC Account and Build Campaign in Excel
Your PPC account should be well structured so that you can have a comprehensive understanding on-
- how each metric is performing
- which areas need more attention
- where optimizations are required
- whether targets are fulfilling
- Amount of profit
The PPC account structure is built according to the requirement of you or your client. In the excel sheet, you can organize things according to location, device, match type, ad-group, max CPC.
It also makes way for-
- Reporting of different levels
- Better optimization due to clear understanding
- Improvement of quality score that enhances the relevance of ad copy
- Easier campaign building
10 Useful Tips on How to Employ Excel for PPC
You need to be swift on saving time and effort in excel. There are lots of tricks and tips to shorten the time taken in doing lengthy calculations. Some of them are:
1. Shortcut for currency and percentage calculation
While calculating currency and percentage keyboard shortcuts come handy. The keys ctrl and shift help to remember shortcuts.
Sometimes, even use of mouse seems time taking while turning a number into currency or short cut. Just press ctrl+shift+4 in the desired cell and the amount will turn into the dollar.
In this case, only pressing shift+4 will not work until ctrl is also pressed. Ctrl will turn the number into a currency number.
The process goes same while turning a number into a percentage. Ctrl+shift+5 will convert the cell number into a percentage number.
2. Calculate sums automatically with “ALT+”
Tricks are meant to save valuable time. Summing the values of each cell through the consecutive columns or rows requires you to write the formula every time.
There is a simple trick that does not involve putting the formula every time.
Press ALT button then the = (equal) key, the sum of entire row and column will be done in less than a fraction of seconds.
3. Just copy the formula with double click
It is not a keyboard shortcut rather a small trick. During calculation of conversion rate and click through rate same formula requires to be repeated.
Calculating several rows of data with same formula is a tedious job. To complete it within a second first put all the necessary data and calculates the first calculation by typing formula.
Now, just hover the cursor over the cell and take it to the bottom right corner where a plus sign will appear. Just double click on it. The calculation of the remaining cells in all the rows will be done automatically.
4. Freeze the top rows of the spreadsheet
When you have hundreds or thousands of rows and columns of data it becomes difficult to determine which row or column is what. You can fix the header row or column to speed up the job.
Using ‘Freeze Pane’ it can be made possible. To use it, first, highlight the row situated below your desired row which you want to freeze. Go to menu bar and select option ‘view’.
Under view, to select e ‘freeze Panes ‘ button and three options will come, a) freeze pane, b) freeze top row, c) freeze top column. Select ‘freeze pane’ button and the header row will become fix and now you can scroll through your data without going to the top row again and again.
You can also use this function (Alt + W + F + R), it Freezes top row in Excel sheet.
5. Function VLOOKUP
VLOOKUP allows correlating data from different spreadsheets and speeds up your both analytical and practical jobs.
Suppose, a PPC expert has created 200000 keyword level URL which is again classified into a product category, location and searchable in client’s site search function.
It is truly a daunting task to handle such a big amount of data that requires being connected through many categories. In this specific case, if some pre-work is done by creating a master ad-group level product category IDs the job becomes a bit easier.
Next, using VLOOKUP function a massive amount destination URLs can be produced by correlating keywords and Ad- Groups.
VLOOKUPS is also useful when multiple accounts are running and competition against each other. Using VLOOKUP you can tally CPC bids.
This function brings out the maximum and average CPC bids. You can monitor the expense of the entire Ad-group. Keeping average CPC bid price under control is important.
Often certain keywords price gets inflated and that affects the average CPC cost. With VLOOKUP, you can identify that. Just download the keyword report and correlate them with this function.
6. Use of Concatenate
In PPC excel is primarily used to keep all the data and calculations organized. A PPC expert has to slice and dice huge amount of data in order to bring the best combination to work.
Concatenate function allows joining text of different cells into one cell. It also does the next part of the job where VLOOKUP has stopped.
Concatenate, in generation 200000 unique destination URLs, joins all the different elements correlated by VLOOKUP.
It actually makes destination URLs fully functional. This function is very much useful even for connecting URLS with UTM codes.
Concatenate aids in connecting ad copy with the Ad- Group name. First, select a name for ad-group like, ‘Red sandals’. Now, using concatenate you can tag your ad copy around the ad-group name e.g. ‘Buy red sandals now’.
7. Len and formulas of word count
Len counts the number of characters in a cell including spaces. This formula comes handy during counting long-tail- keywords.
Len works like your assistant while restructuring an account. For example, if your exact matches are more than three words long then it will be also added to Modified keywords match.
Len will start finding out keywords conforming to following criteria. Once the job of identifying the keywords is done, all you need to do is copy and paste, concatenate, find and replace to complete the task.
8.’Find’ and ‘Replace’
This old fellow is still useful these days. Not just in MS- Document but ‘find’ and ‘replace’ save a massive amount of time for PPC account users’ big time. Newbies should also learn how to use it judiciously.
With ‘find’ and ‘replace’, you can find certain keywords in an article and can add more details to your keyword research.
While creating ad copy ‘find’ and ‘replace’ can reduce your job if you want to bring change in the ad copy.
As in the previous example, the ad-group exact match keyword was ‘Red sandal’. Now if you want to change this in each ad copy, simply use ‘find’ and ‘replace’ and put your desired keyword in every ad copy.
Making all your broad matches into modified keyword matches is effortlessly easy with find and replaces function.
To make a broad match a modified broad all you need to do is adding plus sign in front of every keyword.
Now, concatenating the space in front of keywords with ‘+’ sign will create modified broad match in seconds.
9. Importance of Pivot Table
Use of Pivot table is a must have for every PPC account manager. For beginners, it may seem bit complicated but actually, it is a helping hand, an indispensable feature.
It helps in looking at account performance, quality score, Ad copy performance, Average position, locations, and devices. Whatever data comes in spreadsheet form, using Pivot table you can have an insight.
It is like having a bird’s view of your entire PPC activity of an account. It helps in creating better marketing strategies.
The things that pivot table brings out are-
a) Insights on quality score
It shows what kind of traffic is pulled by what kind of quality score. Measure it by percentage.
b) The relationship of CTR and conversion rate
It varies depending on the position you are in. Using Pivot tables you can analyze what position works best for your ads.
First segmenting the data and then aggregate them all into the pivot table. It will open a different way of looking at the entire data. You can take different actions by looking at this data for the account.
c) Different variants affect your account in multiple ways.
Put the search query data in the pivot table and see how cost, conversions, CPL and other match type elements are working.
Google always wants to increase the volume for your account. You have to decide whether you want that extra volume for your account or not.
In Pivot table, you can see how the close variants like, phrase or exact match are affecting your account.
For Ad Reviews pivot tables can be used to analyze how headlines are performing in different networks. The performance of headlines can vary depending on the campaign and network types.
By labeling each campaign by their selling propositions campaign type, their performance can be monitored using a pivot table.
A clearly distinguished data structure that carries all the elements and their results helps to figure out where a campaign is better performing. If the performance has dropped the table also helps in chalking out which element is registering outcome.
e) Landing page analysis
It is mostly done through Google analytics. Pivot tables still hold a strong place in landing page analysis for non-Google account users.
Put user behavior statistics using analytics along with Facebook and Bing data. Now add relevant campaign/ad group/landing page info in the same column. Also, add analytics data in the new column.
There is no need to use VLOOKUP. Only make sure that you have removed spaces from the interface data.
f) Lead Generation
In the lead generation in Ad Spend returns is important. Measuring how much profit is registering is important. After a long sales cycle, after accumulating client’s backend data, you can easily sync cost in several networks.
A pivot table changes the entire report that used to take a whole day to prepare with VOLOOKUPS, sums, and subtotals.
Similar to the landing page review technique, put all campaign/ad group info in the spreadsheet. Now, put a related number of close leads and revenue from it.
The blank cells will go away when data arrives in the pivot table. This way the entire picture of sales cycle becomes clear. Submitting the revenue report to the client becomes easy.
It is an important factor of PPC. Not all ads perform throughout the day. Each has its own prime time. Find out this by adding a day of a week as the row and time column in the pivot table.
You can also put CPL in another column. This quick report lets you discover whether your dayparting is bringing out desired results or not.
h) Multiple accounts on different product
When multiple accounts on different product run simultaneously comparing data across accounts becomes necessary. During handling so much data at a time data over becomes a general phenomenon.
You must analyze which are your best-performing keywords and in which location they are performing well.
In the pivot table, make an aggregate of performance across all the sites and also add location or site of the data in order to get a clear picture of the entire performance.
i) The performance of placement in display network
It may look sluggish as not all your sites are getting good clicks. In Google AdWords placement report this scenario becomes visible.
Just put all the data in Pivot table to find out wish sites are garnering low clicks. Just add them up to save some valuable bucks.
j) The performance of search partners in Google Analytic Report
The performance of search partners in Google analytic report is not showed in total. You can put the entire data in the pivot table and get a broader picture.
Put the backend data of the performance of each search partner including the referral URLs and prepare another pivot table based on this table.
10. Do not forget using IFERROR
Often when we calculate something with zero Excel shows #DIV/0! Error. It mostly happens when you are calculating CPA data,
It may happen that in spite of spending a lump some money a keyword has not generated a single click. While calculating, you cannot leave this information out.
At this point, the formula IFERROR comes handy. Use this formula in the field of calculation to count ‘spend’ or ‘cost’ where there is zero conversation.
Open the calculated field again and in the CPA field from the Name Dropdown put the IFERROR formula instead of the previous one. Write the formula this way: =IFERROR (Spend/Conversions, Spend).
Hopefully, these 10 tips will help you in future for mass data analysis. These tips are just initiation on how you can better utilize several tools to enhance the performance or to understand your campaign.
Effective ways of using excel have a direct impact on your campaign management. Now it is your excellence to find out more ways for using this wonderful tool more effectively. The more you test the more you can explore.
Originally published at adstriangle.com on April 27, 2016.