It’s been 3 months since I presented at SMX West in San Jose, and about 2 months since I presented at Pubcon New Orleans. At both shows, I spoke about building an efficient reporting product, as well as another few specific ways to do some common and necessary Paid Search tasks. I thought I would post a recap of that content here, and am finally getting around to doing it. :)

Quick side note – both shows were great. If you are interested in attending a conference about search marketing and social media, both the Pubcon and the SMX series are FANtastic.

Now, without further ado…

Efficiency is key to Search Marketing. There is no end to the amount of time you can spend optimizing and analyzing, tweaking and testing your accounts and programs. You have enough to do just figuring out enhanced campaigns or whatever the new algorithm update is all about – you don’t have time to spend hours every week pulling and massaging numbers. Don’t get me wrong – effective reporting and tracking is vitally important to success in search, and should be in every marketer’s utility belt. But the best reporting is both comprehensive and FAST.

(See what I did there? The italics make the word *look* fast. Typography FTW!)

Before we get into it – a couple of reporting rules I live by:

  • Build all reports so that everything can be repeated and updated. Anything you have pulled and reported on once will likely have to be pulled or updated again. And if you have to do it twice, you can bet your sweet bippy you’ll have to do it a third time, so make it easy on yourself.
  • Any repetitive report you do should be put on a schedule.
  • All reports should have some defined action you take based on it, or output you provide. Don’t just pull numbers because they are interesting.
  • Never pull in any calculated metrics (like CTR) – only ever use hard numbers, and do your calculation on the report itself.
  • Learn pivot tables, and how to build nested formulas, and even some light macros. For Serious.

Over my career running in-house programs and developing agency practices, I’ve learned that the most effective reporting and analysis comes from treating your reports like building a product. Good products and sites have a purpose or goal, user or audience, and typically some kind of measurable outcome or result.

So the question becomes, “How do I build a good product? I’m not a product designer, I’m a search marketer! Are you MAD!?”

Suck it up. If you want to do well in search you have to expand beyond keyword research and writing ad copy. So let’s dig in.

Good Product Design

The following diagram is by Jesse James Garrett, from The Elements of User Experience – a quintessential work in the  field of product and web design (ask your product or UI people. They’ll tell you). It is one of many models for how to build great products and sites, but one that exemplifies good reporting design.

Elements of User Experience by Jesse James Garrett


As you can likely see from the diagram, you start at the bottom with the more abstract and general requirements, and get more refined and specialized as you move towards the top. So let’s also start at the bottom, but adapt each level for reporting specifically.


As it says above, Strategy is where it all begins. Right? Yeah, if only. How many times has reporting grown out of a request by your boss, client or someone else in the organization? Or how much reporting did you “inherit” when you started your job? I have found that most reporting created or inherited lacks this all important step – the foundation, if you will. And you should.

The questions you should be asking at this (the first) step:

  • What is the purpose? Seriously. Ask it.
  • Who are the stakeholders or audience?
  • What are they trying to get out of the data?
  • How will the report be used?

If you can’t answer these questions, you shouldn’t start working on anything until you do. No matter what they tell you, you will be wasting your time.


This is where your strategy should be analyzed and specific requirements built. In any large organization, data lives all over the place. Actually, in most small organizations too. Identifying what data you need to gather, and where it’s hiding is the next step. This is also where you will likely discover the real size of the project you are undertaking, and build a reasonable estimate of the time you will need to build the report.

You should answer the following questions:

  • What features does it need to include?
  • What are the KPIs?
  • How often does it need to be refreshed?
  • What are the data sources necessary?
  • What can be automated?


Now that you have all the pieces defined, and you know what you need to build, the next step is to determine how those pieces fit together. Often you will have to combine data from multiple sources to get the right KPIs. For example, if you want to understand conversions by brand, and how that relates to advertising spend by brand, you will likely have to combine your Google AdWords data with your Analytics and possibly bring in your product database to augment the conversion data. It is critical to define how you will join the data sources – what consistent data points you have across each data set that you can use to roll the data up from both sources.

This is also where you need to start planning for the future. If you are going to pull this report more than once (and you almost always will), some automation needs to be built into data pull so that you are not repeating manual labor. Unless you have interns. Then go for it. ;)

Questions to answer:

  • How will it fit together?
  • What consistent data can you pivot on?
  • How can the data be combined to get to the right metrics?
  • How can we combine the data moving forward?


As the name implies, this is the framework for the actual report. You have gathered the data, combined it in interesting ways, and even thought about how you are going to automate the gathering and combining going forward. You know the KPIs you need to show, and who is going to see and use them. Not only that, but you know what it’s going to be used for. Now you are building the initial views of that data – what makes that data useful. Is it a snapshot of last week? Is it trended over time? What KPI’s should you show together to tell the story you need to tell?

  • What views of the data do you need to provide?
  • What time ranges, trending, and KPI combinations do you need to show?
  • How do those views satisfy the needs of the audience and stakeholders?


Finally, the top of the chart. This is where you determine what the final product looks like. If you think you can just dump some numbers on a spreadsheet, then you are horribly mistaken my friend. People don’t like big, gnarly spreadsheets. I mean, I do, but I’m not normal.

You have to create something presentable (just like with a product or website) so that the user doesn’t have to start using his or her brain just to figure out what they are looking at. You want them to be delighted by simplicity, and amazed at the underlying usefulness that you’ve managed to tease out, right? Right? Right.

So here you should be asking:

  • What will the report look like?
  • What actions will it drive?
  • What visualizations (graphs and charts people) would help achieve the purpose?
  • What embellishments can I add that will make it clearer and easier to read?

That last one is key. A little conditional formatting (up/down arrows, stop lights indicating performance, etc) goes a long way, especially with the suits.

The Proof in the Puddin’

So that’s the strategy, the method, and the theory. The best part is that it works. Really well actually. Since I presented the method at SMX, I’ve had multiple people talk to me and tell me that they have implemented the strategies above to really clean up and streamline their reporting. Which is like the best thing any speaker can hear, honestly.

The example from my own work that I shared at the time? Behold – the reporting I inherited:

Ugly Reports

Yes, I know you can’t really read the numbers, but would you really want to? It’s awful. And this is 1 of 6 or 7 tabs of data vomit. Updated WEEKLY. For the Executives! Ghastly.

The details:

  • It’s a monster – Giant files, really hard to read, & super error prone (due to being manually pulled)
  • It took 2 people 6 hours each to update. EVERY WEEK. From multiple datasources.
  • The best part? No one read them. Why would they?

Welcome to the new hotness…

Pretty Reports

Following the above methodology, we built this. Yes, there is still a ton of data. But that data is designed the same way that the internal executive reporting at my company is built. So it’s familiar, and how they want it. Here’s the best part:

  • It takes 1 person 20 minutes to update. Including executive write-up. BOOM.
  • The files are tiny. More data than before, and 1/10th of the size.
  • There are visualizations and trending built into the report. Oh, and the best part…
  • It’s almost entirely automated. Drop a few numbers here, change a week number there, and everything on the report updates to reflect the new data.


So there you have it. Funny enough, all that written out was only about 1/3 of the presentation I gave, so I’ll have to follow up. Stay tuned for some detail around:

If you can’t wait for those last two – check out the SMX West Presentation on Slideshare. The optimization schedule I only presented at Pubcon, so stay tuned.

As always, love to get your thoughts in the comments…


Leave a reply

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 


Prove your humanity by doing math! * Time limit is exhausted. Please reload the CAPTCHA.