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 customer journey data, linking marketing touchpoints to downstream outcomes such as orders and revenue. They serve as the foundation for multi-touch attribution, enabling clear visibility into how media interactions influence customer behavior and conversion.

---

Customer Journey Views

cjv_dashboard_channel_platform_campaign_dma_table schema Description: The Bonsai Attribution Results Summary Table, by Campaign and DMA.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
date	YES	DATE	The calendar date of the event.
channel	YES	STRING	The marketing channel associated with the row of data.
platform	YES	STRING	The ad platform.
campaign	YES	STRING	An organized effort to promote a product or service. Used to group and track performance data.
dma_id	YES	INT64	Bonsai's DMA ID.
impressions	YES	INT64	Ad impressions.
clicks	YES	INT64	Ad clicks.
visits	YES	INT64	Web or app visits.
cost	YES	FLOAT64	Ad spend.
att_metric_1–att_metric_20	YES	FLOAT64	Fractional attributed metrics associated with this touchpoint using Bonsai’s multi-touch attribution model.

cjv_dashboard_channel_platform_campaign_region_table schema Description: The Bonsai Attribution Results Summary Table, by Campaign and Region.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
date	YES	DATE	The calendar date of the event.
channel	YES	STRING	The marketing channel associated with the row of data.
platform	YES	STRING	The ad platform.
campaign	YES	STRING	An organized effort to promote a product or service.
region	YES	STRING	Geographical dimension related to the user's location.
impressions	YES	INT64	Ad impressions.
clicks	YES	INT64	Ad clicks.
visits	YES	INT64	Web or app visits.
cost	YES	FLOAT64	Ad spend.
att_metric_1–att_metric_20	YES	FLOAT64	Fractional attributed metrics associated with this touchpoint using Bonsai’s multi-touch attribution model.

cjv_attribution_simplified_details_table schema Description: The Bonsai Attribution Results by Customer Outcome, without order-level breakdown.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
customer_id	YES	STRING	The Customer ID.
date	YES	DATE	The calendar date of the event.
created_at	YES	TIMESTAMP	The timestamp the record was created.
order_id	YES	STRING	The unique identifier for an order, if one was placed.
order_type	YES	STRING	The type of business order.
campaign	YES	STRING	Campaign associated with the interaction.
channel	YES	STRING	Marketing channel associated with the interaction.
platform	YES	STRING	Ad platform associated with the interaction.
source	YES	STRING	The website origination of a customer journey touchpoint.
medium	YES	STRING	Channel classification (e.g., CPC, organic, email).
deviceCategory	YES	STRING	User's device category.
operating_system	YES	STRING	User's operating system.
continent	YES	STRING	Geographical dimension related to the user's location.
country	YES	STRING	Geographical dimension related to the user's location.
region	YES	STRING	Geographical dimension related to the user's location.
metro	YES	STRING	Geographical dimension related to the user's location.
dma_id	YES	INT64	Bonsai's DMA ID.
attributed_metric1–attributed_metric20	YES	FLOAT64	Fractional attributed metrics associated with this touchpoint using Bonsai’s multi-touch attribution model.
dim1–dim10	YES	STRING	Client-defined custom 1P touchpoint dimensions.
order_dim1–order_dim10	YES	STRING	Client-defined custom 1P order dimensions.
flag1–flag10	YES	STRING	Logical fields identifying configured event types in the customer journey.
gclid / gbraid / wbraid / fbclid / msclkid / ttclid / twclid / li_fat_id / dclid	YES	STRING	Platform click identifiers used for attribution matching.

cjv_table schema Description: The Customer Journey Data Table containing interaction-level journey records.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
journey_customer_id	YES	STRING	The unique internal identifier for the customer's journey.
date	YES	DATE	The calendar date of the event.
journey_time	YES	INT64	UNIX timestamp of the event within the journey.
source	YES	STRING	The website origination of a customer journey touchpoint.
medium	YES	STRING	Channel classification (e.g., CPC, organic, email).
campaign	YES	STRING	Campaign associated with the interaction.
deviceCategory	YES	STRING	User's device category.
operating_system	YES	STRING	User's operating system.
continent	YES	STRING	Geographical dimension related to the user's location.
country	YES	STRING	Geographical dimension related to the user's location.
region	YES	STRING	Geographical dimension related to the user's location.
metro	YES	STRING	Geographical dimension related to the user's location.
attributed_metric1–attributed_metric20	YES	FLOAT64	Fractional attributed metrics associated with this touchpoint using Bonsai’s multi-touch attribution model.
metric1–metric20	YES	INT64 / FLOAT64	Business-specific metrics associated with an interaction.
dim1–dim10	YES	STRING	Client-defined custom 1P touchpoint dimensions.
order_dim1–order_dim5	YES	STRING	Client-defined custom 1P order dimensions.
key_interaction_flag	YES	INT64	Binary flag indicating if an event is eligible for attributed impact.
order_flag	YES	INT64	Binary flag indicating if an order occurred.
gclid / fbclid / msclkid / ttclid / twclid / li_fat_id	YES	STRING	Platform click identifiers used for attribution matching.

Lifetime Value

ltv_rfm_table schema Description: The Bonsai Customer Lifetime Value and Recency, Frequency, and Monetization Data Table.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
journey_customer_id	YES	STRING	The unique internal identifier for the customer's journey.
customer_value	YES	FLOAT64	Total customer lifetime value.
orders	YES	INT64	Total number of orders for a customer.
touchpoints	YES	INT64	Total customer journey touchpoints.
key_interactions	YES	FLOAT64	Total number of key interactions for a customer.
first_date	YES	DATE	First interaction date.
first_purchase_date	YES	DATE	First purchase date.
recent_date	YES	DATE	Most recent interaction date.
recent_purchase_date	YES	DATE	Most recent purchase date.
days_since_touchpoint	YES	INT64	Days since first interaction.
days_since_first_purchase	YES	INT64	Days since first purchase.
days_alive	YES	INT64	Days between yesterday and first record.

Percent Attributable

percent_attributable_business_table schema Description: The trackable percentage of customer business results in Bonsai attribution.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
pct_att	YES	FLOAT64	The trackable percentage of overall business results viewable by 1P attribution.

percent_attributable_by_channel_platform_table schema Description: The trackable percentage of each channel, by platform.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
channel	YES	STRING	The marketing channel associated with the row of data.
platform	YES	STRING	The ad platform.
visits	YES	INT64	Web or app visits.
clicks	YES	INT64	Ad clicks.
pct_att	YES	FLOAT64	The trackable percentage of overall business results viewable by 1P attribution.