The Complete Guide To Using Excel For PPC
Excel is a crucial tool for any PPC marketer’s daily workflow. From building campaigns and writing ads to analyzing aspects of your account, your job will be made (a bit) easier by being Excel savvy. This whitepaper will outline the most important tasks that we use Excel for in our day-to-day PPC work.
Here’s what we’ll cover:
- Ad Copy Testing
- Bid Management
- Account Structure and Keyword Additions
- Analysis and Reporting Tools
Excel is a great tool for not only writing your ads and bringing them into AdWords through the Editor, but also for testing ads utilizing the label function in AdWords. Below are the best features to employ for ad creation and testing in Excel.
The first thing to note for Ad Testing is the use of the LEN function. Using “=LEN(x)” will allow you to visualize how many characters are in your headline and each description line to assure you have not gone over the maximum amounts (30 characters in each headline, and 80 characters in the description line).
Pivot Tables For Testing
Pivot Tables are obviously a huge part of PPC, and Ad Testing is just another way to utilize them.
First, assure the ads in your test are labeled within AdWords. Then, download the ad data that is involved in the test, and use the labels in the ‘Row Labels’ section of your Pivot Table as seen below:
Use a calculated field to get the CTR, Conversion Rate and potentially Conversion per Impression (or) Impressions Until Conversion numbers. To do this right, click the pivot table and select ‘calculated field’:
At the end, you can format things nicely, and see the results of your latest ad test either account-wide, campaign-wide, or at ad group level as seen below:
Finding Significance Levels in Ad Tests
The last step for ad testing with Excel is finding significance levels for your ad test. In the example, we use the ‘conversion/impression’ metric to judge performance.
The first step is to calculate this metric for both the Control and the Test copy as seen below:
The formula seen in the formula bar can then be used to calculate the Standard Error for the Control and Test ads:
After this step we can get the Z-Score with this formula:
Finally, get the P-Value, which shows the significance level of the Ad in Row 4 vs. the Ad in Row 5. The inverse of this would be the significance level if the Ad in Row 5 is outperforming the Ad in Row 4.
This gives you the perfect setup for an Excel ad test report. You can also create the same report with CTRs and Conversion Rates in a similar manner.
There are an infinite number of ways to use Excel to change PPC bids. However, formulas and functions are the basis of most Excel bid management methods.
Before creating formulas and functions for bid management, PPC metrics are needed. PPC metrics typically used for Excel bid management include: ROAS, CPL (goal metrics) and Average Position, Impression Share, Lost Impression Share (growth availability metrics). Bid management formulas and functions can range from simple to very complex. To start simple, use a formula to identify Lost Impressions.
Lost Impression Formula
Use the formula “=(Impressions/IS)*Lost IS” to find out how many impressions you’ve missed out on for a certain keyword. Then, use the total lost impressions side-by-side with your goal metric to see how much you’d be willing to raise bids in order to gain more impressions, clicks and potentially revenue.
ROAS Goal Max CPC
We usually recommend the formula: =(Revenue/Click)/ROAS Goal (or) Minimum ROAS. This is a reset for bid activity; it will set your bid to the minimum ROAS you are willing to take based on how much revenue you typically receive per each click on a certain keyword.
CPL Goal Max CPC
For CPL goals, the equivalent formula, to assure you are maximizing leads, is: =CPL Goal/(Clicks/Leads)
There are hundreds of ways to use IF statements for Excel bid management.
Regardless of how the IF statement is structured, utilizing impression share instead of average position is usually recommended, as it is more meaningful than average position in most cases. That being said, here’s an example of an IF statement that uses impression share to manage bids:
For an account where the CPL goal is $20, it’s a best practice to increase bids on keywords that are meeting or exceeding that CPL goal and missing out on impressions. One formula might be:
=IF(AND(ImpressionShare<50%, CPL<20), 120%,100%)”. In this case, the second argument of the function, 120%, would multiply the current bid by 120%. Testing bid change formulas utilizing the “IF(AND(“ function in Excel is recommended as the “AND” function allows for more parameters for each bid rule.
Account Structuring and Keyword Additions
Creating large keyword lists and structuring your accounts is a breeze with Excel.
Quick Keyword Tool
Do you want to segment your keyword list by themes, but don’t want to go about it in way where you need to type out each keyword? Apply the formula shown in the image below and create two lists – the formula will match those lists up and provide a keyword list that matches every scenario between the two lists.
Full Campaign Build Outs
Another great use for Excel in PPC is to use Excel to make complete campaign builds.
The first step is to come up with a naming convention for your campaigns and fill each section out as to how you would like to name your campaigns as seen below:
Then, you can create sub-categories and modifiers to build keyword lists off of as seen below:
Use the formula involved below in the campaign section across all the keyword and ad group builds:
Then, use this formula as the Ad Group:
Once all of this is in place, it is easy to implement the new campaigns, ad groups and keywords into your account from the AdWords Editor. This shows why it is so important to keep a consistent naming convention within an account.
Analysis & Reporting Tools
Now that you’ve learned how to build out campaigns and keyword lists and manage your account with bids and ad testing, let’s look at some ways to analyze your account and report on your findings.
We’ve already discussed Pivot Tables earlier in this guide, but they are so valuable that they are worth mentioning again. You can use pivot tables for general account analysis, ads, keywords, landing pages, etc. If you don’t use pivot tables currently for your PPC analysis, we highly suggest you start!
To make a Pivot Table, simply click the shown button and Excel will put your data into a pivot table. From there, you can start manipulating data in any way that you choose using the pivot table builder.
- Use calculated fields for metrics like cost/conversion, CTR, and conversion rates
- Use conditional formatting on a pivot table to utilize heatmapping
Conditional Formatting – Heat Mapping
Conditional formatting is great way to get quick insights from a set of data. Specifically, it’s a function that allows you to automatically change the format of a cell based on its values and the parameters that you define. It can modify text color and format, cell colors, as well as adding data bars and other graphics.
You can use it for dayparting and visualizing data. There are multiple pre-set rules that you can use, or you can create a custom rule.
Here we show a heat map of conversion metrics by time of day. By applying the conditional formatting, we can easy pick out any times that aren’t performing well and set a bid adjustment accordingly.
If you don’t want to highlight the entire cell in one color, choose the icon option. (Below)
Grouping does exactly what it sounds like: it groups rows or columns together.
The most frequent use of grouping is calculating optimal average position. To do this, download a campaign or keyword report segmented by week (or day). Then, create a pivot table with Average Position as your Row Label. Then select one row, right click, and click “group”.
This gives you an easy-to-digest view of performance by ad position.
Text-to-column is a neat little tool to help you separate data that’s contained in one column. Two of the most practical uses for this function are manipulating final URLs and splitting campaign names. For example, take an account structured like this: State – Theme – Match Type, then use Text-to-column to easily pivot the data by one of the three categories.
Navigate to the Data tab and select Text-to-Columns.
Copy and paste the campaign name into a new column before using the text-to-column feature. This way you will still have the original campaign name intact.
Macros are a recorded series of actions that can automate simple tasks. For example, at Hanapin we frequently use account snapshot macros that give the user a quick overview of number of keywords, match types, and quality scores across the account.
You can use macros to analyze your whole account, including match type analysis, ad copy analysis, and impression share analysis.
To record a macro, navigate to the View tab in Excel, and click Record Macro:
Excel makes projections easy. Typically, the formula used is MTD + ((Last 7/7)*Days Left In Month), but of course you can adjust this to whatever method of projecting you prefer. This can be used for cost, conversions, clicks, whichever metric you want. The beauty of Excel is that once the basic formula is set, it can be copied and pasted to automatically calculate for other cells!
Use the =today( ) function to automatically insert today’s date. Set Days Left to subtract today from month end and add 1 to include today.
Charts & Graphs
Charts and graphs are great to show to clients, and can be very helpful in recognizing trends to figure out what’s happening in your account. Select the data you want to represent in a graph or chart, and select the chart layout you want.
- Make sure your graphs are easy to read and avoid unnecessary clutter.
- Make sure your graphs are easy to read and avoid unnecessary clutter.
- Use the =today( ) function to automatically insert today’s date. Set Days Left to subtract today from month end and add 1 to include today.
Now that we’ve gone through how to create graphs, adding a trendline is a great way to quickly see an overall performance trend. As you can see here, there are a few different options as to what kind of trendline you would like to use:
This gives clients (or bosses) an easy way to quickly see how the account is trending overall.
We’ve all been in a situation where you have a huge Excel file with tons of info. The problem is, you can’t scroll down and still see your headers. All you have to do is Freeze your header row and voila, it’ll stick! No more frantic scrolling back to the top to remind yourself what those numbers mean.
Using freeze panes not only helps when viewing large amounts of data, but also assists in making more client-friendly reports.
This tool allows you to copy the formatting from one (or many) cells and copy it to another. (Paintbrush icon)
- Double-click the icon and you can paste the formatting on multiple selections.
- You can use this tool to copy image formatting too!
Work Towards Becoming A Master
Once you become an Excel expert, analyzing your account, building campaigns, and implementing ad testing cycles will become that much easier and faster. With experience, you’ll immediately know which Excel functions and capabilities will solve which problem (or answer a question) in your PPC accounts.