xnurta-query-ads-performance v1.1.0A

Immutable. This exact content is served forever at /api/v1/blob/c82cf82738fb8197.

---
name: xnurta-query-ads-performance
description: >-
  Query advertising performance metrics: spend, clicks, sales, ACOS, ROAS, etc.
  For analyzing ad performance, comparing time periods, ranking by entity, trend analysis.
  Keywords: ad data, performance, spend analysis, ACOS, sales, conversion rate,
  campaign performance, adGroup comparison, keyword analysis, ASIN ads, search term report,
  daily/weekly/monthly trends, top N ranking, week-over-week, year-over-year,
  hourly data, by hour, intraday, AMS, Amazon Marketing Stream, keyword placement
metadata:
  version: 1.1.0
---

# Query Ads Performance Skill

## MCP Tool

This skill maps to MCP tool: **`get_ads_perf`**. Required scope: `amazon_sa_performance_data:read`.

Profile scope is resolved from the authenticated bearer token. `profileIds` is **required** — always call `get_user_authorized_context` first to obtain authorized profile IDs, then pass one or more into `profileIds`. **Every requested ID must be authorized: one unauthorized value fails the entire call** with `invalid_params` / `Requested profileIds contain unauthorized values` — nothing is silently dropped and you never get a partial result (see Platform-Wide Rules below). Take the IDs verbatim from `get_user_authorized_context`; don't hand-type them or reuse them from an earlier session. If the user didn't specify a store, pass **all** authorized `profileIds`. Never pass `tenantId` or `userId`; the server derives them from the token.

**`userContext` (required)**: Must pass a non-empty string on every call. Preserve the user's original query as much as possible, plus the agent's reason for calling this tool. Summarize if too long, max 100 characters.

## Platform-Wide Rules

**Before using this tool, read [`references/platform-notes.md`](references/platform-notes.md)** — it covers auth flow, permission scopes, error handling, pagination/date-limit tables, the Ratio Metric Display Rule (ACOS/CTR/CVR/TACOS), currency rules, the tool-selection decision tree, and implicit inference rules shared across all 3 read tools. That file ships inside this same skill folder, so it travels with this skill regardless of how it's packaged/installed. This SKILL.md only covers what's specific to `get_ads_perf`.

## When to Use

Use this tool when the user needs any of the following:
- Query ad performance data (spend, clicks, conversions, ACOS, etc)
- Filter and aggregate data by time range (daily, weekly, monthly trends)
- Group and rank by entity dimension (Campaign, AdGroup, Keyword, ASIN)
- Get top N rankings or performance leaderboards
- Compare time periods (week-over-week, month-over-month, before/after AI management)
- Analyze ad ROI and cost efficiency

See the Tool Selection Decision Tree above if the user's ask might belong to `get_entity_metadata` or `get_operation_log` instead.

## Core Concepts

### Fact Entity (factEntity)

The query subject determining aggregation granularity:
- `campaign`: campaign level (the only entity that supports `timeGranularity: hourly`)
- `adGroup`: ad group level
- `target`: keyword/targeting level (requires `queryType`: `keyword` / `product` / `auto`)
- `searchTerm`: search term level (requires `queryType`: `keyword` / `product` — **`auto` is not valid here**, unlike `target`)
- `placement`: ad placement level
- `productAd`: product ad level
- `asin`: ASIN business data level (adds business metrics like TotalSalesAmount, TACOS)
- `keywordPlacement`: keyword × placement joint level, sourced from Amazon Marketing Stream (AMS) — **hourly by nature, SP only, max 7-day span**. See Hourly / AMS Data below

Passing an unsupported value fails with `invalid_params`; the enum above is the complete list.

### Dimension Entities

Auxiliary tables for joining and aggregating — referenced via `select`/`filters`, not declared separately:
- `campaign`, `adGroup`, `target`, `asin`, `parentAsin`, `aiGroup`, `portfolio`, `productLine`, `profile`, `automationRule`
- System auto-JOINs based on fields in select/filters, no manual specification needed

## DSL Parameter Format

```json
{
  "userContext": "User's original query + agent's reason for calling",
  "factEntity": "entity_type",
  "dateStart": "YYYY-MM-DD",
  "dateEnd": "YYYY-MM-DD",
  "metrics": ["metric_list"],
  "profileIds": [1234567890123456],
  "select": ["dimension_field_list"],
  "groupBy": ["same as select, only needed if select contains a custom aggregate expression"],
  "filters": {},
  "orderBy": [{"field": "field_name", "direction": "DESC"}],
  "page": 1,
  "pageSize": 100,
  "queryType": "keyword",
  "timeGranularity": "daily",
  "language": "en"
}
```

