A 1st-Party Attribution Suite for eCommerce
  • Introducing Fueled
  • How Fueled Works
    • Key Concepts
    • Use Cases
    • Is Fueled a CDP?
  • Apps
    • Shopify App
      • Shopify Event Tracking
        • Customizing Client-Side Shopify Events
        • Custom Pixel
          • Custom Pixel For Tracking ATC Events
          • Custom Pixel For Tracking Checkout Steps
        • Working with Fueled’s Shopify Data Layer
      • Google Analytics Connector
        • Configuring Google Analytics 4
        • Configuring GA4 Settings in Shopify
          • Shopify Order Source IDs
        • GA4 Shopify App Event Definitions
      • Google Ads Connector
      • Facebook Pixel/CAPI Connector
      • Segment Connector
      • Google Tag Manager Integration
      • Data Warehouse Connector
      • Domain Name Proxy
    • BigCommerce App
      • Google Analytics 4 Connector
        • Configuring Google Analytics 4 for BigCommerce
        • Configuring Fueled’s BigCommerce App
        • BigCommerce Event Tracking
          • Working with Fueled’s BigCommerce Data Layer
          • Customizing Client-Side BigCommerce Events
        • GA4 BigCommerce App Event Definitions
      • Google Ads Connector
      • Segment Connector
      • Data Warehouse Connector
  • Integrations
    • Sources
      • Shopify
      • BigCommerce
      • Headless eCommerce
        • Pack Digital
        • TakeShape
        • Next Commerce
      • Gorgias
        • Ticket Created
        • Ticket Updated
      • Loop Returns
      • Klaviyo
        • Email Opened
        • Email Bounced
        • Email Unsubscribed
        • Email Marked as Spam
        • Email Link Clicked
        • Newsletter Signup Started/Completed
      • KnoCommerce Surveys
      • ReCharge Payments
      • Yotpo Reviews
        • Identify
        • Product Reviewed/Review Updated
        • Site Reviewed/Review Updated
      • WordPress
    • Destinations
      • Google Analytics 4
      • Google Ads
      • Facebook Pixel/CAPI
      • Data Warehouses
      • Segment.com
        • Segment Event Specifications
          • Page
          • Identify
          • Account Created/Updated
          • Cart Viewed
          • Checkout Started
          • Order Completed
          • Product Added/Removed
          • Product List Viewed
          • Product Viewed
          • Products Searched
      • RudderStack
      • MixPanel and Amplitude
  • Resources
    • Recipes & Open Source
      • BigCommerce, dbt & BigQuery
      • Working With GA4 Data In BigQuery
        • Simple 'Page View to Purchase' Funnel Analysis
        • Querying Device Information for Shoppers with Purchases
        • Debugging User IDs in GA4 Events
      • Downgrading To Shopify's Free GA4 Connector
      • Demos
        • Configuration In Under 5 Minutes
    • News, Articles & Podcasts
      • Upgrading To A Paid Fueled Plan On Shopify
      • Feature Comparisons
  • More Info
    • About Us
    • Get Support
Powered by GitBook
On this page
  1. Resources
  2. Recipes & Open Source
  3. Working With GA4 Data In BigQuery

Simple 'Page View to Purchase' Funnel Analysis

The following query is helpful in determining how many purchases in a session included a visit to a specific page on your website.

To get started, update the FROM clause to use your BigQuery Project and Dataset. Then, replace 'https://example.com/example-page' with the page you want to test within this funnel.

-- CTE to identify all unique sessions
WITH all_sessions AS (
    SELECT DISTINCT
        event_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param
    WHERE 
        event_param.key = 'ga_session_id'
),

-- CTE to identify sessions with the specified sales page view
sessions_with_sales_page AS (
    SELECT DISTINCT
        session_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param,
        UNNEST(event_params) AS session_param
    WHERE 
        event_name = 'page_view'
        AND event_param.key = 'page_location'
        AND event_param.value.string_value = 'https://example.com/example-page'
        AND session_param.key = 'ga_session_id'
),

-- CTE to identify sessions with a purchase
sessions_with_purchase AS (
    SELECT DISTINCT
        session_param.value.int_value AS ga_session_id
    FROM 
        `PROJECT.DATASET.events_intraday_*`,
        UNNEST(event_params) AS event_param,
        UNNEST(event_params) AS session_param
    WHERE 
        event_name = 'purchase'
        AND session_param.key = 'ga_session_id'
)

-- Main query to compute the counts
SELECT
    (SELECT COUNT(ga_session_id) FROM all_sessions) AS total_unique_sessions,
    (SELECT COUNT(ga_session_id) FROM sessions_with_sales_page) AS sessions_with_sales_page,
    (SELECT COUNT(ga_session_id) FROM sessions_with_sales_page 
     WHERE ga_session_id IN (SELECT ga_session_id FROM sessions_with_purchase)) AS sessions_with_sales_page_and_purchase;
PreviousWorking With GA4 Data In BigQueryNextQuerying Device Information for Shoppers with Purchases

Last updated 1 year ago