BigQuery Table Schemas

Reference for all tables Vendo creates in your BigQuery dataset.

Orders Table

The orders table contains complete order information.

Column	Type	Description
`id`	INTEGER	Shopify order ID
`order_number`	STRING	Display order number
`email`	STRING	Customer email
`created_at`	TIMESTAMP	Order creation time
`updated_at`	TIMESTAMP	Last update time
`financial_status`	STRING	Payment status
`fulfillment_status`	STRING	Fulfillment state
`currency`	STRING	Currency code
`subtotal_price`	FLOAT	Order subtotal
`total_price`	FLOAT	Order total
`total_tax`	FLOAT	Tax amount
`total_discounts`	FLOAT	Discount amount
`total_shipping_price_set`	FLOAT	Shipping cost
`billing_address`	RECORD	Billing address details
`shipping_address`	RECORD	Shipping address details
`line_items`	REPEATED RECORD	Products in order
`customer`	RECORD	Customer information
`discount_codes`	STRING	Discount codes used
`discount_applications`	REPEATED RECORD	Discount details
`payment_gateway_names`	REPEATED STRING	Payment methods
`tags`	STRING	Order tags
`note`	STRING	Order notes
`custom_order_attributes`	REPEATED RECORD	Custom attributes
`landing_site`	STRING	Landing page URL
`referring_site`	STRING	Referrer URL
`source_name`	STRING	Order source
`app_id`	INTEGER	Creating app ID
`app_name`	STRING	Creating app name
`fulfillments`	REPEATED RECORD	Fulfillment records
`refunds`	REPEATED RECORD	Refund records
`test`	BOOLEAN	Test order flag
`confirmed`	BOOLEAN	Confirmation status
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Line Items Structure

Each line item in an order contains:

Column	Type	Description
`id`	INTEGER	Line item ID
`product_id`	INTEGER	Product ID
`variant_id`	INTEGER	Variant ID
`title`	STRING	Product title
`variant_title`	STRING	Variant title
`sku`	STRING	SKU
`quantity`	INTEGER	Quantity
`price`	FLOAT	Unit price
`total_discount`	FLOAT	Line discount
`vendor`	STRING	Vendor name
`fulfillment_service`	STRING	Fulfillment service
`fulfillment_status`	STRING	Item fulfillment status
`gift_card`	BOOLEAN	Is gift card
`taxable`	BOOLEAN	Is taxable
`tax_lines`	REPEATED RECORD	Tax details
`properties`	REPEATED RECORD	Custom properties
`discount_allocations`	REPEATED RECORD	Discount allocations

Fulfillments Structure

Nested within orders:

Column	Type	Description
`id`	INTEGER	Fulfillment ID
`order_id`	INTEGER	Parent order ID
`status`	STRING	Fulfillment status
`shipment_status`	STRING	Shipping status
`service`	STRING	Fulfillment service
`tracking_company`	STRING	Carrier name
`tracking_number`	STRING	Tracking number
`tracking_url`	STRING	Tracking URL
`tracking_numbers`	REPEATED STRING	All tracking numbers
`tracking_urls`	REPEATED STRING	All tracking URLs
`delivery_date`	TIMESTAMP	Delivery confirmation
`created_at`	TIMESTAMP	Fulfillment creation
`updated_at`	TIMESTAMP	Last update
`line_items`	REPEATED RECORD	Fulfilled items
`location_id`	INTEGER	Origin location
`receipt`	RECORD	Receipt details
`origin_address`	RECORD	Ship-from address

Refunds Structure

Nested within orders:

Column	Type	Description
`id`	INTEGER	Refund ID
`order_id`	INTEGER	Parent order ID
`created_at`	TIMESTAMP	Refund creation
`processed_at`	TIMESTAMP	Processing time
`refund_amount`	FLOAT	Total refund amount
`note`	STRING	Refund note
`restock`	BOOLEAN	Items restocked
`refund_line_items`	REPEATED RECORD	Refunded items
`transactions`	REPEATED RECORD	Transaction details
`order_adjustments`	REPEATED RECORD	Order adjustments

Customers Table

Column	Type	Description
`id`	INTEGER	Shopify customer ID
`email`	STRING	Customer email
`first_name`	STRING	First name
`last_name`	STRING	Last name
`phone`	STRING	Phone number
`created_at`	TIMESTAMP	Account creation
`updated_at`	TIMESTAMP	Last update
`orders_count`	INTEGER	Total orders
`total_spent`	STRING	Lifetime spend
`verified_email`	BOOLEAN	Email verified
`accepts_marketing`	BOOLEAN	Marketing consent
`tax_exempt`	BOOLEAN	Tax exemption
`tags`	STRING	Customer tags
`note`	STRING	Admin notes
`state`	STRING	Marketing state
`addresses`	REPEATED RECORD	Saved addresses
`default_address`	RECORD	Default address
`email_marketing_consent`	RECORD	Email consent details
`sms_marketing_consent`	RECORD	SMS consent details
`last_order_id`	INTEGER	Last order ID
`last_order_name`	STRING	Last order number
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Events Table

Client-side events streamed to BigQuery. See Client-Side Events for the full schema and example payloads.

Column	Type	Description
`id`	INTEGER	Event ID
`subject_id`	INTEGER	Related entity ID
`subject_type`	STRING	Entity type (Order, Customer, etc.)
`verb`	STRING	Event action
`message`	STRING	Event message
`description`	STRING	Event description
`body`	STRING	Event payload (JSON)
`path`	STRING	URL path
`author`	STRING	Event author
`arguments`	REPEATED STRING	Event arguments
`created_at`	TIMESTAMP	Event timestamp
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

Abandoned Checkouts Table

Column	Type	Description
`id`	INTEGER	Checkout ID
`token`	STRING	Checkout token
`email`	STRING	Customer email
`created_at`	TIMESTAMP	Checkout creation
`updated_at`	TIMESTAMP	Last update
`completed_at`	TIMESTAMP	Completion time (null if abandoned)
`abandoned_checkout_url`	STRING	Recovery URL
`currency`	STRING	Currency code
`subtotal_price`	FLOAT	Cart subtotal
`total_price`	FLOAT	Cart total
`total_tax`	FLOAT	Tax amount
`total_discounts`	FLOAT	Discounts applied
`billing_address`	RECORD	Billing info
`shipping_address`	RECORD	Shipping info
`line_items`	REPEATED RECORD	Cart items
`discount_codes`	STRING	Discount codes
`buyer_accepts_marketing`	BOOLEAN	Marketing consent
`source_name`	STRING	Source
`note_attributes`	REPEATED RECORD	Custom attributes
`customer`	RECORD	Customer details
`extracted_at`	TIMESTAMP	Sync timestamp
`is_deleted`	BOOLEAN	Deletion flag

User Identification

Customer ID Mapping

Source	BigQuery Column	Description
Shopify Customer ID	`customer.id`	Primary identifier
Email	`customer.email` / `email`	Email address
Phone	`customer.phone`	Phone number

Joining Data

Link events to orders by customer:


SELECT
  e.*,
  o.order_number,
  o.total_price
FROM events e
JOIN orders o ON e.subject_id = o.customer.id
WHERE e.subject_type = 'Customer'

Last updated on March 25, 2026

Setup Instructions Client-Side Events