Measuring Product Performance with SQL

The customer analysis in the previous article showed that Adventure Works serves two very different customer groups.

Store accounts place fewer but much larger orders, while individuals generate more order activity at a lower average value. Product analysis needs to preserve that context.

A product can lead by revenue without selling the most units. A low-priced accessory can appear on thousands of orders while contributing little sales value. A component with modest direct revenue may still support repairs, manufacturing, or the wider product range.

This article measures those differences instead of reducing product performance to one ranking.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

1. Building a Sales Analytics Solution with SQL: Project Overview
2. Understanding the Sales Data Landscape with SQL
3. Assessing Sales Data Quality with SQL
4. Preparing Sales Data for Analysis with SQL
5. Analysing Customer Purchasing Behaviour with SQL
6. Measuring Product Performance with SQL ← You are here
7. Analysing Regional Sales Performance with SQL
8. Time-Series Analysis with SQL
9. Using Advanced SQL to Generate Business Insights
10. Optimising SQL for Analytical Workloads
11. Designing an Analytics-Friendly Data Model
12. Completing a SQL Sales Analytics Project

Reproduce the Analysis

This article depends on the analytical views created in Article 4. If the Analytics views aren’t installed, run the analytical view creation script against AdventureWorks2025 first.

Then run the complete product performance script in SQL Server Management Studio. The analysis is read-only, uses a session-scoped temporary table, and includes every catalogue, category, SKU, product-model, discount, availability, and rolling-period query referenced below.

The Business Problem

Product reporting often begins with a request for the “best” and “worst” products.

The problem is that those labels are incomplete. Best by what measure?

• Net sales value: Shows commercial contribution before costs.
• Quantity: Shows physical demand but favours inexpensive items.
• Order reach: Shows how widely a product appears across transactions.
• Customer reach: Shows how broadly demand is distributed.
• Discounting: Shows how much gross value is being surrendered.
• Recent direction: Shows whether performance is strengthening or weakening.

No single measure answers every product decision. Inventory planning, range management, pricing, marketing, and account management need different combinations.

I will use NetLineAmount as the default sales measure. It represents quantity multiplied by unit price after the line discount and reconciles with order subtotal. It’s not profit because the analytical layer doesn’t contain a historical cost measure.

Establishing the Product Population

The AdventureWorks2025 catalogue contains:

• 504 product records
• 295 finished goods
• 266 products with recorded sales

These figures come from the catalogue-coverage query.

The distinction matters. The full product table includes parts, materials, and other records that aren’t necessarily intended for direct sale. Ranking all 504 products by sales would place many legitimate non-selling records at the bottom and call them failures.

The main performance analysis therefore starts with products that appear in Analytics.vwSalesOrderLines. I return to unsold finished goods separately, where their availability dates can be considered.

Creating One Row per Sold SKU

The order-line view contains one row per recorded sales line. Product metrics need one row per ProductID, which is the stock-keeping unit, or SKU, grain used in this analysis.

SELECT
    sales_line.ProductID,
    MAX(sales_line.ProductName) AS ProductName,
    MAX(sales_line.ProductCategory) AS ProductCategory,
    COUNT(DISTINCT sales_line.SalesOrderID) AS OrderCount,
    COUNT(DISTINCT sales_line.CustomerID) AS CustomerCount,
    SUM(sales_line.OrderQty) AS QuantitySold,
    SUM(sales_line.GrossLineAmount) AS GrossSalesValue,
    SUM(sales_line.DiscountAmount) AS DiscountValue,
    SUM(sales_line.NetLineAmount) AS NetSalesValue
FROM Analytics.vwSalesOrderLines AS sales_line
GROUP BY sales_line.ProductID;

The complete product-level query also retains product model, subcategory, first sale date, and last sale date. The supporting script stores this result in a temporary table so each later comparison uses the same definitions.

Category Contribution

The broadest view compares the four product categories.

The category-contribution query generates these figures directly from the order-line view.

Bikes are the commercial engine of the dataset. They generate more than $94 million in net sales and account for 86.17% of the total.

Accessories tell a different story. They appear on 19,524 orders, more than any other category, but contribute only 1.16% of sales value. Their commercial role may involve reach, attachment to larger purchases, or customer convenience rather than direct revenue leadership.

The category order counts cannot be added together to recover the total order count. One order can contain products from several categories and therefore appear once in each relevant category. This is a deliberate count of orders containing the category.

Customer type also changes the interpretation. Components are sold only through store accounts in this sample, while accessories, bikes, and clothing appear in both customer groups. The category and customer-type query makes that split visible.

Ranking Products by Sales Value

I use ROW_NUMBER to create a deterministic ranking by net sales value, with ProductID breaking ties.

ROW_NUMBER() OVER
(
    ORDER BY NetSalesValue DESC, ProductID
) AS SalesRank

The leading six SKUs are all Mountain-200 variants.

The full SKU sales-ranking query returns the top ten products. Together, those ten SKUs generate $31.01 million, or 28.23% of total net sales, as calculated by the top-ten contribution query.

That concentration deserves attention, but the SKU grain also fragments a larger pattern.

Moving from SKU to Product Model

Size and colour variants are separate products in AdventureWorks. That is appropriate for ordering and inventory, but a product manager may want to understand the performance of the overall model.

