# RevCent MCP Operation: `BigQueryRunQuery`

This document explains how to use the `BigQueryRunQuery` MCP operation to query RevCent account data with BigQuery Standard SQL. It also explains how and why to use `GetBigQueryTables` before writing queries, and how to correctly reference tables using `project.dataset.table` formatting.

Sources used:
- RevCent MCP operation schema for `BigQueryRunQuery`
- RevCent MCP operation schema for `GetBigQueryTables`

---

## Operation Summary

`BigQueryRunQuery` executes a BigQuery Standard SQL query against the user's RevCent data and returns the query results as an array of objects.

Use this operation for:

- Counts
- Aggregations
- Metrics
- Reports
- Dashboards
- Revenue analysis
- Customer analysis
- Subscription analysis
- Transaction analysis
- Refund analysis
- Fraud analysis
- Shipment analysis
- Joining related data across tables

Important:

- Query runs are limited to **30 seconds total**.
- Queries should be optimized to avoid timeouts.
- BigQuery Standard SQL syntax must be used.
- Table references must be fully qualified and surrounded by backticks.
- Before writing a query, use `GetBigQueryTables` to inspect available tables and schemas.

---

## Required Input

`BigQueryRunQuery` has one required field:

| Field | Type | Required | Description |
|---|---:|---:|---|
| `bigquery_sql` | string | Yes | BigQuery Standard SQL query to execute. |

Example request:

```json
{
  "bigquery_sql": "SELECT COUNT(*) AS total_sales FROM `revcent.user.sale`"
}
```

---

## Output

A successful response includes:

```json
{
  "api_call_id": "XXXXXXXXXXXXXXXXXXXX",
  "api_call_unix": 1740000000,
  "code": 1,
  "query_results": [
    {
      "total_sales": "123"
    }
  ],
  "result": "..."
}
```

Fields:

| Field | Type | Description |
|---|---:|---|
| `api_call_id` | string | 20-character API call ID. |
| `api_call_unix` | integer | Unix timestamp when the API call was initiated. |
| `code` | integer | Response code. `1` indicates success. |
| `query_results` | array<object> | Query result rows. Each object represents one row. |
| `result` | string | Human-readable result message. |

Note:

- Values in `query_results` are returned as key-value pairs corresponding to selected column names.
- Use clear aliases in SQL so returned fields are easy to understand.

---

# Use `GetBigQueryTables` Before Querying

Before calling `BigQueryRunQuery`, use `GetBigQueryTables` to retrieve the available BigQuery tables and their schemas.

`GetBigQueryTables` is especially important when:

- You do not know the exact table name.
- You do not know available fields.
- You need to join multiple tables.
- You need to confirm data types.
- You need to avoid invalid field references.
- You want to optimize the query by selecting only needed fields.

---

## `GetBigQueryTables` Summary

`GetBigQueryTables` returns an object containing all BigQuery tables and schemas available for querying.

It has no input fields.

Request:

```json
{}
```

Example response shape:

```json
{
  "api_call_id": "XXXXXXXXXXXXXXXXXXXX",
  "api_call_unix": 1740000000,
  "code": 1,
  "results": [
    {
      "table_name": "sale",
      "description": "Sales table.",
      "fields": [
        {
          "name": "id",
          "type": "STRING",
          "mode": "NULLABLE",
          "description": "Sale ID."
        },
        {
          "name": "created_date_unix",
          "type": "INTEGER",
          "mode": "NULLABLE",
          "description": "Created date as Unix timestamp."
        }
      ]
    }
  ],
  "current_count": 1,
  "current_page": 1,
  "total_count": 1,
  "total_pages": 1
}
```

Each table includes:

| Field | Description |
|---|---|
| `table_name` | BigQuery table name to use after `revcent.user.` |
| `description` | Table description. |
| `fields` | Array of field definitions for that table. |
| `fields[].name` | Column name. |
| `fields[].type` | BigQuery field type. |
| `fields[].mode` | Field mode, such as nullable or repeated. |
| `fields[].description` | Field description. |

