How to build a Centralized Marketing ROI Template using Supermetrics

Are you overwhelmed by the number of ever-growing marketing reports in your organization?

Is your marketing data scattered over multiple systems?

Are you wasting time pulling reports from different data sources and comparing them?

Do you have problems in centralizing all your data sources?

Do you have problems in finding the right ROI of your marketing efforts?

Are you overwhelmed by the number of ever-growing marketing reports in your organization?

Is your marketing data scattered over multiple systems?

Are you wasting time pulling reports from different data sources and comparing them?

If you face the above problems, you have come to the right case study solution and post to look at, read on to learn more about this.

 

 

Project details & client requirements

The client, an E-commerce retailer wanted to clearly understand their marketing channels ROI so that they can do an optimum distribution of their marketing expenditure.

They were overwhelmed by the number of marketing channels that they were using, the number of reports they were handling and the chaos that all of it created in their team in terms of prioritization.

They wanted to be done with it and create a single source of truth in a basic setup.

They wanted a singular marketing report, a source of truth, where they have every valid marketing channel ’s data, their spend, revenue and the final calculated ROI. They wanted a simple solution which is live and real-time atleast from a daily perspective.

 

 

Approach & Tools to use

This is a very common problem that a lot of our clients face and which is why we build multiple executive dashboards for various business use cases

For any type of business problem like this, you must ask questions to understand the best set of tools to use:

1. How many data sources/connectors do you have for your business that you would want to see in the dashboard?
   1.  Examples of data sources/connectors – Shopify for E-Commerce data, Google analytics for Website data, Facebook for social data, Xero for financial data and so on.
2. How do you want the end product to look like for periodical use? How much do you value the UI/UX/ease of use in your dashboard? What additional features do you really need?
3. How many levels and depths of visualization do you want to see? How many customizations would you need in your final views of the dashboard?
4. Who will be creating the dashboard and how much time will it take?
5. Lastly, what are the costs associated with the dashboard software for the long term and does it fall within our budget?

We had a discussion with the client based on the questions above.

They wanted a basic setup here inside Google sheets only, their main internal collaboration tool. It did make sense, because the data was also not very large.

You can also see the list of tools that we recommend using for more information.

We built this marketing roi template from scratch for the clients using a basic Google Sheets + Supermetrics setup. We’ll be showing you how the project went from start to bottom and how you can use simple tools like Sheets + Supermetrics to build complete reports for clients

 

 

Building a master marketing tool – Marketing ROI Template

After deciding the tools, we had to get started in building the master marketing report.

Initially, the entire list of marketing channels, their data sources, and their importance was considered.

In most cases, except some of the channels, we decided to use the basic ROI formula.

ROI = (Revenue / Spend)

The monthly view was best for them

Here are a couple snapshots of a part of the final report shown below

Note: All numbers are shown as dummy data and all client data is hidden

This was built by creating multiple sub-sheets that power this main sheet. The back end was in all the sub-sheets, which were of two formats

1. Spend data
2. Revenue data

This backend was created using Supermetrics automated reports that were set to run daily and give out latest data accordingly for each report and data source

Eg: All of Organic data was in a couple of sheets for spend and revenue. Both of these had reports set up for data to flow in.

After this, the main sheet (screenshots above) has basic formulae set up so that it picks up all the aggregated final data in the right rows and columns as per the months in the top row and from all the backend sheets.

 

 

How to use Supermetrics for marketing roi template & other reports

Getting started 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 the below video.

 

 

The building blocks of my report included just a few main queries which was easy enough to build from scratch in the backend sheets.

We were splitting everything by day and adding variables like Spend & Revenue in the following columns. We need to mainly look at the following to build a query :

1. Data source
2. Metrics
3. Date ranges
4. Filters

This is what a sample query looks like inside Supermetrics

You can now try and explore out the following:

• 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 etc)

All of the above is quite intuitive for basic metrics and data that needs to be pulled from your data sources. Supermetrics support has very good documentation & also live help whenever needed.

If you like all of the above and feel that it can help you, I would urge you to go ahead and sign up for a free trial of supermetrics right now.

If you have any questions or need any help in building automated reports using Supermetrics, feel free to contact us or comment below.

 

This post was exported in one click from Google Docs into WordPress with Wordable.