looker-studio

Looker Studio (Google Data Studio)

Expert-level guidance for Looker Studio — building dashboards, connecting data sources, designing visualizations, and creating automated marketing reports.

Install

git clone https://github.com/thatrebeccarae/claude-marketing.git && cp -r claude-marketing/skills/looker-studio ~/.claude/skills/

Core Capabilities

Dashboard Design

• Layout and visual hierarchy best practices
• Executive summary vs detailed operational dashboards
• Mobile-responsive report design
• Interactive controls: date range selectors, filters, drill-downs
• Consistent styling with themes and color palettes

Data Sources & Connectors

• Native (free): Google Analytics 4, Google Ads, Google Sheets, BigQuery, Search Console, YouTube Analytics
• Partner connectors: Facebook Ads, Microsoft Ads, LinkedIn Ads, HubSpot, Salesforce, Shopify, Klaviyo, Semrush
• Community connectors: Hundreds of third-party sources
• Data blending: Join multiple sources on shared dimensions
• Custom queries: BigQuery SQL, Google Sheets formulas as data sources

Calculated Fields & Metrics

• Regex-based field creation (REGEXP_MATCH, REGEXP_REPLACE, REGEXP_EXTRACT)
• CASE statements for custom groupings and bucketing
• Date functions for period-over-period comparisons
• Aggregation (SUM, AVG, COUNT_DISTINCT, MEDIAN)
• Blended field calculations across sources

Formula Syntax Rules

• No comments allowed — Looker Studio formulas do not support --, //, or / / style comments. Never include comments in formulas. Add context in the field description instead.
• No inline flags in simple cases — prefer CONTAINS_TEXT(), LOWER(), or exact match over regex when possible
• RE2 regex engine — supports (?i) for case-insensitive, but does NOT support lookaheads/lookbehinds
• String escaping — use \\ for literal backslash in regex patterns within string literals (e.g., "\\s" for whitespace, "\\|" for literal pipe)

Report Automation

• Scheduled email delivery (PDF snapshots)
• Embedded reports in websites and portals
• Template reports for client scaling
• Data freshness monitoring

Dashboard Templates by Use Case

Marketing Performance Dashboard

Page 1: Executive Summary
|- KPI scorecards (Revenue, ROAS, CPA, Spend, Conversions)
|- Period-over-period trend lines
|- Channel performance table (sortable)
|- Budget pacing gauge chart

Page 2: Paid Media Deep-Dive
|- Google Ads performance (campaign breakdown)
|- Meta Ads performance (campaign breakdown)
|- Microsoft Ads performance
|- Cross-channel spend allocation (pie/donut)
|- CPA trend by channel (combo chart)

Page 3: SEO & Organic
|- Google Search Console: impressions, clicks, CTR, position
|- Top queries table
|- Page-level performance
|- Organic landing page engagement (from GA4)

Page 4: Email & CRM
|- Email campaign metrics (opens, clicks, revenue)
|- List growth trend
|- Flow/automation revenue
|- Subscriber engagement tiers

Page 5: Conversion Funnel
|- Funnel visualization (awareness -> consideration -> conversion)
|- Landing page performance table
|- Device breakdown
|- Geographic heatmap

E-commerce Dashboard

Page 1: Revenue Overview
|- Revenue, Orders, AOV, Conversion Rate scorecards
|- Revenue trend (daily/weekly)
|- Revenue by channel
|- Top products table

Page 2: Customer Acquisition
|- New vs returning customer revenue
|- CAC by channel
|- LTV:CAC ratio
|- First-order source attribution

SEO Dashboard

Page 1: Organic Performance
|- Total clicks, impressions, CTR, avg position
|- Trend lines (90-day)
|- Top 20 queries (with position change)
|- Top landing pages
|- Device + country breakdown

Key Visualization Guidelines

| Data Type | Best Chart | Avoid | |-----------|-----------|-------| | KPI with comparison | Scorecard with delta | Pie chart | | Trend over time | Line chart or area chart | Bar chart (if >7 periods) | | Category comparison | Horizontal bar chart | 3D charts | | Part of whole | Stacked bar or donut | Pie with >6 slices | | Distribution | Histogram or heatmap | Scatter (if not correlation) | | Geographic | Geo map or heatmap | Tables for location data | | Funnel | Custom funnel (shapes) | Bar chart | | Table data | Table with heatmap bars | Unsorted tables |

