Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Search Engine People
  • SEO
  • SEM
  • CRO
  • Display
  • Blog
  • Why Us
  • Contact
  • Join Our Team
  • Get A Quote

Toll Free: 1-877-695-7388

GTA: (647) 699-2838

Excel Formatting Tips To Rock Your PPC Reports

Lyena Solomon | April 17th, 2012
Tweet38
Share5
Share
Pin5
48 Shares

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

  1. make decisions quicker,
  2. get alerted when there is a problem,
  3. 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.

PPC campaign data with Conditional Formatting

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.

Filter data in Excel 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.

Check Cell Length in Excel

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.

Data validation settings in Excel

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.

Data error alert in Excel

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:

  •  How to Perform SEO Competitor Research with Excel
  •  Free Excel Template For Optimal PPC Keyword Organization
  •  (Almost) Automatically Reveal A Sites Directory Structure Using Excel
Tweet38
Share5
Share
Pin5
48 Shares
Posted in PPCTagged excel, howto, PPC, tools

About the Author: Lyena Solomon

I am leading ServiceNow digital localization strategy as Director of Localization, overseeing processes and bringing teams together. My SEO, PPC, Analytics, Optimization and UX background assist with internationalization and globalization efforts of our corporate site.

LinkedIn

3 thoughts on “Excel Formatting Tips To Rock Your PPC Reports”

  1. Justin says:
    November 18, 2012 at 3:56 pm

    Great article, do you have an example spreadsheet with the formatting mentioned available as a freebie?

    1. Lyena Solomon says:
      November 23, 2012 at 8:59 am

      Justin,
      Yes, I do have a download that is mentioned in the post. Click on “download the Excel Tips spreadsheet” above. 🙂

Comments are closed.

Recent Posts

  • Maximizing Your E-Commerce Sales:
    A CRO Audit Guide
  • Movin’ On Up! Why Migrating to Google Analytics 4 (GA4) Should be a Priority
  • A Year in Review: The Digital Marketing Trends That Defined 2021
  • The Basics of Video Marketing
  • Just How Much Do Google Reviews Impact Your SEO Ranking?

Categories

  • Analytics & ROI Analysis
  • Company News
  • Content
  • Conversion Optimization
  • CRO
  • Display Advertising/RTB
  • Email Marketing
  • En Español
  • En Français
  • Inbound Marketing
  • Lead Nurture & Marketing Automation
  • Local Search
  • Marketing
  • Mobile
  • Partnership Marketing
  • PPC
  • PR
  • SEO
  • Social Media Marketing
  • Web Design

Additional Posts

How To Optimize Your LinkedIn Profile

April 16th, 2012 | by Brian Farrell
Nothing to be shy about, we can all use these tips!

7 Tips for Twitter Late Adopters on How To Twitter Chat

April 16th, 2012 | by Victor Pan

Inverted Linkbait: Learn How To Target Linkbait At One Special Site

April 13th, 2012 | by Ruud Hein

LET'S TALK

Need more information or want to get in touch?

Get in touch!
  • SEO
  • SEM
  • Display
  • Blog
  • Why Us
  • Join Our Team
  • Contact Us
  • Local SEO
  • Small Business SEO
  • Enterprise SEO
  • International SEO

LOCATION

1305 Pickering Parkway,
5th Floor Pickering, L1V 3P2

PHONE

Toll Free: 1-877-695-7388
Greater Toronto Area: (647) 699-2838

Social

© Search Engine People Inc. 2023 – Canada’s Top Digital Agency
© SEP 2023 – A Search Engine People Company | Privacy Policy

Search Engine People