Pull your data into a BI tool

The Export tab of a report, with the download buttons and the REST API endpoints — Reports > Sales overview > Export

Open in TWICE Admin

reports

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

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.

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.

Advanced reporting is available on the Standard and Enterprise plans. See plans.

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	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

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.

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. The ledger carries every location, so filter on its Location column in your tool.

https://server.twicecommerce.com/internal/reporting/revenue/export?from=2026-01-01&to=2026-09-30&currency=EUR

Test the URL with the key

Send the key in the X-API-KEY header. A working call returns the ledger as CSV.

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

Connect your tool

Point your tool at the URL with the key in the header. Follow the guide for your tool: Power BI, Excel, Google Sheets, Tableau or Looker Studio.

Read the ledger files

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

Revenue ledger, first rows

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

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}"

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

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.

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

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.

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.

The last line reads "Export truncated at 100,000 rows"

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.

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.

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.

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.

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.

Connect Power BI

Load the ledgers into Power BI and refresh them on a schedule.

Connect Google Sheets

Load the ledgers into a sheet with Apps Script.

Finance reports

Every column of the two ledgers, and how each number is derived.

API keys

Creating keys, and the rate limits every key shares.

Reports

Every built-in report and its Export tab.

Launch TWICE

Working in TWICE

How to pull your data into a BI tool

Open in TWICE Admin

Prerequisites

Pick the endpoint for the data you need

The walkthrough

Read the ledger files

Build a data model from the ledgers

Read a built-in report instead

Stay inside the rate limits

How do I know it worked?

Troubleshooting / common pitfalls

Connect Power BI

Connect Google Sheets

Finance reports

API keys

Reports

Open in TWICE Admin

​Prerequisites

​Pick the endpoint for the data you need

​The walkthrough

​Read the ledger files

​Build a data model from the ledgers

​Read a built-in report instead

​Stay inside the rate limits

​How do I know it worked?

​Troubleshooting / common pitfalls

​Related articles

Connect Power BI

Connect Google Sheets

Finance reports

API keys

Reports

Prerequisites

Pick the endpoint for the data you need

The walkthrough

Read the ledger files

Build a data model from the ledgers

Read a built-in report instead

Stay inside the rate limits

How do I know it worked?

Troubleshooting / common pitfalls

Related articles