Aurea Quantra
SQL / ERP / CRM · synthetic demo · manufacturing & distribution scenario
Synthetic demo Saturday, November 21, 2026 11:43 AM All demos Consulting page Book My BI Diagnostic

Platform demo · SQL, ERP, CRM integration

Join-key lab: match rates, orphans, bookings vs billings

Inspect CRM↔ERP bridge confidence, unmatched keys, SQL preview of the star joins, and export orphans—so pipeline and invoices can finally agree.

How this maps to SQL / ERP / CRM

ERP/CRM data integration and semantic modeling.

  • Match-status rates and bridge confidence
  • Orphan key list with SQL preview
  • Bookings vs billings gap KPI
  • Synthetic invented companies—no client systems
Filters Invoice scope
Server CSV

Filters apply to revenue charts and the first two KPIs. Other tabs use the full demo model. Use Copy link to share this view.

SQL join-key lab

CRM ↔ ERP match rates, orphans, and bookings vs billings on shared keys.

Illustrative pattern · synthetic data

Stack

  1. ERP customer table
  2. CRM account table
  3. Key bridge
  4. Conformed dim_customer
  5. Star schema

Illustrative pattern · synthetic data

Exact match %

82

Illustrative pattern · synthetic data

Bridge pairs

443

Illustrative pattern · synthetic data

Orphans shown

50

Illustrative pattern · synthetic data

Bookings−billings

$316,767,992

Illustrative pattern · synthetic data


    

Illustrative pattern · synthetic data

Customers by match status

Illustrative pattern · synthetic data

Average bridge confidence % by status

Illustrative pattern · synthetic data

KPI recipe: gross margin % SQL

SELECT c.match_status,
       SUM(f.gross_margin) * 1.0 / NULLIF(SUM(f.revenue), 0) AS gross_margin_pct,
       COUNT(DISTINCT c.customer_key) AS customers
FROM fact_invoices f
JOIN dim_customer c ON c.customer_key = f.customer_key
GROUP BY c.match_status;

Illustrative pattern · synthetic data

Semantic objects

ObjectTypeNotes
bridge_crm_erp_keysStar-schema tablebridge
dim_channelStar-schema tabledimension
dim_customerStar-schema tabledimension
dim_dateStar-schema tabledimension
dim_productStar-schema tabledimension
dim_regionStar-schema tabledimension
dim_sales_repStar-schema tabledimension
fact_inventory_backlogStar-schema tablefact
fact_invoicesStar-schema tablefact
fact_opportunitiesStar-schema tablefact
fact_opportunity_stage_histStar-schema tablefact
fact_sales_ordersStar-schema tablefact
fact_samplesStar-schema tablefact
Total RevenueSQL metriccertified · Finance
Revenue YTDSQL metriccertified · Finance
Gross MarginSQL metriccertified · Finance
Gross Margin %SQL metriccertified · Finance
Open BacklogSQL metriccertified · Operations
Pipeline ValueSQL metriccertified · Sales
Weighted PipelineSQL metriccertified · Sales
Sample Conversion %SQL metriccertified · Sales
Draft Channel MixSQL metricdraft · Sandbox
Uncertified Discount GuessSQL metricdraft · Sandbox
Bookings vs Billings GapSQL metricdraft · Sandbox

Illustrative pattern · synthetic data

Data-quality checks

Computed from the demo tables when the app loads them. “warn” marks mismatches this scenario includes on purpose.

CheckTableRows checkedFlaggedStatus
Invoices have a customer key fact_invoices 22,000 0 pass
Invoice customer keys exist in dim_customer fact_invoices 22,000 0 pass
Invoice product keys exist in dim_product fact_invoices 22,000 0 pass
Invoice dates exist in dim_date fact_invoices 22,000 0 pass
Invoice revenue is not negative fact_invoices 22,000 0 pass
Gross margin does not exceed revenue fact_invoices 22,000 0 pass
Opportunity customer keys exist in dim_customer fact_opportunities 4,500 0 pass
Customer keys are unique dim_customer 500 0 pass
Customers matched between CRM and ERP dim_customer 500 90 warn
Every month 2023-01..2026-12 has invoices fact_invoices 48 0 pass

Illustrative pattern · synthetic data

Key mismatches / orphans

CustomerStatusERP idCRM id
Customer 001 erp_only CUST-10000
Customer 004 erp_only CUST-10003
Customer 005 erp_only CUST-10004
Customer 015 erp_only CUST-10014
Customer 023 crm_only ACC-20022
Customer 027 crm_only ACC-20026
Customer 029 crm_only ACC-20028
Customer 031 fuzzy CUST-10030 ACC-20030
Customer 035 erp_only CUST-10034
Customer 042 fuzzy CUST-10041 ACC-20041
Customer 055 fuzzy CUST-10054 ACC-20054
Customer 064 erp_only CUST-10063
Customer 068 crm_only ACC-20067
Customer 069 erp_only CUST-10068
Customer 072 erp_only CUST-10071
Customer 075 fuzzy CUST-10074 ACC-20074
Customer 095 erp_only CUST-10094
Customer 102 crm_only ACC-20101
Customer 107 fuzzy CUST-10106 ACC-20106
Customer 116 erp_only CUST-10115
Customer 121 fuzzy CUST-10120 ACC-20120
Customer 122 crm_only ACC-20121
Customer 126 crm_only ACC-20125
Customer 133 erp_only CUST-10132
Customer 137 crm_only ACC-20136
Customer 138 fuzzy CUST-10137 ACC-20137
Customer 156 erp_only CUST-10155
Customer 158 crm_only ACC-20157
Customer 163 fuzzy CUST-10162 ACC-20162
Customer 164 fuzzy CUST-10163 ACC-20163
Customer 168 fuzzy CUST-10167 ACC-20167
Customer 171 fuzzy CUST-10170 ACC-20170
Customer 173 fuzzy CUST-10172 ACC-20172
Customer 179 erp_only CUST-10178
Customer 190 erp_only CUST-10189
Customer 198 erp_only CUST-10197
Customer 208 erp_only CUST-10207
Customer 210 crm_only ACC-20209
Customer 217 fuzzy CUST-10216 ACC-20216
Customer 218 fuzzy CUST-10217 ACC-20217
Customer 224 fuzzy CUST-10223 ACC-20223
Customer 230 crm_only ACC-20229
Customer 232 crm_only ACC-20231
Customer 239 erp_only CUST-10238
Customer 243 erp_only CUST-10242
Customer 251 fuzzy CUST-10250 ACC-20250
Customer 256 fuzzy CUST-10255 ACC-20255
Customer 259 fuzzy CUST-10258 ACC-20258
Customer 263 fuzzy CUST-10262 ACC-20262
Customer 270 fuzzy CUST-10269 ACC-20269

Illustrative pattern · synthetic data

How to present this

  • Example join: dim_customer.crm_account_id = bridge.crm_account_id AND bridge.erp_customer_id = dim_customer.erp_customer_id
  • Star join sketch: fact_invoices ⋈ dim_customer ⋈ dim_date ⋈ dim_product
  • Export unmatched keys as CSV from the control below.

Illustrative pattern · synthetic data

What the AI insight is told about this platform

Platform lens: SQL join-key lab. Emphasize reconciliation of pipeline, orders, and invoices.

The model also receives the synthetic KPIs, the current invoice slice and the semantic model summary.