When the six Mountain-200 variants are grouped together, the model has:

• 14,596 units sold
• $22.29 million in net sales
• 20.29% of total net sales

The next product models are Road-250 at 12.59%, Mountain-100 at 9.02%, and Touring-1000 at 8.84%.

The product-model query generates these figures by joining each SKU to Production.ProductModel before aggregating the variants.

Use the grain that matches the decision

SKU grain supports size, colour, pricing, and inventory decisions. Product-model grain gives a clearer view of the commercial strength of the overall range.

Quantity Produces Different Leaders

The highest-volume product is not a bike. It’s the AWC Logo Cap, with 8,311 units and $51,229 in net sales.

The Water Bottle follows with 6,815 units across 4,688 orders and 4,243 customers, but only $28,654 in net sales. Helmets and jerseys also feature prominently in the quantity ranking.

The complete quantity-ranking query returns quantity, orders, customers, and value together.

This comparison prevents two common mistakes:

• treating unit volume as though it were financial contribution
• treating low sales value as evidence that customers rarely buy the product

A low-priced item can have broad customer reach and operational importance without becoming a major revenue source.

Discounting Is Modest

Discount value is small relative to gross sales across every category.

The category discount query calculates discount value as a percentage of gross line value.

Bikes account for most discount value because they account for most gross sales. Clothing has the highest effective rate, but it remains below 1%.

The data doesn’t support a claim that discounting is driving product performance. A proper pricing assessment would also need promotion timing, eligible customers, margin, and a comparison with expected sales in the absence of the offer.

What Does a Low-Performing Product Look Like?

Sorting every sold product by ascending revenue is easy. Interpreting the result is harder.

For a fairer recent comparison, I restrict the query to finished goods that were available throughout the latest comparable rolling twelve-month period, ending 30 April 2025. The availability-adjusted low-value query applies those conditions before ordering products by net sales.

Most of the lowest-value results are components such as handlebars and frames. The lowest, LL Road Handlebars, generates $2,726 from 102 units in the period. The AWC Logo Cap provides a useful contrast: it sells 4,699 units while generating only $31,546.

These products may have very different roles. A rarely sold frame variant and a widely purchased low-priced tube shouldn’t receive the same recommendation simply because both contribute little revenue.

There are also 29 finished goods with no recorded sales. Fifteen have an end date during the observation period, while fourteen appear available at the end of the data. The unsold-finished-goods query produces that split.

No sales doesn’t automatically mean no value

Before removing an unsold or low-value product, I would check whether it’s genuinely customer-facing, whether it supports service or manufacturing, whether it was stocked, and whether its catalogue dates reflect actual availability.

Checking Recent Direction

Lifetime totals favour products that have been available for longer. I therefore compare adjacent rolling twelve-month periods ending on 30 April 2025, the latest date through which both store and individual activity is observed.

The script derives a shared cutoff from customer-type coverage before running the rolling category query.

Bike growth is commercially significant because it starts from a large base. Accessories have the highest percentage increase, but the earlier period is much smaller. Components also grow by 29.76%, reversing the decline produced when May and June were included without store orders.

The supporting script also identifies the largest SKU increases and decreases. Many of those movements occur within bike families, which may reflect product lifecycle changes or substitution between variants rather than growth or decline in the overall market.

This is a directional check rather than a complete time-series analysis. Monthly trends, seasonality, and year-over-year comparisons belong in the dedicated time-series article.

Business Recommendations

Protect the leading bike families

Mountain-200 alone contributes 20.29% of net sales at model grain. Availability, customer mix, and changes across its variants should be monitored because disruption to one family could materially affect overall performance.

Use separate scorecards for value and reach

Bikes lead commercial value, while accessories and clothing often lead order or customer reach. Product reporting should show those roles separately instead of forcing every product into one universal ranking.

Validate the component growth

Component sales increase by 29.76% between the comparable rolling periods and occur entirely through store accounts. The next investigation should still separate product lifecycle changes, store demand, and substitution between component variants before treating that growth as a persistent trend.

Review unsold finished goods with operational context

The fourteen apparently available finished goods with no sales should be checked against stock availability, catalogue configuration, service requirements, and product ownership. A data review should precede any decision to remove them.

Add margin before making portfolio decisions

Revenue and volume describe demand, but they don’t show profitability. Historical product cost, fulfilment cost, returns, and inventory carrying cost would materially improve a range-management decision.

Limitations

The analysis remains constrained by the sample and analytical layer.

• Profitability: Net sales value isn’t profit.
• Historical attributes: Current product attributes may not reproduce every historical catalogue state.
• Availability: Product availability dates don’t prove that stock was available to buy.
• Causality: Order and customer reach don’t show whether a product caused, complemented, or merely accompanied a purchase.
• Customer comparison: Store and individual demand should not be compared without considering their different channels and order sizes.
• Product lifecycle: Rolling-period changes may reflect product introductions, retirements, or substitution.
• Coverage boundary: Rolling comparisons end on 30 April 2025 because store orders aren’t observed after that date.

The results identify where investigation is worthwhile. They don’t replace commercial, inventory, or product-management context.

Next in the Series

The next article will analyse regional sales performance.

I will compare sales territories and shipping geographies, examine how customer and product mix affect regional results, and identify where apparent regional strength may simply reflect a different combination of stores, individuals, and products.