An ecommerce agency ran ads on Meta, Google, TikTok and Bing. Each platform showed different numbers, and SEO was reported separately. This project put everything into one clear dashboard.
ECOM AGENCY // META + GOOGLE + TIKTOK + BING → BIGQUERY → LOOKER
The main chart shows total revenue and total spend across all channels.
The bars show how much is spent on each channel. The funnel shows where visitors drop off.
SEO keywords appear on the same board, with their Google positions.
The agency ran ads on four platforms. Each one claimed credit for the same sales, so the totals never added up. Nobody knew the real return on ad spend.
SEO results sat in a separate report that was rarely opened. We moved all the data into BigQuery, calculated one combined number, and put paid ads and SEO on the same page.
Ad data from Meta, Google, TikTok and Bing is pulled into BigQuery every day, along with website and SEO data. SQL combines it into one set of numbers, and Looker Studio shows the results. Alerts run on top.
Meta, Google, TikTok, Bing — spend, clicks, conversions on schedule.
Site behaviour and organic queries — the unpaid half of the funnel.
Scheduled cloud functions land every source daily at 07:00 GST.
RAW → STAGING → MARTS. SQL dedupes cross-platform claims and defines blended ROAS once.
Six pages: blended, per-channel, funnel, website, SEO, alerts.
Pacing alerts to Slack + a weekly channel-shift summary.
Two more pages from the same dashboard: the channel table and the alerts page. Built in HTML with sample data.
PAID × ORGANIC // BLENDED IN BIGQUERY
| CHANNEL | SPEND | REVENUE | ROAS | CPA | SHARE |
|---|---|---|---|---|---|
| META | $18.2K | $58.9K | 3.2× | $19.4 | 47% |
| $12.1K | $38.1K | 3.1× | $21.0 | 31% | |
| TIKTOK | $5.4K | $13.8K | 2.6× | $24.6 | 11% |
| BING | $2.7K | $7.4K | 2.7× | $22.8 | 6% |
| ORGANIC | — | $14.9K | — | — | 5% |
AUTOMATION // BIGQUERY RULES → SLACK + AI
Spend pacing 118% of plan at 2.6× vs 3.0 target. Suggest: trim daily caps 15%.
“best {category} 2026” moved 5.4 → 3.1; lander holding 68% engaged rate.
4 ad platforms + GA4 + GSC landed 07:00. 41,206 rows. 0 errors.
Meta and Google carried the blend at 3.2× and 3.1×; TikTok trailed at 2.6× on rising CPMs. Blended held above target all week.
Organic clicks up 9% WoW on the buying-guide cluster — the paid × organic overlap on best-sellers keeps compounding.
All four platforms are combined into one daily return-on-spend figure that everyone can trust.
Paid and organic results now sit in the same report, so both are part of every decision.
The report shows exactly where visitors drop off, from landing page to purchase, for each channel.
NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS
If your ad platforms all show different numbers, this same setup can be built for your business.