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 (REGEXPMATCH, REGEXPREPLACE, 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

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
|- Customer count trend per stage
|- Revenue per customer by stage (combo chart)

3. Revenue Attribution Dashboard

Reconcile Klaviyo-attributed revenue with Shopify actuals.

Page 1: Attribution Overview
|- Scorecards: Shopify Revenue, Klaviyo Attributed, Attribution %, Gap
|- Daily revenue: Shopify total vs Klaviyo attributed (dual axis)
|- Channel breakdown: Email, SMS, Flows, Campaigns (stacked bar)
|- Attribution gap trend line

Page 2: Channel Detail
|- Channel performance table (Klaviyo revenue per channel)
|- Shopify source breakdown (UTM-based)
|- Overlap analysis notes

Data source: Blended — Shopify Orders sheet + Klaviyo Revenue sheet

4. Campaign ROI Tracker

Campaign-level ROI with A/B test insights.

Page 1: Campaign Scorecard
|- Filter: date range, campaign type, channel
|- Campaign table with conditional formatting (green/red on benchmarks)
|- Revenue per recipient trend
|- Best-performing subject lines (top 10)

Page 2: Send Optimization
|- Send time analysis (heatmap: day x hour)
|- Audience size vs performance scatter
|- Frequency analysis: sends per subscriber per month

DTC Calculated Field Library

Copy-paste formulas for common DTC metrics in Looker Studio.

Customer Lifetime Value (LTV)

SUM(total_revenue) / COUNT_DISTINCT(customer_email)

Customer Acquisition Cost (CAC)

SUM(ad_spend) / COUNT_DISTINCT(CASE WHEN order_number = 1 THEN customer_email ELSE NULL END)

LTV:CAC Ratio

(SUM(total_revenue) / COUNT_DISTINCT(customer_email)) / (SUM(ad_spend) / COUNT_DISTINCT(new_customer_email))

Repeat Purchase Rate

COUNT_DISTINCT(CASE WHEN order_count > 1 THEN customer_email ELSE NULL END) / COUNT_DISTINCT(customer_email) * 100

Flow Revenue Percentage

SUM(CASE WHEN source_type = "flow" THEN revenue ELSE 0 END) / SUM(revenue) * 100

Engagement Tier

CASE
  WHEN days_since_last_open <= 30 THEN "Active (0-30d)"
  WHEN days_since_last_open <= 90 THEN "Warm (31-90d)"
  WHEN days_since_last_open <= 180 THEN "At-Risk (91-180d)"
  ELSE "Lapsed (180d+)"
END

Revenue Per Recipient

SUM(revenue) / SUM(recipients)

Discount Impact

SUM(discount_amount) / SUM(gross_revenue) * 100

Data Source Setup

Connecting Klaviyo Data (Free Method)

Paid connectors (Supermetrics, $30-100/mo) work but aren't necessary. The free pattern:

1. Run the data pipeline script to push Klaviyo data to Google Sheets:

``bash python scripts/datapipeline.py --action sync-klaviyo --sheet-id YOURSHEET_ID ``

1. In Looker Studio, add data source > Google Sheets > select the spreadsheet
2. Set date field type to Date in the data source config
3. Schedule sync — run the pipeline daily via cron or n8n

Connecting Shopify Data (Free Method)

1. Push order data to Sheets:

``bash python scripts/datapipeline.py --action sync-shopify --sheet-id YOURSHEET_ID --days 30 ``

1. Connect the Sheet in Looker Studio
2. Blend with Klaviyo sheet on Date dimension for attribution view

Connecting GA4 Data (Native — Free)

1. In Looker Studio, add data source > Google Analytics > select your GA4 property
2. No intermediary needed — native connector is comprehensive and free

Creating Pre-Formatted Sheets

# Create a sheet with correct headers for a CRM dashboard
python scripts/data_pipeline.py --action create-sheet --template crm-dashboard

# See all available templates
python scripts/data_pipeline.py --action list-templates

Analysis Examples

For complete dashboard build walkthroughs and use cases, see [EXAMPLES.md](EXAMPLES.md).

Scripts

The skill includes a data pipeline script for pushing data to Google Sheets:

Sync Klaviyo Data

python scripts/data_pipeline.py --action sync-klaviyo --sheet-id SPREADSHEET_ID

Sync Shopify Orders

python scripts/data_pipeline.py --action sync-shopify --sheet-id SPREADSHEET_ID --days 30

Create Dashboard Sheet

python scripts/data_pipeline.py --action create-sheet --template crm-dashboard

Troubleshooting

Data not refreshing: Google Sheets data in Looker Studio caches for ~15 minutes. Force refresh with the "Refresh data" button in Looker Studio, or set the data source cache to 1 minute in report settings.

Calculated field errors: Common causes:

• Comments in formulas — Looker Studio does NOT support --, //, or / / comments. Never include comments in calculated field formulas. Use the field description for documentation instead.
• Type mismatches — Ensure date fields are typed as Date (not Text) in the data source config. Use CAST() to convert between types.
• Regex escaping — Patterns are inside string literals, so backslashes need double-escaping (e.g., "\\s" for whitespace, "\\|" for literal pipe).

Blending shows nulls: Left outer join means unmatched rows from the right source show null. Ensure join keys match exactly (case-sensitive, same date format).

Sheets row limit: Google Sheets has a 10M cell limit. For large datasets, aggregate before writing (daily summaries instead of per-event data), or use BigQuery instead.

Service account permission errors: The service account email must be shared on the target Google Sheet with Editor access. Check IAM permissions if Drive API calls fail.

Security & Privacy

• Never hardcode API credentials in scripts — use .env files
• Store service account JSON outside version control
• Add .env and credential files to .gitignore
• The data pipeline writes to Google Sheets you control — data stays in your Google Workspace
• Pipeline scripts are read-only against Klaviyo and Shopify APIs
• Use least-privilege API scopes (read-only keys)