---

# BigQuery Table Catalog

The following table names and descriptions come from `GetBigQueryTables`. Use the full reference shown in SQL queries.

| Table name | Full BigQuery reference | Description |
|---|---|---|
| `ai_assistant` | `` `revcent.user.ai_assistant` `` | The AI assistant table contains all AI assistants created. |
| `ai_voice_agent` | `` `revcent.user.ai_voice_agent` `` | The AI voice agent table contains all AI voice agents created. |
| `ai_voice_call` | `` `revcent.user.ai_voice_call` `` | The AI voice call table contains all AI voice calls created. An AI voice call is created when an outbound or inbound call occurs, and is associated with an AI Voice Agent. |
| `api_call` | `` `revcent.user.api_call` `` | The API call table contains all API calls created. |
| `campaign` | `` `revcent.user.campaign` `` | The campaign table contains all campaigns created. A campaign is created by the user and is a mechanism for organizing sales and their origin. |
| `chargeback` | `` `revcent.user.chargeback` `` | The chargeback table contains all chargebacks created. A chargeback is created either manually or via API. |
| `check` | `` `revcent.user.check` `` | The check table contains all checks created. A check is created when a sale is created with a payment type of check_direct. |
| `coupon` | `` `revcent.user.coupon` `` | The coupon table contains all coupons created. A coupon is created by the user with specific discount settings and usage periods. |
| `customer` | `` `revcent.user.customer` `` | The customer table contains all customers created. A customer can be created manually, via API or automatically during a purchase attempt. |
| `customer_card` | `` `revcent.user.customer_card` `` | The customer card table contains all customer cards created. A customer card can be created manually or is automatically created during a purchase attempt. |
| `customer_group` | `` `revcent.user.customer_group` `` | The customer group table contains all customer groups created. A customer group is created by the user to segment customers based on specific qualifiers and values. |
| `discount` | `` `revcent.user.discount` `` | The discount table contains all discounts created. A discount is created either manually or via a coupon. |
| `fraud_detection` | `` `revcent.user.fraud_detection` `` | The fraud detection table contains all fraud detections created. A fraud detection is created either manually, by a third party or when Sentinel detects a fraudulent purchase attempt. |
| `fulfillment_account` | `` `revcent.user.fulfillment_account` `` | The fulfillment account table contains all fulfillment accounts created. A fulfillment account is created by the user and is associated with a third party fulfillment center. |
| `gateway` | `` `revcent.user.gateway` `` | The gateway table contains all gateways created. A gateway is created by the user and is associated with a third party credit card merchant account. |
| `note` | `` `revcent.user.note` `` | The note table contains all notes created. A note is created using the API or within the web app. |
| `offline_payment` | `` `revcent.user.offline_payment` `` | The offline payment table contains all offline payments created. An offline payment is created when a sale with payment type offline_payment is sold to a customer, and may also represent Affirm, Klarna, AfterPay, Sezzle and other alternate payment providers. |
| `payment_profile` | `` `revcent.user.payment_profile` `` | The payment profile table contains all payment profiles created by the user. A payment profile is a credit card transaction routing mechanism. |
| `paypal_account` | `` `revcent.user.paypal_account` `` | The PayPal account table contains all PayPal accounts added by the user. |
| `paypal_dispute` | `` `revcent.user.paypal_dispute` `` | The PayPal dispute table contains all PayPal disputes created. |
| `paypal_transaction` | `` `revcent.user.paypal_transaction` `` | The PayPal transaction table contains all PayPal transactions created. A PayPal transaction is created when a sale with payment type paypal is created. |
| `pending_refund` | `` `revcent.user.pending_refund` `` | The pending refund table, also known as the refund table, contains all refunds created. Individual pending refunds are itemized at the item type level and should often be grouped by api_call when analyzing refund actions. |
| `product` | `` `revcent.user.product` `` | The product table contains all products created. A product is a line item purchased during a sale, and every sale must have at least one product. |
| `product_group` | `` `revcent.user.product_group` `` | The product group table contains all product groups created. A product group organizes related products for management, filtering and reporting. |
| `product_sale` | `` `revcent.user.product_sale` `` | The product sale table contains all product sales created. A product sale is the line-item purchase of a specific product within an individual sale. |
| `sale` | `` `revcent.user.sale` `` | The sale table contains all sales created. A sale is created when a customer attempts to purchase one or more products. |
| `salvage_transaction` | `` `revcent.user.salvage_transaction` `` | The salvage transaction table contains all salvage transactions created. A salvage transaction is created when a credit card transaction is fully or partially declined and allows recovery of revenue later. |
| `shipping` | `` `revcent.user.shipping` `` | The shipping table contains all shipments created. A shipment is created when a shippable product is sold to a customer in a sale, subscription renewal or trial expiration. |
| `subscription` | `` `revcent.user.subscription` `` | The subscription table contains all subscriptions created. A subscription is created when a product with an associated subscription profile is sold to a customer. |
| `subscription_profile` | `` `revcent.user.subscription_profile` `` | The subscription profile table contains all subscription profiles created by the user. Every subscription has an associated subscription profile that determines its renewal cycle. |
| `subscription_renewal` | `` `revcent.user.subscription_renewal` `` | The subscription renewal table contains renewals created when a subscription renewal cycle determines a recurring transaction should be processed. |
| `tax` | `` `revcent.user.tax` `` | The tax table contains tax created during an initial sale, renewal, trial expiration or salvage transaction. |
| `third_party_shop` | `` `revcent.user.third_party_shop` `` | The third party shop table contains shopping cart domains and/or stores added to RevCent by the user. |
| `tracking_domain` | `` `revcent.user.tracking_domain` `` | The tracking domain table contains tracking domains added to RevCent by the user for conversion tracking. |
| `tracking_entry` | `` `revcent.user.tracking_entry` `` | The tracking entry table contains individual page visits by a tracking visitor to a specific tracking domain. |
| `tracking_visitor` | `` `revcent.user.tracking_visitor` `` | The tracking visitor table consists of visitors to a specific user domain tracked by RevCent for conversion statistics and anti-fraud. |
| `transaction` | `` `revcent.user.transaction` `` | The transaction table consists of credit card transactions processed with a specific merchant gateway, either as a capture or a refund. |
| `trial` | `` `revcent.user.trial` `` | The trial table contains all trials created. A trial is created when a product with a trial period setting is sold to a customer. |
| `user` | `` `revcent.user.user` `` | Users associated with the RevCent account, including account owner, administrator, supervisors and employees. |