| Parameter | Type | Required | Default | Description |
|-----------|------|----------|---------|-------------|
| profileIds | array[long] | **Yes** | — | Profile IDs from `get_user_authorized_context.profileIds`. **All must be authorized** — one unauthorized value fails the whole call |
| factEntity | string | **Yes** | — | Fact entity type. Enum: `campaign` / `adGroup` / `target` / `searchTerm` / `placement` / `productAd` / `asin` / `keywordPlacement` |
| dateStart | string | **Yes** | — | Start date `YYYY-MM-DD`. Max span vs `dateEnd` is 90 inclusive calendar days (see platform-notes.md for the precise off-by-one-safe definition) — **7 days for hourly/AMS queries**; cannot be more than 15 months before today |
| dateEnd | string | **Yes** | — | End date `YYYY-MM-DD`. Default to yesterday if user gives no end date (T+2 data delay) |
| metrics | array[string] | **Yes** | — | Metric fields (non-empty). Must be valid for the chosen `factEntity` — see Metrics × Entity Support Matrix below |
| userContext | string | **Yes** | — | User's original query + reason, max 100 chars |
| select | array[string] | No | [] | Dimension fields, format `entity.field_`; omit for global aggregate (no grouping) |
| groupBy | array[string] | No | same as select | GROUP BY fields. Only needed explicitly when `select` contains a custom aggregate expression — otherwise auto-filled from raw dimension fields in `select` |
| filters | object | No | {} | Filter conditions — supports simple value / operator / AND-OR nesting |
| orderBy | array[object] | No | [] | `[{"field": "Spend", "direction": "DESC"}]` (direction ASC or DESC, default DESC) |
| page | int | No | 1 | Page number (1-based). **`page` ≤ 0 is an error**, not a fallback to 1 |
| pageSize | int | No | 100 | Rows per page, **max 500**. **Out of range (≤0 or >500) is an error**, not clamped — floor any computed value at 1 |
| queryType | string | No | — | Required semantically when `factEntity` is `target` or `searchTerm` — but the allowed values differ by entity, see below |
| timeGranularity | string | No | daily | `daily` / `hourly`. **`hourly` is only valid with `factEntity: campaign`**; any other value or entity combination is an error. See Hourly / AMS Data below |
| language | string | No | **en** | Enum translation language: `zh` / `en` / `ja`. An unrecognized value silently falls back to `en`, so set it explicitly to match the user's language |

## Field Naming Rules

- **Dimension fields** (in select/filters): must have entity prefix and `_` suffix
  - Example: `campaign.campaignName_`, `aiGroup.aiStatus_`, `asin.asin_`
- **Metric fields** (in metrics/filters): no `_` suffix, case-sensitive, capitalized as documented
  - Example: `Spend` ✓ — not `spend` or `SPEND`
- **date field**: no `_` suffix, no entity prefix
  - Example: `date` — note this field is returned in **`YYYYMMDD`** format (e.g. `"20240601"`), different from the `dateStart`/`dateEnd` request params' `YYYY-MM-DD` format. See Date Format above.
- **SearchTerm fields** (no entity prefix, but keep `_` suffix): unlike every other entity, searchTerm dimension fields drop the `entity.` prefix
  - Example: `query_`, `matchType_`, `targetId_`, `adGroupId_`, `campaignId_`
- **KeywordPlacement fields** — **write bare names in the request, read `_`-suffixed names in the response.** This entity is the second exception to the convention, and it's asymmetric:
  - In `select` / `filters` / `orderBy`, write the bare name: `keywordId`, `keywordText`, `matchType`, `placement`, `campaignId`, `adGroupId`. These six are explicitly mapped and are the form to use.
  - In the returned rows, the keys come back suffixed: `keywordId_`, `keywordText_`, `matchType_`, `campaignId_`, `adGroupId_`, plus `placement`, `hour` (0-23), `date`, `profileId_`. So you request `keywordText` and read `keywordText_`.
  - **Don't echo the response form back into a request.** A `keywordPlacement.<field>` reference is translated literally, so it only works if it happens to match the underlying column exactly — `keywordPlacement.keywordText_` resolves, but `keywordPlacement.placement_` fails (that column is `placement`, with no underscore). The bare form has no such trap. Also don't carry these bare names into any other `factEntity`.
  - **`campaignType` filters are a no-op here** — the data source is SP-only, so a `campaignType` filter is dropped rather than pushed down: filtering for `sponsoredProducts` changes nothing, and filtering for `sponsoredBrands`/`sponsoredDisplay` gives you an empty result that means "wrong entity", not "no data". Don't use this entity to compare ad types.
