# AdCenter question playbook (ChatGPT / Cursor) — business-mcp only

How to answer common performance questions with **business-mcp** tools. **No AdCenter PHP / route changes** — only formats already on `GET /v1/reports/*`, plus cake RO views for campaign financials.

## Routing rule

| Need | Source | Tools |
| --- | --- | --- |
| Advertiser / campaign **identity** (name → id) | MNGT + AdCenter entity GETs | `search_advertisers`, `get_advertiser`, `adcenter_get_campaign`, `adcenter_list_campaigns` |
| Advertiser **dashboard stats** (impressions, clicks, **`spent`**, conversions, CTR) for a named advertiser + last N days | AdCenter GET reports | `search_advertisers` `{ q }` then `adcenter_get_report` `{ report_type: "daily", advertiser_id, date_range }` with explicit last-N window from clock (today minus N). Label field **`spent`** (AdCenter), not Cake **`spend`**. `get_advertiser_report` is audit.ad financials — do **not** use it for AdCenter dashboard KPIs |
| Publisher **Cake affiliate_id** (name → `AffiliateIDs.id`) | Cake | `search_affiliates` `{ q }` or `{ publisher_id }`. Not `search_publishers` (MNGT name only). Not `get_affiliate_report` (payout) |
| Media stats: impressions, clicks, **`spent`**, conversions, **CTR** | AdCenter GET reports | `adcenter_get_campaign_report`, `adcenter_get_report` |
| Financials: cake **`spend`**, **`sales_revenue`**, ROAS, COS%, advertiser-reported conversions | Cake `lucos_ro_*` at **campaign** grain | `get_campaign_performance_report` (optional `analysis` modes — §5.8) |
| Publisher × advertiser KPI, all-advertisers totals, **or** cross-advertiser affiliate / AM / BD leaderboard | Cake | `get_advertiser_report` (`group_by`: `publisher` / `advertiser` / `affiliate` / `total` / `account_manager` / `account_sales_manager`; AM/BD rank `sort_by=valid_clicks`, `show_oos` default 0; omit `advertiser_id` for cross-advertiser; always read `summary`) |
| Top advertisers by revenue **and** which affiliates drove each one | Cake | `list_advertisers_with_affiliates` — nested `affiliates[]` per advertiser. Do **not** join `group_by=advertiser` to `group_by=affiliate` |
| Affiliate **invalid / suspicious traffic**, drop% / fraud% vs valid, IVT% / IVT clicks | Cake IVT | `get_ivt_report` — rank `affiliates[]` by `ivt_pct` / `fraud_clicks` / `drop_pct_vs_valid` / `fraud_pct_vs_valid`. Not adhoc |
| Advertisers dependent on one affiliate (click % + revenue %) | Cake | `list_advertisers_affiliate_concentration` (prefer over client-aggregating publisher rows) |
| Many active camps, low top-campaign click share | Cake | `list_advertisers_low_traffic_concentration` |
| High campaign-revenue concentration / 20% top-campaign risk | Cake | `list_advertisers_high_revenue_concentration` (never invert `list_advertisers_low_traffic_concentration`) |
| Campaign × affiliate clicks / payout | Cake | `get_campaign_affiliate_report` (no `sales_revenue` at this grain; `publisher_id` e.g. rateit_shop for one affiliate’s campaign mix — **not** RDR3 links/IPs) |
| **Active publisher offers** for one publisher (e.g. fatcoupon) | Cake | `list_publisher_offers` `{ publisher_id }` — AffiliateIDs → approved mappings → live offers. Not CPC. Not `get_campaign_affiliate_report`. Not `get_publisher_caps`. Not `list_publishers_by_active_campaigns`. Not adhoc |
| Affiliate **datafeed traffic** rank | Cake + MNGT tags | `get_datafeed_traffic_report` — whale advertiser_stats clicks on datafeed-tagged campaigns |
| Affiliate **Monitor** clicks / entries | Cake Shorty | `get_click_monitor_report` — `validate_click_monitor_*` entries / unique_clicks. Not datafeed, not whale clicks |
| RDR3 traffic links + IPs | — | Honest refuse — no `lucos_ro_rdr3` view; IPs/URLs are PII. Offer §7.1 safe rephrase. Do **not** `adhoc_explore` |
| Publishers still sending CPC to inactive campaigns | Cake | `list_publishers_traffic_to_inactive_campaigns` |
| Affiliates on 2+ advertisers | Cake | `list_affiliates_on_multiple_advertisers` — COUNT DISTINCT advertisers on whale `lucos_ro_advertiser_stats_daily` (`clicks_source` whale advertiser_stats), not the Admin CPC view (that view times out unscoped). `min_advertisers=2`, row-capped 50 |
| Publishers ranked by active campaign count | Cake | `list_publishers_by_active_campaigns` — whale advertiser_stats clicks merged with directory status=A in process (OD-1, no SQL join). Not one publisher’s offers |
| Campaigns with no traffic in last 7 days | Cake | `list_campaigns_without_traffic` — Admin directory anti-joined to the whale click set; date filter on whale only so zeros survive. Not a CPC LEFT JOIN. Optional `advertiser_id`; `include_inactive` default 0 = active only. Do not use `get_campaign_performance_report` for zeros. Row-capped 50 |
| Period-over-period campaign predicates (ROAS vs prior window, spend up / conversions down) across advertisers | Cake | `compare_campaign_performance` (`compare_date_range` required; omit `advertiser_id`) |
| Advertiser **average CPA last 30 vs previous 30** | Cake | two `get_advertiser_report` `{ group_by: "advertiser" }` — **rolling last 30 vs previous 30**. Read `rows[].cpa`. Cite `actual_cpa` when it differs. Omit when `cpa` is null. Never `kpi`. Not `compare_campaign_performance`. Not MTD vs last month |
| Advertiser CPA **this month vs last month** | Cake | two `get_advertiser_report` `{ group_by: "advertiser" }` — **MTD vs last calendar month**. Same: read `rows[].cpa`. Not last_month vs prior_month |
| Affiliate **click volume drop/surge** | Cake | `compare_affiliate_performance` `{ compare_mode: "clicks_drop" }` — last 7 vs prior 7 by default. Not adhoc |
| Domain whitelist / blacklist hostname (e.g. insuranceandleisure.com) | MNGT | `search_campaigns_by_domain_targeting` (`adv_id` required; `list_mode` include=whitelist). Empty rows mean no `lucos_ro_domain_list_members` match — not a missing tool |
| Currently at budget cap | MNGT | `list_campaigns_at_cap` (`adv_id` required). Snapshot `budget_spent >= budget`, not when the cap was hit |
| Frequently at daily cap / needs budget | MNGT | `list_campaigns_capped_frequently` (`adv_id` optional; omit = top 50 by `days_at_cap`). Daily spent ≥ daily budget — not the lifetime snapshot |
| Advertisers with clicks and no conversions | Cake | `get_advertiser_report` `{ group_by: "advertiser", max_advertiser_conversions: 0 }`. Cake `clicks`, not IVT `valid_clicks`. For valid clicks use `get_ivt_report` default 7-day window (31-day unscoped may timeout) |
| No approved tool matches the requested grain | Catalog (staging) | `adhoc_explore` last resort — exploratory, not reportable |
| Optimization / allocate / pace / health (Q1–Q10) | MCP recommend (staging) | `recommend_actions` — intent enum, never SQL; `causal: false` |