---

# Proper Table Formatting

Every table reference in `BigQueryRunQuery` must use this format:

```text
`project.dataset.table`
```

For RevCent user data, the project and dataset are fixed:

```text
project = revcent
dataset = user
```

So the full table format is:

```text
`revcent.user.<table_name>`
```

Examples:

```sql
`revcent.user.sale`
`revcent.user.customer`
`revcent.user.transaction`
`revcent.user.subscription`
`revcent.user.subscription_renewal`
`revcent.user.shipping`
`revcent.user.pending_refund`
```

---

## Backticks Are Required

BigQuery table references must be surrounded by backticks.

Correct:

```sql
SELECT *
FROM `revcent.user.transaction`
LIMIT 10
```

Incorrect:

```sql
SELECT *
FROM revcent.user.transaction
LIMIT 10
```

Incorrect:

```sql
SELECT *
FROM 'revcent.user.transaction'
LIMIT 10
```

Incorrect:

```sql
SELECT *
FROM "revcent.user.transaction"
LIMIT 10
```

Use backticks because BigQuery treats fully qualified table identifiers as identifiers, not strings.

---

## The Table Name Comes from `GetBigQueryTables`

If `GetBigQueryTables` returns:

```json
{
  "table_name": "transaction"
}
```

Then the query should reference:

```sql
`revcent.user.transaction`
```