- **Custom aggregate expressions**: prefix raw columns with `p.`
  - Example: `sum(p.impression) as total_impressions`
- Dimension fields are evaluated in `WHERE`; metric fields are evaluated in `HAVING`

**⚠️ This convention is specific to `get_ads_perf`.** `get_entity_metadata` uses plain camelCase with no prefix/suffix (`campaignState`, not `campaign.campaignState_`) — do not mix the two conventions.

## Time Aggregation (in `select`)

Do not just write `week`/`month` as bare field names — use the exact ClickHouse expression and alias it:

| Granularity | Expression |
|---|---|
| Daily | `date` |
| Weekly (Monday start) | `toMonday(parseDateTime32BestEffort(date)) as week` |
| Monthly | `toStartOfMonth(parseDateTime32BestEffort(date)) as month` |
| Quarterly | `toQuarter(parseDateTime32BestEffort(date)) as quarter` |
| Yearly | `toYear(parseDateTime32BestEffort(date)) as year` |

**This does not raise the 90-day cap.** If the user's window exceeds 90 days, first split into multiple calls each with `dateStart`/`dateEnd` spanning ≤90 days — aggregation only reduces the row count *within* each of those calls.

## Hourly / AMS Data

Hour-level data comes from Amazon Marketing Stream (AMS) and is reachable two ways. Both are capped at a **7-day span** — not 90.

| What you want | How | Ad types | Constraints |
|---|---|---|---|
| Campaign metrics by hour | `factEntity: "campaign"` + `timeGranularity: "hourly"` | SP + SB + SD | span ≤ 7 days |
| Keyword × placement metrics | `factEntity: "keywordPlacement"` | **SP only** | hourly by nature — **do not pass `timeGranularity`**; span ≤ 7 days; restricted metric set |

**Rules that produce hard errors (not warnings, not silent fallbacks):**

- `timeGranularity` accepts only `daily` and `hourly` (case-insensitive; omitted/empty means `daily`). Anything else → `Parameter 'timeGranularity' must be 'daily' or 'hourly', got: X`.
- `hourly` with any `factEntity` other than `campaign` → `Parameter 'timeGranularity=hourly' is only supported for factEntity=campaign. Current factEntity: X`. It is **not** ignored and does **not** fall back to daily. There is no hourly adGroup, target, searchTerm, placement, productAd, or asin data — if the user asks for "hourly keyword performance", the answer is `keywordPlacement` (keyword × placement), not `target` + hourly.
- `daily` on `keywordPlacement` is not supported either — the entity has no daily rollup. Just omit `timeGranularity`.
- Span over 7 days on either path → a date-range error (`keywordPlacement is hourly granularity, date range cannot exceed 7 days (got N days)`). For "hourly pattern over the last month", either aggregate by hour-of-day across several ≤7-day calls (and say you sampled), or step back to daily granularity and tell the user hourly is limited to a 7-day window.

**Reading hourly results:**

- **Select `hour` explicitly.** `timeGranularity: "hourly"` changes the source table but does not inject an hour grouping into the response. For an intraday curve, include both `date` and `hour` in `select`; omitting `hour` aggregates the hourly source back to the remaining selected dimensions.
- With `hour` selected, rows carry `hour` (0-23) alongside `date`; group by it to build an intraday curve, and remember each row is one hour of one day — averaging hours across days requires you to divide by the number of days, not the number of rows.
- The T+2 delay still applies. The most recent hours of "today" and often all of yesterday are incomplete. Don't present a partial final day as a real dip.
- `keywordPlacement` uses an asymmetric field naming convention (bare names in the request, `_`-suffixed keys in the response) — see Field Naming Rules.

**`keywordPlacement` supports a restricted metric set.** The underlying stream table only carries base SP hourly data, so:

| Metrics | Behaviour |
|---|---|
| `Impressions`, `Clicks`, `Spend`, `Sales`, `Conversions`, `Units`; the Same/Other-SKU variants (`SalesSameSKU`, `ConversionsSameSKU`, `UnitsSameSKU`, `SalesOtherSKU`, `ConversionsOtherSKU`, `UnitsOtherSKU`); derived `CTR`, `CVR`, `CPC`, `CPA`, `ACOS`, `ROAS` | **Available** |
| `AISpend`, `AISales` | Accepted but return **`0`** |
| `AIACOS`, `AIROAS` | Accepted but return **`null`** |
| NTB family, DPV / video / viewability / vCPM / impression-share, ASIN business metrics | **The query fails** — the underlying table has no such column |

