How to create perfect marketing reports from blank Google Sheets

15-MINUTE READ · By Stephen Dawson on August 31 2017.

It goes without saying that the perfect report is a top tool and ally for many marketers. Perfection isn’t easy to come by however and there are many factors to champion in order to achieve top quality.

When it comes to marketing reports, you could say that perfection is:

• Minimal maintenance
• Maximum observation

This is so your report becomes a way forward for you and the reader. With thoughtful planning and an audience in mind, a report can be made from a blank canvas that streamlines your analysis and strategic thinking.

In this post I will take you step by step through 4 vital requirements, planning, functionality, design, maintenance.

After recently acquiring a new PPC services client who saw reporting as a key selling point, I needed to deliver a swift but highly customized reporting service.

By using this guide to shape and execute the report, I was able to produce a report that met the needs of the client and my team. Most importantly, it was a successful path towards going above and beyond their expectations. Happy days!

When reporting is carefully planned you save time, and as you know, time is precious in digital marketing!

Planning

Planning isn’t boring. It’s a chance for you to be as creative as you like. The more time you spend thinking about your approach, designs, functions, and audience, the less time you will have to spend building your report.

The notepad plan

Pen and paper are not to be underestimated. A blank background in which to write, sketch, and plot breeds originality. It focuses your attention and as it’s only saved in one place (not on your company cloud!), encourages you to open up your creative boundaries.

Imagine how your report layout could captivate your audience, deliver the key insights, and most importantly allow the reader to identify the less obvious.

Have you seen a layout in the past and thought ‘How boring is this!?’. Identify why it was a flat, enduring experience and fix the issue. It’s important to make your report fun to work with.

Grab your pad and start with your WHY, WHO and WHAT questions

Who are the audience

1. 1. Executives and managers who care about high-level results (and aren’t super familiar with marketing jargons)
   2. Clients and managers with good marketing knowledge who also care about operational metrics
   3. Yourself. Make your optimisation and management easier

Why do they want to see this report

1. 1. Monitor high-level results
   2. Monitor the progress of execution
   3. Analyze and optimize the campaigns

What type of reports do they want to see

1. Executive dashboards
2. Operational reports
3. Analytical reports

I asked myself these questions about my new client, a regional building society. With a smaller team than their competitors, they wanted to make every effort count. This meant getting across the key insights quickly and concisely. I considered that they:

• Were previously receiving too much detail and no recommendations
• Wanted to follow KPIs closely
• Wanted to hear recommendations on how to improve their KPIs

With this information in mind, it was fairly easy to go off, find a quiet space in the office, and produce a plan.

It might look messy, but the ideas were firmly in implanted in my mind after the planning session!

Within 20 minutes of scribbling I had produced a report plan that was unique to the clients requirements. Most importantly, I could jump back on my laptop and create a report from scratch in no time!

What type of report?

From your notepad plan, you will know the ins and outs of who your report is for. That should be a clear basis for what your report does. Think about whether you will need time to invest in setting up segments or, at the other end of the spectrum, delivering a swift, concise report that doesn’t indulge in detail.

Strategic reports provide the executive team a bird’s-eye view on the business, where it is and where it’s going. This should include headline stats, a few eye catching progression charts, and perhaps some basic explanation. While it’s a basic report, it’s going to be tricky to not hear your client say, ‘can you include this metric too?’ and then throw some wild functionality requests your way!

Operational reports help monitor the operational side of your marketing funnel. They will include a lot of relevant detail. Don’t forget that you will still need make it easy for the user to view overall progression.

Besides creating your marketing campaigns, an important part of your job is to analyze the results and understand why the results were good or bad. That’s where your value shines. Anyone can pull numbers. And that’s when you need analytical reports. These reports should make it easier for yourself to explain trends to clients and most importantly, identify the less obvious.

Check out Supermetrics for Google Sheets Template Gallery for more reporting templates.

Now, think about your KPIs

