# Schema

## Overview

These tables power Bonsai’s incrementality modeling framework by structuring media inputs and business outcomes into statistically modeled results. They serve as the source of truth for measuring true incremental lift, separating baseline performance from marketing-driven impact across channels and time.

***

### MMM Feature Data

<details>

<summary><code>raw_feature_data_validation_table</code> schema<br><br><strong>Description:</strong> The data validation table for input data versus Bonsai Incrementality feature model data.</summary>

| 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 feature.                                       |
| platform       | YES          | STRING     | The ad platform.                                                                         |
| account\_id    | YES          | STRING     | The ad platform account ID from which a campaign derived.                                |
| campaign       | YES          | STRING     | An organized effort to promote a product or service.                                     |
| campaign\_id   | YES          | STRING     | The ad platform ID of the campaign.                                                      |
| feature        | YES          | STRING     | The strategic segment of marketing activity measured in the Bonsai incrementality model. |
| impressions    | YES          | FLOAT64    | Ad impressions.                                                                          |
| clicks         | YES          | INT64      | Ad clicks.                                                                               |
| reach          | YES          | INT64      | Media reach by users.                                                                    |
| conversions    | YES          | FLOAT64    | Media conversions, as measured by ad platform conversion data.                           |
| cost           | YES          | FLOAT64    | Media ad cost.                                                                           |

</details>

### MMM Results

<details>

<summary><code>incrementality_daily_results_table</code> schema<br><br><strong>Description:</strong> The table of results from all live Bonsai incrementality models.</summary>

| 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 feature.                                       |
| feature                | YES          | STRING     | The strategic segment of marketing activity measured in the Bonsai incrementality model. |
| model\_number          | YES          | INT64      | The model number for the client in Bonsai incrementality products.                       |
| training\_number       | YES          | INT64      | The training number for the Bonsai incrementality model.                                 |
| type                   | YES          | STRING     | The type of result: Base, Base+, or Incremental.                                         |
| live\_flag             | YES          | INT64      | Denotes whether an Incrementality model is live in the Bonsai platform.                  |
| spend                  | YES          | FLOAT64    | Media ad cost.                                                                           |
| incremental\_dv        | YES          | FLOAT64    | Incremental outcomes.                                                                    |
| incremental\_dv\_value | YES          | FLOAT64    | Incremental outcome value.                                                               |

</details>

<details>

<summary><code>incrementality_daily_predicted_actual_table</code> schema<br><br><strong>Description:</strong> Bonsai Incrementality model results compared to actual business outcomes.</summary>

| field\_name      | is\_nullable | data\_type | definition                                                  |
| ---------------- | ------------ | ---------- | ----------------------------------------------------------- |
| client\_number   | YES          | INT64      | An internal client identifier.                              |
| date             | YES          | DATE       | The calendar date of the event.                             |
| model\_number    | YES          | INT64      | The model number for the client.                            |
| training\_number | YES          | INT64      | The training number for the model.                          |
| actual\_value    | YES          | FLOAT64    | The actual measured business outcome.                       |
| predicted\_value | YES          | FLOAT64    | The predicted outcome from the Bonsai incrementality model. |

</details>

### Forecaster

<details>

<summary><code>forecaster_validator_table</code> schema<br><br><strong>Description:</strong> The Budget Planner forecast validation data, comparing forecasts to actual outcomes.</summary>

