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.

# Create project using gcloud CLI
gcloud projects create my-analytics-project
gcloud config set project my-analytics-project
gcloud services enable bigquery.googleapis.com

Create dataset and table

-- Create dataset
CREATE SCHEMA `my-project.analytics_dataset`
  OPTIONS(
    description="Analytics dataset",
    location="US"
  );

-- Create example table (GA4 data)
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:

Step 2: Connecting BigQuery in Looker Studio

Using native BigQuery connector (recommended)

1. On Looker Studio homepage, click + Create → Data Source
2. Search for "BigQuery" and select Google BigQuery connector
3. Authenticate with Google account
4. Select project, dataset, and table
5. 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:

-- Calculate and store aggregated data daily in BigQuery
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

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)

-- Show only customers with revenue >= $10,000
WHERE customer_revenue >= 10000

Filter implementation example

-- 1. Date filter
event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL @date_range_days DAY)

-- 2. Dropdown filter (user input)
WHERE country = @selected_country

-- 3. Composite filter
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

-- ❌ Inefficient query
SELECT * FROM events
WHERE DATE(event_timestamp) >= '2026-01-01'

-- ✅ Optimized query (using partition)
SELECT * FROM events
WHERE event_date >= '2026-01-01'  -- use partition key directly

2. Data extraction (Extract and Load)

Extract data to a Looker Studio-dedicated table each night:

-- Scheduled query running at midnight every day
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 event_date, event_name, country, device_category;

3. Caching strategy

• Looker Studio default caching: Automatically caches for 3 hours
• BigQuery caching: Identical queries reuse previous result