Illustrative pattern · synthetic data
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
Related consulting page → · All platform demos · Book My BI Diagnostic
SQL join-key lab
CRM ↔ ERP match rates, orphans, and bookings vs billings on shared keys.
Illustrative pattern · synthetic data
Stack
- ERP customer table
- CRM account table
- Key bridge
- Conformed dim_customer
- Star schema
Illustrative pattern · synthetic data
Bridge pairs
Illustrative pattern · synthetic data
Orphans shown
Illustrative pattern · synthetic data
Bookings−billings
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
| Object | Type | Notes |
|---|---|---|
bridge_crm_erp_keys | Star-schema table | bridge |
dim_channel | Star-schema table | dimension |
dim_customer | Star-schema table | dimension |
dim_date | Star-schema table | dimension |
dim_product | Star-schema table | dimension |
dim_region | Star-schema table | dimension |
dim_sales_rep | Star-schema table | dimension |
fact_inventory_backlog | Star-schema table | fact |
fact_invoices | Star-schema table | fact |
fact_opportunities | Star-schema table | fact |
fact_opportunity_stage_hist | Star-schema table | fact |
fact_sales_orders | Star-schema table | fact |
fact_samples | Star-schema table | fact |
Total Revenue | SQL metric | certified · Finance |
Revenue YTD | SQL metric | certified · Finance |
Gross Margin | SQL metric | certified · Finance |
Gross Margin % | SQL metric | certified · Finance |
Open Backlog | SQL metric | certified · Operations |
Pipeline Value | SQL metric | certified · Sales |
Weighted Pipeline | SQL metric | certified · Sales |
Sample Conversion % | SQL metric | certified · Sales |
Draft Channel Mix | SQL metric | draft · Sandbox |
Uncertified Discount Guess | SQL metric | draft · Sandbox |
Bookings vs Billings Gap | SQL metric | draft · 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.
| Check | Table | Rows checked | Flagged | Status |
|---|---|---|---|---|
| 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
| Customer | Status | ERP id | CRM 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
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.
Executive KPIs
Leadership snapshot — revenue, margin, backlog, pipeline, and samples from one reconciled model.
Revenue YTD
Gross margin %
Open backlog
On-time (open lines)
Pipeline value
Sample conversion %
Revenue & margin % by month Combo
Revenue by channel Donut
Revenue by region & channel Stacked
Top customers
Backlog by aging
What the numbers are saying
Synthetic dataset for the SQL / ERP / CRM platform demo. Revenue and margin trends compare recent periods in this scenario. The point is leadership-ready, reconciled metrics—not a client report or live cloud tenant.
Uses the OpenAI API on synthetic demo numbers only. Public hosts apply rate limits to protect billing—private walkthroughs available on request.
Sales & margin
Where volume grows, where mix shifts, and which reps carry the book—with margin in view.
Revenue by month — top channels Stacked area
Revenue by month: latest year vs prior year YoY
Revenue by category
Sales rep leaderboard
Region performance
Customer performance
Rank, mix, and concentration—who grows revenue while eroding margin?
Customers: revenue vs. margin % Scatter
Customer rank (revenue)
Revenue by customer type
Sample-to-order
Does sampling activity convert into real revenue—and how fast?
Samples shipped by month
Conversion % by collection
Backlog & fulfillment
Where open demand is building and where fulfillment pressure is rising.
Open order value by month
Avg days late by category
CRM ↔ ERP
Pipeline stages and weighted forecast—does CRM tell the same story as orders?
Pipeline by stage
Weighted pipeline by month
Trust center
Refresh status, row counts, and quality checks that keep numbers defensible.
Last refresh
Saturday, November 21, 2026 11:43:33 AM
Row counts by table
Data quality checks
Computed from the demo tables when the app loads them. “warn” marks mismatches this scenario includes on purpose.
- pass Invoices have a customer key — 0 of 22,000 flagged (
fact_invoices) - pass Invoice customer keys exist in dim_customer — 0 of 22,000 flagged (
fact_invoices) - pass Invoice product keys exist in dim_product — 0 of 22,000 flagged (
fact_invoices) - pass Invoice dates exist in dim_date — 0 of 22,000 flagged (
fact_invoices) - pass Invoice revenue is not negative — 0 of 22,000 flagged (
fact_invoices) - pass Gross margin does not exceed revenue — 0 of 22,000 flagged (
fact_invoices) - pass Opportunity customer keys exist in dim_customer — 0 of 4,500 flagged (
fact_opportunities) - pass Customer keys are unique — 0 of 500 flagged (
dim_customer) - warn Customers matched between CRM and ERP — 90 of 500 flagged (
dim_customer) - pass Every month 2023-01..2026-12 has invoices — 0 of 48 flagged (
fact_invoices)
Semantic model
Shared tables and certified measures—the foundation for consistent answers and AI-ready Q&A.
Model tables
- dim_date
- dim_region
- dim_channel
- dim_sales_rep
- dim_customer
- dim_product
- fact_invoices
- fact_sales_orders
- fact_inventory_backlog
- fact_samples
- fact_opportunities
- bridge_crm_erp_keys
- fact_data_quality_events
- fact_pipeline_runs
- dim_semantic_measure
- fact_medallion_stats
- dim_warehouse_object
- demo_scenario
- data_dictionary
Certified measures
- Total Revenue
- Revenue YTD
- Gross Margin
- Gross Margin %
- Order Value
- Open Backlog
- Pipeline Value
- Weighted Pipeline
- Samples Shipped
- Converted Samples
- Sample Conversion %
Example natural language questions
- What is revenue YTD by channel?
- Which customers grew backlog the most?
- Show margin % trend by month.
- Who are the top reps by pipeline value?
- What is the CRM↔ERP match rate?
Illustrative pattern · synthetic data
How to present this