### Approved tools vs `adhoc_explore`

Prefer the approved tool whose **grain matches the question** (table above). The MCP **client** still picks the first tool, but **empty rows, errors, disabled tools, or a grain the tool cannot express always fall back to `adhoc_explore`**. Staging only. Label that answer exploratory / not reportable.

Exception: if the **dataset itself is not in the catalog** (RDR3 traffic links/IPs), **do not** fall back to `adhoc_explore`. Refuse honestly and offer the §7.1 safe rephrase. Datafeed traffic **does** have an approved tool (`get_datafeed_traffic_report`). Monitor questions **must** use `get_click_monitor_report`; a Monitor error is not a reason to call datafeed. Affiliate click-volume drop **must** use `compare_affiliate_performance`; do not start with `adhoc_explore`. Invalid or suspicious traffic **must** use `get_ivt_report`; do not start with `adhoc_explore`. Active publisher offers for a named publisher **must** use `list_publisher_offers` `{ publisher_id }`; do not start with `adhoc_explore` and do not refuse. Advertiser average CPA last 30 vs previous 30 **must** use two `get_advertiser_report` `{ group_by: "advertiser" }` calls (rolling last 30 vs previous 30; read `rows[].cpa`); do not start with `compare_campaign_performance` or `adhoc_explore`. Advertiser dashboard stats **must** use `adcenter_get_report` `{ report_type: "daily" }` after name resolve; do not start with `get_advertiser_report` or `adhoc_explore`.

1. Use the approved tool whose grain matches.
2. If that tool returned a **breakdown** when the user asked for a **total** or **affiliate leaderboard**, retry the **same** tool with the matching grain (`group_by: "total"` or `group_by: "affiliate"` + `sort_by: "advertiser_conversions"`). Do not SUM truncated `rows`. Do not N× loop advertisers.
3. If the approved tool **errors, returns empty rows, is disabled, or cannot express the grain** (including campaign rank with no `advertiser_id`), call `adhoc_explore` with the original question — unless §7 says refuse. `analyst_agent` auto-invokes this fallback. Direct MCP tool errors are stamped `FALLBACK REQUIRED`.

Do **not** use cake or MNGT account `state` for CTR, landing pages, keyword trends, or geo performance.

Do **not** use:

- `get_advertiser_report` to rank **campaigns** (drops `camp_id`; use `group_by=total` / `summary.spend` for portfolio spend, not campaign rank — if you need a campaign winner across advertisers, fall back to `adhoc_explore`)
- `adhoc_explore` **first** when `get_advertiser_report` can already answer (portfolio spend / affiliate leaderboard). After that tool misses, do fall back.
- `get_affiliate_report` as a **leaderboard** (one publisher; requires `affiliate_id` / `publisher_id`)
- `get_campaign_performance_report` for a cross-advertiser affiliate rank (`advertiser_id` is required; campaign grain)
- `get_postback_report` as a conversion **total** (event rows, 7-day max, 2000 cap)
- MNGT `get_campaign_budget` / `list_campaigns` **`budget_spent`** as last-month spend (ledger, not a date window)
- Cake **`kpi`** as raw ROAS (`kpi` is % of advertiser goal)
- Cake **all** `clicks` or IVT **`valid_clicks`** as **datafeed traffic** (use `get_datafeed_traffic_report`)
- `get_datafeed_traffic_report` / whale clicks as **Monitor** entries (use `get_click_monitor_report`; if it errors, refuse — do not substitute)
- `get_affiliate_report` / `adhoc_explore` for **RDR3 traffic links or IPs** (no click-level URL/IP view; those columns are PII)
- `get_publisher_caps` or `list_publishers_by_active_campaigns` or `get_campaign_affiliate_report` for **assigned live offers of one publisher** (use `list_publisher_offers` `{ publisher_id }`)

**Formulas:** `ROAS = sales_revenue / spend`, `COS% = spend / sales_revenue × 100`. AdCenter field is `spent`; cake field is `spend` — do not mix them silently.

**Report formats** (`adcenter_get_report` / `adcenter_get_campaign_report` `report_type`):

