Load a Data Mart from the Responses API

Build a data mart from the Scrunch Responses API by incrementally loading response-level data, normalizing it into fact and dimension tables, and computing metrics in your warehouse.

Prerequisites

A Scrunch API key with Responses access (provision one here)
A brand ID for the brand you want to load
A cloud data warehouse (BigQuery, Snowflake, Redshift, Databricks, etc.)

How the Responses API works

The Responses API returns one record per AI response. Each record includes the full response text, citations, brand and competitor evaluations, and prompt metadata. Key characteristics:

1:1 response model — each item represents a single, distinct AI response
Denormalized — arrays like tags, key_topics, competitors, citations, and shopping_results are embedded in each record
Reverse chronological — newest responses are returned first
Supports date filtering and pagination — ideal for incremental ETL

IDs are integers, not strings. id, prompt_id, persona_id, and each competitor’s id are all 64-bit integers. Type them as INT64 / BIGINT in your warehouse rather than STRING, and be careful with JSON parsers that coerce large integers to floats — response IDs are already nine digits and will lose precision as a 32-bit int or a JavaScript Number past 2^53.

Enum-like fields (platform, brand_sentiment, brand_position, source_type) are returned as lowercase strings. stage and key_topics are the exception — they are returned as display-cased names. For the full response schema, see the Responses API overview.

Incremental loading strategy

Use date windows and pagination to pull new responses on a recurring schedule.

Choose your sync window

Use start_date and end_date (both YYYY-MM-DD, UTC) to define a date range.start_date is inclusive and end_date is exclusive. To load a single UTC day, set end_date to the following day — start_date=2025-06-01&end_date=2025-06-02 returns exactly June 1. Treating end_date as inclusive is the most common cause of duplicated or missing days in a daily job.For daily loads, pull the previous UTC day after midnight to ensure completeness.

Paginate through all results

The API returns paginated results. Increment offset by limit until you have all records for the window.

curl "https://api.scrunchai.com/v1/$BRAND_ID/responses?start_date=2025-06-01&end_date=2025-06-02&limit=1000&offset=0" \
  -H "Authorization: Bearer $SCRUNCH_API_KEY"

The response includes a total count so you know when you’re done:

{
  "total": 2430,
  "offset": 0,
  "limit": 1000,
  "items": [ ... ]
}

limit defaults to 100, so always set it explicitly for bulk loads. The minimum is 1 and no maximum is enforced — values well above 1000 are honored — but 1000 keeps individual requests cheap to retry. Keep offset a multiple of limit.

Deduplicate on id

Each response has a globally unique integer id. Use it as a natural key and upsert into your warehouse to handle re-runs or overlapping date windows.

Some fields like brand_present, brand_sentiment, tags, key_topics, and stage may be re-evaluated if prompt metadata or brand configuration changes in Scrunch. Always upsert rather than append-only. response_text, citations, and created_at are immutable — they record exactly what was observed.

Narrowing the window

If you are backfilling a large history or building per-segment marts, these filters are available alongside the date range and reduce the volume you have to page through:

Parameter	Notes
`platform`	One of `chatgpt`, `claude`, `perplexity`, `google_ai_overviews`, `google_ai_mode`, `google_gemini`, `meta`, `copilot`, `grok`
`prompt_id`	Integer — a single prompt
`persona_id`	Integer — a single persona
`stage`	Stage name, resolved against the brand’s configured stages
`has_shopping_data`	`true` returns only responses carrying shopping results, `false` only those without

For a wide backfill, loop the window by day and by platform rather than requesting one enormous range. Pages stay small, retries are cheap, and a failure only costs you one day-platform slice instead of the whole job.

Schema design

Each API record includes arrays (tags, key_topics, competitors, citations, shopping_results) that should be normalized into separate tables to avoid row explosion.

responses (1 row per response)
   |
   |—— response_tags (1 row per response x tag)
   |
   |—— response_key_topics (1 row per response x key topic)
   |
   |—— response_competitors (1 row per response x competitor)
   |
   |—— response_citations (1 row per response x citation)
   |
   |—— response_shopping_results (1 row per response x product)

