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