| Format | Use for |
| --- | --- |
| *(omit)* / `daily` | Campaign totals or daily trend; **CTR** (MCP derives if API omits `ctr`) |
| `keywords` | Bid keyword + **landing URL** (`destination_url`) when present. If REST omits URLs, still trend by `keyword` + date and **label as bid keywords** — do not refuse |
| `countries` | Country-level geo (closest REST proxy for “state-wise”; **not** US state) |
| `domains` / `os` / … | Publisher domain / device — not landing or search-term |

**Not available without AdCenter REST changes:** `search_query` (true user queries), `dma_by_state` (US state). Do not call those `report_type` values — MCP will reject them.

AdCenter responses may include `impressions`, `clicks`, `spent`, `conversions`, and `ctr`. If AdCenter omits `ctr`, MCP fills `clicks/impressions*100` and sets `ctr_source: "derived"`; when AdCenter returns `ctr`, `ctr_source: "adcenter"`.

Staging synthetic advertiser: **19880** (`lucos_synth_adv`), campaign **90553**. BestBuy / Amazon / real Campaign ABC need a live advertiser + gateway ensure-key.

---

## 1. What is the CTR of Campaign ABC?

1. If the user gave a **numeric campaign_id** (e.g. 100158): pass it; the tool looks up `adv_id` via `get_campaign_budget`. Do **not** assume advertiser **19880**.
2. If advertiser is a name: pass `advertiser` on the AdCenter tool (e.g. sireesha) — it resolves `adv_id`. Never ask the user for advertiser_id.
3. `adcenter_get_campaign` with `{ campaign_id }` or `{ name: "Campaign ABC", advertiser_id }` (not both). If `ambiguous: true`, pick from `matches` and retry with `campaign_id`.
4. `adcenter_get_campaign_report` with `{ campaign_id, advertiser_id, date_range? }` (optional `report_type: "daily"`).
5. Answer with `ctr` (and impressions / clicks for context).

Do not invent CTR from cake clicks alone (no impressions there).

---

## 2. Landing-page trends for BestBuy for the last 7 days

1. `search_advertisers` `{ q: "BestBuy" }` → pick `adv_id` (disambiguate if multiple rows).
2. `adcenter_get_report` with:
   - `advertiser_id`
   - `report_type: "keywords"`
   - `date_range: { from, to }` = last 7 calendar days (YYYY-MM-DD)
3. If `destination_url` is populated, aggregate rows by `destination_url` and `date`; summarize clicks / impressions / ctr.
4. If every `destination_url` is null/empty (envelope `note`; REST often omits landing URL): **still answer**. Rank the same rows by `keyword` + `date` (clicks / impressions / ctr). Label clearly: **bid keywords, not landing pages**. Do **not** refuse. Do **not** invent URLs from `keyword` text. Do **not** use `domains` (publisher domain) or cake.

`keywords` is the landing URL path when AdCenter populates `destination_url` (MCP copies fallback keys `url` / `landing_url` / … and stamps `destination_url_source`).

---

## 3. Search-term trends for BestBuy for the last 7 days

**MCP-only limitation:** true `search_query` is UI/CSV-only on AdCenter, not on Lucos GET allowlist. Keep honesty: this is a **labeled proxy**.

**Closest available answer:**
1. Same name lookup as §2 (`search_advertisers` first; never guess numeric ids).
2. `adcenter_get_report` with `report_type: "keywords"` and last-7-day `date_range`.
3. Trend by `keyword` + `date`. Label clearly: these are **bid keywords**, not raw user search queries.

`analyst_agent` must call `adcenter_get_report` `{ report_type: "keywords" }` after `search_advertisers`. Never Cake CPC / affiliate report. Never `search_query`.

Do **not** substitute cake / Shorty click-stream terms.

---

## 4. State-wise performance against campaigns for BestBuy and Amazon

**MCP-only limitation:** `dma_by_state` is not on GET `/v1/reports`. Closest REST format is **`countries`** (labeled proxy). Say **country, not US state**.

1. `search_advertisers` `{ q: "BestBuy" }` and `search_advertisers` `{ q: "Amazon" }` (two calls). Never guess numeric ids.
2. For each `adv_id`: `adcenter_get_report` `{ advertiser_id, report_type: "countries", date_range }` and/or `adcenter_get_campaign_report` `{ campaign_id, advertiser_id, report_type: "countries", date_range }` (list campaigns first with `adcenter_list_campaigns` when the question is vs campaigns).
3. Present a **country** × campaign table per advertiser. Say explicitly this is country-level, not US state.

Never `dma_by_state`. Never MNGT `state` (HQ address). Gateway redaction caps large report payloads (~5000 rows).

---

## 5. Campaign spend / conversions / revenue / ROAS / COS (nine ops questions)

Prefer `adcenter_list_campaigns` (paginate `limit` ≤ 100) over MNGT `list_campaigns` (cap **50**). Always pass `advertiser_id` in prod. Never pass both `campaign_id` and `name` to `adcenter_get_campaign`.

### 5.0 What was the total spend across all advertisers? (P1 — cake)

`get_advertiser_report` `{ date_range, group_by: "total" }`. Answer from **`summary.spend`** (cake `spend`, not AdCenter `spent`).

If you already called this tool without `group_by` and got publisher × advertiser rows, **retry with `group_by: "total"`**. Do not SUM those rows. If `get_advertiser_report` is disabled, rejected, or returns empty rows, call `adhoc_explore`.

Default grain is publisher × advertiser (the cake UI table). That breakdown does **not** include a pre-calculated portfolio total on each row — `summary` is that total, computed in SQL without the 5000-row cap. If `truncated` is true, do **not** SUM `rows`.