If it returns:

```json
{
  "table_name": "subscription_renewal"
}
```

Then the query should reference:

```sql
`revcent.user.subscription_renewal`
```

Do not guess table names. Retrieve them with `GetBigQueryTables`.

---

# Recommended Workflow

Use this workflow for reliable BigQuery querying:

1. Call `GetBigQueryTables`.
2. Identify the relevant table or tables.
3. Review each table's fields and data types.
4. Build the SQL using fully qualified table references.
5. Use backticks around every table reference.
6. Select only needed columns.
7. Add date filters where possible.
8. Use `LIMIT` when exploring.
9. Run `BigQueryRunQuery`.
10. If there is a query error, fix the SQL and retry.

---

# Query Rules and Important Notes

## 30-Second Timeout

`BigQueryRunQuery` has a total query runtime limit of 30 seconds.

To reduce timeout risk:

- Filter by date.
- Select only required columns.
- Avoid `SELECT *` in production queries.
- Use `LIMIT` when exploring data.
- Aggregate before joining when possible.
- Avoid unnecessary joins.
- Use table schemas to avoid trial-and-error.
- Keep result sets reasonably small.

---

## Use BigQuery Standard SQL

Queries must use BigQuery Standard SQL.

Standard SQL examples:

```sql
SELECT
  COUNT(*) AS total_sales
FROM `revcent.user.sale`
```

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS sale_date,
  COUNT(*) AS sale_count
FROM `revcent.user.sale`
GROUP BY sale_date
ORDER BY sale_date DESC
```

---

## Avoid `COALESCE`

The operation description explicitly says:

```text
Avoid using COALESCE
```

Instead of:

```sql
COALESCE(amount_total, 0)
```

Use conditional logic that avoids `COALESCE`, such as:

```sql
IF(amount_total IS NULL, 0, amount_total)
```

Example:

```sql
SELECT
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS revenue
FROM `revcent.user.sale`
```

---

## `TIMESTAMP_SUB` Restriction

When using `TIMESTAMP_SUB`, only use `INTERVAL` in `DAY`.

Correct:

```sql
WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
```

Avoid:

```sql
WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
```

Avoid:

```sql
WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)
```

If month-like logic is needed, use day intervals such as 30, 60, or 90 days.

---

# Basic Query Examples

## Count Rows in a Table

```sql
SELECT
  COUNT(*) AS total_sales
FROM `revcent.user.sale`
```

Request:

```json
{
  "bigquery_sql": "SELECT COUNT(*) AS total_sales FROM `revcent.user.sale`"
}
```

---

## Preview Recent Rows

Use `LIMIT` when exploring.

```sql
SELECT
  id,
  created_date_unix,
  amount_total,
  status
FROM `revcent.user.sale`
ORDER BY created_date_unix DESC
LIMIT 10
```

---

## Count Sales by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS sale_date,
  COUNT(*) AS sale_count
FROM `revcent.user.sale`
GROUP BY sale_date
ORDER BY sale_date DESC
LIMIT 30
```

---

## Revenue by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS sale_date,
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS total_revenue
FROM `revcent.user.sale`
GROUP BY sale_date
ORDER BY sale_date DESC
LIMIT 30
```

---

## Sales in Last 30 Days

```sql
SELECT
  COUNT(*) AS sales_last_30_days,
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS revenue_last_30_days
FROM `revcent.user.sale`
WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
```

---

# Join Query Examples

Use `GetBigQueryTables` first to confirm table names and join fields.

## Example Join: Sales and Customers

This assumes the `sale` table contains a customer reference field and the `customer` table contains `id`.

Always verify exact field names with `GetBigQueryTables`.

```sql
SELECT
  s.id AS sale_id,
  s.created_date_unix,
  s.amount_total,
  c.id AS customer_id,
  c.email AS customer_email
FROM `revcent.user.sale` AS s
LEFT JOIN `revcent.user.customer` AS c
  ON s.customer_id = c.id
