> ## Documentation Index
> Fetch the complete documentation index at: https://docs.orbit.devotel.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Campaign ROI bulk export

> Export Cost, Revenue, Conversions, and ROI for up to 200 campaigns in one API round trip — the endpoint behind the Insights → Reports 'Campaign ROI' card, with direct-attribution mechanics, a worked fetch-to-CSV example, and the pitfalls to avoid.

# Campaign ROI bulk export

Insights → Reports carries a **Campaign ROI** card: launched campaigns, one row each, with **Cost / Revenue / Conversions / ROI (%)** — the four financial cells every bulk report needs. The same figures are available over a dedicated bulk endpoint, `GET /campaigns/roi-summary`, so you can export a report without fanning out one heavy attribution call per campaign.

This guide covers when to use the bulk endpoint instead of the per-campaign attribution engine, how it attributes revenue, and the exact mapping from API fields to the CSV columns the dashboard downloads. For the full attribution chain behind the per-campaign detail surface, see [Campaign ROAS and revenue attribution](/guides/campaign-roas-attribution).

## When to use the bulk endpoint vs per-campaign ROAS

Two endpoints answer two different questions. Pick by the question, not by habit:

| You need...                                                                                                   | Use...                                              | Why                                                                                                                                                                                                                            |
| ------------------------------------------------------------------------------------------------------------- | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| A flat table of every launched campaign — Cost / Revenue / Conversions / ROI — for a report or CSV export     | `GET /campaigns/roi-summary?ids=<id1,id2,...>`      | One bounded round trip (up to 200 ids) returns one keyed answer. The rows you export are direct-attribution aggregates, cheap to compute.                                                                                      |
| One campaign's revenue split across every campaign, template, or message that touched its converting contacts | `GET /campaigns/:id/roas` (and `/roas/touchpoints`) | The per-campaign engine runs a cross-campaign multi-touch join with an attribution model (`last_touch` / `linear` / `time_decay`). That is the correct shape for "who earned the credit", and it is deliberately per-campaign. |

Never call `/campaigns/:id/roas` in a loop to fill a report. The multi-touch join is guarded by its own query timeout and is heavy by design; running it once per campaign from one report-generation click is the classic N+1 failure a bulk export must avoid. `roi-summary` exists precisely so the export stays one bounded query.

The dashboard's Campaign ROI card issues exactly one `roi-summary` call for the launched campaigns it lists — mirror that shape in your integration and the export stays fast regardless of campaign count.

## The API

```bash theme={null}
curl "https://api.orbit.devotel.io/api/v1/campaigns/roi-summary?ids=cmp_abc123,cmp_def456" \
  -H "X-API-Key: $ORBIT_API_KEY"
```

* **Query** — one `ids` parameter: a comma-separated list of campaign ids. Duplicate and empty entries are dropped server-side.
* **Bound** — at most **200 ids** are honored per call. Anything past 200 is truncated, so batch larger exports into chunks of 200 and merge the results.
* **Timeout** — the query runs behind a bounded statement timeout and fails closed (an empty result) rather than hanging your report click; a pathological batch degrades to no-data rows, not a wedged read.
* **Response** — a JSON object keyed by campaign id. Campaigns and ids you requested but that did not match stay absent from the result:

```json theme={null}
{
  "data": {
    "cmp_abc123": {
      "spend_cents": 1234,
      "revenue_cents": 5900,
      "conversions": 7,
      "roas": 4.78,
      "roi_pct": 378,
      "cost_status": "ok"
    },
    "cmp_def456": {
      "spend_cents": 0,
      "revenue_cents": 0,
      "conversions": 0,
      "roas": null,
      "roi_pct": null,
      "cost_status": "no_spend"
    }
  },
  "meta": { "request_id": "req_...", "timestamp": "..." }
}
```

## Columns returned

The API returns cents, nulls, and an explicit cost status; the dashboard and CSV render dollar columns. Map one entry to one row like this:

| API field       | Report column                      | Meaning                                                                                                   |
| --------------- | ---------------------------------- | --------------------------------------------------------------------------------------------------------- |
| —               | Campaign                           | The campaign name (the client supplies it from its own campaign list).                                    |
| —               | Recipients                         | The campaign's total recipients, also from the campaign list.                                             |
| `spend_cents`   | Cost                               | Send spend for the campaign, in cents → rendered as dollars.                                              |
| `revenue_cents` | Revenue                            | Attributed conversion value, cents → dollars.                                                             |
| `conversions`   | Conversions                        | Count of the campaign's conversion records.                                                               |
| `roi_pct`       | ROI (%)                            | `(revenue − spend) / spend × 100`, rounded to two decimals. Null when the campaign has no recorded spend. |
| `roas`          | — (available to your own pipeline) | `revenue / spend`. Null on the same no-spend condition.                                                   |
| `cost_status`   | —                                  | `"ok"` or `"no_spend"` — tells you whether a null ROI is a genuine zero-spend answer vs a missing row.    |

Both null-safe rules matter for spreadsheet hygiene:

* **`cost_status: "no_spend"` gives real zeros.** A campaign that never recorded spend gives `spend_cents: 0`, `roas: null`, `roi_pct: null` — never an Infinity. The CSV emits an empty cell for a null ROI % so the column stays numeric for SUM/sort.
* **Missing ids stay missing.** An id the campaign list named but the summary did not return has no entry; don't fabricate zeros for it — render it as unavailable until the entry arrives (the dashboard's only fallback is the campaign's own spend counter, and only while the summary request is in flight).

## Direct-attribution mechanics

`roi-summary` deliberately performs **direct attribution**: a campaign's own conversion events credited to that campaign alone, no cross-campaign model. Two grouped aggregates, both keyed by `campaign_id`, make up each row:

1. **Revenue and conversions** come from the campaign's own `journey_goal_conversions` rows — one aggregate count, and a sum of each conversion's `value_cents`. A journey campaign's goal events populate this table when contacts convert; non-journey campaigns (or journeys with no goal) simply have no rows, which becomes an honest `0` for both columns — real zeros, not a fabricated "N/A".
2. **Cost** comes from the campaign's own `messages` rows with a recorded price: `SUM(price)`, converted to cents. This is exactly the send-cost ledger the per-campaign `/:id/roas` detail page uses as its spend denominator, so the bulk export and the detail page can never disagree about what a campaign cost.

ROI % and ROAS are computed with the same math the per-campaign endpoint uses (revenue vs spend, null ratios on no spend), so a campaign's ROI % in this export matches its detail page's ROI % — METRICS-CONSISTENCY on both surfaces.

## Worked example: fetch → CSV mapping

Fetch the summary once for every campaign id in your export set, then join it onto the campaign list your own query already holds:

```python theme={null}
import csv, requests

BASE = "https://api.orbit.devotel.io/api/v1"
HEADERS = {"X-API-Key": ORBIT_API_KEY}

def fetch_roi_summary(campaign_ids):
    summary = {}
    for i in range(0, len(campaign_ids), 200):
        chunk = campaign_ids[i : i + 200]
        body = requests.get(
            f"{BASE}/campaigns/roi-summary",
            headers=HEADERS,
            params={"ids": ",".join(chunk)},
        ).json()
        summary.update(body["data"])
    return summary

campaigns = list_campaigns()  # your existing list call; launched statuses only
summary = fetch_roi_summary([c["id"] for c in campaigns])

with open("campaign-roi.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["Campaign", "Recipients", "Status", "Channel",
                "Cost (USD)", "Revenue (USD)", "Conversions", "ROI (%)"])
    for c in campaigns:
        roi = summary.get(c["id"])
        cost = revenue = conv = roi_pct = ""
        if roi is not None:
            if roi["cost_status"] == "ok":
                cost = round(roi["spend_cents"] / 100, 2)
            revenue = round(roi["revenue_cents"] / 100, 2)
            conv = roi["conversions"]
            roi_pct = roi["roi_pct"] if roi["roi_pct"] is not None else ""
        w.writerow([c["name"], c["total_recipients"], c["status"], c["channel"],
                    cost, revenue, conv, roi_pct])
```

Common pitfalls, all caught in the wild:

* **Empty cells in a numeric column beat "N/A" placeholders.** Excel and Sheets coerce the whole column to text the moment one cell holds "N/A" or "-"; SUM and sort then silently stop working. Emit `""` for unavailable numeric data and keep sentinels to the text columns only.
* **Don't trust the campaign list's spend counter for Cost.** An older denormalized counter on the campaign record still surfaces on some list views and is `0` for many launched campaigns whose send path never populated it — reading it for Cost is what used to make every financial cell in the export \$0 even on campaigns that genuinely cost money. The `roi-summary` entry (the per-message send spend) is authoritative; treat the counter as, at most, a transient fallback while the summary call is in flight.
* **A null ROI % is not a zero ROI %.** `cost_status: "no_spend"` means "there is no spend to divide by", so the ratio is undefined — render the empty cell. A `0.0` ROI % would be a different, and false, statement.
* **Chunk past 200 ids.** The cap is server-side and truncates silently. Slice to 200 per call and merge the keyed results (`summary.update(...)` above); the keyed response makes merging trivial.
* **Campaigns that never launched carry zero signal.** The dashboard card filters drafts, pending approvals, and scheduled-but-unsent campaigns out before it queries — they would only pad the export with all-zero rows. Mirror that filter when you assemble the id list yourself.

## From the export to the goals behind it

The Revenue and Conversions columns are only as meaningful as the goals you declared. If a campaign's Revenue reads 0 and you expected purchases: confirm the campaign's journey has a conversion goal and that goal events are actually recorded — see [Defining and reading Conversion Goals](/guides/conversion-goals-attribution). When you graduate from direct attribution to cross-campaign multi-touch credit, the per-campaign [Campaign ROAS](/guides/campaign-roas-attribution) endpoint teaches the model, window, and currency normalisation you pick up.

## See also

* [Reports catalogue](/guides/reports-catalogue) — the ad hoc Insights card grid this export ships in, plus the scheduled-report counterpart
* [Campaign ROAS and revenue attribution](/guides/campaign-roas-attribution) — the per-campaign multi-touch engine behind the `/:id/roas` detail page
* [Defining and reading Conversion Goals](/guides/conversion-goals-attribution) — declare goals so direct attribution has revenue to sum
* [Multi-touch attribution](/guides/multi-touch-attribution) — split conversion credit across channels and campaigns program-wide