looker-studio-bigquery

What this skill does

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