| Question | `group_by` | Read |
| --- | --- | --- |
| Total spend / clicks / revenue across all advertisers | `total` | `summary.spend` (and other `summary` metrics) |
| Spend per advertiser | `advertiser` | row `spend`; portfolio still in `summary` |
| Publisher mix / KPI vs goal | *(omit)* / `publisher` | row `spend` + `kpi`; identity is `affiliate_id` / `affiliate_name` (not `publisher_id`); portfolio still in `summary` |
| Best affiliate by conversions (all advertisers) | `affiliate` + `sort_by=advertiser_conversions` | row 1 (`affiliate_id` / `affiliate_name`); omit `advertiser_id` |

`group_by=publisher` identity is `affiliate_id` / `affiliate_name`. `publisher_id` is an **input-only** MNGT resolver (Affiliate.name) — it is not an output field.

### 5.0b Which affiliate performed the best last month based on conversions? (P1 — cake)

`get_advertiser_report` `{ date_range: last calendar month, group_by: "affiliate", sort_by: "advertiser_conversions" }`. **Omit `advertiser_id`.** Answer from row 1: `affiliate_name`, `advertiser_conversions`, `clicks`, `sales_revenue`. Cite cake `advertiser_conversions`.

Today: `{ date_range: { from: today, to: today }, group_by: "affiliate", sort_by: "advertiser_conversions" }` — do not omit `date_range` (omit means last 31 days).

Do **not** ask the user for an advertiser ID. Do **not** loop `get_campaign_performance_report` per advertiser. Do **not** use `get_affiliate_report` (that tool is one publisher). If this tool is disabled, rejected, or returns empty rows, call `adhoc_explore`.

If you already pulled publisher × advertiser rows, retry with `group_by: "affiliate"` rather than summing in the client.

### 5.0c Advertisers by revenue and which affiliates performed (P1 — cake)

`list_advertisers_with_affiliates` `{ date_range }` → `rows[]` ranked by advertiser `sales_revenue`, each with `affiliates[]` ranked by that advertiser's `sales_revenue`. Default top 5 advertisers × top 5 affiliates. Read `summary` for the unbounded window total.

This is the compound question ("advertiser-level report which creates more revenue … and which affiliates performed for this"). Do **not** answer it with `get_advertiser_report` `{ group_by: "affiliate" }` (no `adv_id` — cannot attribute). Do **not** use `list_advertisers_affiliate_concentration` (single top-affiliate dependency %, not the performing mix).

### 5.0d Top affiliates in Monitor (last 7 / 30 days) — `get_click_monitor_report`

`get_click_monitor_report` `{ group_by: "affiliate", sort_by: "unique_clicks"|"entries", limit: 5, date_range }`. Rank `rows[]`. Cite **Monitor** `entries` (COUNT(*)) and `unique_clicks` (COUNT(DISTINCT clickid)) from `validate_click_monitor_*`. Window totals only — not a daily series.

If this tool errors, **refuse honestly**. Do **not** fall back to `get_datafeed_traffic_report` (datafeed-tagged whale clicks) or `get_advertiser_report` (Cake clicks). Those are different datasets.

### 5.0e Which affiliate has more click volume drop — `compare_affiliate_performance`

`compare_affiliate_performance` `{ compare_mode: "clicks_drop" }`. Omit `advertiser_id`. Omit dates to use **last 7 days vs the previous 7**. Rank `rows[0]` — largest decline is most negative `clicks_delta_pct`. Clicks are whale `advertiser_stats` (same `clicks_source` as `get_advertiser_report`). Do **not** use the Admin CPC daily view — that view times out unscoped.

Do **not** call `adhoc_explore` first (it cannot compare two periods). Do **not** use `get_advertiser_report` (no period compare).

### 5.0f Which affiliate has more invalid or suspicious traffic — `get_ivt_report`

`get_ivt_report` `{ }` (omit `affiliate_id`; default last 7 days). Rank `affiliates[]` by `ivt_pct` (rate) or `fraud_clicks` (volume). Cite ClearTrust `fraud_clicks` / `dropped_clicks` / `ivt_pct`, not billed Cake clicks.

Do **not** call `adhoc_explore` first (that catalog has no IVT metrics). Do **not** use `get_advertiser_report` or `get_datafeed_traffic_report`.

### 5.1 How many conversions did Campaign Y generate yesterday? (P0 — AdCenter)

1. `search_advertisers` if needed → `advertiser_id`.
2. `adcenter_get_campaign` `{ name: "Campaign Y", advertiser_id }` → if `ambiguous`, retry with `campaign_id`.
3. `adcenter_get_campaign_report` `{ campaign_id, advertiser_id, date_range: { from: yesterday, to: yesterday } }`.
4. Read AdCenter `conversions`. Do not substitute cake `estimated_conversions` / `advertiser_conversions` without labeling.

### 5.2 Which campaign had the highest spend last month?

**P0 (small advertisers only):** paginate `adcenter_list_campaigns` → N× `adcenter_get_campaign_report` with last-month `date_range` → client `max(spent)`. Limits: 100/page, N HTTP calls, gateway ~5000-row cap.

**P1 (preferred):** `get_campaign_performance_report` `{ date_range: last month, sort_by: "spend", limit: 1 }` — pass `advertiser_id` when known; **omit it** to rank across all advertisers. Label cake `spend` (not AdCenter `spent`).

### 5.3 Top 10 campaigns by revenue this month (P1 — cake)

AdCenter GET has **no revenue**. Use:

`get_campaign_performance_report` `{ date_range: this month, sort_by: "sales_revenue", limit: 10 }` — omit `advertiser_id` when ranking across advertisers.

**Unscoped revenue / conversion rank is Admin-seeded:** SQL `ORDER BY sales_revenue` (or `advertiser_conversions`) `LIMIT limit`, then hydrate whale stats — not “top spend, then re-sort.” Response may stamp `scan_order: "admin"` and a short `note`.

Refuse an honest campaign-grain answer from AdCenter or from `get_advertiser_report` alone.