ORDER BY s.created_date_unix DESC
LIMIT 100
```

Important:

- Do not assume `customer_id` exists until confirmed with `GetBigQueryTables`.
- Some tables may store related IDs differently.
- Review schemas before joining.

---

## Example Join: Transactions and Sales

This assumes the `transaction` table contains a sale reference field.

```sql
SELECT
  t.id AS transaction_id,
  t.amount AS transaction_amount,
  t.created_date_unix AS transaction_created_unix,
  s.id AS sale_id,
  s.amount_total AS sale_amount_total
FROM `revcent.user.transaction` AS t
LEFT JOIN `revcent.user.sale` AS s
  ON t.sale_id = s.id
ORDER BY t.created_date_unix DESC
LIMIT 100
```

---

# Aggregation Examples

## Count by Status

```sql
SELECT
  status,
  COUNT(*) AS count
FROM `revcent.user.sale`
GROUP BY status
ORDER BY count DESC
```

---

## Revenue by Campaign

This assumes the sale table contains campaign fields.

```sql
SELECT
  campaign_id,
  campaign_name,
  COUNT(*) AS sale_count,
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS revenue
FROM `revcent.user.sale`
GROUP BY campaign_id, campaign_name
ORDER BY revenue DESC
LIMIT 50
```

---

## Refund Count by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS refund_date,
  COUNT(*) AS pending_refund_count
FROM `revcent.user.pending_refund`
GROUP BY refund_date
ORDER BY refund_date DESC
LIMIT 30
```

---

