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

# How to pull your data into a BI tool

> Get row-level sales, payment and report data out of TWICE Commerce over the API, so Power BI, Excel, Google Sheets, Tableau or Looker Studio can build its own views on it.

<Frame caption="Reports > Sales overview > Export">
  <img src="https://mintcdn.com/twicecommerce/Ab7tx7ih94KQsi0k/images/reports-export.webp?fit=max&auto=format&n=Ab7tx7ih94KQsi0k&q=85&s=5b8746adc8bb592f174ae9f8130924e2" alt="The Export tab of a report, with the download buttons and the REST API endpoints" width="1920" height="1080" data-path="images/reports-export.webp" />
</Frame>

<Card title="Open in TWICE Admin" icon="external-link" href="https://admin.twicecommerce.com/reports" horizontal>
  reports
</Card>

Pull the revenue and payments ledgers over the API and build your own views on them. The two ledgers are the row-level data behind every sales and finance number in TWICE Commerce: one row per order line and one row per payment line, with dates, location, amounts and payment status.

## Prerequisites

<Warning>
  **Required access:** an API key. Every key carries owner-level permissions on your account, so give the key to the person building the dashboards, never paste it into a shared file, and delete it when the work is done. See [API keys](/docs/concepts/integrations/api-keys).
</Warning>

<Info>
  **Decide before you start:**

  * **The currency.** Each ledger call returns one currency. Call once per currency you sell in.
  * **The history you need.** One call returns up to 100,000 rows. Split a longer history into one call per year.
</Info>

