Customers Read

pixiGetCRMCustomers

Returns CRM key figures per customer (order intervals, order counts, net revenue and return amounts per period) calculated from invoices and orders.

Calculates one row of customer lifetime statistics per customer, based on invoices and orders.

Invoices

Only invoices with Total > 0 are used as the base. They are filtered by InvDate between CalculationStart and CalculationEnd (each bound only if set), by the invoice shop InvShopID = CustomerOf (if not empty) and by CustRef (if not 0/NULL).
Amounts are net amounts (Total_Net, and Total_Net - Total_ShipCost_Net for the values without shipping costs).
Return amounts per invoice are 0 minus the sum of the net amounts of all invoices that reference it via RefToInvoiceKey (credit notes). These referencing invoices are not filtered by date or shop.
If the setting MultiCurrencyEnabled is 1, all amounts are divided by the exchange rate of the invoice (ExchRateId) and BaseCurrency contains the code of the base currency of that exchange rate. Otherwise the amounts are returned in the original invoice currency and BaseCurrency is NULL.

Orders

Orders are filtered by UpdateDate between CalculationStart and CalculationEnd, by the customer's CustomerOf and by CustRef. They are grouped by order number; the order date is the latest CreateDate of the order.
From these orders the first, second (next higher order number) and last order date per customer are determined.

Result

Per customer: days since first/last order and between first/second and first/last order (relative to the current date), order counts in the last 90/365/730 days and in total, amounts of the first, second and last invoice (columns named FirstOrder…, SecondOrder…, LastOrder…), amounts in the first and second year after the first invoice, in the 365 days before the last invoice, in the two 365-day windows before CalculationEnd (or now), and overall totals.
Only customers with at least one matching invoice are returned.
Paging: at most Rows rows with CustKey > Offset, sorted by CustKey ascending. Offset is a customer key, not a row count: for the next page pass the last CustKey of the previous page.

Notes

Read-only; all permanent tables except Customers are read WITH (NOLOCK), so uncommitted data may be included.
The columns FirstOrderTotal, SecondOrderTotal, LastOrderTotal etc. are taken from the first, second and last invoice (by InvoiceKey), not from orders.
Day statistics and the 90/365/730-day order counts are always relative to GETDATE(), while the …FromNow… amounts are relative to CalculationEnd (or the current date if it is not set).
Window bounds are partly exclusive: e.g. SecondYear… uses dates strictly after first invoice + 365 days and strictly before + 730 days, so invoices exactly on the boundary are not counted in either year.
The shop filter uses InvShopID of the invoice for amounts but CustomerOf of the customer for orders; date filters use InvDate for invoices but UpdateDate for orders.
With multi-currency enabled, invoices without a matching exchange rate are skipped. A customer with invoices in different base currencies appears once per base currency; the period sums (FirstYear…, LastYear…, etc.) are then not separated by currency.
DaysSince… columns are NULL if no order of the customer matches the filters.
Computation is done with several temporary tables and correlated subqueries; restricting the date range or CustRef improves performance on large databases.

Parameters

Parameter	Type	Default	Description
`CustomerOf`	varchar(3) string	NULL	Shop ID. Filters invoices by `InvShopID` and orders by the customer's `CustomerOf`. NULL or empty = all shops. Example: `SHP`
`CalculationStart`	datetime dateTime	NULL	Lower bound for invoice date (`InvDate`) and order update date (`UpdateDate`). NULL = no lower bound. Example: `2022-01-01T00:00:00`
`CalculationEnd`	datetime dateTime	NULL	Upper bound for invoice date and order update date. Also the reference date for the `…FromNow…` columns; if NULL, the current date is used as reference and there is no upper bound. Example: `2024-12-31T23:59:59`
`CustRef`	int	NULL	Customer key to restrict the calculation to one customer. NULL or 0 = all customers. Example: `10234`
`Rows`	int	1000	Maximum number of rows returned (page size). Example: `500`
`Offset`	int	0	Only customers with `CustKey` greater than this value are returned. Pass the last `CustKey` of the previous page to get the next page. Example: `0`

Response

Result set 1 · Always (may be empty if no invoices match)

One row per customer (and base currency) with CRM key figures, sorted by CustKey ascending.

