Magento 2 sales_order: joins die refundfraude blootleggen

De CFO mailt op een dinsdagmiddag. Het refundpercentage is in zes maanden opgekropen van 3,1% naar 5,4%. Marketing is niet veranderd. De productmix is niet veranderd. Niemand in het supportteam herinnert zich iets anders. De Magento-admin laat credit memo's zien in het gebruikelijke tempo en de gebruikelijke omvang. Er staat niks in brand. Er klopt iets niet.

Het Magento-dashboard is niet gebouwd om dit te beantwoorden. Het toont credit memo's één voor één, gegroepeerd op wie de knop indrukte. Refundfraude ziet er bijna nooit verdacht uit in één losse regel. Het ziet er verdacht uit in de relaties tussen regels. Dus je sluit de admin en je opent een read replica.

Het schema waar je echt om geeft

De interessante tabellen voor refundforensics zijn kleiner dan de volledige sales-graph. Vijf dragen het meeste signaal:

• sales_order: de header-regel. De kolommen die ertoe doen zijn entity_id, increment_id, customer_id, customer_email, base_grand_total, total_refunded, status, created_at.
• sales_creditmemo: één regel per refund-document, gekoppeld aan de order via order_id.
• sales_creditmemo_item: de regelitems binnen elke credit memo.
• sales_order_payment: de betaalmethode en, afhankelijk van je gateway, een fingerprint van de kaart of wallet.
• sales_order_address: factuur- en verzendadressen, opgeslagen per order in plaats van per klant.

Wil je de veld-voor-veld-definitie die elke versie-upgrade overleeft, lees dan de db_schema.xml-bestanden van de Sales-module op de GitHub van Magento. Dat is de bron van waarheid; blogposts over het schema lopen er altijd achteraan.

Klanten met meer accounts dan zou moeten

Het schoonste externe signaal van refundfraude is één persoon die meerdere Magento-klanten draait. Zelfde verzendstraat, zelfde kaart, andere e-mail. De admin laat twee losse accounts zien; de database niet.

Begin met hergebruik van het verzendadres over customer_ids heen:

SELECT
  CONCAT(LOWER(TRIM(soa.street)), '|', soa.postcode, '|', soa.country_id) AS addr_hash,
  COUNT(DISTINCT so.customer_id)    AS customers,
  COUNT(DISTINCT so.customer_email) AS emails,
  SUM(so.base_grand_total)          AS lifetime_sales,
  SUM(so.total_refunded)            AS lifetime_refunded,
  ROUND(SUM(so.total_refunded) / NULLIF(SUM(so.base_grand_total), 0), 3) AS refund_rate
FROM sales_order so
JOIN sales_order_address soa
  ON soa.parent_id   = so.entity_id
 AND soa.address_type = 'shipping'
WHERE so.created_at >= NOW() - INTERVAL 12 MONTH
GROUP BY addr_hash
HAVING customers   >= 3
   AND refund_rate >= 0.25
ORDER BY lifetime_refunded DESC
LIMIT 50;

De vorm die terugkomt is niet subtiel. Een normaal huishouden heeft één of twee customer_ids op een adres over een jaar. Een refundring laat er zeven, twaalf, twintig zien. De kolom refund_rate is wat een nieuwsgierig resultaat omslaat in een onderzoek.

Refunds geboekt voor het geld is verplaatst

Magento staat offline credit memo's toe: een refund-document in het grootboek, zonder de payment gateway opdracht te geven om geld terug te sturen. Daar zijn legitieme redenen voor. Rembours, banktransfer-stornering die de boekhouding al heeft afgehandeld, store credit. Het is ook het pad dat een oneerlijke medewerker het vaakst gebruikt, omdat er geen echte transactie hoeft te clearen.

Adobe documenteert de mechaniek van offline refunds in zijn credit-memo-gids. Wat de gids niet doet, is je vertellen welke je moet bekijken. Deze query wel:

SELECT
  so.increment_id,
  so.customer_email,
  so.base_grand_total,
  scm.created_at         AS refunded_at,
  scm.grand_total        AS refund_amount,
  sop.method             AS payment_method,
  sop.last_trans_id      AS gateway_txn
FROM sales_creditmemo scm
JOIN sales_order         so  ON so.entity_id  = scm.order_id
JOIN sales_order_payment sop ON sop.parent_id = so.entity_id
LEFT JOIN sales_invoice  si  ON si.order_id   = so.entity_id
WHERE scm.created_at >= NOW() - INTERVAL 6 MONTH
  AND (si.entity_id IS NULL OR sop.last_trans_id IS NULL)
ORDER BY scm.created_at DESC;

De eerlijke gevallen in deze set herken je snel zodra je ze leest: rembours-orders, handmatige bankrefunds, store credit voor beschadigde goederen. Al het andere is een gesprek.

Adres-fingerprints over 'verschillende' klanten heen