Workflow: Build a Dashboard

When asked to create a Looker Studio dashboard:

1. Define Purpose — Who views it? How often? What decisions does it inform?
2. Identify Data Sources — Which platforms/connectors needed? Any blending?
3. Design KPI Framework — Primary metrics, secondary metrics, diagnostic metrics
4. Plan Layout — Page structure, visual hierarchy, interactivity
5. Create Calculated Fields — Custom metrics, CASE groupings, regex transformations
6. Build Visualizations — Chart types matched to data types, consistent formatting
7. Add Controls — Date range, filters, drill-down parameters
8. Style & Polish — Theme, colors, fonts, logos, white space
9. Test & Validate — Cross-reference numbers with source platforms
10. Set Up Delivery — Scheduled emails, sharing permissions, embedding

Calculated Field Recipes

Period-over-Period Comparison

CASE
  WHEN date_field >= DATE_DIFF(TODAY(), INTERVAL 30 DAY) THEN "Current Period"
  WHEN date_field >= DATE_DIFF(TODAY(), INTERVAL 60 DAY) THEN "Previous Period"
  ELSE "Older"
END

Channel Grouping (Custom)

CASE
  WHEN REGEXP_MATCH(source_medium, "google.*cpc|google.*paid") THEN "Google Ads"
  WHEN REGEXP_MATCH(source_medium, "facebook|fb|meta|instagram") THEN "Meta Ads"
  WHEN REGEXP_MATCH(source_medium, "bing.*cpc|microsoft") THEN "Microsoft Ads"
  WHEN REGEXP_MATCH(source_medium, "email|klaviyo|braze") THEN "Email"
  WHEN source_medium = "organic" THEN "Organic Search"
  WHEN REGEXP_MATCH(source_medium, "social") THEN "Organic Social"
  ELSE "Other"
END

ROAS Calculation

SUM(revenue) / SUM(cost)

How to Use This Skill

Ask me questions like:

• "Build a marketing performance dashboard in Looker Studio"
• "How do I connect Facebook Ads data to Looker Studio?"
• "Create a calculated field for custom channel grouping"
• "Design an executive summary page with KPI scorecards"
• "How do I blend Google Ads and GA4 data?"
• "Build an SEO dashboard with Search Console data"
• "What's the best way to show period-over-period comparisons?"
• "Help me set up automated email reports for my client"

For detailed Looker Studio function reference, connector setup guides, and advanced techniques, see [REFERENCE.md](REFERENCE.md).

DTC Dashboard Recipes

Dashboard templates designed for DTC e-commerce teams running Klaviyo + Shopify + GA4.

1. CRM Performance Dashboard

Track email and SMS marketing effectiveness with Klaviyo data.

Page 1: Email & SMS Overview
|- Scorecards: Total Revenue, Flow Revenue %, Campaign Revenue %, List Size
|- Revenue trend: flows vs campaigns over time (area chart)
|- Channel split: email vs SMS revenue (stacked bar)
|- Engagement tiers donut: Active / Warm / At-Risk / Lapsed

Page 2: Flow Performance
|- Flow revenue table (sortable by revenue, click rate)
|- Welcome Series funnel (sent -> opened -> clicked -> converted)
|- Abandoned Cart recovery rate trend
|- Flow-over-flow comparison (combo chart)

Page 3: Campaign Performance
|- Campaign table: send date, subject, open rate, click rate, revenue
|- A/B test results (winner highlighting)
|- Send time heatmap (day of week x hour)
|- Unsubscribe rate trend

Data source: Google Sheets (fed by data_pipeline.py --action sync-klaviyo)

2. Lifecycle Marketing Dashboard

Map flow performance across the customer journey.

Page 1: Journey Overview
|- Stage funnel: Prospect -> New Customer -> Active -> VIP -> At-Risk -> Lapsed
|- Revenue by stage (horizontal bar)
|- Stage transition rates

Page 2: Stage Deep-Dive
|- Filter control: select lifecycle stage
|- Flow performance for selected stage
|- C