BigQuery + Looker Studio Dashboard Tutorial: Free BI on GCP

BigQuery + Looker Studio is GCP's standard BI stack — you can build a production-grade dashboard for free. This article walks through visualizing GA4 / sales data, accelerating queries with BI Engine, and implementing Calculated Fields and Filter Controls with concrete examples.

Build Flow

1. Prepare data in BigQuery (Tables / Views)
2. Connect a Data Source in Looker Studio
3. Create a report and add charts
4. Add Calculated Fields / Filter Controls
5. Reserve BI Engine for acceleration
6. Share / Embed

Step 1: Prepare BigQuery Data

-- 売上集計 View
CREATE OR REPLACE VIEW analytics.sales_dashboard AS
SELECT
  DATE(order_timestamp) AS order_date,
  product_category,
  region,
  COUNT(DISTINCT order_id) AS order_count,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order_value
FROM raw.orders
WHERE order_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 730 DAY)
GROUP BY 1, 2, 3;

Step 2: Connect a Data Source in Looker Studio

1. lookerstudio.google.com — open the site
2. "Create" → "Data Source"
3. Choose the "BigQuery" connector
4. Select Project → Dataset → Table / View
5. For Custom Query, paste SQL directly

Step 3: Choosing the Right Chart

Step 4: Calculated Field

-- 売上前年比
(revenue - revenue_yoy) / revenue_yoy * 100

-- 月の最初の日
DATE_TRUNC(order_date, MONTH)

-- カテゴリ集約
CASE
  WHEN product_category IN ('Electronics', 'Computers') THEN 'Tech'
  WHEN product_category IN ('Books', 'Music') THEN 'Media'
  ELSE 'Other'
END

-- LTV 概算
revenue / NULLIF(unique_customers, 0)

Step 5: Filter Control

• Date Range Control: filter by date (default: Last 30 days)
• Drop-down List: pick a dimension (region / category)
• Input Box: free numeric input from the user
• Advanced Filter: complex conditions (AND/OR)
• Parameter: pass as a variable into SQL

Step 6: Accelerate with BI Engine

1. BigQuery Console → BI Engine → create a Reservation
2. Size: start with 1 GB ($30/month)
3. Configure Preferred Tables (the tables Looker Studio uses)
4. Looker Studio queries automatically route through BI Engine
5. Result: sub-second queries with zero BigQuery query charges

Step 7: Share / Embed

• Sharing: individual / group / entire org / Public Link
• Embedding: drop into an internal portal or website via iframe
• Scheduled PDF / email delivery
• Looker Studio Pro: org-level management with Service Account auth

A Typical Dashboard Layout

E-commerce Sales Dashboard

• Header: period filter + region filter
• KPI Row: Revenue / Orders / AOV / MoM (Scorecard × 4)
• Time Series: daily revenue trend (YoY comparison)
• Bar Chart: top 10 revenue by category
• Geo Map: revenue by prefecture
• Table: per-product detail (drill-down)

GA4 / Ads Integration

• GA4 → BigQuery Linking pipes raw data in
• Google Ads Data Transfer auto-syncs ad data
• Connectors also exist for Search Console, YouTube Analytics, and more
• The standard stack for Marketing Mix Analytics

Comparison with Other BI Tools

Google Cloud, Looker, and Looker Studio are trademarks of Google LLC. For the latest information, see the official Looker Studio site.