By now, you should have your KPIs agreed. These should form the basis of what metrics and dimensions you need to include in your report. As a dependant factor, your KPIs will be influenced by other metrics. Take note of what these metrics are and use them as guiding metrics to analyse KPI performance.

For an ecommerce business, strategic KPIs could be revenues, customers, advertising costs, customer acquisition cost, customer lifetime value, etc.

Operational KPIs could be cpc, CTR, clicks, orders, conversion rate, average order value, cost per conversion.

Analytical KPIs could be bounce rate and average time on page of your landing pages, search to purchase conversion rate, the cart and checkout completion rates, percent zero result and zero yield searches, and the percent high, medium and low click depth visits if you want to analyze efficiency of your post-click funnel.

Summary

Want to impress your client? Get your basics covered. Remember to:

• Plan in detail – WHY, WHO and WHAT
• Think primarily on how to reach your audience
• Include what matters to them
• Create a clear link between KPIs to other supporting metrics

Once you believe you’ve planned a robust report then you can go above and beyond with innovative functionality and design.

Functionality

Depending on you plan, your report functionality could be quite complex or straight forward. Nevertheless, functionality can be the most tricky part of your report to master. As I’ve talked about before, get your basics covered. This will ensure you don’t over complicate your functionality.

What is functionality? Well, how can your end user read from your report and what do you intend for them to find from the report?

Your planning phase should mean that you have a vision of what data you need to import into the report and what analysis you want the reader to gain from it. But how do we take our intentions and turn them into a visual dashboard? Think about:

• Telling a story. Creating a flow from one metric to the next (i.e. we spent this much which gave us this many transactions which led to an RoI of X)
• What comparisons are going to be relevant. This come down to your date range but will a year on year comparison also show an accurate reflection of progression too?
• Trimming the edges. What’s really going to show progress and demonstrate your value?
• Interactions. Could the reader change the way the data is shown?

In my example I based our progress analysis on three metrics that were pivotal for our strategy. CPC, Conv Rate, and Cost per conversion.

By including these three metrics on their own, I didn’t necessarily need to include anything to tell the client how our strategy is performing.

Illustrate what’s important to your client:

Building your report

First things first. Learn your way around Supermetrics for Google Sheets, an add-on which helps pull data from various marketing platforms into a single spreadsheet. Getting strted with Supermetrics is extremely easy, just follow the steps in this article. Practice a couple of test queries on a separate sheet. You can take a look at how queries are built for an AdWords report in this video. Try:

• Exploring what metrics would suit your reporting plan
• Importing your data by month (i.e. Split to rows – Year & Month)
• Testing filter options (i.e. filter by campaign name)

The building blocks of my report included one main query which was easy enough to build from scratch.

How?

First, select your data source and account(s).

Then, select your date range:

For most reports, a date range of the previous 13 months will be sufficient. This gives you a chance to compare your previous month year on year.

Now, select your metrics that you need to import. There’s a lot of options here. Look back at your report plan, do you need to include all factors that affect your KPIs?

Next, choose what dimensions you want your data to be split by. If you’re creating a monthly report, chose year & month. If you want to split the data by weeks or days, make sure your select enough # of rows for the data to appear!

Now for the options! Tick the ‘Show all time values’ option when running your supermetrics queries. This will save you a headache if your range of data expands beyond your original cell references.

If you’re anticipating new data sets to be used in the future but want to set up your supermetrics queries now, type in RETURN_NO_DATA_RESULT into the advanced setting box to avoid errors.

When you’ve mastered your queries. Get your data in order for your report:

1. Place your data outside of the viewable area of the report. By placing the import ranges in a separate area you are allowing yourself more flexibility when it comes to displaying the data. You can use cell references to import what metrics you need and create formulas for custom metrics.
2. Plan for changes in required number of dimensions. For example if the look back window for your report changes from 12 months to 24 months, you will need to allow space for this.

Formula for success

