SQL Business Metrics

Overview

Two Channels, Inverted

Online orders made up 87.9% of AdventureWorks orders but only 26.7% of merchandise revenue, and in July 2013 the online channel shifted almost overnight from bike orders to accessories and clothing.

I built this project on Microsoft’s AdventureWorks2022 sample database. The pipeline is SQL Server → SQL modules → JSON → Python → interactive HTML. SQL handles the business logic and aggregation. JSON provides a structured interchange layer. Python validates and reshapes the exported results, builds the Plotly charts and tables, and renders the page.

The dataset contains 31,465 sales orders, 19,119 customers with orders, 266 products sold, 17 salespeople and 10 sales territories. Across the full recorded period, merchandise revenue totals $109.85 million, with an average order value of $3,491.

AdventureWorks is a fictional company, Adventure Works Cycles, which Microsoft describes as a multinational manufacturer that produces and distributes bicycles, parts and accessories. Microsoft publishes the database as sample data for SQL Server and its analytics tutorials (Microsoft, sample databases; Microsoft, Internet Sales tutorial).

• $109.85MMerchandise revenue
• 31,465Orders
• 19,119Customers with orders
• $3,491Average order

Data

Data and Analytical Scope

I used AdventureWorks2022. Microsoft notes that the database has not changed significantly since the 2012 version apart from its name and compatibility level, so the original column documentation still applies. The 2025 release adjusted the dates, so my dates won’t match that version (Microsoft, AdventureWorks readme).

The raw sales history runs from May 31, 2011 through June 30, 2014. I use the complete dataset for full-period figures, but monthly comparisons use June 2011 through May 2014, a 36-month window.

The two boundary months

May 2011 contains only one day of data, so it cannot be compared fairly with full months. An incomplete period plotted beside complete ones creates a change that did not happen. Few makes that point about an incomplete final period, and I applied the same principle at the start of the series (Few, 2015). Microsoft’s Power BI documentation makes the same distinction when it recommends selecting only complete calendar months for month-to-month comparison (Microsoft, Power BI date filters).

June 2014 is a complete calendar month, but it is not a comparable business period. In May 2014 the database still records 2,232 online orders and 179 sales-assisted orders, with $3.42 million in merchandise revenue. In June, the sales-assisted channel disappears entirely. The month contains 939 online orders, $49,005.84 in merchandise revenue and an average order value of $52.19. The issue is not that June has fewer days; the recorded business stops halfway through it. I therefore end the monthly comparison window at May 2014.

Method

One Definition of Revenue

AdventureWorks exposes several monetary fields on SalesOrderHeader. Microsoft documents SubTotal as the sum of the order’s line totals, TaxAmt and Freight as separate charges, and TotalDue as all three combined (Microsoft, SalesOrderHeader table).

On this page, revenue means merchandise revenue: SubTotal at the order level and LineTotal at the detail level. Tax and freight are excluded, which keeps order-level and product-level figures on the same basis.

Excluding tax follows ASC 606, which defines the transaction price as excluding amounts collected on behalf of third parties, such as some sales taxes (FASB, 2014, para. 606-10-32-2; Deloitte, Roadmap §6.7). Those sources do not say freight must be excluded, and I am not using them for that. Freight is excluded because I chose to define the measure as product sales revenue.

Results

Sales Channels

AdventureWorks separates online orders from salesperson orders through OnlineOrderFlag. Microsoft documents a value of 1 as an order placed online by the customer and 0 as an order placed by a salesperson (Microsoft, SalesOrderHeader table). I label the second group Sales-Assisted rather than “Non-Online,” because that describes what the field actually records.

Online accounts for 27,659 orders, or 87.9% of all orders, but generates only $29.36 million, or 26.7% of merchandise revenue, at about $1,061 per order. Sales-Assisted accounts for 3,806 orders, or 12.1%, but produces $80.49 million, or 73.3% of revenue, at about $21,148 per order.

Why monthly revenue zigzags

Sales-Assisted order counts are extremely uneven. Across the comparison window they range from zero (June, September and November 2011) to 271 orders per month. February 2014 has only 3 sales-assisted orders, March jumps to 271, and April drops to 2. Because each sales-assisted order is worth about twenty online orders, those swings create the sawtooth pattern in monthly revenue: $1.34 million in February 2014, $7.22 million in March, $1.80 million in April.

The July 2013 shift

The biggest structural change happens in the online channel in mid 2013, and the product mix shows what happened. In May 2013 there were 333 online orders, and all 333 contained a bike. In June, total online orders rose to 533 and bike-containing orders to 498, leaving 35 online orders without a bike. In July, total online orders jumped to 1,564 while bike-containing orders held at 497, so 1,067 July online orders contained no bike at all.

The category detail shows the same shift. Online accessory orders went from 22 in May to 408 in June to 1,309 in July, and clothing orders from 8 to 158 to 518. (One order can contain several categories, so these counts are not added together.) I describe this as an abrupt change in online product mix, beginning in June and exploding in July, not as an expansion of online purchasing. The channel moved from bike orders toward many more low-priced accessory and clothing orders, which is why order volume surged without a comparable rise in revenue and why the new orders pulled online average order value down.

The same break appears in the customer data. New customers went from 303 in June 2013 to 1,284 in July and stayed above 950 a month through May 2014, consistent with a wave of first-time online buyers placing small accessory and clothing orders.

Results

Product Mix

Bikes generate $94.65 million, or 86.2% of merchandise revenue. Components contribute $11.80 million, clothing $2.12 million and accessories $1.27 million. Clothing and accessories together sold 135,602 units, more than the 90,268 bikes, but brought in about 3% of revenue.

The product-level chart shows the same concentration. The ten highest-revenue products are all bikes, led by variants of the Mountain-200 and Road-250 families. Of the 295 sellable (finished-goods) products in the catalog, 266 appear in recorded sales, about 90.2%.

Results

Geographic Performance

The Southwest is the highest-revenue territory at $24.18 million, followed by Canada at $16.36 million and the Northwest at $16.08 million.

Order volume tells a different story. Australia has the most orders of any territory, 6,843 against 6,224 in the Southwest, yet its revenue is only $10.66 million, about $1,557 per order. Central, Southeast and Northeast show the opposite pattern. Central records only 385 orders from 69 customers but generates $7.91 million, about $20,543 per order. Southeast has 486 orders from 91 customers and $7.88 million; Northeast has 352 orders from 57 customers and $6.94 million.

Those patterns are consistent with territories built on a small number of high-value accounts, although the territory table alone is not enough to assign each difference to customer type.

Results

Salespeople

The Sales-Assisted channel has 17 salespeople; the chart shows the top ten by merchandise revenue. Linda Mitchell ranks first at $10.37 million, followed by Jillian Carson at $10.07 million and Michael Blythe at $9.29 million. The top three account for about 36.9% of sales-assisted revenue, and the top five for about 56.4%.

Salespeople did not all start selling at the same point in the dataset, so total revenue reflects time active as well as performance. Normalizing by active month would be needed before making productivity comparisons.

Results

Repeat Purchasing

Of the 19,119 customers with orders, 11,649 placed one order and 7,063 placed two to four. Only 290 recorded five to nine orders, and 117 recorded ten or more.

Those 117 customers, 0.6% of customers with orders, generated $25.24 million, about 23% of merchandise revenue, across 1,679 orders. They averaged 14.35 orders each, a pattern consistent with recurring store-account purchasing rather than ordinary individual-customer retention. AdventureWorks mixes individual and store customers, so this distribution describes two populations at once, and I have not yet split the cohort by customer type.

Method

Reporting Architecture

I split the analysis into domain-focused SQL modules instead of building one enormous query. Separate files handle exploration, sales performance, products, customers, geography, salespeople, findings and validation. Each module returns structured JSON, which gives the Python reporting layer a predictable contract without tying chart generation to the database connection.

1. 01SQL ServerAdventureWorks 2022 database
2. 02SQL modulesEight domain-focused query files
3. 03JSONOne structured output per module
4. 04PythonValidation, zero-filling, charts, tables
5. 05Web reportInteractive Plotly page

Python does the presentation-side work: reading the JSON files, checking that the expected structures are present, filling in true zeros, reshaping data for charts, formatting tables and generating the HTML. The zero-filling mattered in Figure 3. Months with no sales-assisted orders have to appear as zero rather than be omitted; otherwise Plotly connects the surrounding points and implies orders that did not happen. The reporting layer now builds the complete month-by-channel grid so those months stay visible.

Revisions

What Changed During the Analysis

Several of the strongest parts of the project came from finding problems in my own first version.

• I initially used TotalDue as revenue in some order-level queries while product reporting used LineTotal. That put $123.2 million and $109.85 million on the same page under the same label. I standardized revenue to SubTotal and LineTotal.
• I renamed “Non-Online” to Sales-Assisted after checking the documented meaning of OnlineOrderFlag.
• I dropped a customer-concentration figure (“top 100 customers = 42.5% of revenue”) because it mixed individual and store customers.
• I replaced 266 of 504 products (52.8%) with 266 of 295 sellable products (90.2%) after filtering on the finished-goods flag. The other 209 records are parts and assemblies that are not sold as finished products.
• I removed June 2014 from monthly comparison after finding that the sales-assisted channel disappears and merchandise revenue falls to $49,005.84.
• I zero-filled the sales-assisted series so the channel chart no longer draws across months with no orders.

Those corrections changed both the numbers and the story. They are also why validation is an explicit stage of the pipeline rather than treating the first successful query as the answer.

Limits

What the Results Do Not Support

• AdventureWorks is synthetic sample data, so the findings demonstrate analytical method rather than describe a real company.
• The customer population mixes individuals and store accounts, which limits what the repeat-purchase and territory figures can say until they are split by customer type.
• Salesperson totals are influenced by differing active periods.
• June 2014 is retained in full-period totals but excluded from month-to-month comparison because its channel structure is not comparable with the preceding months.
• Revenue is defined as merchandise revenue before tax and freight. Other reporting contexts could reasonably use a different measure, but it would need to be applied consistently throughout.

Conclusion

What I Would Claim

AdventureWorks runs two channels that behave very differently: a high-volume online channel and a low-volume, high-value Sales-Assisted channel. Bikes generate 86.2% of merchandise revenue, and the 2013 online surge came from a sudden shift toward accessory and clothing orders rather than growth in bike orders. Geographic and customer results show why order volume alone is a weak proxy for value.

The pipeline keeps those definitions separate from presentation, so the business logic stays visible enough to audit when the numbers change. The same separation could later support a reporting system in which approved questions map to controlled SQL modules and chart templates rather than arbitrary database access.

Source

Repository

The eight SQL modules, the Python reporting layer and the JSON outputs are in the repository. This is the query from 08_validation_checks.sql behind the July 2013 finding: online and sales-assisted orders by product category, May through July 2013.

SELECT   YEAR(soh.OrderDate)  AS SalesYear,
         MONTH(soh.OrderDate) AS SalesMonth,
         CASE WHEN soh.OnlineOrderFlag = 1
              THEN 'Online' ELSE 'Sales-Assisted' END AS SalesChannel,
         pc.Name                         AS CategoryName,
         COUNT(DISTINCT soh.SalesOrderID) AS SalesOrders,
         SUM(sod.OrderQty)                AS UnitsSold,
         SUM(sod.LineTotal)               AS ProductRevenue
FROM     Sales.SalesOrderHeader AS soh
         JOIN Sales.SalesOrderDetail AS sod
              ON soh.SalesOrderID = sod.SalesOrderID
         JOIN Production.Product AS p
              ON sod.ProductID = p.ProductID
         LEFT JOIN Production.ProductSubcategory AS psc
              ON p.ProductSubcategoryID = psc.ProductSubcategoryID
         LEFT JOIN Production.ProductCategory AS pc
              ON psc.ProductCategoryID = pc.ProductCategoryID
WHERE    soh.OrderDate >= '2013-05-01'
         AND soh.OrderDate < '2013-08-01'
GROUP BY YEAR(soh.OrderDate), MONTH(soh.OrderDate),
         soh.OnlineOrderFlag, pc.Name
ORDER BY SalesYear, SalesMonth, SalesChannel, CategoryName;

• Repository

Sources

References

1. Deloitte. 6.7 Sales taxes and similar taxes collected from customers. In Deloitte’s Roadmap: Revenue Recognition. Deloitte Accounting Research Tool. dart.deloitte.com
2. Financial Accounting Standards Board (2014). Revenue from Contracts with Customers (Topic 606). Accounting Standards Update No. 2014-09, para. 606-10-32-2. storage.fasb.org
3. Few, S. (2015). Displaying missing values and incomplete periods in time series. Visual Business Intelligence Newsletter, January–March 2015. Perceptual Edge. perceptualedge.com
4. Microsoft. Adventure Works Internet Sales tutorial (1500). Microsoft Learn, Analysis Services. learn.microsoft.com
5. Microsoft. AdventureWorks readme. sql-server-samples repository, GitHub. github.com
6. Microsoft. AdventureWorks sample databases. Microsoft Learn, SQL Server. learn.microsoft.com
7. Microsoft. Create a relative date slicer and filter in Power BI. Microsoft Learn, Power BI. learn.microsoft.com
8. Microsoft (2010). SalesOrderHeader table (AdventureWorks). SQL Server 2008 Books Online, archived on Microsoft Learn. learn.microsoft.com