The problem: why do businesses need marketplace orders in their own database?
Because real management reporting has to combine marketplace orders with data only the business has: cost of goods, sales plans, receivables, sales-based pay. That combining happens in your database and cannot be done on the marketplace's pages.
The common approach is to hire someone to write a script that calls the marketplace API. The script runs for a few weeks, then stops because a token expired, the marketplace changed API version, or the author left the company. The other approach is importing files by hand every week, which means the data warehouse is always a week behind.
Quantri360 Partner takes on holding the app, the tokens and the normalization. You only keep the tables.
Thinking leads the way: what data do you need, and how do you read it correctly?
The table you receive is flat: one row per product in an order, with order columns repeated on every row. Understand this structure before writing your first query.
| Column group | Examples | Level |
|---|---|---|
| Keys | platform, platform_shop_id, order_id, item_line_id |
row |
| Status, timestamps | status, created_at, delivered_at (UTC) |
order |
| Order money | subtotal, platform_fee, escrow_amount |
order |
| Line money | item_quantity, item_unit_sale_price, item_line_voucher |
row |
| Write time | _synced_at |
row |
Column types are set by the system: money is DECIMAL(18,2) (PostgreSQL NUMERIC), timestamps are DATETIME(3) / TIMESTAMPTZ / DATETIME2(3), JSON is JSON / JSONB / NVARCHAR(MAX).
Three traps that produce wrong numbers on dashboards:
- SUM on an order-level column double counts. An order with 3 products repeats
escrow_amounton 3 rows.SUM(escrow_amount)across the table comes out three times too high. Order-level columns must first be grouped by order (MAXbyorder_id); only row-level columns should be summed withSUM. - A blank cell is not zero.
platform_feeandescrow_amountstay blank while the marketplace has not settled.COALESCE(x, 0)turns "unknown" into "free", and reconciliation will report a false match. - Times are stored in UTC. Daily reports in Vietnam time must add 7 hours before cutting by day, otherwise orders from midnight to 7 AM fall into the previous day.
Use item_line_id (the line ID issued by the marketplace) as the row key, not the row sequence number. With a partially cancelled order, "row 2" may be a different product next time, and a sequence-based key would overwrite the wrong row.
AI speeds it up: how long does setup take, and what are the steps?
About 30 minutes, and the longest part is creating the database user and opening the firewall. You do not need to write any code to write the data.
- Prepare the database: create a dedicated database and a user with permissions only on that database (CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE). The server must have a public address. Send the server's domain name or IP for Quantri360 to approve into the brand's allowlist.
- Sign up at Quantri360 Partner, then authorize your shop on TikTok Shop or Shopee.
- Destinations → MySQL, PostgreSQL or SQL Server: enter the host, port, database, user, password, table name and SSL mode. The password is encrypted and never shown again. Click Kiểm tra (Test): the system only reads the version and permissions, and reports any missing permissions.
- Connections: choose the platform → order type → destination → an hourly, daily or weekly schedule.
The first run creates the table on its own with a primary key of exactly the 4 key columns. Later runs add missing columns, overwrite rows with matching keys, and delete line items no longer in the order just delivered (this can be turned off in the destination form). The automatic schedule is a paid add-on: see the pricing.
Data type status: processed TikTok Shop and Nhanh.vn orders are available now. Processed Shopee orders are "Coming soon"; the raw type is available, with one row per order and a raw_json column holding Shopee's original response. There is also a BigQuery destination for Google Cloud users.
Query for daily revenue in Vietnam time, at the correct order level (MySQL):
SELECT DATE(created_at + INTERVAL 7 HOUR) AS ngay, COUNT(*) AS don, SUM(escrow) AS tien_ve
FROM (SELECT order_id, MIN(created_at) created_at, MAX(escrow_amount) escrow
FROM don_san WHERE status NOT IN ('cancelled','delivery_failed') GROUP BY order_id) d
GROUP BY ngay ORDER BY ngay;
Use AI to write queries. Paste the table structure (SHOW CREATE TABLE) into ChatGPT or Claude along with the rules:
"In this table each row is one SKU, and the columns subtotal, platform_fee and escrow_amount are order-level and repeat on every row. Times are stored in UTC. Write a PostgreSQL query: failed delivery rate by province and by week (Vietnam time) for the last 8 weeks. Do not replace NULL values with 0."
Stating the level of each column is the simplest way to stop AI from double counting.
Measurable results
The three metrics below can be measured right in your database. No outside data is needed.
- Latency:
NOW() - MAX(_synced_at)tells you how long ago the last write was. With an hourly schedule it is under about one hour. - Completeness: compare the number of orders in the table with the number on the seller page for the same period; the connection's run log records the order count and errors for each run.
- Discrepancies found: the gap between raw
SUM(escrow_amount)and the total by order shows how much the old report double counted.
No database of your own? Start with Google Sheets for TikTok Shop orders or Shopee. If you need to pull more APIs beyond the order table yourself, see Shopee with n8n.
Related APIs
- get_order_list (Shopee): source of the list of Shopee orders changed within a window.
- get_escrow_detail (Shopee): source of the money actually received and the fees on each order.
- orders/search (TikTok Shop): TikTok Shop order list by time range.
- price_detail (TikTok Shop): discounts and vouchers by line.
- order/list (Nhanh.vn): Nhanh.vn orders by update date.
Read more: business process automation, the overview article for this topic.