### 5.4 Which campaign has more ROAS? / high COS% / top ROAS with min clicks (P1)

`get_campaign_performance_report` with `sort_by: "roas"` or `"cos_pct"`, optional `min_clicks`, `min_cos_pct`, `limit`. MCP returns derived `roas` / `cos_pct` (`*_source: "derived"`). Do not use `kpi` as ROAS.

### 5.5 Spend up, conversions down vs previous month (P2)

`compare_campaign_performance` `{ date_range: mtd, compare_date_range: prior_mtd, compare_mode: "spend_up_conversions_down" }`. **Omit `advertiser_id`** for all campaigns. `prior_mtd` is equal-length: first of last calendar month through the same UTC day-of-month as today, capped to that month’s last day. Cite `estimated_conversions` and `cpa` (`spend/estimated_conversions`). Mention actual CPA (`spend/advertiser_conversions`) only when it differs. Campaign CPA from this tool is allowed.

Do **not** use `get_campaign_performance_report` `compare_date_range` for this unscoped question — that path still requires `advertiser_id` and LIMITs spend before the filter. Each window ≤ **92** days.

Do **not** use this tool for **advertiser** average CPA last 30 vs previous 30 — see §5.5b.

### 5.5b Advertiser average CPA last 30 vs previous 30 / this month vs last month

Treat “last 30 days vs previous 30 days” as a **rolling 30/30 slice** (today minus 29 through today, vs the 30 days before that). Do **not** remap it to calendar months. Treat “this month vs last month” as **MTD vs last calendar month**, not last_month vs prior_month (that skips the current month).

Two `get_advertiser_report` calls with `group_by: "advertiser"`:

1. Last 30 vs previous 30: `date_range` = last 30 inclusive days, then `date_range` = the previous 30
2. This month vs last month: `date_range` = MTD, then `date_range` = last calendar month

**Average CPA** is `rows[].cpa`. Cite `actual_cpa` only when it differs, never as the answer to “average CPA”. Never `kpi`. Omit when `cpa` is null. Do not blend portfolio CPA from `summary.estimated_conversions` (never `summary.spend / summary.estimated_conversions`) when a few advertisers contribute huge estimated counts and 0 advertiser conversions (e.g. VanguardProxyVote). Do not invent advertiser-grain average CPA from `compare_campaign_performance` (campaign grain, filtered predicates). Campaign CPA is allowed via `cpa` on `spend_up_conversions_down`.

### 5.6 Underperforming historical average ROAS by more than 15% (P2)

`compare_campaign_performance` `{ date_range: recent window, compare_date_range: prior window, compare_mode: "roas_underperform", roas_underperform_pct: 15 }`. **Omit `advertiser_id`.**

“Historical” = the client-supplied `compare_date_range`, not lifetime. Each window independently ≤ 92 days. Baseline ROAS = Σsales_revenue / Σspend over that window. Keep `roas < baseline_roas * 0.85`. Do not use `kpi` (goal) or AdCenter `estimated_ROAS`.

Read `matched_count` (pre-limit) and `truncated` (whale scan hit 5000). Response cap is 50.

### 5.7 Campaigns with no traffic in the last 7 days (P0 / P1)

**Prefer** Cake `list_campaigns_without_traffic` `{ date_range: last 7 days }` (optional `advertiser_id`; `include_inactive` default 0 = currently active campaigns only). Admin directory anti-joined to the whale `advertiser_stats` click set; the date filter is on whale only so zero-click campaigns still appear. Not a CPC LEFT JOIN. Row-capped at 50 — unscoped results are not a complete inventory.

P0 fallback for **impression-level** zeros: paginate `adcenter_list_campaigns` `{ advertiser_id }` then N× `adcenter_get_campaign_report` last 7 days and flag missing / `impressions === 0`. Do **not** use `get_campaign_performance_report` to emit zeros (stats views omit zeros; that tool is sparse/ranker).

### 5.8 Campaign analysis modes (ops Q1–Q5) — extend `get_campaign_performance_report`

Pass optional `analysis` on the **same** tool. Default rank/compare/baseline behavior is unchanged when `analysis` is omitted. Analysis defaults to the **last 28 days** when `date_range` is omitted (still max 92). Traffic proxy is **clicks** (Cake has no impressions). Responses include `meta.limitations`.

| Question | `analysis` value | What it returns |
| --- | --- | --- |
| Consistent traffic, inconsistent conversions (4 weeks) | `volatile_conversions` | Campaigns with low `clicks_cv` and high `cvr_cv`, plus compact `weekly` buckets |
| Stable traffic, declining conversions/revenue; what changed | `stable_traffic_decline` | Declining campaigns with `decline_start_week`, `onset_level`, optional `publisher_mix_shift` |
| Strong historical performance now deteriorating | `historical_deterioration` | Was-strong baseline drop on **ROAS** when revenue exists; **conversions/CPA** when `sales_revenue` is 0 (CPA advertisers). Always includes `weekly` + `decline_start_week`. Meta `metric_path` + limitations. Empty → `skip_reason`, not silent `[]` |
| Advertiser +N% budget allocation across campaigns | `budget_increase_allocation` | ROAS-weighted `incremental_spend` / `recommended_spend` / `share_pct` (default +30%) |
| Campaigns where more spend likely improves ROI | `spend_opportunity` | Heuristic scale candidates with `opportunity_score` (`opportunity_source: "heuristic"`) |

**Shared inputs:** `date_range?`, `min_clicks?` (analysis default 100), `traffic_cv_max?` (0.25), `conversion_cv_min?` (0.5), `decline_pct?` (20), `budget_increase_pct?` (30), `include_publisher_mix?` (1/0), `baseline_date_range?` / `roas_underperform_pct?` (historical mode; baseline defaults to prior 28 days).

**`advertiser_id`:**

