Skip to main content

Context Layer

Your ground truth

The Context Layer is the notes our data experts write about your warehouse: which date a sale counts on, which amount is revenue, which orders to leave out. Sovarium’s AI works from them on every question, so that its answers provide an unmatched level of accuracy.

Your warehouse

Messy data with no definitions, no business logic or context.

The Context Layer

Our data experts create your Context Layer, which maps out the business and data.

  • dt_2 means Date of sale
  • amt_1 means Full price, not revenue
  • ord_stat_cd means Misses some cancellations
  • src_sys_cd means Test orders, left out

Sovarium’s AI

Reads your warehouse through the layer before every answer, providing insights you can count on.

marketplace.orn_ord_hdr

18 columns

Description

Our note on marketplace.orn_ord_hdr: Order spine, one row per order. All money columns are bare amounts in ord_ccy_cd - never sum across regions without converting via lgcy.fx_daily_rate. None of the header amounts is settled revenue (that is pmt_txn.cap_amt), the header status misses ~15% of cancellations (check ord_cxl), and the sale timestamp is dt_2, not dt_1.

Purpose

Its purpose: Revenue reporting recipe: revenue = pmt_txn.cap_amt of the order's captured payment (pmt_stat_cd IN (2,5)), in ord_ccy_cd, converted to USD at the payment capture date via lgcy.fx_daily_rate; timed by dt_2. Exclude test orders (src_sys_cd='INTERNAL_TEST' OR the buyer/guest identity is a test account), exclude cancelled orders (an ord_cxl row exists OR ord_stat_cd=7), and net refunds in the period each refund settles.

  1. 1ord_idtextnot null
  2. 2cust_idtextnullable
  3. 3guest_ident_idtextnullable
  4. 4cust_email_hash_snaptextnot null
  5. 5ord_rgn_cdtextnot null
  6. 6ord_ccy_cdtextnot null
  7. 7dt_1timestamptznot null

    Our note: Order PLACED (buyer submitted checkout), UTC. Not the sale moment - payment may still fail or be abandoned.

  8. 8dt_2timestamptznullable

    Our note: Payment captured, UTC. THE authoritative 'when the sale happened' timestamp for revenue, cohorts and activity windows. Null when payment never captured (~2,700 orders stall at cart/placed).

  9. 9dt_3datenullable
  10. 10amt_1numeric(12,2)not null

    Our note: Gross: sum of line list prices before promo discounts. UK/EU orders: VAT-inclusive; US orders: excludes sales tax (see tax_amt). Not revenue.

  11. 11amt_2numeric(12,2)not null

    Our note: Net of promo/discount, same tax behavior as amt_1. Still not settled revenue: the captured amount (pmt_txn.cap_amt) routinely differs by small processor rounding and occasionally substantially on partial captures.

  12. 12tax_amtnumeric(12,2)not null

    Our note: US sales tax added at checkout. Always 0.00 for UK/EU orders because their tax is already inside amt_1/amt_2 - a zero here does not mean the order was untaxed.

  13. 13ord_stat_cdsmallintnot null

    Our note: Status code (set ORD_STAT): 1=cart, 2=placed, 3=paid, 4=shipped, 5=delivered, 6=complete, 7=cancelled, 8=refunded, 9=partially refunded. Unreliable for cancellation detection: ~15% of cancelled orders never had the header synced and still read 6=complete - always also check ord_cxl. Status code values are meaningful only within their own set; the digit 3 means paid here, delivered in shipping, paused in campaigns.

  14. 14fulfil_type_cdsmallintnot null
  15. 15src_sys_cdtextnot null

    Our note: WEB | APP | INTERNAL_TEST. Filtering only on INTERNAL_TEST misses about a third of true test volume - also exclude orders whose buyer resolves to cust_mst.is_test_acct = true.

  16. 16x_orig_ord_idtextnullable
  17. 17crt_tstimestamptznot null
  18. 18rec_upd_tstimestamptznot null

    Our note: Nightly batch stamp, not a change signal.

