GA4 SQL: Query Google Analytics 4 with Standard SQL

Google Analytics 4 SQL Bridge - Query your GA4 data using standard SQL syntax. Transform analytics insights into actionable data queries.

Overview

The GA4 SQL Driver allows you to query your Google Analytics 4 property using familiar SQL syntax. This integration transforms GA4’s Data API into a SQL-queryable interface, making it easy to analyze your analytics data alongside other databases.

Quick Start

1. Set up Google Service Account

Add our service account email as a Viewer to your GA4 property (find email below)

data-team@synehq.iam.gserviceaccount.com

2. Connect SyneHQ to your GA4 Property

Navigate to Add Connections

3. Run Your First Query

-- Explore available data
SELECT * FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-07'
LIMIT 5;

-- Get top countries by user count
SELECT country, totalUsers, sessions
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY country
ORDER BY totalUsers DESC
LIMIT 10;

Finding Your Property ID

Your GA4 Property ID is the numeric ID (not the Measurement ID starting with G-):

Go to GA4 Admin > Property Settings
Copy the Property ID (numeric, e.g., 123456789)

SQL Syntax Reference

Supported SQL Subset

The GA4 driver supports a focused SQL subset optimized for analytics queries:

SELECT <dimensions..., metrics...> 
FROM ga4 
[WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' 
  [AND <dimension> = 'value' ...]]
[GROUP BY <dimensions...>]
[ORDER BY <field> [ASC|DESC]]
[LIMIT N [OFFSET M]]

Field Types

Dimensions: Categorical data (country, deviceCategory, source, etc.)
Metrics: Numerical data (totalUsers, sessions, revenue, etc.)

Metric Prefixing

Metrics can be prefixed with m: for clarity (optional):

-- Both are equivalent:
SELECT country, totalUsers FROM ga4;
SELECT country, m:totalUsers FROM ga4;

Date Filtering

Date filtering is required for data queries:

-- Required date range
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'

-- Combined with other filters
WHERE date BETWEEN '2024-01-01' AND '2024-01-31' 
  AND country = 'United States'
  AND deviceCategory = 'mobile'

Discovery Commands

Explore available data with these special SQL commands:

List Available Fields

-- List all dimensions
LIST DIMENSIONS;

-- List all metrics  
LIST METRICS;

-- List both dimensions and metrics
LIST FIELDS;

-- Limit results
LIST DIMENSIONS LIMIT 10;

Schema Exploration

-- Show databases
SHOW DATABASES;

-- Show schemas
SHOW SCHEMAS;

-- Show tables
SHOW TABLES;

-- Describe the ga4 table
DESCRIBE ga4;
DESC TABLE ga4;

Query Examples

Basic Analytics

-- Daily active users over time
SELECT date, activeUsers, newUsers
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY date;

-- Top traffic sources
SELECT source, medium, totalUsers, sessions
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY source, medium
ORDER BY totalUsers DESC
LIMIT 10;

Device & Technology Analysis

-- Device category breakdown
SELECT deviceCategory, operatingSystem, activeUsers, bounceRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND country = 'United States'
GROUP BY deviceCategory, operatingSystem
ORDER BY activeUsers DESC;

-- Browser performance
SELECT browser, browserVersion, totalUsers, engagementRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY browser, browserVersion
HAVING totalUsers > 100
ORDER BY engagementRate DESC;

Geographic Analysis

-- Country performance
SELECT country, region, totalUsers, sessions, engagementRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY country, region
ORDER BY totalUsers DESC
LIMIT 20;

-- City-level analysis
SELECT city, country, activeUsers, averageSessionDuration
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND country IN ('United States', 'Canada', 'United Kingdom')
GROUP BY city, country
ORDER BY activeUsers DESC;

Page Performance

-- Top pages by views
SELECT pagePath, pageTitle, screenPageViews, uniquePageviews
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY pagePath, pageTitle
ORDER BY screenPageViews DESC
LIMIT 15;

-- Landing page analysis
SELECT landingPage, totalUsers, bounceRate, engagementRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY landingPage
ORDER BY totalUsers DESC;

Campaign Analysis

