> ## Knowledge Base Index
> Fetch the complete knowledge base index at: https://help.review-collect.com/sitemap.xml
> Use this file to discover available pages before exploring further.
> Pure-Markdown content can be obtained by appending a '.md' suffix to the content URLs listed in the sitemap (without the trailing slash).

# 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

1. Ask **your account manager** for your API key, from the chat bubble or by email
2. 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](https://developers.review-collect.com).

1. Call the endpoint with `limit=1000`
2. While `has_more` is `true`, call it again with `cursor` set to the `next_cursor` you received
3. 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](https://app.review-collect.com/home/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

1. Schedule the script every morning
2. Pass the `since` parameter with the start time of the previous run, minus five minutes
3. 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.