Copying and pasting campaign data into a spreadsheet is the worst hour of an analyst’s week. It’s boring, it’s easy to get wrong, and a single misaligned paste can quietly poison a whole month of reporting.

This is a 7-step method for building an automated marketing dashboard in Google Sheets. Setting it up the first time takes a while because you’re learning the pieces. Once you’ve done it once, rebuilding it for a new client takes under 30 minutes.

Step 1: centralize the data

Every report starts with clean, centralized data. You want all the raw information inside one Google Sheet, split into a separate tab per source. Decide which sources feed the report, then pull them in.

Option A: automatic connectors

Connectors like Detrics pull data from Meta Ads, Google Ads, Shopify and the rest straight into your Google Sheet. If you already have Detrics installed, open a new Spreadsheet and launch the sidebar from Extensions > Detrics > Launch to start setting up automated data flows.

Step 1 - Open Detrics

Step 1.2 - Configure Query in Detrics

Set up a tab for each source: “Raw_Meta”, “Raw_Google”, “Raw_Shopify”. Schedule the updates daily and manual downloads stop being your problem.

Tip: color-code your columns so you can tell raw connector data apart from anything a formula produced. In the example below we use ARRAYFORMULA to stamp the channel name into each tab. ARRAYFORMULA applies a formula to a whole row or column at once, so you never have to drag it down again when the data refreshes and the table gets longer.

The headers in black carry raw information straight from the connector, exactly as the platform sent it. The column in blue, where we define the channel, is generated by a formula.

How the ARRAYFORMULA works

=ARRAYFORMULA( IF( ISBLANK(A2:A) , , "Meta" ) )

Paso Array Formula

  1. ARRAYFORMULA activates “for the entire column” mode.
  2. Inside each cell, the IF starts working. The first thing it does is ask ISBLANK: “Hey, check the adjacent cell in column A. Is it empty?” (For example, if the formula is in B2, it checks A2. If it’s in B3, it checks A3, and so on).
  3. ISBLANK responds to the IF with a TRUE or FALSE.
  4. The IF makes the final decision based on the response:
    • If ISBLANK says TRUE (the cell in A is empty), the IF formula returns the second value. In your formula it’s ,,. That double comma with nothing in between means “do nothing, leave it blank”.
    • If ISBLANK says FALSE (the cell in A is not empty), the IF formula returns the third value, which in this case is the word “Meta”.

Without a connector, the manual route still works. Export each platform’s report (a CSV of Meta Ads campaign data, say) and paste it into its tab.

Key advice: keep the column structure identical across every tab. A typical order is Date, Campaign, Investment, Clicks, Impressions, Conversions, Revenue. In Detrics you can reorder columns after running your queries with the Sort & Order fields function.

Step 2: unify the raw data

Your data now sits in separate tabs. Next you merge it into one master table. Create a new tab called “Consolidated”.

Why one consolidated table

With everything in a single table, your summary formulas (sums, averages) point at one range instead of five. That one decision removes most of the fiddly work later on.

Using VSTACK to stack data

If all your raw data tabs have exactly the same columns in the same order, VSTACK is your best option. This formula “stacks” data ranges on top of each other.

In cell A1 of your “Consolidated” tab, write:

={'Raw_Meta'!A1:G1; VSTACK('Raw_Meta'!A2:G; 'Raw_Google'!A2:G)}
  • {'Raw_Meta'!A1:G1; ...}: This takes the headers from one of your tabs.
  • VSTACK(...): Vertically stacks all the data (without headers) from the Raw_Meta and Raw_Google tabs.

Using QUERY to unify and filter

QUERY is SQL inside Google Sheets. You can select, filter and sort with it, which makes it the most flexible tool in this whole build.

Important: the columns in Raw_Meta and Raw_Google have to be in the same order. If they aren’t, the consolidated table will silently misalign, which is the kind of bug you find three weeks later in a client meeting.

To unify, you can use a construction with curly braces . In cell A1 of “Consolidated”, write:

={QUERY('Raw_Meta'!A:G, "SELECT * WHERE A IS NOT NULL");
QUERY('Raw_Google'!A2:G, "SELECT * WHERE A IS NOT NULL")}
  • {... ; ...}: The curly braces and semicolon stack the results of the two QUERY queries.
  • "SELECT * WHERE A IS NOT NULL": Selects all columns (*) but only from rows where column A (the date) is not empty. This avoids bringing blank rows.

Step 3: name your ranges

Most people skip this step. It is the one that decides whether you can still maintain the sheet in six months.

What is a named range and why use one?

A named range is a label you attach to a set of cells. Instead of writing the investment column as Consolidated!D2:D1000, you call it “Consolidated_Investment”.

SUM(Consolidated_Investment) tells you what it’s summing. SUM(‘Consolidated’!D2:D1000) does not. When the table grows past row 2000, every formula pointing at the named range follows it without you touching anything, and because the reference is descriptive rather than positional, dragging formulas around stops producing off-by-one disasters.