- **Required** for `budget_increase_allocation` / `spend_opportunity` (and for explicit `compare_date_range` / `baseline_date_range` on this tool).
- **Optional** for `volatile_conversions` / `stable_traffic_decline` / `historical_deterioration`: omit → run analysis for the **top 3 spend advertisers** only; response stamps `analysis_advertiser_ids` and limitation `unscoped analysis limited to top 3 spend advertisers`. Do not scan all advertisers’ daily series.

**Routing:**

1. `search_advertisers` → `advertiser_id` when known; otherwise use unscoped top-3 for the three modes above.
2. Call `get_campaign_performance_report` with the matching `analysis` (and `budget_increase_pct` for allocation).
3. For decline/deterioration: state `onset_level` and `publisher_mix_shift` as **inference**, not a settings audit — MNGT/AdCenter have **no** budget/bid/status change-history.
4. For allocation: optionally call `get_campaign_budget` to clip recommendations vs configured caps (allocator uses Cake period `spend`, not MNGT ledger `budget_spent`).
5. Impression-level stability: AdCenter `adcenter_get_campaign_report` with `report_type: "daily"` (N calls; no revenue).
6. Creative-only traffic follow-up (no Cake revenue at creative grain): `adcenter_get_creative_report`.

Do **not** use `get_advertiser_report` to rank campaigns (publisher grain; drops `camp_id`).

### 5.9 Highest affiliate dependency (Q5) — `list_advertisers_affiliate_concentration`

`list_advertisers_affiliate_concentration` `{ date_range }` → per advertiser `top_affiliate_*`, `click_share`, `revenue_share` (null when advertiser `sales_revenue` is 0), `same_affiliate_for_clicks_and_revenue`. Ranked by `max(click_share, revenue_share)`. Prefer this over client-aggregating `get_advertiser_report` `{ group_by: "publisher" }`. Refuses if the publisher grain is truncated.

### 5.10 High revenue concentration (Q2) — `list_advertisers_high_revenue_concentration`

`list_advertisers_high_revenue_concentration` `{ date_range, max_performing_campaigns?, min_top_share? }` → per advertiser `performing_campaign_count`, `top_campaign_id` / `top_campaign_name`, `top_campaign_revenue`, `advertiser_revenue`, `top_campaign_share`, **`revenue_risk_20pct = 0.20 * top_campaign_revenue`** (server-derived). Ranked by `top_campaign_share` DESC.

Optional `max_performing_campaigns` (question’s “fewer than 3 performing camps”) and `min_top_share` (e.g. 0.8 to catch advertisers with many camps but ≥80% in the top 1–2). Do **not** N× loop `get_campaign_performance_report` per campaign. Do **not** invert `list_advertisers_low_traffic_concentration` — that tool is many active camps with **low** top-campaign **click** share, not high campaign-revenue concentration.

### 5.11 Campaign × affiliate (Q3) — `get_campaign_affiliate_report`

`get_campaign_affiliate_report` `{ affiliate_id?, publisher_id?, advertiser_id?, campaign_id?, active_only?, date_range }` → rows with `affiliate_id`, `affiliate_name`, `camp_id`, `campaign_name`, `campaign_status`, `clicks`, `affiliate_earning`. Cake has **no** `sales_revenue` at the (affiliate, campaign) grain — traffic / campaign-responsible answers use clicks; advertiser revenue stays on campaign or affiliate reports. Do **not** name-parse affiliates. Publisher identity is `affiliate_id` / `affiliate_name` (`publisher_id` is an input-only MNGT resolver, e.g. `rateit_shop` / `fatcoupon`). This is the **safe** answer for “how much traffic did this affiliate send, by campaign” — never RDR3 links or IPs.

### 5.11b Active publisher offers for a named publisher — `list_publisher_offers`

**Question:** List out the active publisher offers for fatcoupon

`list_publisher_offers` `{ publisher_id: "fatcoupon" }`. Resolves `AffiliateIDs.Affiliate` → Cake `affiliate_id`, reads `affiliate_offers_mappings` (default `status=1` approved; 0=pending, 2=disapproved, 3=inactive), then offer details from `publisher_advertiser_offers_from_accounts` with catalog `status` live. List `offer_id`, `adv_name`, `mapping_status_label`, `offer_status` (and `geo` / `category` if useful). This is **assignment**, not CPC.

Do **not** refuse. Do **not** call `adhoc_explore` first. Do **not** use `get_campaign_affiliate_report` (CPC campaigns with traffic in the window). Do **not** use `get_publisher_caps` (caps, not offers). Do **not** use `list_publishers_by_active_campaigns` (that ranks publishers by active-campaign count).

Requires DBA `CREATE VIEW Admin.lucos_ro_affiliate_offers` + `GRANT SELECT` to `lucos_cake_ro`.

### 5.12 Advertisers with clicks and no conversions

`get_advertiser_report` `{ group_by: "advertiser", max_advertiser_conversions: 0, date_range }`. Server keeps rows with `advertiser_conversions <= 0` and `clicks > 0` (post-merge; not SQL HAVING). Read `filtered_count`. **`summary` is still the unbounded window total** (includes converting advertisers) — do not treat it as the zero-conversion subset.

Cite cake `advertiser_conversions` and `clicks` (`clicks_source` whale). Do **not** call this “valid clicks”. For IVT `valid_clicks` use `get_ivt_report` with the **default 7-day** window (or pass `affiliate_id`); unscoped 31-day IVT may timeout.

### 5.13 Currently at cap vs capped frequently

1. Name → `search_advertisers` → `adv_id`.
2. **Now at cap:** `list_campaigns_at_cap` `{ adv_id }` — lifetime ledger snapshot.
3. **Often at daily cap / budget adjustment:** `list_campaigns_capped_frequently` `{ adv_id?, min_days_at_cap: 3 }`. Omit `adv_id` for unscoped top 50 by `days_at_cap`. Empty scoped rows with a live snapshot hit means daily-cap days < threshold (not a tool miss).

