analytics-design

yusuke-suzuki's avatarfrom yusuke-suzuki

Design Looker Studio reports and dashboards. Use when creating Looker Studio reports, designing data sources, or writing visualization specifications. Trigger on "Looker Studio", "レポート設計", "ダッシュボード".

⭐1stars🔀0forks📁View on GitHub🕐Updated Dec 26, 2025
[Reporting]

When & Why to Use This Skill

This Claude skill facilitates a comprehensive data analytics and visualization workflow, specializing in Looker Studio dashboard design and BigQuery data exploration. It guides users through the entire lifecycle of data analysis—from clarifying business objectives and discovering dataset schemas to writing optimized SQL queries and implementing visualization best practices. By providing structured templates and performance-oriented design tips, it ensures that reports are not only visually appealing but also cost-effective and aligned with actionable business goals.

Use Cases

  • Case 1: Designing end-to-end Looker Studio dashboards, including data source configuration, page structure, and the selection of optimal chart types for KPI monitoring.
  • Case 2: Exploring BigQuery schemas and executing optimized SQL queries using dry-runs to estimate costs and Common Table Expressions (CTEs) for better readability.
  • Case 3: Standardizing data analysis documentation by following a structured workflow that clarifies the 'who, what, and why' before technical implementation.
  • Case 4: Improving report performance by pre-aggregating data in SQL and designing data sources that align with specific analytical purposes.
nameanalytics-design
descriptionDesign data analysis from purpose clarification to visualization. Use when analyzing data, exploring BigQuery schemas, building queries, or creating Looker Studio reports.

Analytics Design

Workflow

Use references/analytics-design-template.md to document every analysis.

  1. Clarify Purpose: What do you want to know? Why is this analysis needed? Who will use it? One-time or ongoing monitoring?

  2. Discover Data: Explore available datasets and understand schema.

    • Ask user for project/dataset context and business background
    • bq ls, bq show --schema for BigQuery tables
    • db/schema.rb for Rails projects
    • API docs or sample data for external services
    • Understand table relationships (ER diagrams help)

  3. Build Query: Write SQL based on discovered schema.

    • Use CTEs for readability
    • Execute using BigQuery CLI (see BigQuery Query Execution below)
    • Interpret results and document findings

  4. Create Dashboard (if ongoing monitoring needed):

    • Use references/looker-studio-template.md to design
    • Define decisions: What actions will users take based on this dashboard?
    • Check existing resources: Similar dashboards or queries already exist?
    • Align time granularity with usage frequency (daily/weekly/monthly)
    • Design data sources, pages, and charts

BigQuery Query Execution

Prerequisites

Check gcloud configuration before running queries:

gcloud config get-value project
  • If authentication error: prompt user to run gcloud auth login, then resume
  • If project unset: prompt user to run gcloud config set project <PROJECT_ID>

Execution Process

  1. Dry run: Validate syntax and estimate cost

    bq query --use_legacy_sql=false --dry_run "SELECT * FROM \`project.dataset.table\`"

    Cost: ~$5/TB. <1GB is light, 2GB+ needs optimization.

  2. Execute: Run and confirm results

    bq query --use_legacy_sql=false --format=csv "SELECT * FROM \`project.dataset.table\`"

Always use fully-qualified table names: project.dataset.table

Query Design Tips

  • Specify exact date ranges
  • Filter partitioned tables by partition key
  • Avoid correlated subqueries (use JOINs/CTEs)
  • Filter early with CTEs before joining large tables

Looker Studio Best Practices

Data Source Design

  • One data source per analytical purpose
  • Pre-aggregate in SQL for performance
  • Include bucket fields for distribution analysis
  • Include sort-order fields for proper chart ordering
  • Descriptive data source names

Report Structure

  • Separate pages by time granularity (daily/monthly)
  • Group related metrics per page
  • Consistent filter scopes within pages

Chart Type Selection

Purpose Chart Type
KPI current value Scorecard
Time series trend Time series chart
Category breakdown over time Stacked area / Stacked bar
Category comparison Bar chart
Composition Pie chart
Detailed data Table
Distribution (percentile) Time series (multiple metrics)