Column	Type	Description
`CustKey`	int	Customer key.
`DaysSinceFirstOrder` Nullable	int	Days between the first order and today.
`DaysSinceLastOrder` Nullable	int	Days between the last order and today.
`DaysBetweenFirstAndSecondOrder`	int	Days between first and second order; 0 if there is no second order.
`DaysBetweenFirstAndLastOrder`	int	Days between first and last order; 0 if not available.
`Orders90days`	int	Number of orders created in the last 90 days.
`Orders365days`	int	Number of orders created in the last 365 days.
`Orders730days`	int	Number of orders created in the last 730 days.
`OrdersTotal`	int	Total number of matching orders.
`FirstOrderTotal` Nullable	decimal(19,9) decimal	Net amount of the first invoice.
`FirstOrderTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Net amount of the first invoice without shipping costs.
`FirstOrderReturnTotal` Nullable	decimal(19,9) decimal	Return amount (credit notes referencing the first invoice).
`FirstOrderReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Return amount of the first invoice without shipping costs.
`SecondOrderTotal` Nullable	decimal(19,9) decimal	Net amount of the second invoice.
`SecondOrderTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Net amount of the second invoice without shipping costs.
`SecondOrderReturnTotal` Nullable	decimal(19,9) decimal	Return amount of the second invoice.
`SecondOrderReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Return amount of the second invoice without shipping costs.
`LastOrderTotal` Nullable	decimal(19,9) decimal	Net amount of the last invoice.
`LastOrderTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Net amount of the last invoice without shipping costs.
`LastOrderReturnTotal` Nullable	decimal(19,9) decimal	Return amount of the last invoice.
`LastOrderReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	Return amount of the last invoice without shipping costs.
`FirstYearTotal` Nullable	decimal(38,9) decimal	Net invoice amount within 365 days after the first invoice.
`FirstYearTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `FirstYearTotal`, without shipping costs.
`FirstYearReturnTotal` Nullable	decimal(38,9) decimal	Return amount for invoices of the first year.
`FirstYearReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `FirstYearReturnTotal`, without shipping costs.
`SecondYearTotal` Nullable	decimal(38,9) decimal	Net invoice amount between 365 and 730 days after the first invoice (exclusive bounds).
`SecondYearTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `SecondYearTotal`, without shipping costs.
`SecondYearReturnTotal` Nullable	decimal(38,9) decimal	Return amount for invoices of the second year.
`SecondYearReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `SecondYearReturnTotal`, without shipping costs.
`LastYearTotal` Nullable	decimal(38,9) decimal	Net invoice amount within 365 days before (and including) the last invoice.
`LastYearTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `LastYearTotal`, without shipping costs.
`LastYearReturnTotal` Nullable	decimal(38,9) decimal	Return amount for invoices of that period.
`LastYearReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `LastYearReturnTotal`, without shipping costs.
`NextToLastYearFromNowTotal` Nullable	decimal(38,9) decimal	Net invoice amount between 730 and 365 days before `CalculationEnd` (or now).
`NextToLastYearFromNowTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `NextToLastYearFromNowTotal`, without shipping costs.
`NextToLastYearFromNowReturnTotal` Nullable	decimal(38,9) decimal	Return amount for invoices of that period.
`NextToLastYearFromNowReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `NextToLastYearFromNowReturnTotal`, without shipping costs.
`LastYearFromNowTotal` Nullable	decimal(38,9) decimal	Net invoice amount within the 365 days before `CalculationEnd` (or now).
`LastYearFromNowTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `LastYearFromNowTotal`, without shipping costs.
`LastYearFromNowReturnTotal` Nullable	decimal(38,9) decimal	Return amount for invoices of that period.
`LastYearFromNowReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `LastYearFromNowReturnTotal`, without shipping costs.
`OverallTotal` Nullable	decimal(38,9) decimal	Net amount of all matching invoices.
`OverallTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `OverallTotal`, without shipping costs.
`OverallReturnTotal` Nullable	decimal(38,9) decimal	Return amount for all matching invoices.
`OverallReturnTotalWithoutShipcosts` Nullable	numeric(17,4) decimal	As `OverallReturnTotal`, without shipping costs.
`BaseCurrency` Nullable	varchar(3) string	Base currency code of the amounts if `MultiCurrencyEnabled` = 1; otherwise NULL (amounts in original currency).

Response example

<SqlRowSet1>
  <row>
    <CustKey>10234</CustKey>
    <DaysSinceFirstOrder>812</DaysSinceFirstOrder>
    <DaysSinceLastOrder>45</DaysSinceLastOrder>
    <DaysBetweenFirstAndSecondOrder>63</DaysBetweenFirstAndSecondOrder>
    <DaysBetweenFirstAndLastOrder>767</DaysBetweenFirstAndLastOrder>
    <Orders90days>1</Orders90days>
    <Orders365days>3</Orders365days>
    <Orders730days>5</Orders730days>
    <OrdersTotal>6</OrdersTotal>
    <FirstOrderTotal>84.033613445</FirstOrderTotal>
    <FirstOrderTotalWithoutShipcosts>79.8319</FirstOrderTotalWithoutShipcosts>
    <FirstOrderReturnTotal>0.000000000</FirstOrderReturnTotal>
    <FirstOrderReturnTotalWithoutShipcosts>0.0000</FirstOrderReturnTotalWithoutShipcosts>
    <SecondOrderTotal>42.008403361</SecondOrderTotal>
    <SecondOrderTotalWithoutShipcosts>37.8067</SecondOrderTotalWithoutShipcosts>
    <SecondOrderReturnTotal>42.008403361</SecondOrderReturnTotal>
    <SecondOrderReturnTotalWithoutShipcosts>37.8067</SecondOrderReturnTotalWithoutShipcosts>
    <LastOrderTotal>125.210084034</LastOrderTotal>
    <LastOrderTotalWithoutShipcosts>121.0084</LastOrderTotalWithoutShipcosts>
    <LastOrderReturnTotal>0.000000000</LastOrderReturnTotal>
    <LastOrderReturnTotalWithoutShipcosts>0.0000</LastOrderReturnTotalWithoutShipcosts>
    <FirstYearTotal>210.084033613</FirstYearTotal>
    <FirstYearTotalWithoutShipcosts>197.4790</FirstYearTotalWithoutShipcosts>
    <FirstYearReturnTotal>42.008403361</FirstYearReturnTotal>
    <FirstYearReturnTotalWithoutShipcosts>37.8067</FirstYearReturnTotalWithoutShipcosts>
    <SecondYearTotal>125.210084034</SecondYearTotal>
    <SecondYearTotalWithoutShipcosts>121.0084</SecondYearTotalWithoutShipcosts>
    <SecondYearReturnTotal>0.000000000</SecondYearReturnTotal>
    <SecondYearReturnTotalWithoutShipcosts>0.0000</SecondYearReturnTotalWithoutShipcosts>
    <LastYearTotal>250.420168068</LastYearTotal>
    <LastYearTotalWithoutShipcosts>242.0168</LastYearTotalWithoutShipcosts>
    <LastYearReturnTotal>0.000000000</LastYearReturnTotal>
    <LastYearReturnTotalWithoutShipcosts>0.0000</LastYearReturnTotalWithoutShipcosts>
    <NextToLastYearFromNowTotal>85.210084034</NextToLastYearFromNowTotal>
    <NextToLastYearFromNowTotalWithoutShipcosts>81.0084</NextToLastYearFromNowTotalWithoutShipcosts>
    <NextToLastYearFromNowReturnTotal>0.000000000</NextToLastYearFromNowReturnTotal>
    <NextToLastYearFromNowReturnTotalWithoutShipcosts>0.0000</NextToLastYearFromNowReturnTotalWithoutShipcosts>
    <LastYearFromNowTotal>250.420168068</LastYearFromNowTotal>
    <LastYearFromNowTotalWithoutShipcosts>242.0168</LastYearFromNowTotalWithoutShipcosts>
    <LastYearFromNowReturnTotal>0.000000000</LastYearFromNowReturnTotal>
    <LastYearFromNowReturnTotalWithoutShipcosts>0.0000</LastYearFromNowReturnTotalWithoutShipcosts>
    <OverallTotal>545.714285715</OverallTotal>
    <OverallTotalWithoutShipcosts>520.5042</OverallTotalWithoutShipcosts>
    <OverallReturnTotal>42.008403361</OverallReturnTotal>
    <OverallReturnTotalWithoutShipcosts>37.8067</OverallReturnTotalWithoutShipcosts>
    <BaseCurrency>EUR</BaseCurrency>
  </row>
</SqlRowSet1>

Examples

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiGetCRMCustomers>
      <!-- <CustomerOf>SHP</CustomerOf> -->
      <!-- <CalculationStart>2022-01-01T00:00:00</CalculationStart> -->
      <!-- <CalculationEnd>2024-12-31T23:59:59</CalculationEnd> -->
      <!-- <CustRef>10234</CustRef> -->
      <!-- <Rows>500</Rows> -->
      <!-- <Offset>0</Offset> -->
    </ns1:pixiGetCRMCustomers>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiGetCRMCustomers>
      <!-- <CustomerOf>SHP</CustomerOf> -->
      <!-- <CalculationStart>2022-01-01T00:00:00</CalculationStart> -->
      <!-- <CalculationEnd>2024-12-31T23:59:59</CalculationEnd> -->
      <!-- <CustRef>10234</CustRef> -->
      <!-- <Rows>500</Rows> -->
      <!-- <Offset>0</Offset> -->
    </ns1:pixiGetCRMCustomers>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
    // 'CustomerOf' => 'SHP',
    // 'CalculationStart' => '2022-01-01T00:00:00',
    // 'CalculationEnd' => '2024-12-31T23:59:59',
    // 'CustRef' => '10234',
    // 'Rows' => '500',
    // 'Offset' => '0',
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiGetCRMCustomers', $soapArguments);

Tables used

Invoices · Read ExchRate · Read Currency · Read Orderheader · Read Customers · Read Settings · Read

SQL object:: dbo.pipiGetCRMCustomers