Looker Studio BigQuery Integration
When to use this skill
- Analytics dashboard creation: Visualizing BigQuery data to derive business insights
- Real-time reporting: Building auto-refreshing dashboards
- Performance optimization: Optimizing query costs and loading time for large datasets
- Data pipeline: Automating ETL processes with scheduled queries
- Team collaboration: Building shareable interactive dashboards
Instructions
Step 1: Prepare GCP BigQuery Environment
Project creation and activation
Create a new project in Google Cloud Console and enable the BigQuery API.
gcloud projects create my-analytics-project
gcloud config set project my-analytics-project
gcloud services enable bigquery.googleapis.com
Create dataset and table
CREATE SCHEMA `my-project.analytics_dataset`
OPTIONS(
description="Analytics dataset",
location="US"
);
CREATE TABLE `my-project.analytics_dataset.events` (
event_date DATE,
event_name STRING,
user_id INT64,
event_value FLOAT64,
event_timestamp TIMESTAMP,
geo_country STRING,
device_category STRING
);
IAM permission configuration
Grant IAM permissions so Looker Studio can access BigQuery:
| Role |
Description |
BigQuery Data Viewer |
Table read permission |
BigQuery User |
Query execution permission |
BigQuery Job User |
Job execution permission |
Step 2: Connecting BigQuery in Looker Studio
Using native BigQuery connector (recommended)
- On Looker Studio homepage, click + Create → Data Source
- Search for "BigQuery" and select Google BigQuery connector
- Authenticate with Google account
- Select project, dataset, and table
- Click Connect to create data source
Custom SQL query approach
Write SQL directly when complex data transformation is needed:
SELECT
event_date,
event_name,
COUNT(DISTINCT user_id) as unique_users,
SUM(event_value) as total_revenue,
AVG(event_value) as avg_revenue_per_event
FROM `my-project.analytics_dataset.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY event_date, event_name
ORDER BY event_date DESC
Advantages:
- Handle complex data transformations in SQL
- Pre-aggregate data in BigQuery to reduce query costs
- Improved performance by not loading all data every time
Multiple table join approach
SELECT
e.event_date,
e.event_name,
u.user_country,
u.user_tier,
COUNT(DISTINCT e.user_id) as unique_users,
SUM(e.event_value) as revenue
FROM `my-project.analytics_dataset.events` e
LEFT JOIN `my-project.analytics_dataset.users` u
ON e.user_id = u.user_id
WHERE e.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY e.event_date, e.event_name, u.user_country, u.user_tier
Step 3: Performance Optimization with Scheduled Queries
Use scheduled queries instead of live queries to periodically pre-compute data:
CREATE OR REPLACE TABLE `my-project.analytics_dataset.daily_summary` AS
SELECT
CURRENT_DATE() as report_date,
event_name,
user_country,
COUNT(DISTINCT user_id) as daily_users,
SUM(event_value) as daily_revenue,
AVG(event_value) as avg_event_value,
MAX(event_timestamp) as last_event_time
FROM `my-project.analytics_dataset.events`
WHERE event_date = CURRENT_DATE() - 1
GROUP BY event_name, user_country
Configure as scheduled query in BigQuery UI:
- Runs automatically daily
- Saves results to a new table
- Looker Studio connects to the pre-computed table
Advantages:
- Reduce Looker Studio loading time (50-80%)
- Reduce BigQuery costs (less data scanned)
- Improved dashboard refresh speed
Step 4: Dashboard Layout Design
F-pattern layout
Use the F-pattern that follows the natural reading flow of users:
┌─────────────────────────────────────┐
│ Header: Logo | Filters/Date Picker │ ← Users see this first
├─────────────────────────────────────┤
│ KPI 1 │ KPI 2 │ KPI 3 │ KPI 4 │ ← Key metrics (3-4)
├─────────────────────────────────────┤
│ │
│ Main Chart (time series/comparison) │ ← Deep insights
│ │
├─────────────────────────────────────┤
│ Concrete data table │ ← Detailed analysis
│ (Drilldown enabled) │
├─────────────────────────────────────┤
│ Additional Insights / Map / Heatmap │
└─────────────────────────────────────┘
Dashboard components
| Element |
Purpose |
Example |
| Header |
Dashboard title, logo, filter placement |
"2026 Q1 Sales Analysis" |
| KPI tiles |
Display key metrics at a glance |
Total revenue, MoM growth rate, active users |
| Trend charts |
Changes over time |
Line chart showing daily/weekly revenue trend |
| Comparison charts |
Compare across categories |
Bar chart comparing sales by region/product |
| Distribution charts |
Visualize data distribution |
Heatmap, scatter plot, bubble chart |
| Detail tables |
Provide exact figures |
Conditional formatting to highlight thresholds |
| Map |
Geographic data |
Revenue distribution by country/region |
Real example: E-commerce dashboard
┌──────────────────────────────────────────────────┐
│ 📊 Jan 2026 Sales Analysis | 🔽 Country | 📅 Date │
├──────────────────────────────────────────────────┤
│ Total Revenue: $125,000 │ Orders: 3,200 │ Conversion: 3.5% │
├──────────────────────────────────────────────────┤
│ Daily Revenue Trend (Line Chart) │
│ ↗ Upward trend: +15% vs last month │
├──────────────────────────────────────────────────┤
│ Sales by Category │ Top 10 Products │
│ (Bar chart) │ (Table, sortable) │
├──────────────────────────────────────────────────┤
│ Revenue Distribution by Region (Map) │
└──────────────────────────────────────────────────┘
Step 5: Interactive Filters and Controls
Filter types
1. Date range filter (required)
- Select specific period via calendar
- Pre-defined options like "Last 7 days", "This month"
- Connected to dataset, auto-applied to all charts
2. Dropdown filter
Example: Country selection filter
- All countries
- South Korea
- Japan
- United States
Shows only data for the selected country
3. Advanced filter (SQL-based)
WHERE customer_revenue >= 10000
Filter implementation example
event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL @date_range_days DAY)
WHERE country = @selected_country
WHERE event_date >= @start_date
AND event_date <= @end_date
AND country IN (@country_list)
AND revenue >= @min_revenue
Step 6: Query Performance Optimization
1. Using partition keys
SELECT * FROM events
WHERE DATE(event_timestamp) >= '2026-01-01'
SELECT * FROM events
WHERE event_date >= '2026-01-01'
2. Data extraction (Extract and Load)
Extract data to a Looker Studio-dedicated table each night:
CREATE OR REPLACE TABLE `my-project.looker_studio_data.dashboard_snapshot` AS
SELECT
event_date,
event_name,
country,
device_category,
COUNT(DISTINCT user_id) as users,
SUM(event_value) as revenue,
COUNT(*) as events
FROM `my-project.analytics_dataset.events`
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY ev