Two different failure modes, so handle them differently: the AI values are **placeholders, not real zeros** — never report AI performance from this entity and never use them as a denominator (use `factEntity: campaign` for AI attribution). The last row isn't a "returns empty" case at all: requesting one of those metrics breaks the whole call, so don't include them — ask for them at campaign / adGroup / target level instead.

## Supported Dimension Fields, Metrics × Entity Matrix, and Metrics Reference

**See [`references/field-reference.md`](references/field-reference.md)** for the complete field dictionary: all dimension fields by entity (Campaign, AdGroup, Target, SearchTerm, Placement, ProductAd, ASIN, ParentAsin, ProductLine, Portfolio, AI Group, AutomationRule, Profile), the Metrics × Entity Support Matrix, and the full Metrics Reference (including derived ratio formulas and user-to-field mapping).

## Core Metrics Combo

When user says "core metrics":
```
["Impressions", "Clicks", "Spend", "Sales", "Conversions", "ACOS", "ROAS", "CTR", "CVR", "CPC", "CPA"]
```

For ASIN business analysis, additionally (asin entity only):
```
["TotalSalesAmount", "OrderCount", "TACOS"]
```

## Filter Syntax

```json
{
  "campaign.campaignState_": "enabled",
  "campaign.campaignId_": [123, 456],
  "campaign.campaignName_": {"like": "%test%"},
  "Spend": {">": 100},
  "AND": [
    {"campaign.campaignState_": "enabled"},
    {"Spend": {">": 100}}
  ],
  "OR": [
    {"Sales": {">": 1000}},
    {"ACOS": {"<": 20}}
  ]
}
```

Note the `ACOS` threshold above is `20`, not `0.2` — `ACOS`/`CTR`/`CVR` are confirmed returned pre-scaled ×100 (e.g. `17.61` means 17.61%), so `{"ACOS": {"<": 20}}` means "ACOS under 20%". `TACOS`'s scale is not independently confirmed — see the Tier 2 note in Ratio Metric Display Rule above. Using `0.2` would be a common construction mistake for the confirmed Tier 1 fields.

Supported operators: `>`, `<`, `>=`, `<=`, `=`, `in`, `like`

Nested example:
```json
{
  "AND": [
    {"campaign.campaignState_": "enabled"},
    {"OR": [
      {"Spend": {">": 100}},
      {"Sales": {">": 1000}}
    ]}
  ]
}
```

Dimension-field filters run in `WHERE`; metric-field filters run in `HAVING`.

**`like` wildcard note**: any `%` you write is stripped and replaced with an automatic leading+trailing `%` — the match is always "contains" regardless of where you place `%` in the pattern.

## Response Structure

```json
{
  "isError": false,
  "toolName": "get_ads_perf",
  "rows": [
    {
      "campaign.campaignName_": "Brand-SP-Auto",
      "date": "20240601",
      "Impressions": 12500,
      "Clicks": 320,
      "Spend": 156.80,
      "Sales": 890.50,
      "ACOS": 17.61,
      "ROAS": 5.68
    }
  ],
  "rowCount": 1,
  "page": 1,
  "pageSize": 100,
  "hasNextPage": false,
  "effectiveProfileIds": [4404871489220462],
  "requestId": "a1b2c3d4e5f6",
  "currency": "USD"
}
```

| Field | Type | Description |
|---|---|---|
| `isError` | boolean | Whether the call errored — check this before reading `rows` |
| `toolName` | string | Tool name |
| `requestId` | string | Trace ID for this call. **Quote it when reporting a failure to the user** so the platform team can trace it. May be absent in local/dev environments |
| `rows` | array[object] | Result rows — each row has the select dimension fields + metric fields |
| `rowCount` | int | Row count on current page |
| `page` | int | Current page number |
| `pageSize` | int | Rows per page |
| `hasNextPage` | boolean | Whether more pages exist — loop `page` while true |
| `effectiveProfileIds` | array[long] | Profile IDs the query actually ran against — an echo of your request (unauthorized IDs fail the call outright rather than being dropped here). Verify it's the store the user meant |
| `currency` | string | Currency of monetary metrics: the profile's local code for a single profile, `USD` for multiple |

On error, the response instead follows the shared error envelope described in Platform-Wide Rules above — note there are **two shapes** (`errorType` for tool errors, `error` for pipeline errors such as `rate_limited` / auth failures).

