AI Workflow Automation with n8n & BigQuery: Orchestrating Leads to Ad Audiences

-- Query raw GA4 user sessions in BigQuery to enrich CRM lead score
SELECT 
  user_pseudo_id,
  COUNTIF(event_name = 'page_view') AS total_pageviews,
  COUNTIF(event_name = 'view_pricing') AS pricing_views,
  MAX(CASE WHEN event_name = 'file_download' THEN 1 ELSE 0 END) AS downloaded_case_study,
  TIMESTAMP_DIFF(
    MAX(TIMESTAMP_MICROS(event_timestamp)), 
    MIN(TIMESTAMP_MICROS(event_timestamp)), 
    MINUTE
  ) AS session_duration_minutes
FROM 
  `analyticsdev-warehouse.analytics_319482109.events_*`
WHERE 
  _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  AND user_pseudo_id = @user_pseudo_id
GROUP BY 
  user_pseudo_id;