-- Campaign performance
SELECT campaignName, source, medium, totalUsers, conversions, totalRevenue
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND campaignName IS NOT NULL
GROUP BY campaignName, source, medium
ORDER BY totalRevenue DESC;

-- Channel performance
SELECT sessionDefaultChannelGroup, activeUsers, sessions, engagementRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY sessionDefaultChannelGroup
ORDER BY activeUsers DESC;

E-commerce Analysis

-- Revenue by source
SELECT source, medium, totalRevenue, ecommercePurchases, 
       totalRevenue / ecommercePurchases as avgOrderValue
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND totalRevenue > 0
GROUP BY source, medium
ORDER BY totalRevenue DESC;

-- Product performance
SELECT itemName, itemCategory, itemRevenue, itemsPurchased
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND itemName IS NOT NULL
GROUP BY itemName, itemCategory
ORDER BY itemRevenue DESC;

Event Analysis

-- Top events
SELECT eventName, eventCount, totalUsers
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY eventName
ORDER BY eventCount DESC
LIMIT 10;

-- Event funnel analysis
SELECT eventName, deviceCategory, eventCount, 
       eventCount / totalUsers as eventsPerUser
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND eventName IN ('page_view', 'scroll', 'click', 'purchase')
GROUP BY eventName, deviceCategory
ORDER BY eventName, eventsPerUser DESC;

Field Reference

Common Dimensions

Field	Description
`date`	Date in YYYY-MM-DD format
`country`	User’s country
`region`	User’s region/state
`city`	User’s city
`deviceCategory`	Device type (desktop, mobile, tablet)
`operatingSystem`	Operating system
`browser`	Browser name
`source`	Traffic source
`medium`	Traffic medium
`campaignName`	Campaign name
`sessionDefaultChannelGroup`	Default channel grouping
`pagePath`	Page path
`pageTitle`	Page title
`eventName`	Event name

Common Metrics

Field	Description
`totalUsers`	Total number of users
`activeUsers`	Number of active users
`newUsers`	Number of new users
`sessions`	Number of sessions
`engagedSessions`	Number of engaged sessions
`engagementRate`	Engagement rate percentage
`bounceRate`	Bounce rate percentage
`screenPageViews`	Number of page/screen views
`averageSessionDuration`	Average session duration
`conversions`	Number of conversions
`totalRevenue`	Total revenue
`eventCount`	Number of events

Discovery Commands

-- Get complete field lists
LIST DIMENSIONS;    -- All available dimensions
LIST METRICS;       -- All available metrics  
LIST FIELDS;        -- Both dimensions and metrics
DESCRIBE ga4;       -- Table schema with data types

Best Practices

1. Always Include Date Ranges

-- ✅ Good: Always filter by date
SELECT country, totalUsers FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';

-- ❌ Bad: Missing date filter (will fail)
SELECT country, totalUsers FROM ga4;

2. Use Appropriate Aggregation

-- ✅ Good: Group by dimensions when using metrics
SELECT country, SUM(totalUsers) as users FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY country;

-- ✅ Good: Use built-in aggregated metrics
SELECT country, totalUsers FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY country;

3. Optimize Query Performance

-- ✅ Good: Use specific date ranges
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'

-- ✅ Good: Limit results
LIMIT 100

-- ✅ Good: Filter early
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND country = 'United States'

4. Handle GA4 Limitations

-- ✅ Good: Respect GA4 cardinality limits
SELECT country, totalUsers FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY country
LIMIT 1000;

-- ⚠️ Be careful with high-cardinality dimensions
SELECT pagePath, totalUsers FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY pagePath
LIMIT 100; -- Limit to avoid sampling

Troubleshooting

Common Errors

1. “Field X is not a valid dimension”

-- ❌ Error: Invalid field name
SELECT campaign FROM ga4; 

-- ✅ Fix: Use correct field name
SELECT campaignName FROM ga4;

Solution: Use LIST DIMENSIONS to see valid field names. 2. “User does not have sufficient permissions” Solution: Ensure your service account has Viewer access to the GA4 property. 3. “Property not found” Solution: Verify you’re using the correct Property ID (numeric, not Measurement ID). 4. Missing date range

-- ❌ Error: Missing required date filter
SELECT country, totalUsers FROM ga4;