Example data.

Why do we need it?

Here is what the answers look like with and without it. Here is one question, answered with them and without them.

Asked: What was our busiest day of the week for orders in 2025?

AI onlyWrong

Frontier model (GPT-5.6 Sol), no Context Layer

Friday was our busiest day of the week for orders in 2025, with 858 orders, representing 16.57% of the year’s 5,178 orders.

Bar chart of orders by the day they were placed, Monday to Sunday: Monday 643, Tuesday 679, Wednesday 682, Thursday 700, Friday 858, Saturday 838, Sunday 778.

5,178 orders by dt_1, the day each was placed

SovariumRight

With Orion’s Context Layer

Saturday was the busiest day of the week for orders in 2025, accounting for 656 orders (16.60% of total annual order volume). …

Bar chart of orders by the day they were paid, Monday to Sunday: Monday 478, Tuesday 534, Wednesday 520, Thursday 521, Friday 633, Saturday 656, Sunday 609.

3,951 orders by dt_2, the day each was paid

What the layer told it4 notes from Orion’s layer

  • Columnorn_ord_hdr.dt_2

    Payment captured, UTC. THE authoritative 'when the sale happened' timestamp …

  • Columnorn_ord_hdr.dt_1

    Order PLACED (buyer submitted checkout), UTC. Not the sale moment - payment may still fail or be abandoned.

  • Tableord_cxl

    Cancellation records - the authoritative cancellation signal, one per cancelled order. …

  • Columnorn_ord_hdr.src_sys_cd

    WEB | APP | INTERNAL_TEST. Filtering only on INTERNAL_TEST misses about a third of true test volume …

Asked: Customers seem to spend more per order in the UK than in the US. Is that real?

AI onlyCouldn’t answer

Frontier model (GPT-5.6 Sol), no Context Layer

Order value: which order-header field represents customer spend—amt_1 or amt_2? The schema does not define them.

  • amt_1numeric(12,2)No description
  • amt_2numeric(12,2)No description

SovariumRight

With Orion’s Context Layer

No, the perceived higher spend in the UK is primarily an artifact of VAT inclusion and currency conversion rather than larger underlying basket demand.

  • amt_1numeric(12,2)Gross: sum of line list prices before promo discounts. …
  • amt_2numeric(12,2)Net of promo/discount, same tax behavior as amt_1. …

What the layer told it2 notes from Orion’s layer

  • Columnorn_ord_hdr.amt_1

    Gross: sum of line list prices before promo discounts. UK/EU orders: VAT-inclusive; US orders: excludes sales tax (see tax_amt). Not revenue.

  • Columnorn_ord_hdr.tax_amt

    US sales tax added at checkout. Always 0.00 for UK/EU orders because their tax is already inside amt_1/amt_2 - a zero here does not mean the order was untaxed.

Asked: How much money did we refund to customers in 2025?

AI onlyWrong

Frontier model (GPT-5.6 Sol), no Context Layer

In 2025, we refunded customers $26,926.74 USD, €11,379.71 EUR, and £8,729.01 GBP, across 571 refunds.

Read
rfnd
Refunded
$26,926.74 USD€11,379.71 EUR£8,729.01 GBP

571 refunds201 missed

SovariumRight

With Orion’s Context Layer

In calendar year 2025, Orion refunded a total of $65,697.61 USD across 772 settled refund transactions.

Read
rfndord_ln
Refunded
$65,697.61 USD

772 refunds

What the layer told it2 notes from Orion’s layer

  • Tablerfnd

    Refund/return records - NOT the complete refund population: ~30% of refund events exist only as negative order lines in ord_ln with no row here (see ord_ln for the combination rule). Refunds net against revenue in the fiscal period the refund settles (proc_ts), never retroactively against the original sale period.

  • Tableord_ln

    True total refunded for an order = SUM(rfnd.rfnd_amt) + SUM(-ln_amt of negative lines whose x_rfnd_ref is null or does not appear in rfnd). About 30% of refund events are negative-line-only; ~5% were double-booked in both places and share an x_rfnd_ref string - count those once. Summing both representations naively overstates refunds.