| field\_name                   | is\_nullable | data\_type | definition                                                         |
| ----------------------------- | ------------ | ---------- | ------------------------------------------------------------------ |
| client\_number                | YES          | INT64      | An internal client identifier.                                     |
| date                          | YES          | DATE       | The calendar date of the event.                                    |
| year                          | YES          | INT64      | The calendar year (YYYY).                                          |
| month                         | YES          | INT64      | The month of the year.                                             |
| channel                       | YES          | STRING     | The marketing channel associated with the feature.                 |
| feature                       | YES          | STRING     | The strategic segment of marketing activity measured in the model. |
| model\_number                 | YES          | INT64      | The model number for the client.                                   |
| type                          | YES          | STRING     | The type of result: Base, Base+, or Incremental.                   |
| bid\_ratio                    | YES          | FLOAT64    | Estimated auction position relative to historical position.        |
| spend\_forecast               | YES          | FLOAT64    | The spend forecasted.                                              |
| incremental\_dv\_actual       | YES          | FLOAT64    | Incremental outcomes, actual.                                      |
| incremental\_dv\_forecast     | YES          | FLOAT64    | Incremental outcomes forecasted.                                   |
| incremental\_return\_actual   | YES          | FLOAT64    | Incremental return, actual.                                        |
| incremental\_return\_forecast | YES          | FLOAT64    | Incremental return forecasted.                                     |
| incremental\_return\_rank     | YES          | INT64      | Incremental return volume rank (descending).                       |
| max\_inc\_return\_forecast    | YES          | FLOAT64    | Maximum incremental return forecasted.                             |
| min\_inc\_return\_forecast    | YES          | FLOAT64    | Minimum incremental return forecasted.                             |

</details>

<details>

<summary><code>forecaster_table</code> schema<br><br><strong>Description:</strong> The table of all Budget Planner forecasts, for all scenarios.</summary>

| field\_name                      | is\_nullable | data\_type | definition                                                         |
| -------------------------------- | ------------ | ---------- | ------------------------------------------------------------------ |
| client\_number                   | YES          | INT64      | An internal client identifier.                                     |
| date                             | YES          | DATE       | The calendar date of the forecast.                                 |
| year                             | YES          | INT64      | The calendar year (YYYY).                                          |
| month                            | YES          | INT64      | The month of the year.                                             |
| weekday                          | YES          | INT64      | The calendar weekday (1 = Sunday).                                 |
| channel                          | YES          | STRING     | The marketing channel associated with the feature.                 |
| feature                          | YES          | STRING     | The strategic segment of marketing activity measured in the model. |
| model\_number                    | YES          | INT64      | The model number for the client.                                   |
| forecast\_number                 | YES          | INT64      | Bonsai internal ID for the Budget Planner forecast result.         |
| type                             | YES          | STRING     | The type of result: Base, Base+, or Incremental.                   |
| Bid\_Ratio                       | YES          | FLOAT64    | Estimated auction position relative to historical position.        |
| spend\_base                      | YES          | FLOAT64    | Base spend.                                                        |
| spend\_forecast                  | YES          | FLOAT64    | Forecasted spend.                                                  |
| click\_base                      | YES          | FLOAT64    | Base click estimate.                                               |
| click\_forecast                  | YES          | FLOAT64    | Forecasted click totals.                                           |
| impression\_base                 | YES          | FLOAT64    | Base expected impressions.                                         |
| impression\_forecast             | YES          | FLOAT64    | Forecasted impressions.                                            |
| reach\_base                      | YES          | FLOAT64    | Base media reach.                                                  |
| reach\_forecast                  | YES          | FLOAT64    | Forecasted media reach.                                            |
| incremental\_dv\_base            | YES          | FLOAT64    | Base business outcomes.                                            |
| incremental\_dv\_forecast        | YES          | FLOAT64    | Forecasted incremental outcomes.                                   |
| incremental\_dv\_value\_base     | YES          | FLOAT64    | Base business outcome value.                                       |
| incremental\_dv\_value\_forecast | YES          | FLOAT64    | Forecasted incremental outcome value.                              |
| incremental\_return\_forecast    | YES          | FLOAT64    | Forecasted incremental return.                                     |
| max\_return\_forecast            | YES          | FLOAT64    | Maximum incremental return forecasted across all features.         |
| next\_yr\_cpc\_index             | YES          | FLOAT64    | Forecasted CPC index.                                              |
| next\_yr\_demand\_index          | YES          | FLOAT64    | Forecasted demand index.                                           |
| next\_yr\_value\_index           | YES          | FLOAT64    | Forecasted customer response index.                                |

</details>