Schema

This documentation describes the standard tables in your Bonsai data warehouse, enabling reliable access to core marketing and business data with consistent naming, data types, and performance.

Overview

These tables consolidate first-party business outcome data, including revenue, orders, customer attributes, and KPI metrics, into a standardized reporting framework. They serve as the source of truth for measuring business performance across channels, enabling consistent reporting, downstream modeling, and executive-level visibility into marketing impact.

---

Client Business Results

client_business_results_dma_dev_table schema Description: Business Results by Bonsai DMA ID

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
date	YES	DATE	The calendar date of the event.
dma_id	YES	INT64	Bonsai's DMA ID
spend	YES	FLOAT64	Ad spend
impressions	YES	INT64	Ad impressions
clicks	YES	INT64	Ad clicks
conversions_3p	YES	FLOAT64	Ad platform conversions, as reported by the ad platform
conversion_value_3p	YES	FLOAT64	Ad platform conversion value, as reported by the ad platform
num_visits	YES	INT64	Web or app visits
num_visitors	YES	INT64	Web or app visitors
metric1–metric20	YES	INT64 / FLOAT64	Business-specific KPI metrics (e.g., revenue, page views, conversions)

client_business_results_dev_mmm_table schema Description: Business Results for Incrementality Modeling

field_name	is_nullable	data_type	definition
client_number	YES	INT64	Internal client identifier
date	YES	DATE	Calendar date
spend	YES	FLOAT64	Ad spend
impressions	YES	INT64	Ad impressions
clicks	YES	INT64	Ad clicks
conversions_3p	YES	FLOAT64	Ad platform conversions
conversion_value_3p	YES	FLOAT64	Ad platform conversion value
num_visits	YES	INT64	Web/app visits
num_visitors	YES	INT64	Web/app visitors
metric1–metric20	YES	INT64 / FLOAT64	Business-specific KPI metrics

client_business_results_dev_table schema Description: Business Results Data Table

field_name	is_nullable	data_type	definition
client_number	YES	INT64	Internal client identifier
date	YES	DATE	Calendar date
spend	YES	FLOAT64	Ad spend
impressions	YES	INT64	Ad impressions
clicks	YES	INT64	Ad clicks
conversions_3p	YES	FLOAT64	Ad platform conversions
conversion_value_3p	YES	FLOAT64	Ad platform conversion value
num_visits	YES	INT64	Web/app visits
num_visitors	YES	INT64	Web/app visitors
metric1–metric20	YES	INT64 / FLOAT64	Business-specific KPI metrics

Business Results

business_results_customer_dim_table schema Description: Business Results at a Customer ID level

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier
customer_id	YES	STRING	Journey customer ID used in Bonsai Platform
first_name	YES	STRING	Customer first name
last_name	YES	STRING	Customer last name
email	YES	STRING	Customer email
email2	YES	STRING	Secondary email
email3	YES	STRING	Tertiary email
phone	YES	STRING	Customer phone number
phone2	YES	STRING	Secondary phone
phone3	YES	STRING	Tertiary phone
zip	YES	STRING	Postal code
city	YES	STRING	Customer city
state	YES	STRING	Customer state
country	YES	STRING	Customer country
dob	YES	DATE	Date of birth
gender	YES	STRING	Customer gender
klaviyo_id	YES	STRING	Klaviyo platform customer ID
dim1–dim30	YES	STRING	Client-defined custom 1P touchpoint dimensions
flag1–flag25	YES	BOOL	Logical fields identifying configured customer journey event types

business_results_order_facts_table schema Description: Business Results Facts Table

field_name	is_nullable	data_type	definition
client_number	YES	INT64	Internal client identifier
order_id	YES	STRING	Unique identifier for an order
metric1–metric20	YES	INT64 / FLOAT64	Business-specific order-level KPI metrics

business_results_order_dim_table schema Description: Business Results Order-Level Data Table

field_name	is_nullable	data_type	definition
client_number	YES	INT64	Internal client identifier
order_id	YES	STRING	Unique order ID
order_type	YES	STRING	Type of business order
customer_id	YES	STRING	Journey customer ID
created_at	YES	TIMESTAMP	Record creation timestamp
dayofweek	YES	INT64	Day of week
year	YES	STRING	Calendar year (YYYY)
month	YES	STRING	Calendar month
dma_id	YES	INT64	Bonsai DMA ID
order_dim1–order_dim10	YES	STRING	Client-defined custom order dimensions
cookie_match_dim1	YES	STRING	1P event parameter used for matching
cookie_match_dim2	YES	STRING	Alternative 1P matching parameter