<Note>
  Advanced reporting is available on the **Standard** and **Enterprise** plans. See [plans](/docs/twice-commerce-overview#pricing).
</Note>

## Pick the endpoint for the data you need

Use the two ledgers as the tables your model is built on. A built-in report returns the numbers one admin view shows, capped at 1,000 rows, so it suits a single chart and not a data model.

| Data                                                                   | Endpoint                                              | Rows per call |
| ---------------------------------------------------------------------- | ----------------------------------------------------- | ------------- |
| Every order line, with its dates, location, amounts and payment status | `GET /internal/reporting/revenue/export`              | 100,000       |
| Every payment, refund and platform fee                                 | `GET /internal/reporting/payments/export`             | 100,000       |
| One built-in report, as the admin shows it                             | `GET /internal/reporting/reports/{reportId}/data`     | 1,000         |
| Customers, stock items, listings                                       | The list endpoints of the [Admin API](/docs/api-reference) | Paged         |

All paths go to `https://server.twicecommerce.com`. Report data sits outside the versioned Admin API, so these paths have no page in the **API** tab and can change. Check this page if a connection stops returning data.

## The walkthrough

<Steps>
  <Step title="Create an API key">
    Create a key under **Settings → Integrations & API** in the **API keys** section, and copy it. The full key is shown once.
  </Step>

  <Step title="Build the ledger URL">
    Add the period and the currency to the ledger path:

    | Parameter  | Value                                           | Required |
    | ---------- | ----------------------------------------------- | -------- |
    | `from`     | First day of the period, `YYYY-MM-DD`           | Yes      |
    | `to`       | Last day of the period, `YYYY-MM-DD`, inclusive | Yes      |
    | `currency` | ISO 4217 code, for example `EUR`                | Yes      |

    The dates are read in your account's time zone, set under [Account details](/docs/settings/account). The ledger carries every location, so filter on its `Location` column in your tool.

    ```text theme={null}
    https://server.twicecommerce.com/internal/reporting/revenue/export?from=2026-01-01&to=2026-09-30&currency=EUR
    ```
  </Step>

  <Step title="Test the URL with the key">
    Send the key in the `X-API-KEY` header. A working call returns the ledger as CSV.

    <CodeGroup>
      ```bash cURL theme={null}
      curl "https://server.twicecommerce.com/internal/reporting/revenue/export?from=2026-01-01&to=2026-09-30&currency=EUR" \
        -H "X-API-KEY: {key}" \
        -o revenue-2026.csv
      ```
    </CodeGroup>
  </Step>

  <Step title="Connect your tool">
    Point your tool at the URL with the key in the header. Follow the guide for your tool: [Power BI](/docs/guides/reports/connect-power-bi), [Excel](/docs/guides/reports/connect-excel), [Google Sheets](/docs/guides/reports/connect-google-sheets), [Tableau](/docs/guides/reports/connect-tableau) or [Looker Studio](/docs/guides/reports/connect-looker-studio).
  </Step>
</Steps>

## Read the ledger files

Open the file you saved in the test and check it starts like this:

```text Revenue ledger, first rows theme={null}
Create_Date;Start_Date;End_Date;Created_In_Period;Started_In_Period;Ended_In_Period;Document_Type;Revenue_Category;Order_Number;Item_Name;Quantity;Account;Location;Cost_Center;Timezone;Currency;Amount_Excl_Tax;Tax_Rate_1;Tax_Amount_1;Amount_Total;Payment_Status;Amount_Paid;Amount_Refunded;Amount_Outstanding;First_Payment_Date;Order_ID;Order_Line_Item_ID;Row_ID
2026-03-02;2026-03-14;2026-03-15;Yes;Yes;Yes;sale;booking;#1042;Meeting room A;1;Northwind Spaces;Helsinki;HEL-01;Europe/Helsinki;EUR;161.29;25.5;41.13;202.42;paid;202.42;0.00;0.00;2026-03-02;7d1c9e4a-3b2f-4c8e-9a61-5f0e2b7c4d13;a3e8f0b2-6c41-4d9a-8e27-1b5c9f3d2e60;a3e8f0b2-6c41-4d9a-8e27-1b5c9f3d2e60
2026-03-05;2026-03-05;2026-03-05;Yes;Yes;Yes;sale;sale;#1043;Used road bike, size M;1;Northwind Spaces;Helsinki;HEL-01;Europe/Helsinki;EUR;318.73;25.5;81.27;400.00;paid;400.00;0.00;0.00;2026-03-05;2f6b8d10-9e3c-4a57-b1d4-6c8e0a2f7b95;c9d2a7e4-1f38-4b6c-a5e0-3d7b9c1f8a42;c9d2a7e4-1f38-4b6c-a5e0-3d7b9c1f8a42
```

Set these four options in every tool that reads the files:

| Setting           | Value                                     |
| ----------------- | ----------------------------------------- |
| Delimiter         | Semicolon (`;`)                           |
| Encoding          | UTF-8, with a byte order mark             |
| Decimal separator | Full stop, with two decimals (`202.42`)   |
| Dates             | `YYYY-MM-DD`, in your account's time zone |

Read amounts in the currency's main unit. Refunds and platform fees are negative, so sum `Amount_Total` for net revenue or net proceeds.

The number of tax columns follows the tax rates used in the period: `Tax_Rate_1` and `Tax_Amount_1`, then `_2`, and so on. A period with a new rate adds a column. Refer to columns by name, never by position.

[Finance reports](/docs/concepts/reports/finance-reports#csv-export) describes every column, and which date column to sum for sold, delivered and returned revenue.

## Build a data model from the ledgers

Load the two ledgers as fact tables and join them on the revenue key:

| Table           | Key      | Joins to                            |
| --------------- | -------- | ----------------------------------- |
| Revenue ledger  | `Row_ID` |                                     |
| Payments ledger | `Row_ID` | `Revenue_Row_ID` → revenue `Row_ID` |

Group orders on `Order_ID`, and locations on `Location` and `Cost_Center`. Platform fees and payments with no order line have an empty `Revenue_Row_ID`, so keep the join a left join from payments.

`Row_ID` is stable across calls. Use it to de-duplicate when two calls cover overlapping periods.

## Read a built-in report instead

Copy the **JSON endpoint** from the **Export** tab of any report, with the filters you want applied. The response carries the report's `columns` and `rows`:

<CodeGroup>
  ```bash cURL theme={null}
  curl "https://server.twicecommerce.com/internal/reporting/reports/sales-overview/data?from=2026-08-28T21%3A00%3A00.000Z&to=2026-09-28T20%3A59%3A59.999Z" \
    -H "X-API-KEY: {key}"
  ```

  ```json Response (trimmed) theme={null}
  {
    "reportId": "sales-overview",
    "columns": [
      { "field": "id", "headerName": "Order ID" },
      { "field": "date", "headerName": "Date", "type": "date" },
      { "field": "customer", "headerName": "Customer" },
      { "field": "location", "headerName": "Location" },
      { "field": "total", "headerName": "Total", "type": "currency", "currency": "EUR" },
      { "field": "status", "headerName": "Status" }
    ],
    "rows": [
      { "id": "7d1c9e4a-3b2f-4c8e-9a61-5f0e2b7c4d13", "date": "2026-09-01", "customer": "Aino Virtanen", "location": "Helsinki", "total": 20242, "status": "open" },
      { "id": "2f6b8d10-9e3c-4a57-b1d4-6c8e0a2f7b95", "date": "2026-09-02", "customer": "Lumo Events Oy", "location": "Tampere", "total": 40000, "status": "closed" }
    ],
    "totalCount": 212,
    "truncated": false
  }
  ```
</CodeGroup>

Columns of type `currency` hold minor units (cents): divide by 100 for euros. `truncated: true` means the report hit the 1,000-row cap, so narrow the dates or use a ledger.

For a built-in report as CSV, call `/internal/reporting/reports/{reportId}/export` with `from` and `to`. It returns the same rows in the ledger file format.

## Stay inside the rate limits

Plan the refresh around the limits. A refresh that fires many calls at once hits them before the data does.

| Call                          | Limit per IP address | Limit per account |
| ----------------------------- | -------------------- | ----------------- |
| Ledger and report CSV exports | 5 per minute         | 10 per minute     |
| Report JSON data              | 30 per minute        | 60 per minute     |

Keep one refresh to five export calls or fewer. Every call also counts against your API key's allowance, described under [API keys](/docs/concepts/integrations/api-keys#rate-limits).

## How do I know it worked?

* The test call saves a CSV whose first line starts `Create_Date;Start_Date;End_Date`.
* The sum of `Amount_Total` where `Started_In_Period` is `Yes` matches the **Revenue report** in the admin for the same period, currency and **Started in period** recognition.
* The last line of the file is a data row, not a message.

## Troubleshooting / common pitfalls

<AccordionGroup>
  <Accordion title="The call returns 401 Unauthorized">
    The key is missing, mistyped, or deleted. Send it in a header named `X-API-KEY`, not in the URL, and check the key still exists under **Settings → Integrations & API**.
  </Accordion>

  <Accordion title="The call returns 400 Bad Request">
    A required parameter is missing or malformed. The ledgers need `from`, `to` and `currency`, and the dates must be plain `YYYY-MM-DD` without a time.
  </Accordion>

  <Accordion title="The last line reads &#x22;Export truncated at 100,000 rows&#x22;">
    The period holds more rows than one call returns. Split the period, for example one call per year, and append the results. `Row_ID` keeps the appended table free of duplicates.
  </Accordion>

  <Accordion title="The call returns 429 Too Many Requests">
    The refresh made too many calls in one minute. Wait the number of seconds in the `Retry-After` header, then cut the number of export calls per refresh to five or fewer.
  </Accordion>

  <Accordion title="The CSV endpoint on the Export tab returns JSON">
    The **CSV endpoint** shown on a report's **Export** tab returns the same JSON as the **JSON endpoint**. Replace `/data` in the path with `/export` and drop the `compareTo` parameter to get CSV.
  </Accordion>

  <Accordion title="The totals are 100 times too large">
    The data comes from a built-in report's JSON endpoint, which returns money in cents. Divide `currency` columns by 100. The ledger files are already in euros.
  </Accordion>

  <Accordion title="A column disappeared or a new one appeared">
    The tax columns follow the tax rates used in the period. Refer to columns by name, and treat `Tax_Rate_n` and `Tax_Amount_n` as optional.
  </Accordion>
</AccordionGroup>

## Related articles

<CardGroup cols={2} className="doc-rows-condensed">
  <Card title="Connect Power BI" href="/docs/guides/reports/connect-power-bi">
    Load the ledgers into Power BI and refresh them on a schedule.
  </Card>

  <Card title="Connect Google Sheets" href="/docs/guides/reports/connect-google-sheets">
    Load the ledgers into a sheet with Apps Script.
  </Card>

  <Card title="Finance reports" href="/docs/concepts/reports/finance-reports">
    Every column of the two ledgers, and how each number is derived.
  </Card>

  <Card title="API keys" href="/docs/concepts/integrations/api-keys">
    Creating keys, and the rate limits every key shares.
  </Card>

  <Card title="Reports" href="/docs/reports">
    Every built-in report and its Export tab.
  </Card>
</CardGroup>