This article is also available in:

Export your invitations to BigQuery or Snowflake

Load the history of your review invitations into BigQuery or Snowflake, then keep it up to date every day with the Review Collect API.

Get your API key

Ask your account manager for your API key, from the chat bubble or by email
Store it in your warehouse's secret manager, never in a shared file

The key gives access to the data of your account only. It goes in the X-Api-Key header of every request.

Load the history

The GET /invitations endpoint returns one invitation per customer contacted, with the messages sent, the rating left and the published review. The full reference is on developers.review-collect.com.

Call the endpoint with limit=1000
While has_more is true, call it again with cursor set to the next_cursor you received
Write each page to your table before asking for the next one

Template to copy and paste:

import requests

BASE = "https://api.review-collect.com/api:v1"
HEADERS = {"X-Api-Key": RC_API_KEY}

def invitations(**params):
    params = {"limit": 1000, **params}
    while True:
        page = requests.get(f"{BASE}/invitations", headers=HEADERS, params=params, timeout=60).json()
        yield from page["data"]
        if not page["has_more"]:
            return
        params["cursor"] = page["next_cursor"]

for invitation in invitations():
    write_to_warehouse(invitation)

100,000 invitations are 100 calls. Load them one after the other: the limit is 300 requests per minute per key.

Create the table

One row per invitation, with id as the key. The columns worth exposing for analysis:

Identifiers — id, order.id, order.number, customer.email, customer.phone
Sending — source, sent_at, status, clicked_at
Rating left — review.rating, review.comment, review.submitted_at
What followed — routing.type, routing.destination, routing.published, routing.published_review.id
Update — updated_at

routing.published and routing.published_review require Trustpilot's Connect module, a paid option of Trustpilot Business connected from the Integrations page. Without it, these two fields stay empty.

In BigQuery, load each invitation as is into a JSON column and expose these columns in a view. In Snowflake, same approach with a VARIANT column.

For one row per message sent (initial, reminder 1, reminder 2), unnest the messages array with UNNEST in BigQuery or LATERAL FLATTEN in Snowflake: each element carries step, channel and sent_at.

Update every day

Schedule the script every morning
Pass the since parameter with the start time of the previous run, minus five minutes
Merge the rows received on id: update when the row exists, insert otherwise

BigQuery template:

MERGE `dataset.invitations` t
USING `dataset.invitations_staging` s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET data = s.data, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (id, data, updated_at) VALUES (s.id, s.data, s.updated_at)

The since filter applies to updated_at: an invitation sent in April and rated in October comes back in October's run. An invitation comes back each time it changes, and the merge on id simply updates it.

Trustpilot reviews are matched within 24 hours. For reliable weekly reporting, read a week two days after it ends.

Updated on: 22/10/2026

Was this article helpful?

Thank you!