# Schema

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

<details>

<summary><code>pcv_troas_adjuster_table</code> schema<br><br><strong>Description:</strong> The specific tROAS changes applied (or suggested) for every campaign on pCV management.</summary>

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

</details>

<details>

<summary><code>pcv_historical_table</code> schema<br><br><strong>Description:</strong> A historical record of Google Ads clicks scored by pCV.</summary>

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

</details>

<details>

<summary><code>pcv_upload_table</code> schema<br><br><strong>Description:</strong> The pCV clicks and values uploaded into Google Ads, updated daily.</summary>

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

</details>