looker-studio（观察者工作室）

Looker Studio Skill

Complete guide for creating dashboards and calculated fields in Google Looker Studio.

Main Workflow

1. Identify the task type:

- Create calculated field → See function references - Connect database → See postgresql-connection.md - Create visualization → Follow best practices

1. For calculated fields:

- Determine the function type needed (date, text, aggregation, logic) - Check the corresponding reference - Test the formula in Looker Studio

Quick Syntax Reference

Most Used Functions

-- Subtract time (e.g., convert UTC to local)
DATETIME_SUB(date_field, INTERVAL 5 HOUR)

-- Simple conditional
IF(condition, value_if_true, value_if_false)

-- Multiple conditions
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

-- Default value if NULL
IFNULL(field, 'default_value')

-- Aggregations
SUM(field), AVG(field), MAX(field), MIN(field), COUNT(field)

-- Text
CONCAT(text1, text2), UPPER(text), LOWER(text)

-- Format date
FORMAT_DATETIME('%d/%m/%Y %H:%M', date_field)

Common Timezones

Timezone Conversion

-- Simple method: subtract/add hours
DATETIME_SUB(utc_field, INTERVAL 5 HOUR)

-- With FORMAT for display
FORMAT_DATETIME('%Y-%m-%d %H:%M', DATETIME_SUB(field, INTERVAL 5 HOUR))

PostgreSQL/Supabase Connection

Recommended configuration:

• Host: aws-X-REGION.pooler.supabase.com
• Port: 5432 or 6543
• Username: user.PROJECT_REF
• SSL: Disabled (if causing certificate issues)

Limitations:

• Maximum 150,000 rows per query
• Only public schema
• ASCII headers only

Available References

• date-functions.md - DATETIME_ADD, DATETIME_SUB, EXTRACT, FORMAT_DATETIME
• text-functions.md - CONCAT, SUBSTR, REPLACE, REGEXP_EXTRACT
• aggregation-functions.md - SUM, AVG, COUNT, MAX, MIN, PERCENTILE
• logic-functions.md - CASE, IF, IFNULL, COALESCE, operators
• conversion-functions.md - CAST, data types
• postgresql-connection.md - PostgreSQL/Supabase configuration
• resources.md - Courses, tutorials, official documentation

Best Practices

1. Data source level calculated fields for reusability
2. Use IFNULL to handle null values
3. Avoid division by zero with NULLIF(divisor, 0)
4. Limit data for better performance
5. Use filters before complex aggregations