How to Improve Your Startup’s Weekly Marketing Report

Overview

As an operator, you’ve undoubtedly consumed marketing KPIs from consultants, employees, or vendors—but creating a report from scratch may be a new challenge. This guide will help founders create a dashboard that’s easily adaptable to common marketing channels and other data sources. We’ll also provide industry context on marketing metrics, and we’ll help you get started on your own marketing dashboard by walking you through a customizable template for weekly reporting.

In this guide, we’ll cover:

1. Defining the purpose of a dashboard
2. Creating data sources and taxonomy
3. Deciphering data movement and automation
4. Applying marketing taxonomy to raw data
5. Interpreting your dashboard
6. Ensuring quality assurance
7. Resources we love
8. Takeaway & next steps

‍
We’ll also show how to use the three sections in the Weekly Marketing Dashboard Template to create a simple but effective reporting template:

• Sources of raw data
• Automation of lookups and quality assurance checks
• Dashboards of visual reports

Compiling paid marketing reports across all channels can be challenging, but this guide can make it easier for early-stage founders to start. This guide will be most helpful for founders of post-revenue consumer tech companies who are spending money on paid marketing and who have decided not to use an off-the-shelf dashboard for marketing performance measurement. If you don’t know whether any off-the-shelf marketing business intelligence (BI) solutions are right for you, we hope you'll reach out to us.

Let’s begin by making a copy of this dashboard template so you can follow along.

Weekly Marketing Dashboard Template

Any consumer tech founder needs to understand which marketing channels are efficiently reaching customers and driving them to the product. Without that knowledge, founders will find it difficult to consistently make good marketing decisions that keep them ahead of the competition.

‍

Defining the purpose of a dashboard

At minimum, an effective marketing dashboard must convey the health of the marketing funnel to an executive audience. Naturally, the details will vary by company, but most marketing dashboards should summarize these types of metrics:

• delivery metrics (e.g. spend, impressions)
• outcome metrics (e.g. app downloads, purchases)
• efficiency metrics (e.g. cost per conversion, return on ad spend)

‍
Outcome metrics should provide quick feedback for marketers to track and optimize against, but the best outcome metrics are good leading indicators of a company’s North Star metric.

For an e-commerce company focusing on growing contribution margin, New Customer Return on Ad Spend (ROAS) might be a better efficiency metric than Customer Acquisition Cost (CAC), because the size of a customer’s first order is probably a good leading indicator for a customer’s total Lifetime Value (CLTV).

Much like a car’s dashboard, the purpose of a marketing dashboard is to convey the health and status of the marketing funnel, and to draw attention to potential issues.

Dashboards aren’t diagnostic tools; they’re intentionally coarse. Second- and third-order questions like “Why did the Non-Brand Paid Search ROAS decrease last week?” and “Which keywords should I exclude from my campaign?” can only be addressed with more granular data and diagnostic metrics.

