# RevCent BigQuery Overview for Ecommerce Reporting

RevCent BigQuery gives ecommerce businesses direct SQL access to their RevCent account data for reporting, analytics, dashboards, AI analysis, and custom business intelligence workflows.

Sources used:
- RevCent MCP/API operations:
  - `BigQueryRunQuery`
  - `GetBigQueryTables`
  - `GetBigQueryTable`
- RevCent Knowledge Base: BigQuery — `https://kb.revcent.com/en/reporting/bigquery`
- RevCent Knowledge Base: BigQuery Access — `https://kb.revcent.com/tools/bigquery-access`

---

## What Is RevCent BigQuery?

RevCent stores each user’s account data securely in Google BigQuery, an enterprise cloud data warehouse.

RevCent streams account updates into BigQuery when account data changes, such as sales, customers, transactions, subscriptions, shipments, refunds, chargebacks, tracking data, AI activity, and API activity.

BigQuery is designed for large-scale analytical queries, making it the correct tool for reporting, metrics, aggregations, trends, dashboards, and advanced ecommerce analysis.

---

## Why Ecommerce Businesses Use BigQuery

Use BigQuery when you need to answer business questions across many records.

Examples:

- How much revenue did we generate today?
- Which campaigns produced the most sales?
- What is our approval rate by gateway?
- Which products have the highest refund rate?
- How many subscriptions renewed successfully this week?
- Which customers have the most chargebacks?
- What percentage of trial expirations succeeded?
- Which fulfillment accounts have delayed shipments?
- What is the daily declined transaction rate?
- Which tracking visitors converted to paid sales?

BigQuery is built for analytics. Normal list/search API operations are built for operational retrieval of individual records or small result sets.

---

# How RevCent BigQuery Works

RevCent BigQuery works in four main ways:

1. RevCent keeps user account data in BigQuery.
2. RevCent streams updates when data changes.
3. Each user has a queryable dataset containing RevCent tables.
4. Users can query that data through the API, MCP, AI Assistants, or direct Google BigQuery access.

Important:

- BigQuery data is read-only / immutable from the user’s query perspective.
- DML statements and table modifications are not supported.
- Use queries for analysis, not for editing data.
- Use RevCent API operations to create, edit, refund, void, or otherwise modify business data.

---

# Ways to Access BigQuery Data

| Access Method | Best For |
|---|---|
| `BigQueryRunQuery` API / MCP operation | Running SQL queries programmatically or through MCP. |
| `GetBigQueryTables` API / MCP operation | Discovering all available tables and schemas. |
| `GetBigQueryTable` API / MCP operation | Inspecting one specific table schema. |
| AI Assistant system tools | Letting AI run reporting queries as part of a workflow. |
| Direct Google BigQuery access | Data analysts, BI tools, custom SQL exploration, dashboards. |
| RevCent dashboard | Built-in reporting powered by the same BigQuery data. |

---

# BigQuery API / MCP Operations

## `BigQueryRunQuery`

Runs a BigQuery Standard SQL query against the user’s RevCent data and returns results.

Use for:

- Counts
- Aggregations
- Reports
- Metrics
- Joins
- Trend analysis
- Dashboard data
- AI-generated analysis
- Custom ecommerce reporting

Required input:

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

Example:

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

Important operation rules:

- Query runtime is limited to 30 seconds.
- Use BigQuery Standard SQL.
- Use fully qualified table references.
- Surround table references with backticks.
- Avoid `COALESCE`.
- When using `TIMESTAMP_SUB`, only use `INTERVAL ... DAY`.
- If a query errors, fix the SQL and retry.

---

## `GetBigQueryTables`

Returns all available BigQuery tables and their schemas.

Use before writing queries when:

- You need table names.
- You need field names.
- You need field types.
- You need to build joins.
- You need to avoid guessing.
- You want AI or MCP to generate valid SQL.

Request:

```json
{}
```

Response includes:

| Field | Description |
|---|---|
| `table_name` | BigQuery table name. |
| `description` | Table description. |
| `fields` | Array of field definitions. |
| `fields[].name` | Field/column name. |
| `fields[].type` | BigQuery type. |
| `fields[].mode` | Field mode. |
| `fields[].description` | Field description. |

---

## `GetBigQueryTable`

Retrieves details for one specific table, including schema.

Use when:

- You know the table you need.
- You want to inspect a single table’s fields.
- You are not joining many tables.

Required input:

| Field | Type | Required | Description |
|---|---:|---:|---|
| `table_name` | string | Yes | BigQuery table name. |

