Preparing Sales Data for Analysis with SQL

The first three articles established the business questions, mapped the sales data, and assessed its quality.

I could now begin analysing customers and products directly from the operational tables. The problem is that every query would need to solve the same set of problems again.

How should a customer with both PersonID and StoreID be classified? Which territory should represent a sale? How should a salesperson’s name be assembled? Should product analysis use gross value or line value after discount? Which date should drive monthly reporting?

Repeating those decisions across a collection of analytical queries makes inconsistency almost inevitable.

This article creates a small analytical layer so the later work can concentrate on business questions instead of rebuilding the same joins and definitions.

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 ← You are here
5. Analysing Customer Purchasing Behaviour with SQL
6. Measuring Product Performance with SQL
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

To reproduce this article, run the analytical view creation script against AdventureWorks2025 in SQL Server Management Studio. It creates the Analytics schema and the three reusable views developed below without changing any AdventureWorks source rows.

Then run the analytical view validation script to verify row preservation, key uniqueness, descriptive completeness, and financial reconciliation. Later articles depend on these views, so leave them installed after validation.

The Business Problem

The AdventureWorks2025 sales data is structurally sound, but it is organised for transaction processing. Its design also reflects an older operational model rather than a contemporary analytical blueprint.

I don’t need to approve of every source-system design decision to analyse its data responsibly. In practice, inherited systems often contain awkward relationships and conventions that analysts cannot simply replace. The task is to make those choices visible, define a safe interface over them, and preserve a clear path back to the source.

A useful product-level sales query crosses several tables:

• Sales.SalesOrderHeader
• Sales.SalesOrderDetail
• Sales.Customer
• Person.Person or Sales.Store
• Sales.SalesTerritory
• shipping address tables
• Sales.SpecialOfferProduct
• Sales.SpecialOffer
• Production.Product
• product subcategory and category tables

That complexity is manageable once. It becomes a maintenance problem when every analysis implements a slightly different version.

The immediate requirement is a reusable layer that:

I don’t need a full data warehouse yet. I need a stable interface between the operational database and the analysis.

Why Use Views?

For this stage, SQL views are a practical fit.

They let me centralise joins and definitions without copying the source data into another set of tables. The results remain current with the operational database, and readers can inspect the view definitions to understand exactly where each field comes from.

There are tradeoffs.

Normal views don’t store their own data, so the underlying joins still run when the view is queried. They also don’t solve historical tracking, surrogate keys, slowly changing dimensions, or workload isolation. Those concerns belong in the later dimensional-modelling and performance articles.

For now, the layer is intentionally lightweight.

IF SCHEMA_ID(N'Analytics') IS NULL
BEGIN
    EXEC(N'CREATE SCHEMA Analytics AUTHORIZATION dbo;');
END;

Keeping the views in an Analytics schema separates them from Microsoft’s operational objects and makes their purpose clear.

The schema is created by the opening section of the view creation script.

Three Views, Three Grains

The layer contains three views:

Customer grain

19,820 rows with one stable customer identifier and one customer-type rule.

Order grain

31,465 rows for customer, channel, geography, dates, and order-level value.

Order-line grain

121,317 rows for products, quantities, offers, discounts, and net line value.

The view definitions establish these grains explicitly: customer view, order view, and order-line view.

This separation is more important than the number of views. If order subtotal appeared in the order-line view, it would repeat for every product on the order. Somebody could then sum it without noticing that revenue had been multiplied by the number of lines.

The view design makes the safer path the easier path.

Standardising Customers

The customer view contains the rule established during data exploration and quality assessment:

If StoreID is populated, classify the account as a store. Otherwise, classify it as an individual.