Some business intelligence tools, like [Looker](https://looker.com/), can be set up to provide a high-level dashboard while also allowing dynamic slicing and drilling for second- and third-order questions—but most early-stage growth companies don’t have the resources to purchase and administer these tools.

In this guide, we’ll provide a spreadsheet-based alternative to costly enterprise business intelligence solutions that is right-sized for many seed and Series A companies.

Download the Weekly Marketing Dashboard Template to follow along.

Weekly Marketing Dashboard Template

‍

Creating data sources and taxonomy

Tabs referred to in this section: “Reference” | Data sources, Marketing platforms, Site/app tracking, Marketing taxonomy, Dashboard dimension, UTM naming rules

So, you’ve decided that you need a marketing dashboard.

Your task at hand is to compile standardized and relevant marketing metrics from several different sources and organize them into a comprehensive and coherent view. At this point you should be thinking about your data sources and the marketing taxonomy that you’ll use to organize your data across platforms.

Data sources

Most of your data sources will fall into two categories:

1. Marketing platforms provide delivery and outcome data.
2. Site and/or app-side tracking (a.k.a. owned properties) provide data about actions that happen on your website or app.

Marketers use marketing platforms like Google and Facebook to create campaigns, which are high-level categories of ads that have unique tracking links. When a user clicks these links, the tracking information embedded in the ad is passed to your website or mobile app. The marketing platform keeps track of every impression and ad click. Marketing platforms can also collect conversion data from your site or app and attribute it to specific impressions or clicks. Marketing platforms use customizable attribution rules to attribute conversions to ads.

Site and/or app data comes from the user’s “click stream” on your owned properties. This data is captured by first-party browser cookies or mobile apps. It’s commonly visualized with the help of software like Google Analytics and Amplitude.

When a user clicks an advertising link and lands on your owned property, the advertising link usually includes UTM variables that pass information to your website. UTM variables are oftentimes called “UTM codes” or “UTM parameters” but they all refer to the same thing. Learn more about UTM links

This process works a bit differently for mobile apps, but marketing technology (martech) partners like Branch can attribute app sessions to UTM parameters (e.g. mediums, sources, campaigns) in a similar way. Click-stream data in these sources can be used to create outcome metrics, like sessions or purchases—however, site and app data can’t provide delivery metrics, because your website and app can’t collect any data about the ads that weren’t clicked.

Marketing taxonomy

Every impression and ad click is part of a marketing campaign—whether it’s an email open or a YouTube impression—and campaigns roll up into marketing channels.

Your marketing taxonomy defines the levels of the hierarchy and the campaign naming rules that govern how a campaign is categorized into Channels and Subchannels. A sample marketing taxonomy for paid search and social may look something like this:

The marketing taxonomy shows the marketing dimensions that your dashboard will display (Channel Type, Channel, and Subchannel) but also—importantly—maps those dimensions to the UTM parameters.

Dashboard dimensions: Channel Type, Channel, and Subchannel

Channels and Subchannels are usually the highest levels of your marketing taxonomy, but some marketers find it helpful to add Channel Type as a level higher than Channel.

In the example in Figure 2.1, the highest level is Channel Type, which just separates “Paid” channels from “Organic” ones. The next level separates ads by Channel—essentially, format. Common digital channels are Paid Search and Paid Social. Subchannels are one level lower in the hierarchy, and some common examples include: “Prospecting” versus “Retention” or “Text Ads” versus “Shopping Ads.”

UTM parameters: Source, medium, campaign

Once the levels of the marketing taxonomy have been defined, they need to be mapped to UTM variables. Think of the UTM variables as the “back-end” implementation of the marketing taxonomy, while the Channel Type, Channel, and Subchannel form the “front-end” levels of the hierarchy that the executive audience will see.

The UTM variables are set by marketers when they create an ad in a marketing platform, then they are passed into the site or app. The purpose of the UTM variables is to enable on-site user  behavior to be linked to the specific marketing tactics that drove the users to the site. The easiest way to ensure that the link is never broken is to ensure that marketing platform campaign names always match the utm campaign parameter. Many marketing platforms offer an option to enforce this rule.

We strongly recommend that marketers use a standardized naming convention for campaigns that allow the dashboard admin to infer Channel and Subchannel from the Campaign Name. We also strongly recommend that campaign names are not changed once set.

Sloppy campaign naming practices are guaranteed to cause confusion and data loss downstream.

In the example in Figure 2.1, the marketing taxonomy defines those rules. For example, all Paid Search - Brand campaigns start with the prefix “BR,” which allows the dashboard administrator to easily categorize campaigns into the correct Subchannel—more on this in Lesson 4.

Customize your marketing taxonomy to your use case

For the purposes of a starter dashboard, Subchannel is usually the finest grain of the marketing taxonomy worth displaying, but there are many more dimensions that can be folded into the campaign name and the marketing taxonomy. For example, a healthcare company might have separate campaigns for patients and caregivers. Including an Audience Target dimension in the marketing taxonomy to separate them.

‍

Deciphering data movement and automation

Tabs referred to in this section: “Queries,” SOURCE tabs.

Once you’ve identified your data sources, you’ll need to start pulling them into the M13 dashboard template

First, a quick primer on the three sections of this Google Sheet:

1. Dashboard tabs are the outputs that aggregate data from the source tabs according to the lookups defined in the automation tabs.
2. Source tabs include raw data from marketing platforms and from owned site/app data sources."
3. Automation tabs allow the dashboard admin to review data flows, edit the marketing taxonomy, and quality check the data transformations in the dashboard.

Each of the source tabs correspond to a marketing platform (e.g. “Google”, “FBIG”) or an owned site/app data source (e.g. “GA Traffic,” “Event Traffic”). The number of source tabs that your Google Sheet has will depend on the number of marketing platforms that you want to track.

The source tabs in the template include:

• raw data (in Column F)
• lookup formulas (in Columns A-D) that organize the raw data into the Channels and Subchannels

We’ll cover the lookup formulas in the next section, but first let’s discuss data movement.

There are two methods for pulling data into the template: manual pulling or automated queries. Some data sources are easier to automate than others, and it’s likely that your final Google Sheet dashboard will include a mix of manual and automated data sources.

For manual data entry, it’s vital that you minimize the number of transformations you make to the data you’re pasting in. Remember: Any edits you make between the platform raw source data and the template source tabs will need to be imitated every week by the dashboard admin.

‍

‍

Luckily, there are many SaaS options for low-cost data movement between commonly used platforms—including Google Sheets. Supermetrics is a provider that automates queries from sources like Google Adwords, Google Analytics, and Facebook into Google Sheets automatically. The “Queries” tab in the Template shows the status of existing queries. In this case, we can see that the Supermetrics queries were last active in April 2021.

Supermetrics is budget-friendly and easy to use, but it doesn’t have integrations with every marketing platform. If you find an alternative that you prefer to Supermetrics, reach out to us at data@m13.co so we can share it with the rest of the community!

‍

Applying marketing taxonomy to raw data

Tabs referred to in this section: SOURCE tabs, DASHBOARD tabs, “Reference”

With your marketing taxonomy and your campaign-level raw data, you have all of the ingredients you need for a marketing dashboard. The essential functions of this template apply the marketing taxonomy to the raw data, and aggregate it into summary tables and charts.

In this section, we’ll show you a few examples of how you can apply a marketing taxonomy to raw data.

There are two methods you can use to systematically infer the Subchannel from the Campaign Name: lookup or parse. This template shows examples of both.

The lookup method

Figure 4.1 shows an example of a lookup formula on a source tab. The lookup formula refers to a lookup table on the “Reference” tab, which is shown in Figure 4.2.

Figure 4.2 shows the top two rows of a lookup table that maps Google campaign names to Channels and Subchannels.

Most marketing platforms only have one channel (for example, the FBIG tab only has Paid Social campaigns), but some marketing platforms can have multiple channels. For marketing platforms that can have multiple channels, the Channel should be identifiable from the campaign name. Google is an example of a single marketing platform that contains data for several Channels (Display, Paid Search, Video).

The biggest downside with the lookup method is that someone needs to manually add new campaign names to this lookup table, and fill in the Channel and Subcahnnel to ensure all rows from the source tab are being matched.

The parsing method

‍
Parsing is an alternative to the lookup method.

In Figure 4.4, you’ll find an example of Subchannel being parsed from the Campaign Name field directly. All the TikTok campaigns in this example are named according to a strict convention, they all include a “PS” or “RT” prefix. This method is easy on the dashboard admin, but it leaves no room for error on the part of the marketer naming the campaigns. If the marketer accidentally creates a campaign without a standard prefix, they’ll cause a lookup error downstream. This is an example of a brittle process, but there are a few ways to de-risk it.

Combining the lookup and parsing methods into one

Figure 4.5 shows another example of the parsing method on the “FBIG” tab. In this example the parsing formula is referring to the first two characters of the Campaign Name, but the parsing formula uses a nested IF statement to fix parsing errors.

From this example, we can see that the naming convention requires the campaign name to start with either “PS” or “RT,” but there are a couple of non-conformant campaigns that start with “Stray.” When the parsing logic fails to find “PS” or “RT” in the campaign prefix, it defaults to a lookup reference—similar to the Google example in Figure 4.2.

This approach takes the best of both methods. The dashboard admin doesn’t need to manually categorize every campaign name—just the ones that deviate from the standard naming convention.

Remember: Any time you move or transform data, you risk introducing an error downstream.

In the case of this dashboard, your executive stakeholders are downstream, and they’ll use this dashboard for high-level budget allocation decisions. It’s worth having an intentional strategy to mitigate errors. We’ll cover some quality assurance strategies in Lesson 6.

‍

Interpreting your dashboard

To summarize, only marketing platforms can provide complete delivery information, but both marketing platforms and your owned site/app provide data about outcomes. However, conversion data from platforms usually won’t match conversion data from your site or app, because the tracking technologies have different limitations.  

Marketing platform and site/app tracking discrepancies

Let’s take a YouTube video ad as an example. Imagine that your ad reaches a prospect on YouTube before her video plays. She doesn’t click on the ad. Instead, she opens a new tab and purchases something on your site while listening to her video in the original tab.

• In this example, your site click stream would show a conversion from a new visitor through a direct address bar type-in.
• As far as Google Analytics is concerned, this would look like an organic conversion from the Direct channel.
• However, Google Ads would be able to link the conversion to the impression and Google Ads would attribute that conversion to the Video channel.

There are many reasons why owned data sources will contradict platform data, but the contradictions aren’t necessarily problematic. It’s impossible to reconcile reporting differences across multiple platforms. That isn’t the goal. You should aim to understand the limitations of the data, and make the best decisions you can under ambiguity.

Marketing platform data

Many platforms use a combination of click-through and view-through attribution to create performance metrics (e.g. 7-day click, 1-day view). We strongly recommend using the platform data to track a channel’s performance over time, and to make optimization decisions within the platform.

However, marketing platforms can’t help you understand the broader trends driving your business. You’ll need to refer to your owned data sources to truly understand broad trends in your business.

Site and app (owned) data

Sources like Google Analytics or Branch are vital for understanding your total traffic and business performance, but they lack the sophistication that marketing platforms use for ad attribution.

Site and app data will only attribute traffic to the “last click” that led the user to your site. These sources are likely to underemphasize tactics that drive awareness (social, video), and overemphasize channels that capture demand (search, affiliate).

As we saw in the earlier YouTube example, many marketing channels also drive traffic to organic sources like direct and organic search. Despite that, it’s crucial to understand the trend in unpaid versus paid traffic to understand how reliant your business is on paid marketing for growth.

Dashboard views

The marketing template shows three examples of dashboard views that are fairly typical:

• Growth W/W: Shows spend, outcomes, and efficiency metrics at a Channel grain for the last complete week versus the previous week. This tab includes data from marketing platforms as well as from Google Analytics."
• Growth L6W: Shows a 6-week trend for site/app data at an aggregate level, and shows overall efficiency metrics for the entire marketing budget.
• Monthly - Subchannel: Shows a single efficiency metric—ROAS in this example—at a monthly level by Subchannel (Z61:AB71)

Relevant metrics

Edit the efficiency metrics on your dashboard to reflect your business priorities. For many startups, ROAS isn’t the best leading indicator of their North Star metric. For example, Airbnb famously chose “nights booked” as their North Star metric, so their marketers probably weren’t optimizing toward a ROAS target.Tabs referred to in this section: DASHBOARD tabs

‍

Ensuring data quality

Tabs referred to in this section: SOURCE tabs, DASHBOARD tabs, “Sums Check”

In Lesson 4, we broke down the formulas that apply your marketing taxonomy to the raw source data, then aggregate it into KPIs on the Dashboard tabs. We also showed some examples of process failures that can lead to issues.

Our goal in this section is to create a quality assurance process that runs automatically and fails loudly. Be wary of QA processes that require tedious spot checks or that can fail silently.

Whenever data is moved or transformed, you should anticipate the likeliest process failures or bugs, and have a strategy for mitigating _and_ correcting them. The most obvious likely issues for this marketing dashboard are:

• process failures for data ingestion
• formula errors in the workbook itself
  ‍

Mitigate errors during data ingestion

It’s tedious to compare numbers in a marketing platform to numbers in a dashboard, so it’s probably not feasible to do so on a weekly basis. Therefore, we should aim to mitigate opportunities for error in the data movement process from a marketing platform to the Google Sheet. We can mitigate errors by minimizing the number of steps required to copy data from marketing platforms to source tabs, or by using a data movement SaaS solution to automate the queries.

ETL best practices aren’t just for data engineers

Between the raw source tab and the dashboard, there are many formulas—opportunities for error—but they don’t need to be QA’d individually. We can QA most of them at once with a source-to-target test, which is a logical test that confirms the data at the source matches the data at the destination.

Source-to-target tests are common in data warehouses, but the theory can be applied to spreadsheets easily. In this case, the sources are the raw data table beginning in Column F of the source tabs, and the targets are the “total” rows in the dashboard tabs.

Each row highlighted in light gray on the Sums Check tab represents a single test. Each test ensures the sum of data from a source—or a set of sources, in the case of Figure 6.1—matches the sum of data at the target. Note: the formulas in Column H are written to ensure that a spreadsheet “#N/A” error also results in a “1.”

If any of the spend or revenue totals on the dashboards don’t match the spend or revenue totals from the source tabs, the corresponding test will fail. Each dashboard tab has a conditionally formatted cell referencing the Checks Failed total, so that tests fail conspicuously.

Think about test coverage anytime you make a change

Obviously, it’s vital to reflect on test coverage after you’ve set up your tests. With the source-to-target tests we can be sure that every dollar spent, and order captured in the source tabs is being aggregated into the dashboard, but the tests don’t ensure that they’re being aggregated to the correct Channel or Subchannel, so you’ll need to be mindful of the process you use to create campaign lookup tables.

Be intentional about error mitigation when writing formulas

There are also calculations on the dashboard tabs that aren’t covered by the Sums Check QA, such as the efficiency metrics in Column I of the Growth W/W tab. The spend, orders, and revenue columns are being aggregated separately and their totals (in cells C14, E14, G14) are being QA’d. However, the efficiency metrics (Cost per Order, ROAS) aren’t being referenced at all by the Sums Check tab. This template has been set up so that the efficiency metrics are based on a simple formula, with minimal opportunity for error.

An alternative approach—that we _wouldn’t_ recommend—is to collapse both the spend aggregation and order aggregation into a single formula for Cost per Order on the dashboard tab. We don’t recommend this approach because it’s more difficult to QA a complex formula than it is to QA spend and order sums individually.

‍

Resources we love

• A Handy Guide to UTM Codes: Know Which of Your Campaigns Really Work: If you aren’t familiar with UTM codes, this is a great high-level intro. [The Daily Egg]
• The 28 Advertising KPIs That Every Marketer Should Be Tracking: We don’t think any marketer needs to track 28 KPIs, but this guide summarizes the most important, roughly in order, and their relationships to each other. [Databox]
• Choosing Your North Star Metric: Marketing metrics should be leading indicators of North Star metrics. Learn how dozens of successful startups defined theirs. [a16z.com]

‍

Takeaways & next steps

By now, you should be familiar with the essential inputs of a marketing dashboard and the mechanics that turn the inputs into executive-level KPIs.

Marketing dashboards omit many details by definition, but a customer acquisition funnel is a very complex system with many levers and components. This dashboard is guaranteed to elicit more questions than it can answer.

Many companies have built a practical and intelligible business intelligence suite by starting with simple dashboards, then using next-level questions to inform the requirements for the next round of iterations. It doesn’t hurt to know what you want before you undertake a large data modeling project or evaluate a software solution.

As always, please reach out to data@m13.co with any questions about data strategy.