Skip to main content
EmergentEMERGENT
Emergent SolutionsKnowledge Base

Looker Studio

Build custom dashboards and reports using Looker Studio with GA4 and BigQuery data.

Getting Started

Data Sources

| Source | Best For | Limitations | |--------|----------|-------------| | GA4 Native | Quick setup, standard metrics | Limited customization | | BigQuery | Custom queries, joined data | Requires SQL knowledge | | Blended Data | Multiple sources combined | Complexity | | Google Sheets | Manual data, simple updates | Not real-time |

Creating a Report

  1. Go to lookerstudio.google.com
  2. Click Create → Report
  3. Choose data source (GA4 or BigQuery)
  4. Start building

GA4 Native Connection

Setup

  1. Add Data → Google Analytics
  2. Select your GA4 property
  3. Choose dimensions and metrics

Available Metrics

| Category | Key Metrics | |----------|-------------| | Users | Total users, New users, Active users | | Sessions | Sessions, Engaged sessions | | Engagement | Engagement rate, Avg engagement time | | Revenue | Total revenue, Purchase revenue | | Conversions | Conversions, Conversion rate |

Limitations

  • Pre-aggregated data only
  • Limited custom event access
  • Can't access raw event parameters
  • No data joining

BigQuery Connection

Setup

  1. Add Data → BigQuery
  2. Select project and dataset
  3. Choose Custom Query or table

Custom Query Example

