Transaction Table Dictionary

Design & Features

have a 1 row per transaction table
- incremental appending (INT) - built from ga4_events
- full scan for functions that are outside the increment window (OUTPUT)
  - deduplication (optional)
  - refund aggregation
  - running customer totals
property_id and property_name columns for multi-property identification
nested: items (1 transaction can have multiple items)
nested: refunds (1 transaction can have multiple refunds)
- a refund row is basically the same as a transaction row: 1 row per refund
  - nested: the refunded items
aggregated: transaction metrics, item metrics, refund metrics

Config Options

Defaults:

  // by default, we leave duplicate transaction_ids alone, but you can deduplicate here
  // note: setting this to true will still keep all transactions with NULL transaction_id
  TRANSACTIONS_DEDUPE: false,

  // we keep a running count for transactions, based on an identifier. Defaults to "user_pseudo_id"
  // if you have another one, you can change it here (e.g. "user_id" - make sure it's a valid column)
  TRANSACTION_TOTALS_UID: 'user_pseudo_id',

TRANSACTIONS_DEDUPE is used in definitions/core/03_outputs/ga4_transactions.sqlx

column duplicate_count is 1 if not duplicate, or 2, 3, etc if duplicate transaction_id found
if TRANSACTIONS_DEDUPE is true, then only the 1st transaction ID ends up in the table
- via helper function generateTransactionsDedupeSQL

TRANSACTION_TOTALS_UID is used like this:

a column is SELECTed from ga4_events and added to definitions/core/02_intermediate/int_ga4_transactions.sqlx
- this can be any column of the events table.
  - user_pseudo_id ← default
  - user_id (a logical choice)
  - event_params_custom.email_hash ← super custom
  - coalesce(user_id, user_pseudo_id) ← super power user trick

${config.TRANSACTION_TOTALS_UID} as uid

in definitions/core/03_outputs/ga4_transactions.sqlx
- fill running totals column

struct(
    if( uid is null, null, row_number() over user ) as transactions,
    if( uid is null, null, sum(ecommerce.purchase_revenue) over user ) as purchase_revenue,
    ...
) as running_totals,
window user as ( partition by uid order by time.event_timestamp )

Model Overview

Transactions

1 row per transactions (purchase events), with in it:

Transaction_id (the key)
Transaction_date (the partition column)
Session, user, and stream info
The time struct with all timestamps
The ecommerce struct
The items of course
A column with item_totals – containing e.g. item revenue

If you have refunds configured properly, this table will also aggregate all refund events in the same row:

A struct net_item_totals will be calculated based on item value minus refund value (and quantity)
A refund_info stuct, with aggregated refund info AND an array with all refund events

User totals

Based on a user identifier, we calculate running purchase totals, based on what we know since the start of data collection:

Transactions – this is the Nth purchase of this user
Running sums of purchase_revenue and item quantity
Time since last purchase (and for convenience: time to next purchase) – if any

With this information, it’s super easy to build queries to generate useful queries, like a line items table, or more.

Main Columns

Column	Description
`transaction_date`	Partition column: the event_date of the purchase event
`is_final`	Indicates if the data is final or subject to change (based on 72h window)
`transaction_id`	`ecommerce.transaction_id`
`event_id`	Unique identifier for the event to link to events table
`session_id`	Unique identifier for the session. NULL if event is cookieless ping - link to sessions table
`user_pseudo_id`	Pseudonymous identifier for the user (could be NULL for cookieless pings)
`user_id`	The unique ID assigned to a user
`uid`	The configured ID used to calculate the running totals
`stream_id`	The numeric ID of the data stream from which the event originated
`platform`	The data stream platform (Web, IOS, or Android)
`property_id`	Google Analytics property ID (dataset identifier)
`property_name`	Custom property name from multi-property config, or same as `property_id` if no name set
`currency`	Currency code
`coupon`	Coupon code
`duplicate_count`	Nth occurrence of this transaction_id in the dataset (1 = first, 2 and up: duplicate)
`is_duplicate`	`true` if this transaction id is a duplicate
`number_of_refunds`	Count of refund events on this transaction