## Subscription Renewals by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS renewal_date,
  COUNT(*) AS renewal_count,
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS renewal_amount
FROM `revcent.user.subscription_renewal`
GROUP BY renewal_date
ORDER BY renewal_date DESC
LIMIT 30
```

---

---

# Metadata-Aware BigQuery Queries

Metadata is important for `BigQueryRunQuery` because many business-specific reporting dimensions are stored as metadata rather than standard table columns.

A user may ask for a query using business language such as:

```text
Group sales by affiliate.
```

```text
Show revenue by media buyer.
```

```text
Filter sales where traffic source is Facebook.
```

```text
Group chargebacks by sub ID.
```

The user may not say the word “metadata,” but the requested field may actually be stored as metadata with a name such as:

```text
affiliate_id
media_buyer
traffic_source
sub_id
utm_source
utm_campaign
click_id
```

Do not guess the metadata name or value. Discover metadata first, then write the BigQuery SQL.

---

## Why Metadata Discovery Matters

Metadata names and values are user-defined and account-specific.

That means AI cannot know in advance whether the account uses:

```text
affiliate
affiliate_id
aff_id
affiliateID
partner_id
source_affiliate
```

for the same business concept.

Before generating BigQuery SQL that groups, filters, or joins by metadata, use the metadata API operations to discover what metadata actually exists.

---

## Metadata API Operations

### `GetMetadata`

`GetMetadata` returns a paginated list of metadata names saved in the account.

Important behavior:

- Returns metadata names.
- Does not return values saved for each metadata name.
- Use this first when the user asks for a report involving a business-specific dimension that may be metadata.
- Requires `limit` and `page`.

Request:

```json
{
  "limit": 25,
  "page": 1
}
```

Response fields:

| Field | Description |
|---|---|
| `id` | 20-character Metadata ID. |
| `name` | Metadata name. |
| `enabled` | Whether the metadata entry is enabled. |
| `created_date_unix` | Created timestamp. |
| `updated_date_unix` | Updated timestamp. |

Example response concept:

```json
{
  "results": [
    {
      "id": "XXXXXXXXXXXXXXXXXXXX",
      "name": "affiliate_id",
      "enabled": true
    },
    {
      "id": "YYYYYYYYYYYYYYYYYYYY",
      "name": "utm_campaign",
      "enabled": true
    }
  ]
}
```

---

### `GetMetadataEntry`

`GetMetadataEntry` retrieves one specific metadata entry by Metadata ID and returns its saved values.

Important behavior:

- Requires `metadata_id`.
- Returns the metadata name.
- Returns up to 500 unique saved values for that metadata entry.
- Use this when a query needs to filter by a specific metadata value.
- Use this when the user mentions a value that needs to be matched to real saved values.

Request:

```json
{
  "metadata_id": "XXXXXXXXXXXXXXXXXXXX"
}
```

Response fields:

| Field | Description |
|---|---|
| `id` | 20-character Metadata ID. |
| `name` | Metadata name. |
| `enabled` | Whether the metadata entry is enabled. |
| `values` | Up to 500 saved metadata values. |
| `created_date_unix` | Created timestamp. |
| `updated_date_unix` | Updated timestamp. |

Example response concept:

```json
{
  "id": "XXXXXXXXXXXXXXXXXXXX",
  "name": "affiliate_id",
  "enabled": true,
  "values": [
    "affiliate_123",
    "affiliate_456",
    "affiliate_789"
  ]
}
```

---

## Metadata Query Workflow

When the user asks to group, filter, or report on a business-specific field that may be metadata:

1. Determine whether the requested dimension sounds like a standard BigQuery table field or a custom metadata field.
2. Use `GetBigQueryTables` or `GetBigQueryTable` to inspect standard table schemas.
3. If the field is not a clear standard field, call `GetMetadata`.
4. Review metadata names for likely matches.
5. If a specific value is needed, call `GetMetadataEntry` for the matching metadata ID.
6. Use the discovered metadata name and value in the `BigQueryRunQuery` SQL.
7. If no likely metadata name is found, explain that the metadata name was not found rather than guessing.

---

## Examples of User Language That May Mean Metadata

| User says | Possible actual metadata name |
|---|---|
| affiliate | `affiliate_id`, `affiliate`, `aff_id`, `partner_id` |
| sub ID | `sub_id`, `subid`, `sub_1`, `sub1` |
| click ID | `click_id`, `gclid`, `fbclid`, `ttclid` |
| UTM source | `utm_source`, `source` |
| UTM campaign | `utm_campaign`, `campaign_code` |
| media buyer | `media_buyer`, `buyer`, `traffic_buyer` |
| landing page | `landing_page`, `lp`, `page_slug` |
| funnel | `funnel`, `funnel_id`, `flow` |
| creative | `creative`, `creative_id`, `ad_id` |
| ad set | `adset`, `adset_id`, `ad_set_id` |

These are examples only. Always discover the real metadata names for the account before writing the query.

---

## Example: User Asks to Group Sales by Affiliate

User request:

```text
Show revenue by affiliate for the last 30 days.
```

Correct reasoning:

1. “Affiliate” may not be a standard sale table field.
2. Call `GetMetadata`.
3. Look for metadata names such as `affiliate`, `affiliate_id`, `aff_id`, or `partner_id`.
4. If `affiliate_id` exists, use that metadata name in the BigQuery query.
5. If the exact metadata storage structure is unclear, inspect BigQuery table schemas with `GetBigQueryTables`.

Do not immediately generate a query using a guessed field:

```sql
sale.affiliate
```

or:

```sql
sale.affiliate_id
```

unless the schema confirms that field exists.

---

## Example: User Asks to Filter by a Specific Affiliate

User request:

```text
Show sales for affiliate ABC123.
```

Correct workflow:

1. Call `GetMetadata`.
2. Find likely metadata name, such as `affiliate_id`.
3. Call `GetMetadataEntry` for that metadata ID.
4. Confirm whether `ABC123` or a similar value exists.
5. Generate the `BigQueryRunQuery` SQL using the actual metadata name/value.

This avoids mistakes where the user’s label differs from the stored value.

---

## Metadata and BigQuery Tables

Metadata may appear in BigQuery as fields or nested/repeated data depending on the table schema.

Because metadata storage can vary by table and item type, always inspect the relevant BigQuery table schema before writing SQL.

Relevant tables may include:

- `sale`
- `customer`
- `subscription`
- `subscription_renewal`
- `trial`
- `shipping`
- `transaction`
- `chargeback`

Use:

```text
GetBigQueryTables
```

or:

```text
GetBigQueryTable
```

to locate metadata-related fields in the relevant table.

Common patterns may include fields such as:

```text
metadata
metadata_name
metadata_value
metadata_entries
```

but the actual schema should be treated as the source of truth.

---

## Metadata Query Pattern

The exact SQL depends on the table schema returned by `GetBigQueryTables`.

If metadata is represented as a repeated array of objects, the pattern may involve `UNNEST`.

Conceptual example only:

```sql
SELECT
  metadata_entry.value AS affiliate_id,
  COUNT(*) AS sale_count,
  SUM(IF(s.amount_total IS NULL, 0, s.amount_total)) AS revenue
