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 standardize marketing platform data across channels into a unified schema, normalizing campaign structure, spend, impressions, clicks, and conversion metrics. They serve as the integration layer between external ad platforms and the Bonsai platform, ensuring consistent cross-channel reporting, attribution, and modeling readiness.
Customer Journey Views
cjv_key_gclid_map_table schema
Description: A mapping table for Google click IDs used by the Bonsai platform.
gclid
YES
STRING
A Google click ID used by Google Ads.
Event Touchpoint
event_touchpoint_dim_table schema
Description: A table of characteristics about each touchpoint used by the Bonsai platform
key
YES
STRING
A primary key for the specific interaction or event within the journey.
journey_time
YES
INT64
The timestamp, in UNIX seconds, of the event or interaction within the journey.
cookie_id
YES
STRING
the 1P cookie id from web & app event tracking
cookie_match_dim1
YES
STRING
the 1P event parameter utilized to match event data to business results data
cookie_match_dim2
YES
STRING
an alternative 1P event parameter utilized to match event data to business results data
cookie_match_flag
YES
INT64
denotes if a 1P event will be utilzed for cookie_match_dim
region
YES
STRING
Geographical dimension related to the user's location.
continent
YES
STRING
Geographical dimension related to the user's location.
dayofweek
YES
INT64
The day of the week, 1 = Sunday
medium
YES
STRING
The category of the source. Used to classify the type of channel that drove traffic (e.g., CPC, organic, email).
referrer
YES
STRING
web or app event referrer information
mobile_brand_name
YES
STRING
User's mobile brand.
mobile_model_name
YES
STRING
The mobile device's model name
deviceCategory
YES
STRING
User's device category
product_category
YES
STRING
The category of the product
item_category5
YES
STRING
the product 5th-level category
netsalesquantity
YES
INT64
Sales Quantity
net_sales_amount_usd
YES
FLOAT64
Net Sales in US Dollars
key_interaction_flag
YES
INT64
A binary flag, indicating if an event in the customer journey is eligible for attributed impact
key_interaction_weight
YES
FLOAT64
the attributable weight assigned for a key event
dclid
YES
STRING
A click ID used by DoubleClick.
fbclid
YES
STRING
A click ID used by Facebook and Instagram Ads.
gbraid
YES
STRING
A Google click ID used for iOS campaigns, specifically for app-to-web measurement on browsers that do not support third-party cookies. (Google Brand Referrer ID)
gclid
YES
STRING
A Google click ID used by Google Ads.
ko_click_id
YES
STRING
li_fat_id
YES
STRING
A click ID used by LinkedIn.
msclkid
YES
STRING
A click ID used by Microsoft Advertising (formerly Bing Ads).
ttclid
YES
STRING
A click ID used by TikTok for its advertising campaigns.
twclid
YES
STRING
A click ID used by Twitter (X) for its advertising platform.
wbraid
YES
STRING
A Google click ID for iOS campaigns, used for web-to-app measurement.
dim1–dim10
YES
STRING
Client-defined custom 1st-party touch point dimensions.
flag1–flag10
YES
STRING
a logical field, identifying event types for custom configured events in Bonsai customer journey tables
event_touchpoint_facts_table schema
Description: A table of critical characteristics for each touchpoint in the Bonsai Platform
client_number
YES
INT64
An internal client identifier.
cookie_id
YES
STRING
the 1P cookie id from web & app event tracking
cookie_match_dim1
YES
STRING
the 1P event parameter utilized to match event data to business results data
cookie_match_dim2
YES
STRING
an alternative 1P event parameter utilized to match event data to business results data
cookie_match_flag
YES
INT64
denotes if a 1P event will be utilzed for cookie_match_dim
journey_time
YES
INT64
The timestamp, in UNIX seconds, of the event or interaction within the journey.
key
YES
STRING
A primary key for the specific interaction or event within the journey.
key_interaction_flag
YES
INT64
A binary flag, indicating if an event in the customer journey is eligible for attributed impact
key_interaction_weight
YES
FLOAT64
the attributable weight assigned for a key event
user_id
YES
STRING
the user ID captured in 1P event tracking
Google Search Console
google_search_console_brand_demand_daily_table schema
Description: A table of data utilized by the Bonsai Platform to measure a client's brand demand, using Google Search Console data
brand_demand
YES
FLOAT64
The ad platform account ID, where applicable
client_number
YES
INT64
An internal client identifier.
date
YES
DATE
The calendar date of the event.
google_search_console_category_demand_daily_table schema
Description: A table of data utilized by the Bonsai Platform to measure a client's category demand, using Google Search Console data
Category_Demand
YES
FLOAT64
An index of category search demand
client_number
YES
INT64
An internal client identifier.
date
YES
DATE
The calendar date of the event.
Performance Marketing
performance_marketing_campaign_key_all_table schema
Description: A dimension table for all marketing campaigns available in a client's Bonsai platform
account_id
YES
STRING
The ad platform account ID, where applicable
campaign
YES
STRING
An organized effort to promote a product or service. Used to group and track performance data.
campaign_id
YES
STRING
The campaign ID utilized in the Bonsai platform, applicable to different levels of marketing depending on the input platform
Channel
YES
STRING
the marketing channel associated with the row of data
client_number
YES
INT64
An internal client identifier.
Platform
YES
STRING
the ad platform
performance_marketing_campaign_key_table schema
Description: A dimension table for all marketing campaigns available in a client's Bonsai platform
account_id
YES
STRING
The ad platform account ID, where applicable
campaign
YES
STRING
An organized effort to promote a product or service. Used to group and track performance data.
campaign_id
YES
STRING
The campaign ID utilized in the Bonsai platform, applicable to different levels of marketing depending on the input platform
Channel
YES
STRING
the marketing channel associated with the row of data
client_number
YES
INT64
An internal client identifier.
medium
NO
ARRAY
The category of the source. Used to classify the type of channel that drove traffic (e.g., CPC, organic, email).
Platform
YES
STRING
the ad platform
source
NO
ARRAY
The website origination of a customer journey touchpoint
performance_marketing_daily_campaign_dma_stats_table schema
Description: A daily stats table, segmented by campaign and DMA, utilized by the Bonsai platform, covering all integrated marketing channels.
account_id
YES
STRING
The ad platform account ID, where applicable
campaign_id
YES
STRING
The campaign ID utilized in the Bonsai platform, applicable to different levels of marketing depending on the input platform
clicks
YES
INT64
ad clicks
client_number
YES
INT64
An internal client identifier.
conversion_value
YES
FLOAT64
ad platform conversion value, as reported by the ad platform
conversions
YES
FLOAT64
ad platform conversions, as reported by the ad platform
date
YES
DATE
The calendar date of the event.
dma_id
YES
INT64
Bonsai's DMA ID
impressions
YES
INT64
ad impressions
Platform
YES
STRING
the ad platform
reach
YES
INT64
ad reach, in Users
spend
YES
FLOAT64
ad spend
performance_marketing_daily_campaign_stats_table schema
Description: A daily stats table, segmented by campaign , utilized by the Bonsai platform, covering all integrated marketing channels.
account_id
YES
STRING
The ad platform account ID, where applicable
campaign_id
YES
STRING
The campaign ID utilized in the Bonsai platform, applicable to different levels of marketing depending on the input platform
clicks
YES
INT64
ad clicks
client_number
YES
INT64
An internal client identifier.
conversion_value
YES
FLOAT64
ad platform conversion value, as reported by the ad platform
conversions
YES
FLOAT64
ad platform conversions, as reported by the ad platform
date
YES
DATE
The calendar date of the event.
impressions
YES
FLOAT64
ad impressions
Platform
YES
STRING
the ad platform
reach
YES
INT64
ad reach, in Users
spend
YES
FLOAT64
ad spend
performance_marketing_daily_dma_stats_table schema
Description: A daily stats table, segmented by DMA, utilized by the Bonsai platform, covering all integrated marketing channels.
account_id
YES
STRING
The ad platform account ID, where applicable
clicks
YES
INT64
ad clicks
client_number
YES
INT64
An internal client identifier.
conversion_value
YES
FLOAT64
ad platform conversion value, as reported by the ad platform
conversions
YES
FLOAT64
ad platform conversions, as reported by the ad platform
date
YES
DATE
The calendar date of the event.
dma_id
YES
INT64
Bonsai's DMA ID
impressions
YES
INT64
ad impressions
Platform
YES
STRING
the ad platform
reach
YES
INT64
ad reach, in Users
spend
YES
FLOAT64
ad spend
performance_marketing_daily_region_stats_campaign_table schema
Description: A daily stats table, segmented by campaign and region, utilized by the Bonsai platform, covering all integrated marketing channels.
account_id
YES
STRING
The ad platform account ID, where applicable
campaign_id
YES
STRING
The campaign ID utilized in the Bonsai platform, applicable to different levels of marketing depending on the input platform
clicks
YES
INT64
ad clicks
client_number
YES
INT64
An internal client identifier.
conversion_value
YES
FLOAT64
ad platform conversion value, as reported by the ad platform
conversions
YES
FLOAT64
ad platform conversions, as reported by the ad platform
country
YES
STRING
Geographical dimension related to the user's location.
date
YES
DATE
The calendar date of the event.
impressions
YES
INT64
ad impressions
Platform
YES
STRING
the ad platform
reach
YES
INT64
ad reach, in Users
region
YES
STRING
Geographical dimension related to the user's location.
spend
YES
FLOAT64
ad spend
performance_marketing_daily_stats_table schema
Description: A daily stats table, utilized by the Bonsai platform, covering all integrated marketing channels.
account_id
YES
STRING
The ad platform account ID, where applicable
clicks
YES
INT64
ad clicks
client_number
YES
INT64
An internal client identifier.
conversion_value
YES
FLOAT64
ad platform conversion value, as reported by the ad platform
conversions
YES
FLOAT64
ad platform conversions, as reported by the ad platform
date
YES
DATE
The calendar date of the event.
impressions
YES
INT64
ad impressions
Platform
YES
STRING
the ad platform
reach
YES
INT64
ad reach, in Users
spend
YES
FLOAT64
ad spend
Google Analytics 4
ga4_event_dim_table schema
Description:
client_number
YES
INT64
An internal client identifier.
key
YES
STRING
A primary key for the specific interaction or event within the journey.
journey_time
YES
INT64
The timestamp, in UNIX seconds, of the event or interaction within the journey.
date
YES
DATE
The calendar date of the event.
year
YES
INT64
the year, YYYY
month
YES
INT64
The calendar month
week
YES
INT64
the week of the year (1-53)
dayofweek
YES
INT64
The day of the week, 1 = Sunday
event_name
YES
STRING
the name of the customer behavior event
campaign
YES
STRING
An organized effort to promote a product or service. Used to group and track performance data.
source
YES
STRING
The website origination of a customer journey touchpoint
medium
YES
STRING
The category of the source. Used to classify the type of channel that drove traffic (e.g., CPC, organic, email).
cookie_id
YES
STRING
the 1P cookie id from web & app event tracking
cookie_match_dim1
YES
STRING
the 1P event parameter utilized to match event data to business results data
cookie_match_dim2
YES
STRING
an alternative 1P event parameter utilized to match event data to business results data
cookie_match_flag
YES
INT64
denotes if a 1P event will be utilzed for cookie_match_dim
session_id
YES
INT64
the 1P event tracking session ID
stream_id
YES
STRING
the 1P event stream ID
user_id
YES
STRING
the user ID captured in 1P event tracking
transaction_id
YES
STRING
the Order ID used in the Bonsai Platform
continent
YES
STRING
Geographical dimension related to the user's location.
sub_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.
deviceCategory
YES
STRING
User's device category
mobile_brand_name
YES
STRING
User's mobile brand.
mobile_marketing_name
YES
STRING
The mobile device's marketing name
mobile_model_name
YES
STRING
The mobile device's model name
operating_system
YES
STRING
User's operating system.
operating_system_version
YES
STRING
User's operating system, version segmented.
referrer
YES
STRING
web or app event referrer information
item_category
YES
STRING
the highest-level product category
item_category2
YES
STRING
the product sub-category
item_category3
YES
STRING
the product 3rd-level category
item_category4
YES
STRING
the product 4th-level category
item_category5
YES
STRING
the product 5th-level category
product_category
YES
STRING
The category of the product
product_brand
YES
STRING
The Brand of the product
product_name
YES
STRING
The name of the product
product_sku
YES