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.

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

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:

{
  "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:

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

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. When you graduate from direct attribution to cross-campaign multi-touch credit, the per-campaign Campaign ROAS endpoint teaches the model, window, and currency normalisation you pick up.

WhatsApp

Guides

Messaging Controls

Audience & Support Tools

Campaign ROI bulk export

Campaign ROI bulk export

When to use the bulk endpoint vs per-campaign ROAS

The API

Columns returned

Direct-attribution mechanics

Worked example: fetch → CSV mapping

From the export to the goals behind it

See also

​Campaign ROI bulk export

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

​The API

​Columns returned

​Direct-attribution mechanics

​Worked example: fetch → CSV mapping

​From the export to the goals behind it

​See also

Campaign ROI bulk export

When to use the bulk endpoint vs per-campaign ROAS

The API

Columns returned

Direct-attribution mechanics

Worked example: fetch → CSV mapping

From the export to the goals behind it

See also