Example:

```json
{
  "table_name": "sale"
}
```

If joining multiple tables, use `GetBigQueryTables` instead so all schemas are available in one call.

---

# Table Reference Formatting

When using `BigQueryRunQuery`, table references must use:

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

For RevCent MCP/API BigQuery queries:

```text
project = revcent
dataset = user
table = table name
```

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

Correct:

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

Incorrect:

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

---

# Direct Google BigQuery Access

RevCent also allows users to grant direct BigQuery access to their own Google account or trusted analysts.

This is useful for:

- Data scientists
- BI analysts
- External reporting tools
- Custom dashboards
- SQL exploration in the BigQuery UI
- Connecting Looker Studio or other BI tools
- Large ad hoc analysis

Important:

- Direct BigQuery access requires a Google account.
- The person running queries in Google BigQuery may incur Google BigQuery query costs.
- The BigQuery free tier may be sufficient for many RevCent datasets, but users should confirm their own Google billing settings.

---

## Direct Access Security

No access is available by default.

A RevCent account owner or authorized user must explicitly enable BigQuery access and provide one or more email addresses.

Warning:

Granting BigQuery access means the user can see raw account data, including customer contact information. Only grant access to trusted people.

Recommended access control:

- Grant access only to trusted users.
- Remove users who no longer need access.
- Use role-based internal policies for data access.
- Avoid granting access to shared inboxes.
- Treat BigQuery access like access to customer and revenue data.

---

## Direct Access Dataset

When using direct Google BigQuery access:

- The dataset name is the RevCent user ID.
- The dataset appears under the `revcent-shared` project.
- RevCent provides a “Link To Add Dataset” in the BigQuery Access page.
- If the link does not work, users can manually add the project in BigQuery.

Manual add flow:

1. Open Google BigQuery UI.
2. Click `+ ADD` in Explorer.
3. Choose `Star a project by name`.
4. Enter:

```text
revcent-shared
```

5. If access was granted, the dataset named with the RevCent user ID appears under the `revcent-shared` project.

Important distinction:

- API/MCP queries use `revcent.user.<table_name>`.
- Direct Google BigQuery UI access uses the shared project/dataset shown in the user’s BigQuery Access page.

---

# Read-Only / Immutable Data

RevCent BigQuery data is for querying and reporting.

Do not use BigQuery for:

- Updating records
- Deleting records
- Inserting records
- Modifying tables
- Creating tables
- DML statements

Use RevCent API operations or the RevCent web app for data modifications.

---

# When to Use BigQuery Instead of List Operations

Many RevCent list operations explicitly say not to use them for counts, metrics, or aggregations.

Use BigQuery for:

- Counts
- Sums
- Averages
- Rates
- Grouping
- Filtering across large datasets
- Time-series reports
- Joins across tables
- Funnel analysis
- Operational dashboards
- AI-generated reporting

Use list/get/search operations for:

- Finding a specific record.
- Retrieving details for one item.
- Operational workflows.
- Editing or acting on individual records.
- Small result lists for user review.

---

# Ecommerce Reporting Use Cases

## Revenue Reporting

Questions BigQuery can answer:

- Revenue today
- Revenue by day
- Revenue by campaign
- Revenue by product
- Revenue by payment method
- Refund-adjusted revenue
- Subscription renewal revenue
- Trial conversion revenue

Example:

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

---

## Payment Gateway Analysis

Questions:

- Approval rate by gateway
- Decline rate by gateway
- Refund volume by gateway
- Gateway response trends
- Gateway outage detection
- Gateway performance by campaign

Tables often involved:

- `transaction`
- `gateway`
- `sale`
- `payment_profile`
- `salvage_transaction`

---

## Customer Analytics

Questions:

- New customers by day
- Customers by campaign
- Repeat customers
- Customers with multiple failed payments
- Customers with chargebacks
- Customer lifetime value

Tables often involved:

- `customer`
- `sale`
- `transaction`
- `chargeback`
- `customer_group`
- `customer_card`

---

## Subscription Analytics

Questions:

- Active subscriptions
- Renewals by day
- Renewal success rate
- Renewal failure reasons
- Overdue subscriptions
- Subscription cancellations
- Revenue by subscription profile

Tables often involved:

- `subscription`
- `subscription_renewal`
- `subscription_profile`
- `transaction`
- `sale`

---

## Refund and Chargeback Analytics

Questions:

- Refund volume by day
- Pending refunds by type
- Chargeback count by month
- Chargebacks by campaign
- Refund-to-sale ratio
- High-risk products
- Refunds grouped by API call

Tables often involved:

- `pending_refund`
- `chargeback`
- `transaction`
- `sale`
- `product_sale`
- `product`

Note:

The `pending_refund` table may contain itemized refund records. For some analyses, group by `api_call` or the relevant refund grouping field to avoid overcounting a single refund action.

---

## Product Analytics

Questions:

- Best-selling products
- Revenue by product
- Refunds by product
- Product group performance
- Product-level subscription creation
- Product sales by campaign

Tables often involved:

- `product`
- `product_sale`
- `product_group`
- `sale`
- `subscription`
- `trial`

---

## Shipping and Fulfillment Analytics

Questions:

- Shipments created by day
- Shipment status counts
- Fulfillment provider performance
- Delivered vs voided shipments
- Shipping refund activity
- Shipment timing by product or campaign

Tables often involved:

- `shipping`
- `fulfillment_account`
- `product_sale`
- `sale`
- `pending_refund`

---

## Tracking and Conversion Analytics

Questions:

- Tracking visitors by domain
- Page visits by domain
- Conversion by visitor
- Conversion by tracking domain
- Visitor behavior before sale
- Anti-fraud tracking analysis

Tables often involved:

- `tracking_domain`
- `tracking_entry`
- `tracking_visitor`
- `sale`

---

## AI and Automation Analytics

Questions:

- AI Assistants created
- AI Voice Agents created
- AI Voice Calls by day
- API calls by day
- AI call or assistant activity trends
- Automation-related volume

Tables often involved:

- `ai_assistant`
- `ai_voice_agent`
- `ai_voice_call`
- `api_call`

---

# BigQuery in AI Assistants

AI Assistants can use BigQuery as a system tool to analyze account data.

Example AI Assistant step:

```text
Run a BigQuery analysis of yesterday's sales and transaction decline rate. If the decline rate is above normal, create an AI Memo summarizing the issue.
```

Useful AI Assistant workflows:

- Daily revenue summary
- High decline-rate alert
- Gateway outage detection
- Refund spike monitoring
- Chargeback pattern detection
- Subscription failure analysis
- Campaign performance summaries
- Product performance summaries

Best practices:

- Ask the AI to use `GetBigQueryTables` before writing SQL.
- Ask for a specific date range.
- Ask for concise output.
- Set AI Assistant token limits.
- Use AI Memos for alerts.
- Use Email Templates for scheduled reporting emails.

---

# BigQuery in MCP / AI Harnesses

The RevCent MCP exposes BigQuery operations for external AI tools and agents.

This allows prompts such as:

```text
Run a report to find gateways with approval rate below 70% in the last 7 days.
```

or:

```text
Find the top 10 products by revenue for the last 30 days.
```

Recommended MCP workflow:

1. Use `GetBigQueryTables`.
2. Identify relevant table schemas.
3. Write BigQuery Standard SQL.
4. Use `BigQueryRunQuery`.
5. If SQL errors, fix and retry.
6. Summarize the results clearly.

---

# BigQuery Table Catalog

These are the available BigQuery table names and descriptions.

Use the full API/MCP table reference:

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

