git:20260306.b04d94a to v2.0.0

- description: Design and configure Looker Studio dashboards with BigQuery data sources. Use when creating analytics dashboards, connecting BigQuery to visualization tools, or optimizing data pipeline performance. Handles BigQuery connections, custom SQL queries, scheduled queries, dashboard design, and performance optimization.

- tags: Looker-Studio, BigQuery, dashboard, analytics, visualization, GCP, data-studio, SQL

- platforms: Claude, ChatGPT, Gemini

-

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

-

- ```bash

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

-

- ```sql

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

-

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

-

- ```sql

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

-

- ```sql

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

-

- ```sql

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

- ```sql

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

- WHERE customer_revenue >= 10000

- ```

- **Filter implementation example**

- ```sql

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

- ```sql

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

- ```sql

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

- ```javascript

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

-

- ```sql

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

- ```markdown

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

- 1. **Date filter required**: Include date range filter in all dashboards

- 2. **Use partitions**: Directly use partition keys in BigQuery queries

- 3. **Permission separation**: Clearly configure access permissions per data source

- ### Prohibited (MUST NOT)

- 1. **Excessive charts**: Do not place more than 25 charts on a single dashboard

- 2. **SELECT ***: Select only necessary columns instead of all columns

- 3. **Overusing live queries**: Avoid directly connecting to large tables

-

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

-

- - [Looker Studio Help](https://support.google.com/looker-studio)

- - [BigQuery Documentation](https://cloud.google.com/bigquery/docs)

- - [Connect to BigQuery](https://cloud.google.com/looker/docs/studio/connect-to-google-bigquery)

- - [Community Connectors](https://developers.google.com/looker-studio/connector)

- - [Dashboard Design Best Practices](https://lookercourses.com/dashboard-design-tips-for-looker-studio-how-to-build-clear-effective-reports/)

-

- ## Metadata

-

- ### Version

- - **Current Version**: 1.0.0

- - **Last Updated**: 2026-01-14

- - **Compatible Platforms**: Claude, ChatGPT, Gemini

-

- ### Related Skills

- - [monitoring-observability](../monitoring-observability/SKILL.md): Data collection and monitoring

- - [database-schema-design](../../backend/database-schema-design/SKILL.md): Data modeling

-

- ### Tags

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

-

- ## Examples

-

- ### Example 1: Creating a Basic Dashboard

-

- ```sql

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

-

- ```sql

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

- ```