It goes without saying that a perfect report requires some nifty formulas to do the hard work for you. These formulas should make the setup and management of the report much less time intensive.

Hide errors

Use IFERROR syntax in your formulas to prevent unsightly errors from appearing in your report. For example, if you’re using a formula to calculate cost per conversion use IFERROR to replace an error with a blank cell:

=IFERROR(G70/H70, ” “)

 

Add metric selectors

Use data validation if there are several variables in your report that influence the analysis. You could make a report where Google Analytics segments such as age groups or converters are used to filter the data. Data validation and FILTER formulas will make your report interactive and will save space from filling out columns of additional tables.

Filter formulas are surprisingly easy to to use and are very powerful for organising your data. In the example report, I wanted to include an option for the user to select any metric they need to review. A data validation field does the trick here:

I then set up a FILTER formula in each of the data fields to return only the values of the metric selected:

 

=FILTER($B$15:$H$15, $B$2:$H$2 = A119)

 

Yellow text = the range to reference

Purple text = the columns to check for the specified condition (selected metric)

Cyan text = the data validation cell to reference (drop down box)

Selecting one of these metrics will dynamically change the data within respective fields. The result? An interactive report!

FILTER formulas are used frequently in Supermetrics Template Gallery. The simplicity and functionality makes it easy to produce dynamic marketing reports. Your planning will tell which variables you need to report on and using FILTER formulas will undoubtedly make your report easier to use.

Want to take this functionality to the next level? Take a look this article by David Krevitt on Google Sheet Queries. Queries go above and beyond filter formulas but take some practice.

Automate the boring stuff

Why spend time on the boring stuff? Automate your report and get down to business.

Schedule a data refresh

This is where Supermetrics really comes into its element. Set up a data refresh when it suits you and your client. If you’re confident that the report is exactly what your client needs to see without any additional commentary or notes, use the scheduled email feature to send it straight to their inbox.

There’s nothing like being able to inform a client that their report is ready to view first thing at the beginning of the month. If you’ve planned your report carefully enough you can rest easy in the knowledge that you can get straight down to analyzing t the right time.

Tip: set up your scheduled update in the early hours of the morning. This way, you will get your data at first light and allow some room for slow internet connectivity.

Use date formulas

There’s no need to rewrite date values every time you want to send your report. For example, the formula below will write the previous months date:

=( TEXT(TODAY()-DAY(TODAY()),”MMMM YYYY”))

 

Use automatic client name inputs

If you’re using a report template for more than one client, using this formula will extract the client name (if you’ve named your advertiser account appropriately) from your Supermetrics queries:

=IFERROR(LEFT(REGEXEXTRACT(SupermetricsQueries!W21,”\`(.*?)\]”),(LEN(REGEXEXTRACT(SupermetricsQueries!W21,”\`(.*?)\]”))-1)), “Client Name”)

 

Create dynamic headline statements

You can create simple if statements to highlight trends in certain metrics. Let’s say that you wanted to create a bold statement at the start of report which your users could instantly relate to. The formula below asks if the referenced cell contains a value that has increased or decreased. Either way, it will produce a statement to save you time:

=IF(F7 = E7, “clicks didn’t change”, IF(E7 > F7, “Clicks increased”, “Clicks decreased”))

 

Summary

With some investment, your report can be dynamic, easy to update, and easy to read. Start with your data and build up your functions. Keep in mind what you originally planned and don’t forget your audience.

Remember: Functionality first, design second. Your report should look good but reports are designed for data analysis first!

Design

So you’ve got your functions covered. The client is happy with what the report does. But how should it appear? How much time should you invest in appearance?

There are a few musts when it comes to report design. Don’t skip these!

• Hide grid lines
• Highlight the key details
• Make it clear what the data is trying to explain

Beyond the essentials, you may want to consider this;

A report is one of the few tangible items that your client will receive for your fee.

While they may want it to be basic, easy to use, and perhaps not that detailed at all, aesthetic qualities will go along way in making your agency stand out.

Starting from scratch will ensure your layout is unique, bespoke to the client, and as close to your brand guidelines as possible.

Smart layouts

Place your key data headlines in prominent positions. I usually follow a left to right layout with the important details sitting at the top left of my reports. This is where the eye typically gets drawn to.

Show your colors

A clean layout and a simple color palette is all you need to create something bold and compelling. Use a secondary color to accent key parts of your report (trend lines, top level figures etc). Google’s material design guide can act as your inspiration for your color palette.

I know what you’re thinking here, this guide is for app design! The guide mentions UX on several occasions though and you would be wise to apply something that has UX at its core. The same principles apply for us and after all we want to deliver a high quality user experience. Think back to that last report that sent you to sleep! This is the answer.

I used a ‘corporate’ blue as a primary color with a modern shade of green as a highlight color:

Primary

Secondary

Want to add a drop shadow effect to your headers? Insert a line filled with a darker shade of your primary color. Then narrow the height of that row to its minimal size:

Graphs

Consider replacing conventional graphs with spark lines. They don’t indulge the same level of detail but do look a lot easier on the eye. Accompany your spark lines with period on period % changes and minimum and maximum values. Ensure you make it clear to the client what period of time the trend line is covering. If they are keen users of Google Sheets, insert comments as hover over disclaimers!

Spark lines aren’t for every report though. If detail is your primary aim, you will be better off including charts.

Summary

Behind functionality, getting your design on point will give you a really good chance of creating the perfect marketing report.

Design doesn’t always come top when creating most reports. I’ve seen some corkers in my time. But if you consider it a complimenting factor to your functionality, you could deliver something special to your client.

Remember to:

• Refer to your plan to organize your report layout
• Highlight the important details
• Use a simple color scheme and a secondary color as an accent
• Consider simplifying graphs to spark lines

Maintenance

Prepare your report to be future proof!

A perfect report should cause no headaches when being actively used. Ensure your report doesn’t fall short when being updated with new data. Then, make it easy for the end user not to break your valuable formulas!

The only thing to consider is that client circumstances will change. These changes will need to be passed on to your report. Values, KPIs, and layouts may need to adapt. Your report should handle this. You know you’ve failed when you’ve had to create a new one from scratch.

Want to find out how to make your report future proof? The pointers below will give you the best chance:

1. Prepare for change. You and your stakeholders will want to see different analysis on different occasions.
2. Follow the logic of your formulas from calculation to delivery. Think about different data scenarios. What happens if zero values are returned? What if the list of dimensions grows?
3. Prepare a document explaining the ins and outs of your report. What happens if things go wrong while you’re kicking back on the beach?
4. Protect your valuable ranges. Provide a warning to other users that they are about to change parts of your report that are vital for its outcome!
5. Back up a blank copy of your original report template. Your report will look a lot different after it’s been through client sign off!

It can be easy to overlook these points when making your report. After all, your primary focus will be on delivering your report on time to your client. Set aside time for this though and you will be doing yourself a big favor!

Closing thoughts

Now you’ve made it this far, you’re close to creating your perfect report. Keep these principles in mind:

• Planning makes perfect
• Functionality delivers
• Design reflects you
• Future proof your report

At the start of this post, we set out that perfection is:

• Minimal maintenance
• Maximum observation

As with anything, it’s easier to achieve an objective if you know your aims. If you think you’re spending too much of your time on maintenance, take a step back and rethink your report foundations. If you’re not delivering observations and recommendation, ask yourself if you’re report is focusing on the right dimensions and metrics.

Happy reporting!

About Stephen Dawson

Stephen Dawson is a PPC & analytics expert at Fingo Marketing, a cross channel integrated agency, and a guest writer for Supermetrics. Inspired by meaningful data and report aesthetics he is a big fan of automation and bespoke layouts. Passionate for delivering an impact on bottom line objectives, especially for start ups and charities. You can find him on LinkedIn.