How to create a named range in 2 clicks

  1. Go to your “Consolidated” tab and select the entire column you want to name (e.g., column D, which contains investment).
  2. Go to Data menu > Named ranges.
  3. A sidebar will open. Write a descriptive name like Consolidated_Investment (no spaces) and click “Done”.
  4. Repeat this for your key metrics and dimensions: Consolidated_Country, Consolidated_Date, Consolidated_Clicks, Consolidated_Conversions, etc.

Step 3 - Named Ranges

Step 4: design the front end

With the table built and the ranges named, you can start on the part people actually look at.

Creating the “Dashboard” tab

Create a new tab called “Dashboard”. This is the only tab you show stakeholders, so keep it narrow and focused on the results you or your client care about.

Structuring the main KPIs

Lay out a simple table listing your most important metrics, and leave the result cells empty for now. Order them by business importance rather than by what’s easy to calculate:

  • Conversions
  • Total Investment
  • ROAS (Return on Ad Spend)
  • Impressions
  • Clicks
  • CTR (Click-Through Rate)
  • CVR (Conversion rate)
  • CPA (Cost per Acquisition)

Step 4 - Dashboard Frontend

A static report answers one question. Add a dropdown to filter by country, client or channel and the same sheet answers a dozen.

  1. Create an auxiliary tab called “Aux” or “Config”.
  2. In cell A1, use the UNIQUE formula to extract a list without duplicates of all countries, clients, or channels from your consolidated database
  3. =UNIQUE(Consolidated_Channel); =UNIQUE(Consolidated_Country)

Implementing data validation

  1. Return to your “Dashboard” tab. Select a cell where you want the dropdown menu to appear (e.g., C1).
  2. Go to Data > Data validation.
  3. In “Criteria”, choose “List from a range”.
  4. Click the grid icon and select the range from the “Aux” tab where you created the list of countries, channels, etc… To make it truly dynamic, choose the entire column without considering the header
  5. Click “Save”. You now have your dropdown menu!

Step 5 - Dropdown Menus

Step 6: populate the front end

Now you wire the logic to the display, using summary formulas that read from the named ranges and the dropdown filter.

Formulas for summarizing data

The workhorse here is SUMIFS (SUMAR.SI.CONJUNTO in Spanish), which sums a column only where conditions in other columns are met.

Example: In the cell where you want to display the total investment of your campaigns, write:

=SUMIFS(Consolidated_Investment, Consolidated_Channel, C1)
  • Consolidated_Investment: The range we want to sum (our named range!).
  • Consolidated_Channel: The range where we’ll apply the criterion.
  • C1: The cell that contains our dropdown menu.

Change the Channel in the dropdown and the investment figure updates on its own. Repeat the same logic for Clicks, Conversions and the rest.

We also lean on the SUM and FILTER combination:

=SUM(FILTER(Consolidated_Investment,Consolidated_Channel=C1))

With this combination, SUM will only sum the data that FILTER filters, and in this case, it’s when the channel from the consolidated base equals cell C1 (the dropdown we created)

Step 6 - Dashboard Formulas

Calculating derived metrics safely with IFERROR

The metrics worth reporting are usually ratios: CPA, ROAS, CTR. Ratios involve division, and division throws #DIV/0! the moment the denominator hits zero, which happens on any day with no conversions or no clicks. Besides looking sloppy, those errors break the charts that reference them.

Wrap the formula in IFERROR.

Example for CPA (Cost per Acquisition):

  • Standard formula (with risk): =Investment_Cell / Conversions_Cell
  • Safe formula (recommended): =IFERROR(Investment_Cell / Conversions_Cell, 0)

IFERROR reads as: “try this calculation, show the result if it works, and if it errors show this other value instead” (a 0, in our case). Your dashboard stays clean on the bad days as well as the good ones.

Step 6.2 - IFERROR

Step 7: formatting and charts

The report has to work. It also has to be pleasant to look at, or people won’t linger on it.

Branding and readability

Use your company’s colors, or the client’s. Set up conditional formatting (Format > Conditional formatting) so cells turn green or red depending on whether a value moved the right way, which is especially useful on things like CPA variation.

Creating the charts

  1. Evolution Chart: Select your date data and a key metric (e.g., Conversions) and go to Insert > Chart. A line chart is ideal for showing trends over time.
  2. Distribution Chart: To see which channels or campaigns contribute most, a pie or bar chart is perfect. Show, for example, the distribution of investment by source.

Step 7 - Charts

What you’ve got now

You’ve gone from a folder of disconnected exports to one sheet that refreshes itself: raw tabs per source, a consolidated table stacked with VSTACK or QUERY, named ranges holding the references together, and IFERROR keeping the ratios from blowing up on a zero-conversion day.

The point of all this is not that it saves you a few hours a week, although it does. It’s that the numbers stop being something you assemble and start being something you interrogate. Which is the part of the job worth doing.

Back to all posts