-- Custom query for Looker Studio
SELECT
  PARSE_DATE('%Y%m%d', event_date) as date,
  traffic_source.source,
  traffic_source.medium,
  COUNT(DISTINCT user_pseudo_id) as users,
  COUNT(DISTINCT CONCAT(user_pseudo_id, '-',
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id'))) as sessions,
  COUNTIF(event_name = 'purchase') as purchases,
  SUM((SELECT value.double_value FROM UNNEST(event_params)
       WHERE key = 'value')) as revenue
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
GROUP BY date, source, medium

Parameters in Queries

Use Looker Studio parameters for dynamic date ranges:

SELECT *
FROM `project.dataset.daily_metrics`
WHERE date BETWEEN
  PARSE_DATE('%Y%m%d', @DS_START_DATE)
  AND PARSE_DATE('%Y%m%d', @DS_END_DATE)

Building Dashboards

Layout Best Practices

┌─────────────────────────────────────────────────────────┐
│                    Dashboard Header                      │
│            Date Range | Filters | Logo                  │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐       │
│  │  KPI 1  │ │  KPI 2  │ │  KPI 3  │ │  KPI 4  │       │
│  │  Users  │ │Sessions │ │Revenue  │ │Conv Rate│       │
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘       │
│                                                         │
│  ┌──────────────────────────────────────────────────┐  │
│  │             Main Trend Chart                      │  │
│  │         (Revenue over time)                       │  │
│  └──────────────────────────────────────────────────┘  │
│                                                         │
│  ┌────────────────────┐ ┌────────────────────┐         │
│  │   Source Table     │ │  Top Products       │         │
│  │                    │ │                     │         │
│  └────────────────────┘ └────────────────────┘         │
│                                                         │
└─────────────────────────────────────────────────────────┘

Scorecards (KPIs)

Configuration:

  • Metric: Choose your KPI
  • Comparison: Previous period
  • Compact Numbers: Enable for readability
  • Conditional Formatting: Green/red for up/down

Time Series Charts

Configuration:

  • Dimension: Date
  • Metric: Your measure (users, revenue, etc.)
  • Breakdown: Optional second dimension
  • Sort: Date ascending

Tables

Configuration:

  • Dimension: Source/Medium, Page, etc.
  • Metrics: Multiple measures
  • Bars: Show data bars for visual
  • Sorting: By key metric descending

Calculated Fields

Creating Calculated Metrics

-- Conversion Rate
SUM(purchases) / SUM(sessions)

-- Revenue per User
SUM(revenue) / COUNT_DISTINCT(user_id)

-- Engagement Rate
SUM(engaged_sessions) / SUM(sessions)

-- Average Order Value
SUM(revenue) / SUM(purchases)

Calculated Dimensions

-- Channel Grouping
CASE
  WHEN REGEXP_MATCH(medium, 'cpc|ppc|paid') THEN 'Paid Search'
  WHEN REGEXP_MATCH(medium, 'organic') THEN 'Organic Search'
  WHEN REGEXP_MATCH(medium, 'social|facebook|twitter|linkedin') THEN 'Social'
  WHEN REGEXP_MATCH(medium, 'email') THEN 'Email'
  WHEN REGEXP_MATCH(source, 'direct') THEN 'Direct'
  ELSE 'Other'
END

-- Device Category
CASE
  WHEN device_category = 'mobile' THEN 'Mobile'
  WHEN device_category = 'tablet' THEN 'Tablet'
  ELSE 'Desktop'
END

Blended Data

When to Use

  • Combining GA4 with advertising data
  • Joining multiple BigQuery sources
  • Adding CRM data to analytics

Setup Process

  1. Resource → Manage blended data
  2. Click Add a blend
  3. Select data sources
  4. Define join keys

Example: GA4 + Google Ads

GA4 Data Source          Google Ads Data Source
├─ date                  ├─ date
├─ source                ├─ campaign
├─ sessions              ├─ cost
├─ revenue               └─ impressions
│
└─── Join on: date + source = date + campaign

Join Types

| Type | Use Case | |------|----------| | Left Outer | Keep all GA4 rows, match ads | | Right Outer | Keep all ads rows, match GA4 | | Inner | Only matching rows | | Full Outer | Keep all rows from both |

Interactive Features

Filters

Filter Control - Let users filter data:

  • Dimension: source
  • Style: Drop-down or list
  • Default: All

Date Range Controls

Date Range Control:

  • Apply to all charts or specific
  • Default: Last 30 days
  • Allow comparison periods

Drill-Down

Enable drill-down for hierarchical data:

  1. Add multiple dimensions (Country → City → Page)
  2. Enable "Drill down" in chart settings
  3. Users can click to explore

Parameters

Create user-controlled inputs:

  1. Resource → Manage parameters
  2. Define parameter (text, number, list)
  3. Use in calculated fields
-- Dynamic goal threshold
CASE WHEN revenue > @revenue_goal THEN "Above Goal" ELSE "Below Goal" END

Dashboard Templates

Executive Overview

| Section | Charts | |---------|--------| | KPIs | Users, Sessions, Revenue, Conv Rate | | Trends | Revenue by day, Users by day | | Breakdown | By channel, By device | | Top Content | Top pages, Top products |

Marketing Performance

| Section | Charts | |---------|--------| | Campaign KPIs | Spend, Revenue, ROAS | | Channel Performance | Table with all metrics | | Funnel | Impressions → Clicks → Conversions | | Trends | Cost & Revenue over time |

E-commerce Dashboard

| Section | Charts | |---------|--------| | Revenue KPIs | Revenue, AOV, Transactions | | Product Performance | Top products table | | Shopping Behavior | Funnel visualization | | Customer Segments | New vs returning |

Sharing & Embedding

Access Levels

| Level | Can Do | |-------|--------| | View | View report | | Edit | Modify report | | Owner | Full control |

Scheduled Delivery

  1. Share → Schedule email delivery
  2. Set recipients
  3. Choose frequency
  4. Select pages to include

Embedding

<!-- Embed code -->
<iframe
  width="100%"
  height="600"
  src="https://lookerstudio.google.com/embed/reporting/REPORT_ID/page/PAGE_ID"
  frameborder="0"
  style="border:0"
  allowfullscreen
></iframe>

Note: Embedded reports require viewer authentication unless published publicly.

Performance Optimization

Query Optimization

| Issue | Solution | |-------|----------| | Slow loading | Pre-aggregate in BigQuery | | High costs | Cache data, limit date range | | Timeouts | Simplify queries |

Caching

Set data freshness:

  • Resource → Manage data sources
  • Edit source → Data freshness
  • Options: 1 hour, 4 hours, 12 hours

Design Tips

  • Limit charts per page (8-12 max)
  • Use efficient chart types
  • Avoid complex calculated fields
  • Pre-compute in BigQuery when possible

Previous: BigQuery for GA4 Next: Predictive Analytics