Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Read

pixiReport_NewCustomersStats

Returns a monthly report for one year that compares order counts, order values and averages of new customers with those of existing customers.

Builds a pivoted report with one row per report line and one column per month (Month01–Month12) plus an Average column.

Parameters and filters

  • Year defaults to the current year if NULL.
  • Shop (compared with Customers.CustomerOf) and Country (compared with the country of the order line address) are optional; an empty string is treated like NULL, i.e. no filter.

New customers A customer counts as new in a month if the earliest OrderDate of all his order lines (within the shop/country filter) lies in that month of Year. Only the order lines with exactly this first order date are treated as the new-customer order; further orders in the same month count as orders of existing customers.

Report lines (GroupNumber / LineNumber)

  • Group 1 – order counts:
    • 1 Orders new customers: number of customers whose first order falls in the month.
    • 2 Orders customers: line 3 minus line 1.
    • 3 Total: number of distinct order numbers in the month, excluding order lines with status STO or NLB.
    • 4 rate new customers: line 1 / line 3 × 100 (0 if line 3 is 0).
  • Group 2 – order values (sum of OrderLineAmount, order lines with status STO/NLB excluded):
    • 5 value of first orders of new customers.
    • 6 line 7 minus line 5.
    • 7 Total: value of all order lines of the month.
    • 8 share of new customers (line 5 / line 7 × 100), 9 share of existing customers (line 6 / line 7 × 100).
  • Group 3 – averages per order line:
    • 10 average OrderLineAmount of all candidate order lines in the month.
    • 11 average OrderLineAmount of the first-order lines of new customers.

Average column: the arithmetic mean of the monthly values that exist for the line.

Rows are sorted by LineNumber. The columns ParYear, ParShop and ParCountry echo the effective parameters; dbCurrency contains the database currency code if multi-currency is enabled.

Notes

  • Multi-currency: if the setting MultiCurrencyEnabled is 1, order values are converted into the database currency (setting HomeCurrency, exchange rate source from DatabaseCurrencyExchRateSourceId) using pixi.ExchRate of the order header's exchange date. Order lines without matching OrderHeader / exchange rate then drop out of the value lines. Otherwise the rate is 1 and dbCurrency is NULL.
  • Label pitfall: line 10 is labelled as average of new customers but averages all candidate order lines (including status STO/NLB), while line 11 is labelled as average of customers but averages the first-order lines of new customers. Lines 10 and 11 do not exclude STO/NLB order lines.
  • Averages in group 3 are per order line, not per order.
  • Lines 6, 7, 8, 9 and 10 only contain months that have order lines; other months stay 0 and are not included in Average, so Average is the mean of the months with data only.
  • First orders are determined without status filter; a customer whose earliest order line has status STO/NLB is still counted in line 1.
  • The new-customer determination uses inner joins on address and customer, so lines without country or CustomerOf are not considered, while the total lines accept NULL country/shop values.
  • A month can disappear from lines 2–4 if all its orders are removed by the filters.
  • All reads use NOLOCK (dirty reads possible). Line 3 joins order lines via MONTH(OrderDate), which scans the whole Orderlines table and can be slow on large databases.

Tables used

Orderlines · Read Addresses · Read Customers · Read OrderHeader · Read ExchRate · Read Settings · Read Currency · Read
SQL object:
dbo.pipiReport_NewCustomersStats
Updated at 2026-10-30