# Schema

## Overview

These tables consolidate first-party business outcome data, including revenue, orders, customer attributes, and KPI metrics, into a standardized reporting framework. They serve as the source of truth for measuring business performance across channels, enabling consistent reporting, downstream modeling, and executive-level visibility into marketing impact.

***

### Client Business Results

<details>

<summary><code>client_business_results_dma_dev_table</code> schema<br><br><strong>Description:</strong> Business Results by Bonsai DMA ID</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.                                        |
| dma\_id               | YES          | INT64           | Bonsai's DMA ID                                                        |
| spend                 | YES          | FLOAT64         | Ad spend                                                               |
| impressions           | YES          | INT64           | Ad impressions                                                         |
| clicks                | YES          | INT64           | Ad clicks                                                              |
| conversions\_3p       | YES          | FLOAT64         | Ad platform conversions, as reported by the ad platform                |
| conversion\_value\_3p | YES          | FLOAT64         | Ad platform conversion value, as reported by the ad platform           |
| num\_visits           | YES          | INT64           | Web or app visits                                                      |
| num\_visitors         | YES          | INT64           | Web or app visitors                                                    |
| metric1–metric20      | YES          | INT64 / FLOAT64 | Business-specific KPI metrics (e.g., revenue, page views, conversions) |

</details>

<details>

<summary><code>client_business_results_dev_mmm_table</code> schema<br><br><strong>Description:</strong> Business Results for Incrementality Modeling</summary>

| field\_name           | is\_nullable | data\_type      | definition                    |
| --------------------- | ------------ | --------------- | ----------------------------- |
| client\_number        | YES          | INT64           | Internal client identifier    |
| date                  | YES          | DATE            | Calendar date                 |
| spend                 | YES          | FLOAT64         | Ad spend                      |
| impressions           | YES          | INT64           | Ad impressions                |
| clicks                | YES          | INT64           | Ad clicks                     |
| conversions\_3p       | YES          | FLOAT64         | Ad platform conversions       |
| conversion\_value\_3p | YES          | FLOAT64         | Ad platform conversion value  |
| num\_visits           | YES          | INT64           | Web/app visits                |
| num\_visitors         | YES          | INT64           | Web/app visitors              |
| metric1–metric20      | YES          | INT64 / FLOAT64 | Business-specific KPI metrics |

</details>

<details>

<summary><code>client_business_results_dev_table</code> schema<br><br><strong>Description:</strong> Business Results Data Table</summary>

| field\_name           | is\_nullable | data\_type      | definition                    |
| --------------------- | ------------ | --------------- | ----------------------------- |
| client\_number        | YES          | INT64           | Internal client identifier    |
| date                  | YES          | DATE            | Calendar date                 |
| spend                 | YES          | FLOAT64         | Ad spend                      |
| impressions           | YES          | INT64           | Ad impressions                |
| clicks                | YES          | INT64           | Ad clicks                     |
| conversions\_3p       | YES          | FLOAT64         | Ad platform conversions       |
| conversion\_value\_3p | YES          | FLOAT64         | Ad platform conversion value  |
| num\_visits           | YES          | INT64           | Web/app visits                |
| num\_visitors         | YES          | INT64           | Web/app visitors              |
| metric1–metric20      | YES          | INT64 / FLOAT64 | Business-specific KPI metrics |

</details>

### Business Results

<details>

<summary><code>business_results_customer_dim_table</code> schema<br><br><strong>Description:</strong> Business Results at a Customer ID level</summary>

| field\_name    | is\_nullable | data\_type | definition                                                         |
| -------------- | ------------ | ---------- | ------------------------------------------------------------------ |
| client\_number | YES          | INT64      | An internal client identifier                                      |
| customer\_id   | YES          | STRING     | Journey customer ID used in Bonsai Platform                        |
| first\_name    | YES          | STRING     | Customer first name                                                |
| last\_name     | YES          | STRING     | Customer last name                                                 |
| email          | YES          | STRING     | Customer email                                                     |
| email2         | YES          | STRING     | Secondary email                                                    |
| email3         | YES          | STRING     | Tertiary email                                                     |
| phone          | YES          | STRING     | Customer phone number                                              |
| phone2         | YES          | STRING     | Secondary phone                                                    |
| phone3         | YES          | STRING     | Tertiary phone                                                     |
| zip            | YES          | STRING     | Postal code                                                        |
| city           | YES          | STRING     | Customer city                                                      |
| state          | YES          | STRING     | Customer state                                                     |
| country        | YES          | STRING     | Customer country                                                   |
| dob            | YES          | DATE       | Date of birth                                                      |
| gender         | YES          | STRING     | Customer gender                                                    |
| klaviyo\_id    | YES          | STRING     | Klaviyo platform customer ID                                       |
| dim1–dim30     | YES          | STRING     | Client-defined custom 1P touchpoint dimensions                     |
| flag1–flag25   | YES          | BOOL       | Logical fields identifying configured customer journey event types |

</details>

<details>

<summary><code>business_results_order_facts_table</code> schema<br><br><strong>Description:</strong> Business Results Facts Table</summary>

| field\_name      | is\_nullable | data\_type      | definition                                |
| ---------------- | ------------ | --------------- | ----------------------------------------- |
| client\_number   | YES          | INT64           | Internal client identifier                |
| order\_id        | YES          | STRING          | Unique identifier for an order            |
| metric1–metric20 | YES          | INT64 / FLOAT64 | Business-specific order-level KPI metrics |

</details>

<details>

<summary><code>business_results_order_dim_table</code> schema<br><br><strong>Description:</strong> Business Results Order-Level Data Table</summary>

| field\_name              | is\_nullable | data\_type | definition                             |
| ------------------------ | ------------ | ---------- | -------------------------------------- |
| client\_number           | YES          | INT64      | Internal client identifier             |
| order\_id                | YES          | STRING     | Unique order ID                        |
| order\_type              | YES          | STRING     | Type of business order                 |
| customer\_id             | YES          | STRING     | Journey customer ID                    |
| created\_at              | YES          | TIMESTAMP  | Record creation timestamp              |
| dayofweek                | YES          | INT64      | Day of week                            |
| year                     | YES          | STRING     | Calendar year (YYYY)                   |
| month                    | YES          | STRING     | Calendar month                         |
| dma\_id                  | YES          | INT64      | Bonsai DMA ID                          |
| order\_dim1–order\_dim10 | YES          | STRING     | Client-defined custom order dimensions |
| cookie\_match\_dim1      | YES          | STRING     | 1P event parameter used for matching   |
| cookie\_match\_dim2      | YES          | STRING     | Alternative 1P matching parameter      |

</details>