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 operationalize predictive click valuation and bidding optimization. They capture modeled click-level value scores, upload activity to ad platforms, and tROAS adjustment logic, enabling more efficient media spend allocation based on predicted customer value rather than last-click outcomes.

---

Google Ads Algorithm

pcv_troas_adjuster_table schema Description: The specific tROAS changes applied (or suggested) for every campaign on pCV management.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
campaign	YES	STRING	The campaign name running on pCV.
campaign_id	YES	STRING	The ad platform campaign ID for the campaign.
segments_date	YES	DATE	The calendar date of the event.
pcv_flag	YES	INT64	A binary flag. 1 = the campaign runs on pCV. 0 = the campaign does not run on pCV.
impressions	YES	INT64	Ad impressions.
clicks	YES	INT64	Ad clicks.
spend	YES	FLOAT64	Ad platform media spend.
missed_clicks	YES	FLOAT64	Eligible ad clicks lost due to improper daily budget allocation, relative to tROAS bid setting.
missed_impressions	YES	FLOAT64	Eligible ad impressions lost due to improper daily budget allocation, relative to tROAS bid setting.
CPC	YES	FLOAT64	Cost-per-click based on media spend.
Optimized_CPC	YES	FLOAT64	The optimal available cost-per-click given existing media spend.
efficiency_improvement	YES	FLOAT64	The difference between Optimized_CPC and CPC.
tROAS_increase_required	YES	FLOAT64	The percentage increase required for a pCV campaign to achieve Optimized_CPC.

pcv_historical_table schema Description: A historical record of Google Ads clicks scored by pCV.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
gclid	YES	STRING	The Google Ads Click ID.
date	YES	DATE	The calendar date of the event.
country	YES	STRING	Utilized for Google Ads Conversion API acceptance.
index	YES	FLOAT64	The predicted click value from pCV.
append_datetime	YES	DATETIME	The date of the click upload to Google Ads.
conversion_event_time	YES	INT64	Utilized for Google Ads Conversion API acceptance.

pcv_upload_table schema Description: The pCV clicks and values uploaded into Google Ads, updated daily.

field_name	is_nullable	data_type	definition
client_number	YES	INT64	An internal client identifier.
gclid	YES	STRING	The Google Ads Click ID.
date	YES	DATE	The calendar date of the event.
country	YES	STRING	Utilized for Google Ads Conversion API acceptance.
index	YES	FLOAT64	The predicted click value from pCV.
conversion_event_time	YES	INT64	Utilized for Google Ads Conversion API acceptance.