### 5.14 Domain whitelist (insuranceandleisure.com)

1. `search_advertisers` `{ q: "State Farm" }` → `adv_id` (e.g. 17237).
2. `search_campaigns_by_domain_targeting` `{ domain: "insuranceandleisure.com", adv_id, list_mode: "include" }`. `adv_id` is required. MCP retries exact `www.` / bare-host aliases.
3. Optional: `search_oos_site_mapping` `{ q: "insuranceandleisure" }` confirms OOS mapping — **not** a campaign whitelist.
4. Empty rows + note → hostname is not on `lucos_ro_domain_list_members` for that advertiser. Then `adhoc_explore`.

### 5.15 Which affiliate has more datafeed traffic — `get_datafeed_traffic_report`

`get_datafeed_traffic_report` `{ date_range, traffic_id? }` → affiliates ranked by whale `advertiser_stats` `clicks` on campaigns tagged with **datafeed** product tags (`lucos_ro_campaign_product_targeting`). Same `clicks_source` as `get_advertiser_report`. Auto-detects `lucos_ro_traffic_channels` whose `traffic_id` / `traffic_name` match datafeed or xml feed; pass `traffic_id` to override. Read `summary` for the unbounded tagged-campaign total. Cite **datafeed-tagged advertiser_stats clicks**, not all Cake clicks, not IVT `valid_clicks`, and not the Admin CPC view (that view times out on a 31-day `camp_id IN`). Do **not** use `list_traffic_channels` / `list_campaigns_by_product_tag` as volume. If a long window times out, the tool retries last 7 days (`window_shortened`).

---

## 6. Optimization questions (Q1–Q10) — `recommend_actions` (staging only)

Use **`recommend_actions`** for these ten question classes. Staging only. Caller sends an **intent enum**, never SQL. Output is **advisory** (`causal: false` always) — not a reportable metric; numbers come from `evidence[]`.

Prefer `recommend_actions` over `analyst_agent` looping and over N× looping reports. Prefer it over `adhoc_explore` for these classes. If a **single** approved report already answers (e.g. portfolio spend → `get_advertiser_report` `{ group_by: "total" }`), call that report instead.

Do **not** use `get_affiliate_report` for Q8 (publisher ranking vs margin) — that tool is one publisher. Do not move spend across advertisers (Q9). Forecast (Q1, Q7) is `holt_damped_v1`; incremental $ is `half_window_saturation_v1` on `intent=incrementality` / modeled on `scale` when a daily series exists. Both are operational heuristics (`causal: false`), **not** geo-lift / MMM. They may return `unsupported: true` if history is too short or all-zero. No YoY inside the 92-day cap; do not query future SQL dates. Details: [`RECOMMEND-ACTIONS.md`](./RECOMMEND-ACTIONS.md).

**Scale / incrementality (Q3):** ChatGPT should **omit `advertiser_id`**. Unscoped = top 3 advertisers by `sales_revenue` (skip $0/CPA), max **8** actions, `summary` totals. Prefer `recommend_actions` over `analyst_agent`. Do not N× reports. Cake `spend_opportunity` still requires `advertiser_id`; the solver calls it scoped internally after the revenue rank.

**Forecast (Q1, Q7):** Holt is on spend (DoW seasonality only; no YoY). Revenue is spend × window ROAS after lag trim — CPA advertisers print revenue n/a, not 0. Do not treat `revenue 0` as the CPA story. ROAS advertisers with Cake conversion value (e.g. BestBuy) must not forecast revenue 0 when window `sales_revenue` > 0. Q1 unscoped every-advertiser forecast is top 50 by spend, not a full inventory / lowest IDs. Unscoped does not N× `list_campaigns` / `get_campaign_budget`; one `list_campaigns_capped_frequently`; last-7d spend is the activity signal. Scoped forecast may annotate at-cap / inactive campaigns but must not clip projection to current remaining monthly budget.

| Q | Question class | `intent` | `objective` | `constraint` | `horizon` |
| --- | --- | --- | --- | --- | --- |
| 1 | Forecast next-month spend for every advertiser | `forecast` | `max_revenue` (default) | — | `next_month` |
| 2 | Who will exceed monthly budget, and when? | `pace` | `max_revenue` (default) | — | `mtd` |
| 3 | Campaigns where more spend lifts ROI (+ incremental revenue) | `scale` (omit `advertiser_id` for portfolio); incremental $ is also P2 `incrementality`. Unscoped: top 3 by `sales_revenue`, max 8 actions, `summary` totals. ChatGPT should omit `advertiser_id`. | `max_revenue` (default) | — | `last_month` |
| 4 | Why did overall ROAS decline last month? | `diagnose` (if ROAS did not fall, the solver must say so) | `max_revenue` (default) | — | `last_month` |
| 5 | +20% revenue at constant spend | `allocate` | `max_revenue` (default) | `hold_spend` | `last_month` |
| 6 | Unusual affiliate changes and likely cause | `diagnose` (affiliate grain); seasonality/tracking root-cause may be `unsupported` | `max_revenue` (default) | — | `last_month` |
| 7 | 3-month forecast per advertiser | `forecast` | `max_revenue` (default) | — | `next_3_months` |
| 8 | Prioritize / deprioritize publishers vs margin | `publisher_priority` | `max_revenue` (default) | `min_publisher_margin` (default `min_cos_pct` 50) | `last_month` |
| 9 | Advertiser X +30% budget allocation | `allocate` | `max_revenue` (default) | `increase_pct` (default 30). Never move spend across advertisers. | `last_month` |
| 10 | Full last-month business health + top 5 actions | `health` | `max_revenue` (default) | — | `last_month` |