De adres-join is krachtiger dan e-mailmatching, omdat adressen lastiger te spoofen zijn dan mailboxen. Een Gmail-truc met punten geeft één persoon tien e-mails. Een afleveradres is nog steeds waar iemand het pakket aanneemt.

Normaliseer voor je joint. Magento slaat adressen op zoals de klant ze intypte. Hoofdletters verschillen, 'Straat' tegenover 'straat', losse spaties aan het eind, optionele huisnummertoevoegingen. Twee regels die voor een mens identiek zijn, kunnen tot vier verschillende strings hashen.

Een MySQL-normaliser, goed genoeg voor een eerste pass:

CREATE OR REPLACE VIEW v_addr_norm AS
SELECT
  soa.parent_id AS order_id,
  soa.address_type,
  LOWER(TRIM(REGEXP_REPLACE(soa.street, '[[:space:]]+', ' '))) AS street_n,
  LOWER(TRIM(soa.postcode))                                    AS postcode_n,
  LOWER(TRIM(soa.country_id))                                  AS country_n,
  MD5(CONCAT_WS('|',
        LOWER(TRIM(REGEXP_REPLACE(soa.street, '[[:space:]]+', ' '))),
        LOWER(TRIM(soa.postcode)),
        LOWER(TRIM(soa.country_id))))                          AS addr_fp
FROM sales_order_address soa
WHERE soa.address_type IN ('billing', 'shipping');

Join die view terug aan sales_order overal waar je groepeerde op ruwe velden. De fingerprint-kolom geeft je een stabiele handvat op 'dit is hetzelfde adres', zelfs als de ene regel Damrak 1 zegt en de andere DAMRAK 1 .

Patronen aan de personeelskant

Interne refundfraude komt minder vaak voor dan externe ringen, maar verliest meer geld per incident. Het signaal is concentratie: refunds geclusterd buiten kantooruren, of één terminal die een disproportioneel aandeel hoogwaardige credit memo's produceert.

Open-source Magento legt de admin-gebruiker niet vast op de credit-memo-regel zelf. Op Adobe Commerce, of met de admin-action-log-module ingeschakeld, krijg je attributie. Zonder dat is de tijdsverdeling wat je hebt, en dat is genoeg om mee te beginnen:

SELECT
  DATE(scm.created_at)                                        AS day,
  SUM(CASE WHEN HOUR(scm.created_at) BETWEEN 8 AND 19 THEN 1 ELSE 0 END) AS in_hours,
  SUM(CASE WHEN HOUR(scm.created_at) NOT BETWEEN 8 AND 19 THEN 1 ELSE 0 END) AS out_of_hours,
  SUM(scm.grand_total)                                        AS refund_eur,
  SUM(CASE WHEN HOUR(scm.created_at) NOT BETWEEN 8 AND 19
           THEN scm.grand_total ELSE 0 END)                   AS refund_eur_out_of_hours
FROM sales_creditmemo scm
WHERE scm.created_at >= NOW() - INTERVAL 12 MONTH
GROUP BY day
HAVING out_of_hours > 0
ORDER BY refund_eur_out_of_hours DESC
LIMIT 60;

Je zoekt niet één regel, je zoekt een verdeling. Als het meeste volume binnen kantooruren valt en één dinsdag goed is voor 19% van de buiten-uren-refunds van het jaar, is dat de dag die je uit logs reconstrueert.

In een nightly job zetten

Geen van deze queries is duur op een gezonde sales_order met fatsoenlijke indexen op created_at, customer_email en parent_id. Draai ze als een set SQL-bestanden tegen de read replica, elke nacht om 02:30, schrijf de uitkomsten in een flagged_orders-tabel, en laat de operations lead 's ochtends de nieuwe regels zien.

Dat is de hele vorm. Replica, zes queries, één tabel, één e-mail. Geen nieuwe SaaS, geen fraude-detectievendor, geen machine-learning-model dat je niet aan de auditor kunt uitleggen.

Wat je deze week kunt doen

Run je een Magento 2-shop en heb je sales_order in het afgelopen kwartaal niet direct geopend, begin dan met de adres-fingerprint-query hierboven over een venster van twaalf maanden. Hij draait in tien minuten en is in twee minuten te lezen. De uitkomst is óf stil óf erg luid. Allebei zijn nuttig.

Toen wij dit voorjaar de refund-forensics-job bouwden voor een Magento-groothandelsklant, was het deel dat we onderschatten de adresnormalisatie; de helft van de ringen hield pas stand als cluster nadat we de regex-pass van hierboven hadden toegevoegd. Dat soort opschoonwerk is het grootste deel van wat een echt Magento-traject oplevert zodra je voorbij de demo bent.

Open een SQL-client. Wijs hem aan op de read replica. Draai de adres-fingerprint-query over de laatste twaalf maanden. Dat is de kleinste eerste stap.