---
topic: statistics
title: Statistics & reporting reads
summary: Orders, revenue, traffic and stock — the analytics tools first, run_sql_read for the rest, plus the gross/net and currency traps.
appliesTo: []
derivedFrom:
  - build/components/vue-installer/initial_schema.sql:565-595
  - build/components/vue-installer/initial_schema.sql:2063-2098
  - build/components/vue-installer/initial_schema.sql:179-218
  - includes/defines_inc.php:98-103
  - includes/src/Checkout/Bestellung.php:590-595
  - includes/src/Checkout/OrderHandler.php:297-332
  - includes/src/dbeS/Sync/Products.php:301
  - Plugin\tnt_mcp\src\Mcp\Analytics\OrderReader::salesSummary
  - Plugin\tnt_mcp\src\Mcp\Analytics\StatsReader::visitors
  - Plugin\tnt_mcp\src\Mcp\Db\SqlReadGuard::check
  - Plugin\tnt_mcp\src\Mcp\Db\ResultShaper::shape
---

## Use the analytics tools first

Nine tools cover the questions this guide used to answer with hand-written SQL, and they already
know the traps below. Reach for `run_sql_read` only for something they do not return.

- `analytics_summary` — sales, traffic, carts and top sellers in one call. Start here.
- `sales_summary` — revenue, order count, average order, a time series bucketed to the window.
- `list_orders` / `search_orders` / `get_order` — the order book, then one order in full.
- `top_products` — best sellers by net revenue or by units.
- `visitor_statistics` · `top_searches` · `cart_statistics` — traffic, on-site search, saved carts.

All of them take the same window vocabulary (`since: "7d"` / `"2026-07-01"`, `until`), and all of
them return a `notes` array stating which filters and defaults were applied. **Read `notes` before
quoting a number** — the window defaults to the last 30 days, so an unqualified call is a monthly
figure, not an all-time one.

## Domain model

An order is one row of `tbestellung` (`initial_schema.sql:565`), created at `dErstellt` (a
`datetime`, `:594`) with a state in `cStatus` (`char(2)`, `:593`) and a total in `fGesamtsumme`
(`:576`). Order lines are in `twarenkorbpos` (`:4825`), reached through `tbestellung.kWarenkorb`
(`:567`) — **not** through `kBestellung` — with `kArtikel` (`:4828`), `fPreis` (`:4835`) and
`nAnzahl` (`:4837`). Customers are `tkunde` (`:2063`); `nRegistriert` (`tinyint`, `:2098`) is 1 for a
registered account and 0 for a guest checkout, and `tkunde.dErstellt` is a `date`, not a `datetime`
(`:2094`) — the two `dErstellt` columns are not the same type. Products are `tartikel` (`:179`):
`fLagerbestand` (`:198`), `cLagerBeachten` (`:216`), `cLagerKleinerNull` (`:217`), `cArtNr` (`:194`),
`cName` (`:195`). Nothing is deleted on cancellation — a cancelled order keeps its row with
`cStatus = '-1'` (`BESTELLUNG_STATUS_STORNO`, `defines_inc.php:98`).

Visitors live in **two** tables at once: `tbesucher` is the live session table and `tbesucherarchiv`
the history, with the shop's cron moving rows between them. `visitor_statistics` merges and
de-duplicates them; a hand-written query against either one alone loses today or loses everything
before today.

## The three traps

1. **`fGesamtsumme` is already in the shop's default currency — do not divide by
   `fWaehrungsFaktor`.** The factor converts *out* of shop currency, not into it:
   `fGesamtsummeKundenwaehrung` is derived as `fGesamtsumme * fWaehrungsFaktor`
   (`Bestellung.php:590-595`), and the factor is the browsing currency's conversion factor captured
   at checkout (`OrderHandler.php:332`). `SUM(fGesamtsumme)` is therefore the correct shop-currency
   revenue, and dividing by the factor under-reports every foreign-currency order. (An earlier
   revision of this guide claimed the opposite; the code above is the evidence.)