| Table | Full API/MCP Reference | Description |
|---|---|---|
| `ai_assistant` | `` `revcent.user.ai_assistant` `` | Contains AI Assistants created in the account. |
| `ai_voice_agent` | `` `revcent.user.ai_voice_agent` `` | Contains AI Voice Agents created in the account. |
| `ai_voice_call` | `` `revcent.user.ai_voice_call` `` | Contains AI Voice Calls created when outbound or inbound calls occur, associated with AI Voice Agents. |
| `api_call` | `` `revcent.user.api_call` `` | Contains API calls created in the account. |
| `campaign` | `` `revcent.user.campaign` `` | Contains campaigns used to organize sales and their origin. |
| `chargeback` | `` `revcent.user.chargeback` `` | Contains chargebacks created manually or via API. |
| `check` | `` `revcent.user.check` `` | Contains checks created when a sale uses payment type `check_direct`. |
| `coupon` | `` `revcent.user.coupon` `` | Contains coupons created with discount settings and usage periods. |
| `customer` | `` `revcent.user.customer` `` | Contains customers created manually, via API, or automatically during purchase attempts. |
| `customer_card` | `` `revcent.user.customer_card` `` | Contains customer cards. Does not contain raw sensitive card numbers. |
| `customer_group` | `` `revcent.user.customer_group` `` | Contains customer groups used to segment customers. |
| `discount` | `` `revcent.user.discount` `` | Contains discounts created manually, via coupons, or sale/API logic. |
| `fraud_detection` | `` `revcent.user.fraud_detection` `` | Contains fraud detections created manually, by third parties, or by Sentinel. |
| `fulfillment_account` | `` `revcent.user.fulfillment_account` `` | Contains fulfillment accounts connected to third-party fulfillment centers. |
| `gateway` | `` `revcent.user.gateway` `` | Contains gateways associated with third-party credit card merchant accounts. |
| `note` | `` `revcent.user.note` `` | Contains notes created through API or web app. |
| `offline_payment` | `` `revcent.user.offline_payment` `` | Contains offline payments and alternate payment provider records. |
| `payment_profile` | `` `revcent.user.payment_profile` `` | Contains payment profiles used for credit card transaction routing. |
| `paypal_account` | `` `revcent.user.paypal_account` `` | Contains PayPal accounts added by the user. |
| `paypal_dispute` | `` `revcent.user.paypal_dispute` `` | Contains PayPal disputes. |
| `paypal_transaction` | `` `revcent.user.paypal_transaction` `` | Contains PayPal transactions created from PayPal sales. |
| `pending_refund` | `` `revcent.user.pending_refund` `` | Contains pending refund/refund records. |
| `product` | `` `revcent.user.product` `` | Contains products available for sale. |
| `product_group` | `` `revcent.user.product_group` `` | Contains product groups used for management, filtering, and reporting. |
| `product_sale` | `` `revcent.user.product_sale` `` | Contains product line items purchased within sales. Useful for product-level metrics. |
| `sale` | `` `revcent.user.sale` `` | Contains sales created when customers attempt to purchase products. |
| `salvage_transaction` | `` `revcent.user.salvage_transaction` `` | Contains salvage transactions used to recover revenue after full or partial credit card declines. |
| `shipping` | `` `revcent.user.shipping` `` | Contains shipments created from sales, renewals, or trial expirations. |
| `subscription` | `` `revcent.user.subscription` `` | Contains subscriptions created when subscription products are sold. |
| `subscription_profile` | `` `revcent.user.subscription_profile` `` | Contains subscription profiles that define renewal cycles. |
| `subscription_renewal` | `` `revcent.user.subscription_renewal` `` | Contains subscription renewal attempts and renewal records. |
| `tax` | `` `revcent.user.tax` `` | Contains tax records from sales, renewals, trials, or salvage transactions. |
| `third_party_shop` | `` `revcent.user.third_party_shop` `` | Contains third-party shopping cart domains/stores. |
| `tracking_domain` | `` `revcent.user.tracking_domain` `` | Contains tracking domains used for conversion tracking. |
| `tracking_entry` | `` `revcent.user.tracking_entry` `` | Contains individual page visits by tracking visitors. |
| `tracking_visitor` | `` `revcent.user.tracking_visitor` `` | Contains tracked visitors for conversion statistics and anti-fraud analysis. |
| `transaction` | `` `revcent.user.transaction` `` | Contains credit card transactions processed with merchant gateways. |
| `trial` | `` `revcent.user.trial` `` | Contains trials created from products with trial period settings. |
| `user` | `` `revcent.user.user` `` | Contains users associated with the RevCent account. |

---

---

# Metadata-Aware BigQuery Reporting

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

A user may ask for a report 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
```

AI and MCP tooling should not guess the exact metadata name or available values. The correct workflow is to discover metadata first.

---

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

---

## Relevant Metadata API Operations

### `GetMetadata`

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

Important behavior:

- Returns metadata names.
- Does not return the 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 includes:

| Field | Description |
|---|---|
| `id` | 20-character Metadata ID. |
| `name` | Metadata name. |
| `enabled` | Whether the metadata 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, or when the user mentions a value that needs to be matched to real saved values.

Request:

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

Response includes:

| Field | Description |
|---|---|
| `id` | 20-character Metadata ID. |
| `name` | Metadata name. |
| `enabled` | Whether metadata 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 Reporting 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 query.
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.

---

## 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 BigQuery query 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.

---

## AI Guidance for Metadata-Based Reporting

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; otherwise explain the ambiguity.
- If the user asks for a grouped report and metadata names are ambiguous, present the likely metadata names and ask which one should be used only if the query cannot be safely inferred.

---

## Metadata Reporting Checklist

Before writing a BigQuery 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 report.


# Query Writing Best Practices

## Use `GetBigQueryTables` First

Always inspect schemas before writing SQL, especially for joins.

```text
GetBigQueryTables → inspect fields → write SQL → BigQueryRunQuery
```

## Select Only Needed Fields

Avoid:

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

Prefer:

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

## Use Date Filters

Date filters reduce query time and result size.

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

## Use `LIMIT` When Exploring

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

## Use Clear Aliases

```sql
SELECT
  COUNT(*) AS sale_count,
  SUM(IF(amount_total IS NULL, 0, amount_total)) AS total_revenue