If your warehouse has strong support for JSON or ARRAY columns (e.g., BigQuery, Snowflake, Databricks), you can skip the bridge tables and store tags, key_topics, competitors, citations, and shopping_results as native array or JSON fields directly on the responses table. Use UNNEST or LATERAL FLATTEN at query time instead of pre-normalizing. This simplifies your ETL at the cost of slightly more complex analytics queries.

Responses fact table

Store one row per response with scalar fields:

Column	Type	Notes
`id`	`INT64`	Primary key (globally unique)
`created_at`	`TIMESTAMP`	When the response was captured (UTC)
`prompt_id`	`INT64`	Foreign key to the prompt
`prompt`	`STRING`	Text of the prompt
`platform`	`STRING`	Lowercase slug — `chatgpt`, `perplexity`, `claude`, `google_ai_mode`, etc.
`persona_id`	`INT64`	Nullable — prompts need not have a persona
`persona_name`	`STRING`	Nullable
`stage`	`STRING`	Stage name, not a fixed enum — see below
`branded`	`BOOLEAN`	Whether the prompt explicitly mentions the brand
`brand_present`	`BOOLEAN`
`brand_sentiment`	`STRING`	`positive`, `mixed`, `negative`, `none`, or `null`
`brand_position`	`STRING`	`top`, `middle`, `bottom`, or `null`
`country`	`STRING`	Nullable — 2-character ISO code
`response_text`	`STRING`	Full AI-generated answer (markdown)

brand_sentiment has four values, not three. none means the brand was mentioned but without discernible sentiment — it is distinct from null, which means the brand was not mentioned at all. A CASE expression that maps only positive/mixed/negative will correctly leave none out of the sentiment average (this is what Scrunch does), but a COALESCE(..., 50) or an ELSE 50 fallback will silently score every unsentimented mention as neutral and drag your numbers toward the middle.

stage is not a fixed enum. Values resolve from the brand’s own configured stages, so they vary per brand and can be renamed or extended. The default sets are intent (Advice, Awareness, Evaluation, Comparison, Other) and funnel (Awareness, Consideration, Conversion, Loyalty, Other), but treat the column as free text and drive any stage dimension table off the values you actually observe rather than a hardcoded list.

Optional scalar and semi-structured columns

These fields are present on every record but are not needed by every mart:

Column	Type	Notes
`competitors_present`	`ARRAY<STRING>`	Names of competitors mentioned. A convenience subset of `competitors` — skip it if you load the `response_competitors` bridge table
`query_fanout`	`JSON`	Nullable. `{ queries: STRING[], engine_metadata: JSON }` — the searches the platform ran internally. Only populated for platforms that expose fan-out

query_fanout is null for most rows on most platforms. Store it as a raw JSON or STRUCT column rather than modelling it into its own table until you know you need it.

Bridge tables

Unnest each array into its own table, keyed by response_id. response_tags

Column	Type	Notes
`response_id`	`INT64`
`tag`	`STRING`	User-defined, free text

response_key_topics

Column	Type	Notes
`response_id`	`INT64`
`key_topic`	`STRING`	Display-cased topic name

tags and key_topics are distinct fields and are not interchangeable. Tags are arbitrary labels a user attaches to a prompt; key topics are Scrunch’s topic classification. Both hang off the prompt rather than the response, so both can change on re-evaluation.

response_competitors

Column	Type	Notes
`response_id`	`INT64`
`competitor_id`	`INT64`	Stable join key — prefer this over the name
`competitor_name`	`STRING`	Display name; can be edited in Scrunch
`present`	`BOOLEAN`	Always `true` in practice — see below
`sentiment`	`STRING`	`positive`, `mixed`, `negative`, or `none`
`position`	`STRING`	`top`, `middle`, `bottom`

