Transaction Table Dictionary
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) |
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) |