Building a Marketing Analytics Dashboard from BigQuery Data

The Marketing Dashboard Blueprint

An effective marketing analytics dashboard from BigQuery data surfaces the metrics that drive decisions — not every metric available. The goal: a marketing manager can open the dashboard Monday morning and answer five questions: How did last week compare to last year? Which channels drove the most revenue? What is the conversion rate trend? Where in the funnel are we losing users? What is ROAS by campaign?

Step 1: Design the Summary Tables in BigQuery

Dashboard queries should hit pre-aggregated summary tables, not raw GA4 events. Create scheduled queries that build:

• daily_channel_metrics: date, channel, sessions, users, conversions, revenue
• daily_product_metrics: date, product, views, add_to_carts, purchases, revenue
• weekly_cohort_retention: acquisition_week, week_number, retention_rate

These tables are small (thousands of rows vs millions in raw events) and fast to query.

Step 2: Connect BigQuery Views to Looker Studio

1. Looker Studio → Create → Data Source → BigQuery
2. Select your summary table or view (e.g. daily_channel_metrics)
3. Map dimensions (date, channel) and metrics (sessions, revenue) → Connect
4. Add to Report → builds the dashboard

Step 3: Essential Dashboard Sections

Header: Scorecard Row

4-6 scorecards: Last 7 days Revenue, Transactions, Conversion Rate, ROAS (if ad spend data is available), New Users, AOV. Each with a comparison to the prior period.

Section 1: Channel Performance

Bar chart or table: channel vs revenue, sessions, conversion rate. Date range selector at the top applies to all charts.

Section 2: Revenue Trend

Line chart: daily revenue over the past 90 days, with comparison line for same period last year (if data available).

Section 3: Product/Category Performance

Table: product or category name, revenue, units sold, average price, ranked by revenue.

Section 4: Funnel Metrics

Funnel chart or metric cards: sessions → product views → add to cart rate → checkout rate → purchase rate.

Summary

A BigQuery-powered marketing dashboard: design summary tables in BigQuery via scheduled queries, connect them to Looker Studio, build scorecards for KPIs, trend charts for time series, and performance tables for channels and products. The separation of data preparation (BigQuery) from visualisation (Looker Studio) makes the dashboard maintainable — business logic lives in SQL, not embedded in chart filters.

See our BigQuery Setup service and Looker Studio service for dashboard development.

Need a marketing analytics dashboard? Contact Adslytics.