# Schema
## 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. |