CREATE OR ALTER VIEW Analytics.vwCustomers
AS
SELECT
    customer.CustomerID,
    customer.AccountNumber AS CustomerAccountNumber,
    CASE
        WHEN customer.StoreID IS NOT NULL THEN 'Store'
        ELSE 'Individual'
    END AS CustomerType,
    COALESCE
    (
        store.Name,
        CONCAT_WS
        (
            ' ',
            person.FirstName,
            NULLIF(person.MiddleName, ''),
            person.LastName
        )
    ) AS CustomerName,
    customer.PersonID,
    customer.StoreID,
    customer.TerritoryID AS CustomerTerritoryID,
    territory.Name AS CustomerTerritory,
    territory.[Group] AS CustomerTerritoryGroup
FROM Sales.Customer AS customer
LEFT JOIN Person.Person AS person
    ON person.BusinessEntityID = customer.PersonID
LEFT JOIN Sales.Store AS store
    ON store.BusinessEntityID = customer.StoreID
LEFT JOIN Sales.SalesTerritory AS territory
    ON territory.TerritoryID = customer.TerritoryID;

The source identifiers remain visible. This is useful when the classification needs to be audited or revised, and it avoids turning the friendly name into an identifier.

The view also includes customer territory separately from sales territory. They may support different questions later, so combining them into one vague Region column would lose meaning.

The complete customer-view definition contains the classification, naming, source identifiers, and territory fields discussed here.

Building the Order View

Analytics.vwSalesOrders provides one row per order.

It brings together:

• the standardised customer
• order, due, and shipping dates
• a readable status
• online or salesperson-assisted channel
• salesperson name
• sales territory
• shipping city, state or province, and country
• subtotal, tax, freight, and total due

The complete definition is in the supporting SQL, but several decisions deserve attention.

A consistent reporting date

OrderDate remains the default sales date. The view also exposes a month-start date, year, and month number:

CONVERT(date, sales_order.OrderDate) AS OrderDate,
DATEFROMPARTS
(
    YEAR(sales_order.OrderDate),
    MONTH(sales_order.OrderDate),
    1
) AS OrderMonthStart,
YEAR(sales_order.OrderDate) AS OrderYear,
MONTH(sales_order.OrderDate) AS OrderMonthNumber

OrderMonthStart is especially useful for grouping and joining because it remains a proper date rather than a formatted string.

This isn’t a replacement for a calendar dimension. It is enough for the immediate analysis and gives the later time-series work a consistent foundation.

Named channels instead of flags

The operational flag remains available as IsOnlineOrder, while a readable label supports reporting:

CASE
    WHEN sales_order.OnlineOrderFlag = 1 THEN 'Online'
    ELSE 'Salesperson'
END AS SalesChannel

The data-quality checks confirmed that this rule agrees exactly with the presence or absence of SalesPersonID.

Geography that says what it means

The order view does not create a generic Region field. It exposes:

• customer territory
• sales territory
• shipping city
• shipping state or province
• shipping country

That makes analytical intent visible. A regional sales-performance query can use sales territory, while logistics analysis can use shipping geography.

Financial columns remain separate

SubTotal, TaxAmount, FreightAmount, and TotalDue are all retained at order grain.

The order-view definition supplies the date, channel, salesperson, geography, and financial fields described in this section.

For commercial sales analysis, subtotal is the usual starting point. Tax and freight remain available without being silently included in a measure called Revenue.

Building the Order-Line View

The order-line view starts from vwSalesOrders, then adds the product, offer, quantity, price, discount, and category path.

It deliberately does not include order subtotal, tax, freight, or total due.

The three line-level financial measures are:

CONVERT
(
    decimal(19, 6),
    sales_line.OrderQty * sales_line.UnitPrice
) AS GrossLineAmount,
CONVERT
(
    decimal(19, 6),
    (sales_line.OrderQty * sales_line.UnitPrice) - sales_line.LineTotal
) AS DiscountAmount,
CONVERT(decimal(19, 6), sales_line.LineTotal) AS NetLineAmount

These names describe the calculation directly:

• Gross line amount is quantity multiplied by unit price.
• Discount amount is the difference between gross and net line value.
• Net line amount is the source LineTotal after discount.

I preserved six decimal places for the calculated line values. Rounding every line to two decimal places before aggregation would introduce unnecessary differences across more than 120,000 rows. Presentation rounding can happen at the end of an analysis.

