One thing you learn quickly when doing PPC is that you need to view and analyze reports. Reports equal a lot of data in Excel. You might use formatting already, but I am here to make your life a little easier yet. I'm going to show you how to introduce color to your reports.
Why does color matter? Color is much easier to process then black-and-white numbers on an Excel spreadsheet.
By using color, you can
- make decisions quicker,
- get alerted when there is a problem,
- and often it's a better way to segment data.
Let's say you need to start a PPC campaign going for a toy company. You do your research, you analyze data, and you develop a strategy, campaigns, ads, key performance indicators, etc.
Research and Analysis
First, you do keyword research, gather basic data: monthly searches, average click through rates and cost per click for your ad groups.
Then, you add some calculations to help you plan your AdWords campaigns. Those might include revenue per conversion, your average conversion rates, your cost per conversion return on investment and profit per conversion.
Your average conversion rate is about 2%. With the current setup, your profit numbers are not very promising. Your profit per conversion clearly shows that you will be losing your shirt if you proceed with the settings for your ad groups.
If only you can see creeping problems and errors quickly. Well, you can. Conditional formatting can be a lifesaver when you have to process a lot of data. You can configure your spreadsheets to alarm you if something is wrong. You can mark your key metrics, like profit or cost per visitor to make sure you are not wasting money.
Conditional Formatting in Excel
Conditional formatting allows you to format cells based on their values. Configure a formula to check data. Then, specify the format to apply to the cell based on the result.
In my spreadsheet I am checking profit per conversion. I want to make sure that I am not losing money. I am using icons to visualize my data. If the value is larger then 100% the icon is green if the value is equal 100% the value would be yellow and if it's less than 100% the value would be red.
When starting a new PPC campaign, it is good to now how much effort it will require to break even. No profit per conversion, which is, essentially zero ROI, will indicate that you have the right minimum settings for your PPC campaign. Color formatting in Excel can help you quickly see which campaigns to tackle first.
One of the metrics that influences your revenue is conversion rate. If you know the minimum conversion rate required to break even, you can determine how difficult it will be to advertise in that space. Change your conversion rate value and watch the ROI column. When the value changes to yellow, you will know your minimum conversion rate. Next question to ask would be how difficult it is to achieve it. To speed up the process of finding your minimum required conversion rate, check out Goal Seek feature in Excel.
Another advantage of conditional formatting is you can segment your data by icon. What a time-saver! No need to specify complex filtering conditions every time. Just apply them once to your conditional formatting and sort by color or by icon.
Data Validation in Excel
There are several restrictions in AdWords for your ads. For example, your headline cannot exceed 25 characters. When you are creating many ads, it could be time-consuming to manually check every entry. If you do not check for errors, you might have problems importing your ads to AdWords.
I have two solutions for you.
You can check the length of the Headline field and change the Headline Length cell to red if the headline is longer than 25 characters.
If you are entering data manually, you might want to be alerted immediately when the data does not meet your requirements. Use Data Validation in Excel (under Data tab).
Specify the maximum length of your text field in the settings. You can customize your error message and error description in Error Alert tab.
When you enter your data and it does not comply with the rules you set up, you will get an error. Note, that you will not be able to move to the next cell until you correct the error. This is the difference between the previous method, which will allow you to proceed even if the data has a problem.
Utilize The Power Of Excel
These are just a few tips on how to help you process your data by using conditional formatting. If you use color in your reports and spreadsheets, you will be able to make decisions quicker by visualizing and segmenting your data. You will get alerted if there is a problem and not waste time. You will be able to quickly understand your data and focus on what's important.
You can download the Excel Tips spreadsheet I was using as an example.
If you want your reports to look professional and clean, check out this presentation by Annie Cushing on basic Excel formatting.
Now, go rock your reports!
More Excel Tips:
