The storefront and the ERP have never met

The problem

Each system answered its own questions competently and none of them answered a business question. The storefront knew what sold and not what it cost. The ERP knew cost and margin and nothing about how anyone arrived. Analytics knew traffic and had no idea which of it turned into an order from a distributor. Anything spanning two of them meant somebody exporting spreadsheets and reconciling them by hand, which is slow enough that the question usually goes unasked. Reporting also could not be allowed to put the live storefront at risk: a reporting query is exactly the kind of thing that takes a production database down at the wrong moment.

What we built

• Built a reporting database the client owns, mirroring their commerce data rather than querying the live store: orders, lines, adjustments, customers, products, variants, categories and divisions
• Connected to the production database strictly read-only, over an SSH tunnel, with a SELECT-only account, so reporting can never write to or slow down the system taking orders
• Pulled cost per item across from the ERP, which is what turns a revenue report into a margin report
• Added the marketing side to the same mirror, so channel and search data sit beside the orders they produced instead of in a separate tool
• Refreshed the whole thing on a nightly schedule, so every report is answering from the same data as every other report
• Organised reports by the business question rather than the chart type, because an ecommerce manager asks who their best customers are, not to be shown a scorecard
• Made adding a report one route and one query against the mirror, so a new question does not mean touching the sync or renegotiating database access

Result

• Questions that span the storefront and the ERP get answered directly instead of being reconciled by hand in a spreadsheet
• Margin is reportable, not just revenue, because item cost comes across from the ERP
• Per-division and per-brand rollups for a business that sells through six divisions and two brands
• Win-back and under-penetration reporting: which customers have gone quiet, and which of them buy one line and not the obvious next one
• The team asks for a new report and gets it in a query rather than a project
• The database, the reports and the pipeline belong to the client, with no per-seat reporting licence

Automation running here

• Reporting that spans the storefront and the ERPA reporting database the client owns, fed nightly from the storefront, the ERP and the marketing data, so a question that needs two of them at once has somewhere to be asked. Cost comes across from the ERP, which is what makes margin reportable rather than just revenue.