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:

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:

-- 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

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)

-- 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 results (6 hours)

• Utilizing scheduled queries: Pre-compute at night

4. Dashboard complexity management

• Use a maximum of 20-25 charts per dashboard

• Distribute across multiple tabs (pages) if many charts

• Do not group unrelated metrics together

Step 7: Community Connector Development (Advanced)

Develop a Community Connector for more complex requirements:

// Community Connector example (Apps Script)

function getConfig() {

  return {

    configParams: [

      {

        name: 'project_id',

        displayName: 'BigQuery Project ID',

        helpText: 'Your GCP Project ID',

        placeholder: 'my-project-id'

      },

      {

        name: 'dataset_id',

        displayName: 'Dataset ID'

      }

    ]

  };

}

function getData(request) {

  const projectId = request.configParams.project_id;

  const datasetId = request.configParams.dataset_id;

  // Load data from BigQuery

  const bq = BigQuery.newDataset(projectId, datasetId);

  // ... Data processing logic

  return { rows: data };

}

Community Connector advantages:

• Centralized billing (using service account)

• Custom caching logic

• Pre-defined query templates

• Parameterized user settings

Step 8: Security and Access Control

BigQuery-level security

-- Grant table access permission to specific users only

GRANT `roles/bigquery.dataViewer`

ON TABLE `my-project.analytics_dataset.events`

TO "user@example.com";

-- Row-Level Security

CREATE OR REPLACE ROW ACCESS POLICY rls_by_country

ON `my-project.analytics_dataset.events`

GRANT ('editor@company.com') TO ('KR'),

      ('viewer@company.com') TO ('US', 'JP');

Looker Studio-level security

• Set viewer permissions when sharing dashboards (Viewer/Editor)

• Share with specific users/groups only

• Manage permissions per data source

Output format

Dashboard Setup Checklist

## Dashboard Setup Checklist

### Data Source Configuration

- [ ] BigQuery project/dataset prepared

- [ ] IAM permissions configured

- [ ] Scheduled queries configured (performance optimization)

- [ ] Data source connection tested

### Dashboard Design

- [ ] F-pattern layout applied

- [ ] KPI tiles placed (3-4)

- [ ] Main charts added (trend/comparison)

- [ ] Detail table included

- [ ] Interactive filters added

### Performance Optimization

- [ ] Partition key usage verified

- [ ] Query cost optimized

- [ ] Caching strategy applied

- [ ] Chart count verified (20-25 or fewer)

### Sharing and Security

- [ ] Access permissions configured

- [ ] Data security reviewed

- [ ] Sharing link created

Constraints

Mandatory Rules (MUST)

• Date filter required: Include date range filter in all dashboards

• Use partitions: Directly use partition keys in BigQuery queries

• Permission separation: Clearly configure access permissions per data source

Prohibited (MUST NOT)

• Excessive charts: Do not place more than 25 charts on a single dashboard

• SELECT *: Select only necessary columns instead of all columns

• Overusing live queries: Avoid directly connecting to large tables

Best practices

Item

Recommendation

Data refresh

Use scheduled queries, run at night

Dashboard size

Max 25 charts, distribute to multiple pages if needed

Filter configuration

Date filter required, limit to 3-5 additional filters

Color palette

Use only 3-4 company brand colors

Title/Labels

Use clear descriptions for intuitiveness

Chart selection

Place in order: KPI → Trend → Comparison → Detail

Response speed

Target average loading within 2-3 seconds

Cost management

Keep monthly BigQuery scanned data within 5TB

References

• Looker Studio Help

• BigQuery Documentation

• Connect to BigQuery

• Community Connectors

• Dashboard Design Best Practices

Metadata

Version

• Current Version: 1.0.0

• Last Updated: 2026-01-14

• Compatible Platforms: Claude, ChatGPT, Gemini

Related Skills

• monitoring-observability: Data collection and monitoring

• database-schema-design: Data modeling

Tags

#Looker-Studio #BigQuery #dashboard #analytics #visualization #GCP

Examples

Example 1: Creating a Basic Dashboard

-- 1. Create daily summary table

CREATE OR REPLACE TABLE `my-project.looker_data.daily_metrics` AS

SELECT

  event_date,

  COUNT(DISTINCT user_id) as dau,

  SUM(revenue) as total_revenue,

  COUNT(*) as total_events

FROM `my-project.analytics.events`

WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

GROUP BY event_date;

-- 2. Connect to this table in Looker Studio

-- 3. Add KPI scorecards: DAU, total revenue

-- 4. Visualize daily trend with line chart

Example 2: Advanced Analytics Dashboard

-- Prepare data for cohort analysis

CREATE OR REPLACE TABLE `my-project.looker_data.cohort_analysis` AS

WITH user_cohort AS (

  SELECT

    user_id,

    DATE_TRUNC(MIN(event_date), WEEK) as cohort_week

  FROM `my-project.analytics.events`

  GROUP BY user_id

)

SELECT

  uc.cohort_week,

  DATE_DIFF(e.event_date, uc.cohort_week, WEEK) as week_number,

  COUNT(DISTINCT e.user_id) as active_users

FROM `my-project.analytics.events` e

JOIN user_cohort uc ON e.user_id = uc.user_id

GROUP BY cohort_week, week_number

ORDER BY cohort_week, week_number;