looker-studio-bigquery

仓库: supercent-io/skills-template
安装量: 10.3K
排名: #239

安装

npx skills add https://github.com/supercent-io/skills-template --skill looker-studio-bigquery
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
← 返回排行榜