FROM `revcent.user.sale` AS s,
UNNEST(s.metadata) AS metadata_entry
WHERE metadata_entry.name = 'affiliate_id'
  AND TIMESTAMP_SECONDS(s.created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY affiliate_id
ORDER BY revenue DESC
```

Important:

- Do not use this exact query unless `GetBigQueryTables` confirms that the `sale` table has a repeated `metadata` field with `name` and `value` properties.
- Adjust the SQL to match the actual schema.
- The confirmed metadata name should come from `GetMetadata`.
- If filtering by a specific value, confirm the value with `GetMetadataEntry`.

---

## AI Guidance for Metadata-Based BigQueryRunQuery Requests

When acting as AI or MCP tooling:

- Treat user-defined labels like “affiliate,” “source,” “sub ID,” “funnel,” and “media buyer” as possible metadata.
- Do not guess the metadata name.
- Use `GetMetadata` to discover metadata names.
- Use `GetMetadataEntry` to discover values when filtering by a specific value.
- Use `GetBigQueryTables` or `GetBigQueryTable` to confirm how metadata is represented in BigQuery.
- Only generate SQL after confirming the metadata name and table schema.
- If there are multiple likely metadata names, choose the most likely only when context is strong.
- If ambiguity cannot be safely resolved, explain the likely metadata names and ask which one should be used.

---

## Metadata Query Checklist

Before writing a `BigQueryRunQuery` query involving a custom business dimension:

1. Check whether the requested field exists as a standard column.
2. If not, call `GetMetadata`.
3. Identify the likely metadata name.
4. If filtering by value, call `GetMetadataEntry`.
5. Confirm the value exists or identify the closest available values.
6. Inspect the relevant BigQuery table schema.
7. Write SQL using the actual metadata name and schema.
8. Avoid guessing field names such as `affiliate`, `source`, or `campaign_code`.
9. Use clear aliases so results are understandable.
10. Explain which metadata name was used in the final query.


# Performance Best Practices

## Use Date Filters

Prefer filtering by date fields when available.

Example:

```sql
WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
```

## Avoid `SELECT *`

Instead of:

```sql
SELECT *
FROM `revcent.user.sale`
```

Use:

```sql
SELECT
  id,
  created_date_unix,
  amount_total,
  status
FROM `revcent.user.sale`
```

## Limit Exploration Queries

```sql
LIMIT 10
```

Use `LIMIT` when exploring fields or validating assumptions.

## Aggregate Before Joining

If joining large tables, consider aggregating first.

Example:

```sql
WITH sales_by_customer AS (
  SELECT
    customer_id,
    COUNT(*) AS sale_count,
    SUM(IF(amount_total IS NULL, 0, amount_total)) AS revenue
  FROM `revcent.user.sale`
  GROUP BY customer_id
)
SELECT
  c.id,
  c.email,
  s.sale_count,
  s.revenue
FROM sales_by_customer AS s
LEFT JOIN `revcent.user.customer` AS c
  ON s.customer_id = c.id
ORDER BY s.revenue DESC
LIMIT 100
```

Verify `customer_id` exists before using this pattern.

---

# Common Mistakes

## Missing Backticks

Incorrect:

```sql
FROM revcent.user.sale
```

Correct:

```sql
FROM `revcent.user.sale`
```

---

## Using the Wrong Project or Dataset

Incorrect:

```sql
FROM `my_project.my_dataset.sale`
```

Correct:

```sql
FROM `revcent.user.sale`
```

Project must be:

```text
revcent
```

Dataset must be:

```text
user
```

---

## Guessing Table Names

Incorrect:

```sql
FROM `revcent.user.sales`
```

Correct if the table is named `sale`:

```sql
FROM `revcent.user.sale`
```

Always confirm with `GetBigQueryTables`.

---

## Guessing Field Names

Incorrect if `customer_email` does not exist:

```sql
SELECT customer_email
FROM `revcent.user.sale`
```

Better:

1. Call `GetBigQueryTables`.
2. Review fields on the `sale` table.
3. Use the exact field name.

---

## Using `COALESCE`

Avoid:

```sql
COALESCE(amount_total, 0)
```

Prefer:

```sql
IF(amount_total IS NULL, 0, amount_total)
```

---

## Using Non-DAY Intervals with `TIMESTAMP_SUB`

Avoid:

```sql
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 MONTH)
```

Prefer:

```sql
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
```

---

# BigQueryRunQuery Request Examples

## Simple Count

```json
{
  "bigquery_sql": "SELECT COUNT(*) AS total_sales FROM `revcent.user.sale`"
}
```

## Recent Sales

```json
{
  "bigquery_sql": "SELECT id, created_date_unix, amount_total, status FROM `revcent.user.sale` ORDER BY created_date_unix DESC LIMIT 10"
}
```

## Last 30 Days Revenue

```json
{
  "bigquery_sql": "SELECT COUNT(*) AS sales_last_30_days, SUM(IF(amount_total IS NULL, 0, amount_total)) AS revenue_last_30_days FROM `revcent.user.sale` WHERE TIMESTAMP_SECONDS(created_date_unix) >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)"
}
```

---

# When to Use BigQuery Instead of List Operations

Many RevCent list operations explicitly note that they should not be used for counting, aggregations, or metrics.

Use `BigQueryRunQuery` for:

- “How many sales?”
- “What was revenue by day?”
- “What are my top campaigns?”
- “How many chargebacks occurred?”
- “What is the failed renewal rate?”
- “How many shipments were delivered?”
- “What is refund volume by week?”
- “What are trends over time?”
- “Which products generated the most revenue?”

Use normal Get/List/Search operations when you need:

- A specific item by ID.
- A small list of operational records.
- To edit or act on individual items.
- To retrieve a detail object for operational workflow use.

---

# Implementation Checklist

Before calling `BigQueryRunQuery`:

1. Use `GetBigQueryTables` to retrieve available tables and schemas.
2. Confirm the exact table name.
3. Confirm the exact field names.
4. Use BigQuery Standard SQL.
5. Reference tables as `` `revcent.user.<table_name>` ``.
6. Always surround table references with backticks.
7. Select only the fields needed.
8. Add date filters when possible.
9. Use `LIMIT` for exploratory queries.
10. Avoid `COALESCE`.
11. Use `TIMESTAMP_SUB` only with `INTERVAL ... DAY`.
12. Keep queries optimized for the 30-second limit.
13. Use clear aliases for result columns.
14. If a query error occurs, fix the SQL and retry.

---

# Quick Reference

Correct table reference:

```sql
`revcent.user.transaction`
```

Correct query:

```sql
SELECT
  COUNT(*) AS transaction_count
FROM `revcent.user.transaction`
```

Correct request:

```json
{
  "bigquery_sql": "SELECT COUNT(*) AS transaction_count FROM `revcent.user.transaction`"
}
```

Recommended first step:

```text
Call GetBigQueryTables to inspect all available table names and schemas.
```


---
Document Parent Directory
* [Operations](https://revcent.com/documentation/markdown/mcp/operation/index.md) - AI/MCP details and overviews for operations available within the RevCent MCP.