`intent=health` may **omit** concentration actions with a `limitations` string if `list_advertisers_low_traffic_concentration` times out or is sparse — diagnose actions already computed are still returned (do not treat the whole health call as failed).

**Diagnose prior window:** when `recommend_actions` is called with an explicit `date_range`, the diagnose prior window is **equal-length ending the day before `from`** — not calendar `prior_month`. Calendar `prior_month` only if `date_range` is omitted and `horizon=last_month`.

`next_month` / `next_3_months` are **projection metadata only**. Evidence windows never use a future `date_to` (history ending today, max 92 days). Explicit `date_range` is rejected if `to` is after today.

True geo-lift / MMM needs a new data product (holdouts, spend experiments) and is an **open decision** — out of scope for this tool. See [`RECOMMEND-ACTIONS.md`](./RECOMMEND-ACTIONS.md).

---

## 7. Honest refusals and safe rephrases

### 7.1 RDR3 traffic links + IPs — refuse the raw payload; answer CPC traffic instead

**Asked:** Share me the RDR3 traffic links for the rateit_shop affiliate along with the IP’s

**Refuse (links + IPs):** I can help retrieve that, but I don’t have access to the underlying RDR3 traffic-link/IP dataset through the currently available reporting tools. I don’t want to fabricate links or IP addresses.

**Safe question (no new tool):** How much Cake CPC traffic did rateit_shop send last 7 days, and which campaigns received it?

**Safe answer path:** `get_campaign_affiliate_report` `{ publisher_id: "rateit_shop", date_range: last 7 days }` → `campaign_name`, `clicks`, `affiliate_earning`. Totals only: `get_affiliate_report` `{ publisher_id: "rateit_shop" }`. Never return URLs or IPs.

No `lucos_ro_rdr3` view. Click `ip` / `redirect_url` are PII.

### 7.2 Affiliate datafeed traffic — `get_datafeed_traffic_report`

**Question:** Which affiliate has more datafeed traffic

Call `get_datafeed_traffic_report`. Rank `rows[]` by `clicks`. Cite datafeed-tagged whale advertiser_stats clicks, not all traffic, not IVT, and not the Admin CPC view.

---

## Smoke (local)

```bash
export ADCENTER_FIXTURE=true
export LUCOS_POLICY_ENABLED=true
export LUCOS_BROKER_MOCK_ALLOW=true
npm start
```

Live staging: `search_advertisers` `lucos_synth_adv` → `advertiser_id` **19880** → campaign **90553** (see `docs/fixtures/adcenter-staging.json`). For cake campaign financials: `get_campaign_performance_report` `{ advertiser_id: 19880, date_range }` (needs staging cake RO DSNs).

### Staging re-smoke (after deploy to `dev-mcp.lucos.com`)

| Check | Call |
| --- | --- |
| Q1 unscoped analysis | `get_campaign_performance_report` `{ analysis: "stable_traffic_decline" }` omit `advertiser_id` — rows or `skip_reason` (must not reject `advertiser_id is required when analysis is set`) |
| Q1/Q7 recommend forecast | `recommend_actions` `{ intent: "forecast" }` — Holt spend; revenue = spend×window ROAS or n/a (CPA / no conversion-value), not 0; unscoped top 50 by spend |
| Q2 high revenue concentration | `list_advertisers_high_revenue_concentration` — native `revenue_risk_20pct` without N× campaign loops |
| Q3 campaign×affiliate | `get_campaign_affiliate_report` — campaign rows for a mismatch affiliate; do not name-parse |
| Q4 health / diagnose prior | `recommend_actions` `{ intent: "health" }` — returns actions (or diagnose with equal-length prior window if concentration is omitted) |
| Low-traffic concentration | `list_advertisers_low_traffic_concentration` short window (2–3 days) — finishes inside 20s |
| Inactive traffic | `list_publishers_traffic_to_inactive_campaigns` same window |
| Q5 affiliate dependency | `list_advertisers_affiliate_concentration` |
| Q7 revenue rank | `get_campaign_performance_report` `{ sort_by: "sales_revenue", limit: 20 }` unscoped — Admin-seeded |
| Q9 CPA deterioration | `get_campaign_performance_report` `{ analysis: "historical_deterioration", advertiser_id: 17237 }` (State Farm) — conversions path / non-empty or `skip_reason` |
| Q10 health | `recommend_actions` `{ intent: "health" }` — diagnose survives concentration miss |
| Adhoc fallback | one `adhoc_explore` smoke question (OpenAI wire `plannerQuerySpecSchema`; nested `as`/`agg`/`value` nullable) |
| Zero-conversion advertisers | `get_advertiser_report` `{ group_by: "advertiser", max_advertiser_conversions: 0 }` — server filter, not client |
| Frequent cap unscoped | `list_campaigns_capped_frequently` omit `adv_id` — top 50 or documented empty (daily vs lifetime) |
| Landing-page BestBuy 7d | `adcenter_get_report` keywords — roll up `destination_url` when present; if `note` says URLs omitted, still rank by `keyword` + date and label as bid keywords (do not refuse) |
| Domain whitelist | `search_campaigns_by_domain_targeting` State Farm + `insuranceandleisure.com` — rows or empty+note |

Out of scope: IVT shard backfill; DBA indexes on `lucos_ro_affiliate_cpc_summary_daily`. Qdrant empty-catalog on staging (`adhoc_explore` retrieve miss) is an ops re-index of `lucos_business_catalog`, not a code change.

## Related

- Consumer notes: [`ADCENTER-LEVEL1.md`](./ADCENTER-LEVEL1.md)
- Ops / mint: `api.lucos.com/docs/ADCENTER-LEVEL1-OPS.md`
- Cake RO views: [`CAKE-RO-VIEWS.md`](./CAKE-RO-VIEWS.md)