The killer reconciliation finding for mid-market ecom ops: dollar value of orders that never made it to Business Central GL plus the reason.
At a glance
The dollar value of commerce-platform revenue that has not yet hit the Business Central General Ledger, broken down by reason. Calculated as commerce_total_revenue minus business_central_revenue_booked_gl for the same window, with reason codes attached to each missing dollar.
| What it counts | The signed gap between the sum of total_revenue across every connected commerce platform (Shopify, BigCommerce, Adobe Commerce) and revenue_booked_gl in Business Central for the matching window. The reason breakdown classifies each missing dollar into one of: Released-Not-Invoiced, Voided / Cancelled, Sales Credit Memo Refund, Unmapped Marketplace, Period Boundary Timing, FX Translation, or Unknown. |
| VAT / tax treatment | Net of tax on both sides. Commerce platform tax is stripped if the platform reports tax separately (Shopify taxesIncluded = false, BigCommerce default, Adobe Commerce). Inclusive-tax modes are normalised at compute time. The card compares net-of-tax to net-of-tax, otherwise the gap would always show a 20% UK VAT mismatch. |
| Shipping | Both sides include shipping revenue. Mismatch only occurs if shipping is mapped to a non-revenue G/L account in BC. |
| Sales Credit Memos | Counted on both sides. Commerce gross is subtracted by refunds for parity, BC Sales Credit Memos are pulled in via the salesCreditMemos OData endpoint. |
| Currency | Commerce-platform revenue is FX-translated to the BC Reporting Currency at period-average rate. A residual FX-translation gap appears in the breakdown as FX Translation if the currency mix is large. |
| Multi-Company | Multi-Company tenants: card sums across all connected Companies that have commerce platforms tagged to them in the field map. Single-Company: trivially scoped. |
| Power BI complementarity | Power BI’s BC content pack does not cross-reference commerce platforms. This card is the join Power BI cannot make. |
| Time window | 30D (no vsP comparison, the gap itself IS the trend) |
| Alert trigger | >$10k unreconciled (default), tunable per workspace |
| Sentiment key | revenue_gap |
| Roles | owner, finance |
Calculation
Calculated automatically from your Microsoft Dynamics 365 data. See the At a glance summary above for what the metric tracks and the worked example below for a typical reading.Worked example
A US wholesale apparel distributor (annual revenue ~$70M) running Business Central in a single tenant with Microsoft 365 E5. Connected commerce platforms: Shopify Plus DTC, BigCommerce B2B with Net-30 terms, Adobe Commerce wholesale portal. The 30-day window covers 14 Mar 26 to 12 Apr 26.| Source | Window total (USD, period-avg FX) |
|---|---|
| Shopify Plus, DTC | $1,940,000 |
| BigCommerce, B2B Net-30 | $3,420,000 |
| Adobe Commerce, wholesale portal | $1,180,000 |
| Commerce gross (sum) | $6,540,000 |
| Business Central GL booked revenue | $5,860,000 |
| Gap (this card) | $680,000 |
| Reason | Amount | Explanation |
|---|---|---|
| Released-Not-Invoiced | $392,000 | 41 BigCommerce B2B Sales Orders shipped (Document Type = Order, Status = Released) but not yet posted as Invoices. Net-30 ship-to-bill lag. |
| Sales Credit Memo Refunds | $118,000 | 84 Sales Credit Memos posted in window against orders booked in commerce gross. |
| Voided / Cancelled | $54,000 | 12 cancelled commerce orders that show in the platform’s lifetime totals but voided in BC. |
| Unmapped Marketplace | $48,000 | Adobe Commerce wholesale portal has 7 customer accounts not yet mapped to BC Customer records, so the SOs failed to import via the AL Extension connector. |
| Period Boundary Timing | $34,000 | Orders placed 11pm 12 Apr 26 in the commerce platform; rolled into BC 13 Apr 26 due to the integration scheduler. |
| FX Translation | $22,000 | Adobe Commerce captured GBP transactions at spot rate; BC posted at month-average via Adjust Exchange Rates. |
| Unknown / Investigate | $12,000 | 4 BC orders flagged but no audit trail in the AL connector log. |
- The biggest leak is Released-Not-Invoiced ($392K). This is not lost revenue, it is stuck working capital. Shipping has happened, customer is on the hook, but no Posted Sales Invoice means no Customer Ledger Entry, no DSO clock, and no GL booking. The card lets the Controller drill into the SO list and chase Operations to post the invoices.
- Unmapped Marketplace ($48K) is real revenue at risk. If the Customer mapping in the AL Extension connector is wrong on Adobe Commerce, those orders may never sync. The Controller pings the BC Functional Consultant to fix the mapping; otherwise next month’s gap is even bigger.
- Sales Credit Memo Refunds ($118K) is policy, not error. This is the structural gap between gross commerce headlines and net GL booking. It will always exist; what matters is whether the rate is constant or rising. Rising = quality / fulfilment / pricing problem. Constant = baseline noise.
- The $12K Unknown bucket is the leakage signal. It is small enough not to alarm but it is where reconciliation problems live. The Controller hands this list to the BC Functional Consultant every Monday morning.
- Alert fires at >$10K unreconciled in the Unknown bucket. Released-Not-Invoiced and Sales Credit Memos are explained gaps; only Unknown trips the sentiment.
Sibling cards merchants should reference together
This card is the trigger; the action lives on its companions.| Card | Why pair it with Revenue Gap vs Commerce |
|---|---|
| Revenue Gap, Detailed Breakdown | The drill-down. Each missing dollar with the originating commerce order ID and the BC-side reason. The Controller’s daily worklist. |
| Revenue Booked into GL | The denominator on the BC side. Tells you how big the absolute booked revenue is so the gap percentage makes sense. |
| Open Sales Order Value | Released-Not-Invoiced’s forward-looking sibling. Shows what is queued to become GL revenue. |
| Sales Orders Blocked | Why Released-Not-Invoiced is stuck. Inventory holds, credit holds, missing approvals. |
| Commerce Orders Without BC Sales Order | Specifically the Unmapped Marketplace bucket. The list of commerce orders without a paired BC Sales Order. |
| Sales Credit Memo Value | The Credit Memo Refunds bucket as its own time series. Tells you whether Credit Memo volume is rising structurally. |
| shopify.total_revenue | The DTC half of the commerce-side numerator. |
| bigcommerce.total_revenue | The B2B half on BC platforms. |
Reconciling against the vendor’s own dashboard
Where to look in Business Central: There is no single native BC report for this metric, that is precisely why Vortex IQ surfaces it. Power BI’s BC content pack also has no equivalent because Power BI does not connect to the commerce platforms (it could, with a custom data flow, but no out-of-the-box Microsoft tile does this). The closest manual approach inside BC is:Run a Sales Order List view (Search > “Sales Orders”) with status filterA BC Functional Consultant can build this view as a saved configuration in 10 minutes. Most do not, because the cross-platform comparison requires pulling commerce data manually, and the moment you do that you have lost reproducibility. Vortex IQ runs this every 15 minutes via the OData v2.0 API. Adjacent BC reports that look related but are not:Released, sum theAmountcolumn. Then compare manually to the commerce platform’s last-30-day total revenue export.
- Sales Statistics: shows all open SOs but without commerce-side comparison.
- Customer Ledger Entries: AR-based, looks at unpaid Invoices not pre-Invoice gap.
- Sales by Customer: groups posted revenue by Customer No., loses the per-order traceability.
- Power BI Sales Insights tile: smooths the gap into a monthly trendline; does not include commerce data.
| Reason | Direction | Why |
|---|---|---|
| OData sync lag | Card may overstate gap | BC OData v2.0 sync runs every 5 to 15 minutes. A commerce order placed in the last sync window appears in commerce gross but not yet in the BC Sales Order list. |
| Customer mapping gaps | Card overstates gap | If a commerce customer does not yet have a BC Customer record, the SO cannot be created by the AL Extension connector. The integration logs this as Unmapped Marketplace and the gap shows the gross order. Until the BC admin maps it, the gap persists. |
| FX rate cadence | Small | Commerce platform may capture spot rate; BC may consolidate via the Adjust Exchange Rates batch at period-average. The FX Translation bucket isolates this. |
| Tax reclassification | Either | If the commerce platform sends an inclusive-tax order and BC’s VAT Posting Setup recomputes at a different jurisdiction (e.g. shipping address state), the line totals differ even though gross paid is the same. |
| Period boundary | Card overstates by 1 to 2 days of orders | Orders placed late on the last day of the window are in commerce gross but not yet in BC GL. They show up on the next period’s reconciliation. |
| Multi-Company scope mismatch | Card understates gap | If only some Companies are connected via the field map, commerce platforms tagged to unconnected Companies are excluded. |
Known limitations / merchant FAQs
Should this gap ever be zero? No, and you should be suspicious if it is. A small structural gap (Released-Not-Invoiced + Sales Credit Memos + period-boundary timing) is healthy. Zero gap means either the AL Extension connector is not pulling new commerce orders, or someone is force-posting the books before reconciliation. Aim for a stable gap that scales with revenue, with the Unknown bucket near zero. How big should the gap be vs total revenue? A typical mid-market commerce business on BC runs at 8 to 15% gross commerce vs net GL revenue gap inside a 30-day window:- Released-Not-Invoiced: 3 to 8% (higher for B2B Net-30, lower for DTC).
- Sales Credit Memo Refunds: 4 to 10% (varies by category, apparel skews high; electronics low).
- Voided / Cancelled: 1 to 3%.
- Unknown: ideally < 0.5% of commerce gross.
Revenue Recognition Deferred in the breakdown if the AL Extension reports it, which is policy-correct, not a defect.
Multi-currency stores: does the FX Translation bucket overstate?
The bucket isolates the residual after period-average FX is applied. If your Companies transact in 5+ currencies, expect the FX bucket to be 0.5 to 2% of commerce gross. Anything larger usually means the field map’s FX rate source is misconfigured.
What does the alert look like in practice?
At >$10K Unknown, the Nerve Centre fires a sentiment alert and Ask Viq surfaces the question “What’s the £14K reconciliation drift this week?” with a deep-link to the breakdown card. The Controller typically resolves it in 15 to 30 minutes by mapping a missing Customer or escalating a stuck SO.
Multi-Company vs single-Company tenants, does the card behave differently?
Same logic, simpler scope. Single-Company tenants skip the FX Translation bucket and the elimination logic. Most fields are identical.