## Notes

- Each page returns up to `pageSize` rows (max 500); use `page` for pagination and check `hasNextPage`. An out-of-range `pageSize`/`page` is an **error**, not clamped — floor computed values at 1
- `profileIds` is **required**. Always call `get_user_authorized_context` first. If the user doesn't name a store, pass all authorized `profileIds`
- **Every requested `profileId` must be authorized** — one bad value fails the whole call (`Requested profileIds contain unauthorized values`); nothing is silently dropped
- Hourly/AMS data (`timeGranularity: hourly` on campaign, or `factEntity: keywordPlacement`) is capped at a **7-day** span, and `keywordPlacement` returns `AISpend`/`AISales` as `0` and `AIACOS`/`AIROAS` as `null` — never read AI performance from it
- User says "Orders" -> convert to "Conversions"
- User says "ASIN" -> default to child ASIN, use `asin.asin_`
- Max date span 90 days on the `dateStart`/`dateEnd` request itself, max lookback 15 months — split longer windows into multiple ≤90-day calls first; week/month aggregation only reduces row count within each call, it does not raise the 90-day cap
- `dateStart`/`dateEnd` request params use `YYYY-MM-DD`; the `date` field returned in rows uses `YYYYMMDD` — don't mix them
- Default `dateEnd` to yesterday when unspecified (T+2 data delay)
- Always mind data volume to avoid query timeout
- `groupBy` is auto-filled from raw dimension fields in `select`; only pass it explicitly when `select` contains a custom aggregate expression
- Multi-profile queries auto-normalize monetary metrics to USD, **except** `asin.asinPrice_` which stays local currency
- When `profileIds` has more than one entry, add `profile.profileId_`/`profile.profileName_` to `select` so rows can be attributed to a store
- Only request metrics valid for the chosen `factEntity` — check the support matrix above
- `ACOS`/`CTR`/`CVR` (confirmed Tier 1) are pre-scaled ×100 — don't re-scale, but append `%` when presenting to the user; filters use the raw ×100 number with no `%`. `TACOS`/`*Rate` fields (Tier 2) are unconfirmed — relay as-is with no `%`
- On error, check `errorType` and handle per the guidance above rather than assuming the question is unanswerable

## Reference Docs

- Shared cross-tool behavior (auth, errors, pagination, dates, currency, ratio-metric display rule, decision tree, inference rules): [`references/platform-notes.md`](references/platform-notes.md)
- Field dictionary (dimension fields by entity, metrics support matrix, metrics reference): [`references/field-reference.md`](references/field-reference.md)
- **Ad-type-dependent metrics** (which metrics require `campaignType` filter or specific `factEntity`): [`references/ad-type-dependent-metrics.md`](references/ad-type-dependent-metrics.md) — **must consult before querying**: NTB/Video/DPV/vCPM-attribution metrics (SB+SD only, not SP); AI metrics (campaign entity only); TopOfSearchIS (campaign+target only); ASIN business metrics (asin entity only, no cross-join with ad entities). When a query mixes universal and type-specific metrics, split into separate calls.
- Enum i18n (ZH/EN/JA display labels for all enum values — use this when presenting enum fields to the user or translating between API values and localized display text): [`references/enum-i18n.md`](references/enum-i18n.md)
- Query examples:
  - [Top-N campaign performance](references/example-campaign-performance.md)
  - [Time aggregation (weekly/monthly/quarterly) and splitting windows >90 days](references/example-aggregation-over-time.md)
  - [Hourly / AMS queries (intraday campaign curve, keyword × placement)](references/example-hourly-ams.md)
  - [Keyword target query](references/example-keyword-target-query.md)
  - [Search term query](references/example-search-term.md)
  - [ASIN business metrics query](references/example-asin-query.md)
  - [AI managed group performance](references/example-ai-group-performance.md)
  - [AND/OR nested filters](references/example-nested-filters.md)
  - [Global (store-level) aggregation](references/example-global-aggregation.md)
  - [Multi-profile comparison](references/example-multi-profile-comparison.md)
  - [Period-over-period comparison and Top Movers (WoW/MoM, join by ID, handle new/disappeared entities, zero-denominator deltas)](references/example-period-comparison-top-movers.md)
  - [ACOS root-cause investigation (chaining get_ads_perf → get_operation_log → get_entity_metadata)](references/example-acos-root-cause-investigation.md)
  - [Cross-entity ratio aggregation (never average ACOS/CTR/CVR — sum base metrics then recompute)](references/example-cross-entity-ratio-aggregation.md)