-- ✅ Fix: Always include date range
SELECT country, totalUsers FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';

Debugging Tips

Start Simple: Begin with basic queries and add complexity gradually
Check Field Names: Use discovery commands to verify field names
Test Date Ranges: Ensure your date range contains data
Monitor Limits: Be aware of GA4’s sampling and cardinality limits
Check Permissions: Verify service account access

Performance Optimization

Use Shorter Date Ranges for faster queries
Limit Results with LIMIT clause
Filter Early with WHERE conditions
Avoid High-Cardinality dimensions when possible
Cache Results for repeated queries

Advanced Usage

Custom Calculated Fields

-- Calculate conversion rate
SELECT source, medium, 
       conversions,
       totalUsers,
       (conversions * 100.0 / totalUsers) as conversionRate
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND totalUsers > 0
GROUP BY source, medium
ORDER BY conversionRate DESC;

-- Calculate revenue per user
SELECT country,
       totalRevenue,
       totalUsers,
       (totalRevenue / totalUsers) as revenuePerUser
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-01-31'
  AND totalUsers > 0
GROUP BY country
ORDER BY revenuePerUser DESC;

Time Series Analysis

-- Weekly trends
SELECT 
  CONCAT(year, '-W', LPAD(week, 2, '0')) as week_label,
  SUM(totalUsers) as weekly_users,
  SUM(sessions) as weekly_sessions
FROM ga4 
WHERE date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY year, week
ORDER BY year, week;

-- Month-over-month comparison
SELECT 
  yearMonth,
  totalUsers,
  LAG(totalUsers) OVER (ORDER BY yearMonth) as prev_month_users,
  ((totalUsers - LAG(totalUsers) OVER (ORDER BY yearMonth)) * 100.0 / 
   LAG(totalUsers) OVER (ORDER BY yearMonth)) as growth_rate
FROM (
  SELECT yearMonth, SUM(totalUsers) as totalUsers
  FROM ga4 
  WHERE date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY yearMonth
) monthly_data
ORDER BY yearMonth;

Getting Help

Field Reference: Use LIST DIMENSIONS and LIST METRICS commands
Schema Info: Use DESCRIBE ga4 for table structure
GA4 Documentation: GA4 Data API Schema
Error Messages: Check the details field in error responses for specific guidance

Start exploring your GA4 data with SQL today! 🚀

​Overview

​Quick Start

​1. Set up Google Service Account

​2. Connect SyneHQ to your GA4 Property

​3. Run Your First Query

​Finding Your Property ID

​SQL Syntax Reference

​Supported SQL Subset

​Field Types

​Metric Prefixing

​Date Filtering

​Discovery Commands

​List Available Fields

​Schema Exploration

​Query Examples

​Basic Analytics

​Device & Technology Analysis

​Geographic Analysis

​Page Performance

​Campaign Analysis

​E-commerce Analysis

​Event Analysis

​Field Reference

​Common Dimensions

​Common Metrics

​Discovery Commands

​Best Practices

​1. Always Include Date Ranges

​2. Use Appropriate Aggregation

​3. Optimize Query Performance

​4. Handle GA4 Limitations

​Troubleshooting

​Common Errors

​Debugging Tips

​Performance Optimization

​Advanced Usage

​Custom Calculated Fields

​Time Series Analysis

​Getting Help

Overview

Quick Start

1. Set up Google Service Account

2. Connect SyneHQ to your GA4 Property

3. Run Your First Query

Finding Your Property ID

SQL Syntax Reference

Supported SQL Subset

Field Types

Metric Prefixing

Date Filtering

Discovery Commands

List Available Fields

Schema Exploration

Query Examples

Basic Analytics

Device & Technology Analysis

Geographic Analysis

Page Performance

Campaign Analysis

E-commerce Analysis

Event Analysis

Field Reference

Common Dimensions

Common Metrics

Discovery Commands

Best Practices

1. Always Include Date Ranges

2. Use Appropriate Aggregation

3. Optimize Query Performance

4. Handle GA4 Limitations

Troubleshooting

Common Errors

Debugging Tips

Performance Optimization

Advanced Usage

Custom Calculated Fields

Time Series Analysis

Getting Help