Key your competitor dimension on competitor_id. Competitor display names are editable in the Scrunch UI, so a mart joined on competitor_name will silently split one competitor into two rows the first time someone fixes a spelling.

The competitors array only contains competitors actually mentioned in the response. It is not a row per configured competitor, and it is empty when no competitor was mentioned — so the array length varies from 0 to the number of competitors you track. Every entry carries present: true.This means response_competitors does not give you a presence denominator. Grouping it by competitor and dividing by its own row count yields 100% for every competitor. The denominator has to come from the responses table — see Competitor metrics below.

response_citations

Column	Type	Notes
`response_id`	`INT64`
`url`	`STRING`	Full cited URL
`domain`	`STRING`	Registered domain extracted from `url` — provided for convenience
`title`	`STRING`	Usually an empty string rather than null when the platform omits it — normalize with `NULLIF(title, '')` on load
`snippet`	`STRING`	Nullable — not exposed by all platforms
`source_type`	`STRING`	`brand`, `competitor`, or `other` (`other` is “third-party” in the Scrunch UI)

response_shopping_results Only populated for responses carrying product listings. Filter the load with has_shopping_data=true if this is all you need.

Column	Type	Notes
`response_id`	`INT64`
`url`	`STRING`
`title`	`STRING`
`image`	`STRING`	Nullable primary image; `all_images` holds the full array
`description`	`STRING`	Nullable
`rating`	`FLOAT64`	Nullable
`reviews`	`INT64`	Nullable review count
`purchasing_options`	`JSON`	Nullable nested array of seller/price options

Never unnest multiple arrays in the same query. Crossing two arrays (e.g., tags x competitors) causes row explosion and inflates every metric.

Computing metrics

Once your data mart is loaded, you can aggregate responses into daily performance summaries. Group on DATE(created_at), prompt_id, prompt, platform to match the way Scrunch reports prompt-level performance by default. The following examples use PostgreSQL syntax. Translate as needed for your warehouse.

Response count

COUNT(*) AS responses

Brand presence percentage

COUNT(*) FILTER (WHERE brand_present = true)::float
  / NULLIF(COUNT(*)::float, 0.0) AS brand_presence_pct

Brand sentiment score

AVG(
  CASE
    WHEN brand_sentiment = 'positive' THEN 100
    WHEN brand_sentiment = 'mixed' THEN 50
    WHEN brand_sentiment = 'negative' THEN 0
    ELSE NULL
  END
) AS brand_sentiment_score

Brand position score

AVG(
  CASE
    WHEN brand_position = 'top' THEN 100
    WHEN brand_position = 'middle' THEN 50
    WHEN brand_position = 'bottom' THEN 0
    ELSE NULL
  END
) AS brand_position_score

Both score metrics are averaged only over responses that mention the entity. The ELSE NULL is doing that work: sentiment and position are null when the brand is absent, and AVG skips nulls. Keep the ELSE NULL — replacing it with a numeric default silently mixes non-mentions into the score.

Citation rate

Citation metrics are domain-based and independent of whether the brand was mentioned by name. Compute them from the response_citations bridge table, taking care to count responses rather than citation rows:

SELECT
  COUNT(DISTINCT c.response_id)::float
    / NULLIF((SELECT COUNT(*) FROM responses)::float, 0.0) AS citation_rate,
  COUNT(DISTINCT CASE WHEN c.source_type = 'brand'
                      THEN c.response_id END)::float
    / NULLIF((SELECT COUNT(*) FROM responses)::float, 0.0) AS brand_citation_rate
FROM response_citations c

“How many citations” has several defensible answers that differ by an order of magnitude on the same rows, so name the unit in every citation column you publish. Counting citation rows counts URL occurrences — one response citing a domain under four URLs contributes 4. Counting DISTINCT response_id contributes 1. Counting distinct (response_id, domain) pairs is what the Scrunch Citations dashboard’s composition breakdown shows. Picking a different unit than the dashboard is the usual reason a mart’s citation numbers won’t reconcile.

Putting it together