Time

Column	Description
`event_timestamp`	Event timestamp (microseconds)
`event_timestamp_utc`	Event timestamp in UTC
`user_first_touch_timestamp`	User's first touch timestamp (microseconds)
`user_first_touch_timestamp_utc`	User's first touch timestamp in UTC
`timestamp_local`	Local timestamp of the event (based on settings in Dataform)
`date_local`	Local date of the event (based on settings in Dataform)

Ecommerce

Column	Description
`total_item_quantity`	Total number of items in this event, sum of items.quantity
`purchase_revenue_in_usd`	Purchase revenue in USD
`purchase_revenue`	Purchase revenue in local currency
`refund_value_in_usd`	Refund value in USD
`refund_value`	Refund value in local currency
`shipping_value_in_usd`	Shipping value in USD
`shipping_value`	Shipping value in local currency
`tax_value_in_usd`	Tax value in USD
`tax_value`	Tax value in local currency
`unique_items`	The number of unique items in this event, based on `item_id`, `item_name`, and `item_brand`
`transaction_id`	Transaction ID - set to NULL when `(not set)`

Items

Column	Description
`item_id`	Product ID
`item_name`	Product name
`item_brand`	Product brand
`item_variant`	Product variant
`item_category`	Product category (level 1)
`item_category2`	Product category (level 2)
`item_category3`	Product category (level 3)
`item_category4`	Product category (level 4)
`item_category5`	Product category (level 5)
`price_in_usd`	Product price in USD
`price`	Product price in local currency
`quantity`	Product quantity
`item_revenue_in_usd`	Product revenue in USD
`item_revenue`	Product revenue in local currency
`item_refund_in_usd`	Product refund amount in USD
`item_refund`	Product refund amount in local currency
`coupon`	Coupon code applied to the product
`affiliation`	Product affiliation
`location_id`	Location ID where the product was sold
`item_list_id`	ID of the list containing the product
`item_list_name`	Name of the list containing the product
`item_list_index`	Position of the product in the list
`promotion_id`	ID of the promotion associated
`promotion_name`	Name of the promotion associated
`creative_name`	Name of the creative used in the promotion
`creative_slot`	Slot of the creative used in the promotion

Item Totals

Column	Description
`quantity`	Sum of `item_quantity`
`item_revenue`	Sum of `item_revenue`
`item_revenue_in_usd`	Sum of item revenue in USD
`coupons`	Count of coupons used
`unique_coupons`	Distinct coupons used

Net Item Totals

Column	Description
`quantity`	Sum of `item_quantity`
`item_revenue`	Sum of `item_revenue`
`item_revenue_in_usd`	Sum of item revenue in USD

Running Totals

Column	Description
`transactions`	Nth purchase
`purchase_revenue`	Running sum of purchase revenue
`purchase_revenue_in_usd`	Running sum of purchase revenue in USD
`total_item_quantity`	Running sum of quantity
`seconds_since_previous_purchase`	Seconds since the previous purchase of this user
`seconds_to_next_purchase`	Seconds to the next purchase of this user

Refund Info

Refund Columns

Column	Description
`min_refund_timestamp`	Timestamp (UTC) of first refund
`max_refund_timestamp`	Timestamp (UTC) of last refund

Refund Item Totals

Column	Description
`quantity`	Sum of `item_quantity` for refunds
`item_refund`	Sum of `item_revenue` for refunds
`item_refund_in_usd`	Sum of item refund in USD

Refunds Array

Column	Description
`event_id`	Unique identifier for the refund event
`session_id`	Unique identifier for the session
`user_pseudo_id`	Pseudonymous identifier for the user
`user_id`	The unique ID assigned to a user
`uid`	Configured ID used to calculate running totals
`stream_id`	Numeric ID of the data stream
`platform`	Platform (Web, IOS, or Android)

Design & Features​

Config Options​

Model Overview​

Transactions​

User totals​

Main Columns​

Time​

Ecommerce​

Items​

Item Totals​

Net Item Totals​

Running Totals​

Refund Info​

Refund Columns​

Refund Item Totals​

Refunds Array​