How to build a unified ad report in Google Sheets with OWOX Data Marts
A unified ad report is one table with one row per date, source and campaign. The route that holds up is each ad platform into a data warehouse, one data mart that unifies them with SQL, then Google Sheets as a destination – all in OWOX Data Marts.
If you run ads on Facebook, Google, LinkedIn and TikTok, you know the question: how much did each channel spend yesterday, and what did it bring? The answer you want is one Google Sheet that updates every morning, with one row per channel per day.
A sheet can show that table, but it should not be where it is built. The route that holds up is each ad platform into a data warehouse, one data mart that unifies them with SQL, and Google Sheets as a destination. OWOX Data Marts runs the whole path, on OWOX Cloud or self-managed as free, open-source software.
What a unified ad report needs
What a unified ad report needs
- One grain. One row per date, source, medium and campaign, so any two channels can be compared or summed.
- Shared naming. The same source, medium and campaign spelling as your web analytics, so spend can be set against sessions and revenue.
- Each platform’s own numbers, labelled. Spend, clicks, impressions and the conversions each platform claims, kept apart from what your store recorded.
- A refresh nobody has to remember. New data loads on a schedule and the sheet follows.
How the pieces fit
- One connector data mart per ad platform loads that platform’s raw data into your data warehouse.
- One unifying data mart reads those tables with SQL and outputs the shared grain.
- Google Sheets is a destination. The unifying data mart publishes a report there, or you pull it with the OWOX Data Marts extension.
OWOX Data Marts does not transform your data for you. The analyst writes the SQL; OWOX schedules, governs and publishes it.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Step 1: Load each ad platform into the data warehouse
Step 1: Load each ad platform into the data warehouse
Every platform gets its own data mart of the Connector type (the connectors are open source), so each can have its own credentials, fields and schedule. The step-by-step guides for each one are below; they all end with the data in your data warehouse, and you only need the platforms you actually run:
- Facebook Ads
- LinkedIn Ads
- TikTok Ads
- Microsoft Ads
- Reddit Ads
- X Ads
- Google Ads: see the Google Ads data source
Each guide ends with a Google Sheets report for that one platform. For a unified report you skip that part and go to step 2.
Step 2: Unify the platforms in one data mart
Step 2: Unify the platforms in one data mart
OWOX’s demo e-commerce project does exactly this. Its Unified Ad Spend data mart describes itself as spend, clicks, impressions and each platform’s own conversion claim from every paid platform the business runs, brought together on one daily grain with a common source, medium and campaign naming.
The mart is defined by SQL inside the data mart: one SELECT per platform over the table its connector wrote, all joined with UNION ALL. Every block is commented with the connector data mart it draws from and links straight to it.
Writing that SQL is where the platform differences get settled, once, in the open. A few from the demo project:
- Facebook returns conversions as a JSON array of actions, so the SQL picks out the purchase entry.
- Reddit reports spend in micros, so the block divides it, and it adds click-through and view-through purchases into one conversions column.
- X Ads uses
url_clicks, notapp_clicks, because the store is a web store, and it exposes no conversion fields, so its conversions areNULLrather than a false zero.
Those choices live in one reviewed query instead of in cell formulas nobody can see. The per-platform guides in step 1 show each platform’s own block in detail.
Step 3: Publish the unified data mart to Google Sheets
Step 3: Publish the unified data mart to Google Sheets
Open Destinations in the left menu, click New Destination and choose Google Sheets (more on the Google Sheets destination). The same list holds Looker Studio, Slack and email, so the same data mart can feed all of them.
Then bring the data into a sheet in one of two ways. Both are part of spreadsheet reporting.
Option A: a report on the data mart.
- Open the data mart, go to the Destinations tab and click New Report under your Google Sheets destination.
- Give the report a title and share the Google document with the address the form shows.
- Paste the URL of your Google Sheet, or click New Sheet to create one.
- Choose the report columns you want in the sheet.
- Click Create & Run report.
Each report is listed on the Destinations tab with its last run and status.
Option B: the OWOX Data Marts extension. Install OWOX Data Marts from the Google Workspace Marketplace, open it inside Google Sheets and pull the data mart from there. If your team works in Excel, there is an OWOX Excel add-in too.
Step 4: Schedule the refresh
Two schedules keep the report current, and both are triggers:
- On each platform’s data mart, add a Connector Run trigger so new data loads into the data warehouse daily, weekly, monthly or on an interval.
- On the unified data mart, add a Report Run trigger so the sheet refreshes after the data has landed.
Set the report trigger later than the connector triggers, so the sheet never reads half-loaded data.
Why the sheet is not where the data lives
Why the sheet is not where the data lives
- The sheet has a ceiling. A Google Sheets file holds at most 10 million cells. Ad data spends cells quickly: every day, source and campaign is a row, and every field is a column. The data warehouse has no such limit, and the sheet only needs the columns the report uses.
- History outlives the sheet. Loaded data stays in the data warehouse, so a tab that is overwritten or an API that changes its schema does not take last year’s numbers with it.
- One number in every place. The same data mart feeds the sheet, the dashboard and the Slack message, so they agree.
- Credentials stay out of the sheet. Business users read the report without any data warehouse credentials.
Ask the unified question in plain language
Ask the unified question in plain language
Once spend is one governed data mart, nobody has to write the join to ask about it. Connect Claude to OWOX Data Marts with the OWOX Claude connector and a marketer can ask, for example:
Which campaigns are scaling spend without a matching lift in orders?
Claude asks OWOX which data marts are relevant, reads their fields and the joins the analyst defined, and queries the one it picks. Here is the answer it gave against OWOX’s demo e-commerce project: Reddit was the clear problem, with spend up 9% and conversions down 9%, and one TikTok campaign came second, while across all paid channels spend grew slower than conversions.
MCP is the interface here, not the guarantee. The guarantee is the governed data mart behind it, and every question is logged in Run History. For how that works end to end, read governed MCP analytics. To blend the same spend with your CRM or web analytics, see ad spend with CRM data and GA4 conversions with ad spend.
What it costs
OWOX Data Marts is free and open source when you run it yourself, connectors included, and the code is on GitHub. For OWOX Cloud, see the pricing page. Your data warehouse has its own costs, set by your provider.
What’s next?
- Start with one platform. Pick the guide from step 1 for the channel you spend the most on.
- Browse the connectors. The connectors page lists each source and the data warehouses it loads into.
- Read the code. Check out our GitHub repo, leave us a ⭐ star, and help us build more connectors.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Can I combine ad data from multiple platforms into one report?
Yes. Load each platform into your data warehouse with its own connector data mart, then write one data mart in SQL that reads those tables and outputs one row per date, source, medium and campaign. That unified data mart is what you publish to Google Sheets.
Is there a direct connector from each ad platform to Google Sheets?
No. The route is the ad platform, then a data warehouse, then Google Sheets. Google Sheets is a destination of the data mart, through a report or the OWOX Data Marts extension.
How do I get one row per channel per day in Google Sheets?
Build a unified data mart whose SQL selects from each platform’s table at the same grain: date, source, medium and campaign, with spend, clicks, impressions and conversions. Publish it to Google Sheets with a report, and add a Report Run trigger so the sheet refreshes every morning.
How often does the unified report refresh?
You set it with triggers. Connector Run triggers load each platform into the data warehouse on a schedule, and a Report Run trigger on the unified data mart then pushes the result to the sheet. Schedule the report after the connectors so it never reads half-loaded data.
Do I need to write SQL to unify ad platforms?
Someone has to write the unifying query once, usually an analyst. OWOX Data Marts does not transform data for you: it schedules, governs and publishes the SQL you write. Once it exists, marketers read the sheet or ask questions through OWOX MCP without writing any SQL.