FROM `revcent.user.sale`
```

## Avoid `COALESCE`

Use:

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

instead of:

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

## Use `TIMESTAMP_SUB` Only with DAY

Correct:

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

Avoid hour/month intervals with `TIMESTAMP_SUB` in API/MCP-generated queries.

---

# Example Queries

## Count Sales

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

## Revenue by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS sale_date,
  COUNT(*) AS sale_count,
  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
```

## Last 30 Days Sales

```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)
```

## Refunds by Day

```sql
SELECT
  DATE(TIMESTAMP_SECONDS(created_date_unix)) AS refund_date,
  COUNT(*) AS 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
FROM `revcent.user.subscription_renewal`
GROUP BY renewal_date
ORDER BY renewal_date DESC
LIMIT 30
```

## Transactions by Gateway

Verify exact gateway field names with `GetBigQueryTables` before using.

```sql
SELECT
  gateway_id,
  COUNT(*) AS transaction_count
FROM `revcent.user.transaction`
GROUP BY gateway_id
ORDER BY transaction_count DESC
```

---

# Common Mistakes

## Using List APIs for Metrics

Incorrect:

```text
Call GetSales repeatedly and count rows manually.
```

Correct:

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

## Missing Backticks

Incorrect:

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

Correct:

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

## Guessing Field Names

Do not guess fields such as `customer_email`, `gateway_name`, or `sale_total`.

Use:

```text
GetBigQueryTables
```

or:

```text
GetBigQueryTable
```

to confirm the exact schema.

## Attempting to Modify Data

BigQuery is for reporting. Use RevCent operations to modify data.

---

# Security and Privacy

BigQuery data can include sensitive business and customer information.

Protect access carefully.

Security guidance:

- Grant direct BigQuery access only to trusted users.
- Remember that users with access may see customer contact information.
- Review access periodically.
- Remove access when no longer needed.
- Use API/MCP permissions carefully.
- Avoid exporting sensitive data unnecessarily.
- Be cautious connecting third-party BI tools.

---

# Implementation Checklist

Before querying:

1. Decide whether the task is reporting/analytics or operational retrieval.
2. Use BigQuery for counts, metrics, aggregations, and joins.
3. Use Get/List/Search operations for specific operational records.
4. Call `GetBigQueryTables` to inspect tables and schemas.
5. Use `GetBigQueryTable` if only one table schema is needed.
6. Use BigQuery Standard SQL.
7. Reference tables as `` `revcent.user.<table_name>` `` in API/MCP queries.
8. Use backticks around table references.
9. Add date filters when possible.
10. Avoid `SELECT *` for production queries.
11. Avoid `COALESCE`.
12. Use `TIMESTAMP_SUB` only with `INTERVAL ... DAY`.
13. Keep API/MCP queries optimized for the 30-second runtime limit.
14. Use clear aliases in results.
15. Review direct BigQuery access permissions carefully.

---

# Quick Decision Guide

| Goal | Use |
|---|---|
| Count sales | BigQuery |
| Calculate revenue by day | BigQuery |
| Find top products by revenue | BigQuery |
| Analyze gateway approval rate | BigQuery |
| Retrieve one sale details | `GetSale` |
| Search for customer by email | Search/Get customer operations |
| Refund a transaction | Refund operation |
| Create operational note | `CreateNote` |
| Build dashboard | BigQuery |
| Let AI analyze trends | BigQuery via AI Assistant or MCP |
| Share raw data with analyst | BigQuery Access |

---

# Summary

RevCent BigQuery is the analytics layer for ecommerce data.

Use it to query large datasets, produce metrics, join related tables, power AI analysis, build dashboards, and give trusted analysts access to raw reporting data.

For API/MCP usage, use:

```text
GetBigQueryTables → BigQueryRunQuery
```

For direct Google access, enable BigQuery Access in RevCent and grant specific trusted Google account emails.

BigQuery should be used for analysis and reporting, while RevCent API operations should be used for operational actions and record modifications.


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