2. **Gross and net are different columns and must not be added together.**
   `tbestellung.fGesamtsumme` is a gross order total; `twarenkorbpos.fPreis` is a **net unit** price.
   The tools name them apart on purpose — `grossRevenue` from `sales_summary`, `netRevenue` from
   `top_products` — and the two are not comparable. Never present one as a share of the other.

3. **Counting orders without filtering `cStatus` over-reports.** Cancellations are a state, not a
   delete, and `cStatus` is a nullable `char(2)` (`:593`) — `cStatus <> '-1'` also drops NULL-status
   rows, so use `(cStatus IS NULL OR cStatus <> '-1')` if legacy rows exist. The tools take
   `status: ["paid","shipped"]` by name and translate to the shop's codes for you; with no `status`
   they include cancellations, and say so in `notes`.

A fourth, for the search figures: `tsuchanfrage.nAnzahlGesuche` is a **lifetime** counter. The shop
keeps no per-day search history at all, so a window narrows *which terms* appear, never how often
they were searched. `top_searches` returns the field as `searchesAllTime` for exactly this reason.

## Walkthrough

Question: *"orders and revenue per month for the last 12 months, cancellations excluded."*

```
sales_summary({"since": "365d", "status": ["open","in_progress","paid","shipped","partially_shipped"]})
```

One call. `granularity` comes back as `"month"` (the bucket is chosen from the window length, so a
year is twelve rows and never 365), `series` holds the months, and `byStatus` and `byPaymentMethod`
break the total down. Then report the number **with its filter stated**: "€48,120 across 312 orders,
cancellations excluded, 2025-08 to 2026-08" is an answer; "€48,120" is a guess the merchant cannot
check.

For something the tools do not cover — stock, say, which is a read-only report here:

```
run_sql_read({"sql":
  "SELECT cArtNr, cName, fLagerbestand FROM tartikel
   WHERE cLagerBeachten = 'Y' AND cLagerKleinerNull <> 'Y' AND fLagerbestand <= 0
   ORDER BY fLagerbestand LIMIT 50"})
```

Before writing any SELECT, `describe_table({"table": "tbestellung"})` returns the live column list
plus this server's curated notes and `sampleQueries`. It is cheaper than a wrong query.

## What you cannot do here

**You cannot change anything from this guide.** Every tool named above is read-only, and
`run_sql_read` executes only a single rebuilt `SELECT` (`SqlReadGuard.php:162-190`); anything that is
not one statement, or not a SELECT, is rejected by reason code, as are `SELECT … INTO` (`:227-232`),
locking reads such as `FOR UPDATE` (`:248-260`) and `LOAD_FILE`/`SLEEP`/`BENCHMARK` (`:59-70`).

**The shop does not own product data — JTL-Wawi does.** `tartikel` is written by the Wawi sync,
`upsert('tartikel', $products, 'kArtikel')` (`dbeS/Sync/Products.php:301`), and there is no product
controller in `includes/src/Router/Controller/Backend/` at all. Stock, prices and article master data
are **readable here and never writable through this server**. A stock finding is an answer for the
merchant to act on in Wawi, not a change request for this MCP.

**Every result is capped.** `run_sql_read` defaults to 200 rows and hard-caps at 2000
(`SqlReadGuard.php:72-73`); the analytics tools cap their pages too and report `nextOffset` when more
exist. Compute aggregates in SQL — never by summing the rows you were handed, because the rows may be
a truncated page.

**Credential and session tables are unreachable**: `tadminlogin`, `tsession`, `tsynclogin`,
`tbrocken` and this plugin's token tables are denied outright (`SqlReadGuard.php:41-50`), including
through a JOIN or a subquery. Columns matching `pass|passwort|token|secret|hash|apikey` come back as
`***` even from an allowed table (`ResultShaper.php:26`).

**Guest carts do not exist in the database.** `cart_statistics` reads `twarenkorbpers`, the *saved*
cart of a logged-in customer; a guest's cart lives only in the session. Its value is also an estimate
at the article's current price, because the cart rows store no price of their own.