SELECT
  DATE(created_at)  AS response_date,
  prompt_id,
  prompt,
  platform,
  COUNT(*)          AS responses,
  COUNT(*) FILTER (WHERE brand_present)::float
    / NULLIF(COUNT(*)::float, 0.0)
                    AS brand_presence_pct,
  AVG(CASE
    WHEN brand_sentiment = 'positive' THEN 100
    WHEN brand_sentiment = 'mixed'    THEN 50
    WHEN brand_sentiment = 'negative' THEN 0
  END)              AS brand_sentiment_score,
  AVG(CASE
    WHEN brand_position = 'top'    THEN 100
    WHEN brand_position = 'middle' THEN 50
    WHEN brand_position = 'bottom' THEN 0
  END)              AS brand_position_score
FROM responses
GROUP BY 1, 2, 3, 4;

These metric definitions match the ones used in the Scrunch dashboard and Query API. You can also define your own calculations if your use case requires it.

Competitor metrics

Sentiment and position translate directly, but presence does not. Because response_competitors only holds mentions, its own row count is the wrong denominator — you have to divide by the total response count for the period, which comes from the responses table:

WITH daily AS (
  SELECT DATE(created_at) AS response_date, COUNT(*) AS responses
  FROM responses
  GROUP BY 1
)
SELECT
  d.response_date,
  rc.competitor_id,
  rc.competitor_name,
  COUNT(DISTINCT rc.response_id)::float
    / NULLIF(d.responses::float, 0.0)  AS competitor_presence_pct,
  AVG(CASE
    WHEN rc.sentiment = 'positive' THEN 100
    WHEN rc.sentiment = 'mixed'    THEN 50
    WHEN rc.sentiment = 'negative' THEN 0
  END)                                 AS competitor_sentiment_score
FROM response_competitors rc
JOIN responses r ON r.id = rc.response_id
JOIN daily     d ON d.response_date = DATE(r.created_at)
GROUP BY d.response_date, rc.competitor_id, rc.competitor_name, d.responses;

Note that this emits no row for a competitor that went unmentioned on a given day, rather than a row reading 0%. If your dashboard needs explicit zeros, LEFT JOIN this result onto a date spine crossed with your competitor dimension. The brand equivalent does not have this problem: brand_present is a column on every response row, so COUNT(*) over responses is already the right denominator.

Metrics you cannot reproduce from this API

A few dashboard metrics depend on data the Responses API does not expose. Compute these with the Query API instead of approximating them:

The two APIs treat end_date differently: it is exclusive on Responses and inclusive on Query. Passing the same pair of dates to both compares an N-day window against an N+1-day one, which is a common reason a mart’s numbers sit just below the dashboard’s.

brand_avg_rank / competitor_avg_rank — first-occurrence mention rank is not a field on the response record. Note that position (top / middle / bottom) is a thirds-of-the-response bucket, not a rank, and is not a substitute for it.
Share of voice — normalized across the full tracked set, so it is only correct if your mart holds every tracked competitor for the period.
brand_citation_mention_rate — depends on crawled page content of the cited URLs, which is not part of the response payload.

Responses API Overview

Full schema, filtering, and pagination details.

Responses Quickstart

Make your first API call in cURL, Python, or JavaScript.

​Prerequisites

​How the Responses API works

​Incremental loading strategy

​Narrowing the window

​Schema design

​Responses fact table

​Optional scalar and semi-structured columns

​Bridge tables

​Computing metrics

​Response count

​Brand presence percentage

​Brand sentiment score

​Brand position score

​Citation rate

​Putting it together

​Competitor metrics

​Metrics you cannot reproduce from this API

​Related

Responses API Overview

Responses Quickstart

Prerequisites

How the Responses API works

Incremental loading strategy

Narrowing the window

Schema design

Responses fact table

Optional scalar and semi-structured columns

Bridge tables

Computing metrics

Response count

Brand presence percentage

Brand sentiment score

Brand position score

Citation rate

Putting it together

Competitor metrics

Metrics you cannot reproduce from this API

Related