Tax and freight aren’t allocated to individual products. Any allocation would introduce a business rule that doesn’t exist in the source data and could distort product performance.

The view also follows the declared relationship from the order line through Sales.SpecialOfferProduct, making the special-offer description available for later discount analysis.

The complete order-line-view definition contains the product hierarchy, offer, quantity, price, discount, and line-value logic.

Simpler Analytical Queries

The point of the layer becomes clearer when looking at the queries it enables.

An order-level channel summary now needs one view:

SELECT
    CustomerType,
    SalesChannel,
    COUNT(*) AS Orders,
    SUM(SubTotal) AS SalesValue,
    AVG(SubTotal) AS AverageOrderValue
FROM Analytics.vwSalesOrders
GROUP BY
    CustomerType,
    SalesChannel
ORDER BY
    CustomerType,
    SalesChannel;

A product-category summary is similarly direct:

SELECT
    ProductCategory,
    SUM(OrderQty) AS QuantitySold,
    SUM(GrossLineAmount) AS GrossSalesValue,
    SUM(DiscountAmount) AS DiscountValue,
    SUM(NetLineAmount) AS NetSalesValue
FROM Analytics.vwSalesOrderLines
GROUP BY
    ProductCategory
ORDER BY
    NetSalesValue DESC;

The joins haven’t disappeared. They have moved into named, inspectable objects where they can be tested once and reused consistently.

Validating the Layer

Creating a view successfully only proves that SQL Server accepted the definition. It doesn’t prove that the view preserved the intended data.

I validated four things.

Grain and row preservation

The grain-preservation query generates all source-row, view-row, and distinct-key counts in the table.

The matching row and distinct-key counts confirm that none of the joins duplicated or removed records.

Descriptive completeness

The views contain no missing:

• customer names or customer types
• sales channels or sales territories
• shipping countries
• product names, subcategories, or categories on sold lines

The descriptive-completeness queries return zero missing values for those fields.

Financial reconciliation

Aggregating NetLineAmount by order produces no orders outside a one-cent tolerance of vwSalesOrders.SubTotal.

The maximum difference is 0.000050, which reflects the source precision difference between six-decimal line values and four-decimal order subtotals.

Across the complete line view, the net amount matches the source SalesOrderDetail.LineTotal total exactly at six decimal places.

The order-level reconciliation supplies the tolerance and maximum-difference results, while the source-to-view total check validates the complete line value.

Source database remains unchanged

The new objects live in a separate schema and read from the existing tables. No AdventureWorks source rows or Microsoft-owned objects are modified.

For verification, I created and tested the views inside a transaction, ran the full validation script, and rolled the transaction back. You can apply the layer permanently using the supporting files below.

What This Layer Doesn’t Try to Solve

These views are an analytical interface, not a finished warehouse.

They don’t provide:

• a persisted calendar dimension
• surrogate keys
• historical versions of changing customer or product attributes
• pre-aggregated reporting tables
• workload isolation from the operational database
• a semantic model for Power BI

Adding those features now would bring Article 11’s dimensional-modelling decisions forward before the customer, product, regional, and time analyses have shown what the model needs to support.

The current layer is deliberately modest. It removes repeated complexity while keeping the project close to the source.

Working Conventions for Later Analysis

The views establish several conventions for the rest of the series:

1. Use vwCustomers for customer attributes and classification.
2. Use vwSalesOrders for order counts, order frequency, average order value, and order-level financial measures.
3. Use vwSalesOrderLines for product, quantity, discount, and category analysis.
4. Use subtotal or net line amount as the default commercial sales value.
5. Keep sales territory and shipping geography distinct.
6. Preserve source precision during calculation and round only for presentation.

If a later article needs a different definition, the difference should be explicit rather than hidden in a one-off join.

Next in the Series

The next article will analyse customer purchasing behaviour.

With the analytical layer in place, I can concentrate on order frequency, average order value, repeat purchasing, recency, and customer segmentation without rebuilding the operational model inside every query.