Asked: Of the guest shoppers we saw through the end of 2025, how many had registered a full account by mid-2026?

AI onlyWrong

Frontier model (GPT-5.6 Sol), no Context Layer

249 guest shoppers had registered a full account by June 30, 2026, representing 18.64% of the guest cohort seen through 2025.

Matched on
registered_cust_id

249 of 1,336 guests registered227 missed

SovariumRight

With Orion’s Context Layer

Of the 1,336 unique guest shoppers first seen through the end of 2025 (on or before December 31, 2025), 476 (35.6%) held a registered full account by mid-2026 (June 30, 2026).

Matched on
email_hash

476 of 1,336 guests registered

What the layer told it2 notes from Orion’s layer

  • Columnguest_checkouts.registered_cust_id

    Filled by an occasional manual reconciliation script, and only for about half of the guests who actually registered - do not rely on it; join email_hash directly to find guest-to-registered matches.

  • Columnguest_checkouts.email_hash

    Same hashing scheme as cust_mst.email_hash. When a guest later registers an account, the shared hash is the only reliable link - treat matching hashes as one person and fold guest orders into that customer's history.

Asked: How much did we spend on ads for our UK campaigns in 2025, and what was the ROAS?

AI onlyWrong

Frontier model (GPT-5.6 Sol), no Context Layer

UK campaigns spent £51,326.55 plus $30,825.49 on ads in 2025. Using last-touch attribution, ROAS was 0.079x for GBP campaigns and 0.197x for USD campaigns.

Spend
£51,326.55$30,825.49
ROAS
0.079x0.197x

SovariumRight

With Orion’s Context Layer

In calendar year 2025, Orion spent $96,859.57 USD on UK advertising campaigns, generating $9,767.77 USD in attributed revenue under the standard LAST-touch model for an overall ROAS of 0.10x (10.08%).

Spend
$96,859.57 USD
ROAS
0.10x

What the layer told it2 notes from Orion’s layer

  • Tablecmpgn

    … Budgets and all downstream spend are in bill_cur_cd - the central ad vendor's billing currency, which for ~70% of UK/EU-targeted campaigns is USD, NOT the target region's currency. Convert spend via lgcy.fx_daily_rate before comparing to order revenue (e.g. any ROAS) …

  • Columncmpgn.region_cd

    Target region: US, UK, EU or ALL. Does NOT imply the billing currency.

Asked: What was our average inventory level across the second quarter of 2026?

AI onlyWrong

Frontier model (GPT-5.6 Sol), no Context Layer

Average inventory during Q2 2026 was 193.02 units on hand per recorded day. This is based on 60 available daily snapshots, so coverage is incomplete and the result may not represent every day in the quarter.

193.02units on hand per recorded day

SovariumRight

With Orion’s Context Layer

An average inventory level for Q2 2026 cannot be computed because the underlying inventory snapshot data (ops.inv_snap) is captured at an irregular, incomplete cadence …

—cannot be computed

What the layer told it1 note from Orion’s layer

  • Tableinv_snap

    Inventory snapshots at an IRREGULAR cadence - short bursts of daily rows for some fast-moving listings, sparse weekly and back-dated backfill rows for the rest; platform-held stock only (no visibility into seller-held inventory). Never sum or average quantities across snap_dt: the only valid read is the latest snapshot per (listing_id, whse_id), which can itself be weeks stale.

Example data.

Ask about your busiest daywhat your customers spendyour refundsyour guest shoppersyour ad spendyour stock on your own data.

Tell us about your warehouse and your team. You connect it with a read-only user, our data experts write your Context Layer, then everyone asks, in the web app or in Slack.

Contact SalesView pricing