GA4 Dataform

GA4 Dataform is a Google Cloud Dataform project designed to transform raw GA4 BigQuery data exports into modular, user-friendly tables. It acts as a starter pack for building custom data models on top of GA4 raw data.

Key Features:

• Generation of unique user_key and ga_session_key.
• Output of digestible tables including session, user_transaction_daily, and event tables.
• Optional Gclid widening (mapping GA4 gclid to Google Ads Data Transfer click-view gclid).
• Event-level last-click attribution.
• Helper functions for unnesting BigQuery event_params, extracting page URLs, and applying default channel groupings.

Limitations:

• Currently supports web properties only; app properties are not yet tested.
• Attribution models are device-based (using user_pseudo_id) rather than user-based (user_id).

To deploy the solution, you must connect your Dataform instance to a third-party Git repository.

Prerequisites

• A Google Cloud Project with billing enabled.
• A BigQuery table containing GA4 data exports.
• The following APIs enabled in your Google Cloud project:
  • Cloud Scheduler API
  • BigQuery API
  • Dataform API
  • Workflows API

Deployment Steps

1. Connect your Dataform to a third-party Git repository following the official guide.
2. In the Dataform UI, under 'DEVELOPMENT WORKSPACES', click CREATE DEVELOPMENT WORKSPACE.
3. Configure your environment variables:
   • Update values in includes/constants.js.
   • Update defaultProject and defaultLocation (the BigQuery location) in workflow_settings.yaml.
4. While inside workflow_settings.yaml, click INSTALL PACKAGES.

Once deployed, follow these steps to execute the SQLX files and generate BigQuery tables:

1. Navigate to Dataform in your Google Cloud project and locate the repository (e.g., ga4-dataform-timestamp).
2. Open the created workspace (e.g., ga4-workspace-timestamp) to explore the .sqlx files.
3. Configuration: Update database names and constants in includes/constants.js. Note the output dataset name, as this is where final tables will be stored.
4. Execution:
   • Click Start Execution in the upper menu.
   • Choose All Actions.
   • Select Run with Full Refresh.
5. Verification: Check BigQuery for your output dataset. By default, this is named ga4_dataform_output.

After running the Dataform models, you can query the resulting tables in BigQuery. The following example retrieves the number of sessions grouped by source and medium from the session table.

select
    last_non_direct_traffic_source.source as session_source,
    last_non_direct_traffic_source.medium as session_medium,
    count(distinct ga_session_key) as number_of_sessions
from `your-project-id.ga4_dataform_output.session`
group by
    1,
    2
order by
    3 desc
limit 1000