← Back to portfolio
O2C Reconciliation Modernization
L1 β€” Complete
Process Mapping β€” the Plain-English Version
L0 found where money and data get lost inside 99,441 orders. This layer turns those SQL findings into a picture β€” no finance or operations background required to follow it.
🎯 Goal: findings β†’ process picture πŸ›  Method: plain-language flow + Lucidchart BPMN spec πŸ“… Completed: Jul 2026
Contents
01
The four stages every order goes through
Strip away the internal system jargon β€” this is what actually happens
Every online order β€” at Olist, or anywhere β€” moves through the same four stages, regardless of which team or system owns each one.
Four stages of an order, as-is You order, payment goes through, it ships, money settles β€” with three warning flags placed where L0 found problems. πŸ›’ You order Customer πŸ’³ Payment goes through Processor πŸ“¦ It ships to you Order system πŸ’° Money settles Finance 625 canceled orders kept the money β€” R$143,255 14 orders approved late all in Jan–Feb 2017 775 orders no record of what shipped
πŸ’‘ The technical name for this kind of picture is a BPMN swimlane diagram β€” each stage sits in a lane owned by a different team or system. You don't need to remember the name, just the four boxes and the three flags. The full 8-step technical version (split by system event, for writing SQL exception queries) lives in the Lucidchart spec used for L2.
02
Where the money currently leaks
Straight from the L0 SQL findings β€” no sampling, no estimation
πŸ”΄ Biggest leak
625 orders were canceled, but the money was never given back. R$143,255 is sitting in limbo β€” nobody in the current process decided whether to refund it, write it off, or apply it to a replacement order.
⚠ Data gap
775 orders have no record of what actually shipped. If a customer disputes what they received, there's nothing in the system to check it against.
⚠ System event
14 orders were marked "delivered" without ever being formally approved. All 14 cluster in a two-month window (Jan–Feb 2017) β€” that pattern points to a system outage, not 14 separate human mistakes.
03
The fix β€” what changes
A computer checks every order; a person only reviews what doesn't add up
None of this needs a person checking every order. It needs a computer to check every order, and a person to look only at the ones that don't add up.
Four stages of an order, to-be The first two stages stay the same. After payment settles, three new automated steps replace manual reconciliation, ending in a daily report only for flagged orders. Order placed β†’ ships to you Unchanged from today Money settles Processor Computer checks the numbers Order total vs payment total Sorts by problem type Canceled, missing, underpaid... Person reviews the flagged ones ~0.7% of orders, not 100% Daily report Next morning
TodayAfter the fix
Someone finds problems only when they go looking, usually weeks laterProblems surface automatically the day they happen
Every order gets treated the same, whether it's fine or notOnly the ~0.7% of orders that don't add up need a human look
No consistent label for what kind of problem it isEvery flagged order comes pre-sorted: canceled-but-paid, missing shipment, underpaid, etc.
04
At a glance
The technical BPMN version (8 steps, 2 lanes) lives in the Lucidchart spec for L2
4plain-language stages mapped to the real process
3leak points pinpointed from L0 findings
~0.7%of orders would need human review under the fix