Data Structure Overview Your GA4 data is stored in a dedicated table called reporting_events_ga4_data. This table contains a comprehensive set of fields from your GA4 implementation.
Available Data Categories created_at -- Event timestamp ga4_event_name -- Name of the GA4 event server_distinct_id -- Unique identifier for the user client_id -- GA4 client ID session_id -- Unique session identifier Page Information page_url -- Full URL of the page page_path -- Path component of the URL page_title -- Page title page_protocol -- Protocol (http/https) page_hostname -- Domain name page_hash -- URL hash component page_referrer -- Referring URL Device and Browser Details browser_name -- Browser name browser_version -- Browser version browser_language -- Browser language setting browser_platform -- Operating system platform browser_vendor -- Browser vendor browser_user_agent -- Complete user agent string device_type -- Type of device (mobile/desktop/tablet) Creating Essential Reports Let’s explore how to create meaningful reports with this data.
User Journey Analysis Navigation Analysis Engagement Analysis Session Analysis Track user navigation with:
CASE WHEN page_path IS NOT NULL THEN REGEXP_REPLACE(page_path, '^/', '') ELSE '(not set)' END Track user navigation with:
CASE WHEN page_path IS NOT NULL THEN REGEXP_REPLACE(page_path, '^/', '') ELSE '(not set)' END Measure engagement using:
CASE WHEN session_engagement = '1' THEN 'Engaged' ELSE 'Not Engaged' END Create a session-based analysis using:
CASE WHEN session_id IS NOT NULL THEN session_id ELSE CONCAT(client_id, '_', DATE_FORMAT(created_at, '%Y%m%d')) END E-commerce Tracking
Access e-commerce data using these fields:
transaction_id
transaction_value
transaction_tax
transaction_shipping
transaction_coupon
Track product interactions:
product_1
product_2
product_3
Monitor transaction currencies:
Creating Custom Metrics Session Quality Score CASE WHEN CAST(session_engagement AS INTEGER) > 0 AND CAST(page_load_time AS INTEGER) < 3000 THEN 'High' WHEN CAST(session_engagement AS INTEGER) > 0 THEN 'Medium' ELSE 'Low' END User Type Classification CASE WHEN CAST(is_repeat AS INTEGER) > 0 THEN 'Returning User' ELSE 'New User' END Best Practices Data Accuracy
Session Handling
-- Reliable session identification COALESCE(session_id, CONCAT(client_id, '_', DATE_FORMAT(created_at, '%Y%m%d%H%i')))
User Identification
-- Consistent user tracking COALESCE(server_distinct_id, client_id)
Common Use Cases Marketing Attribution -- Campaign effectiveness SELECT utm_source, utm_medium, utm_campaign, COUNT(DISTINCT client_id) as users, COUNT(DISTINCT session_id) as sessions User Experience Monitoring -- Page performance tracking SELECT page_path, AVG(CAST(page_load_time AS INTEGER)) as avg_load_time, COUNT(DISTINCT session_id) as sessions Troubleshooting Missing Data Inaccuracy Performance If data is missing:
Check event_name filters
Verify GA4 implementation
Confirm date ranges
If data is missing:
Check event_name filters
Verify GA4 implementation
Confirm date ranges
For accuracy issues:
Validate session_id usage
Check client_id consistency
Verify event timestamps
Performance problems:
Simplify calculations
Use appropriate date ranges
Optimize field selection
Next Steps After setting up your basic GA4 reporting:
Create custom dashboards for different stakeholders
Set up automated reporting
Implement custom metrics for your business KPIs
