Sales and Marketing Scenario

Customer and sales-related information is a significant part of the AdventureWorks sample database. This topic provides details about the customers that are represented in the sample database, a schema of the major customer and sales tables and sample queries that demonstrate table relationships.

Customers Types

As a bicycle manufacturing company, Adventure Works Cycles has two types of customers:

  • Individuals. These are consumers who buy products from the Adventure Works Cycles online store.

  • Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives.

The Customer table contains one record for each customer. The column CustomerType indicates whether the customer is an individual consumer (CustomerType= 'I') or a store (CustomerType= 'S'). Data specific to these customer types is maintained in the Individual and Store tables, respectively.

Customer type

Major tables

Number of customers

Additional information

Individual

Person.Contact

Sales.Customer

Sales.Individual

Sales.SalesOrderHeader

Sales.SalesOrderDetail

18,484

Sales and demographic data have been trended for data mining scenarios.

Demographic data (income, hobbies, number of cars, and so on) is stored as xml data in the Demographics column of the Individual table.

Store

Person.Contact

Sales.Customer

Sales.Store

Sales.StoreContact

Sales.SalesOrderHeader

Sales.SalesOrderDetail

701

Data has been trended for Analysis Services scenarios.

Stores are categorized by size: large, medium, and small.

Demographic data stored as xml data.

Store contacts are employees of the store who interact with Adventure Works Cycles sales representatives. For example, the store owner or purchasing manager would be typical contacts for Adventure Works Cycles salespeople.

Examples

You can use the following queries to view customer data and to become familiar with the customer-table relationships.

A. Viewing individual customers (consumers)

The following example returns the first and last name of each customer who is categorized as an individual consumer (CustomerType = 'I').

USE AdventureWorks;
GO
SELECT FirstName, LastName
FROM Person.Contact AS C
    JOIN Sales.Individual AS I
        ON C.ContactID = I.ContactID
    JOIN Sales.Customer AS Cu
        ON I.CustomerID = Cu.CustomerID
WHERE Cu.CustomerType = 'I'
ORDER BY LastName, FirstName ;
GO

B. Viewing individual customer address data

The following example lists the names and addresses of all individual customers.

USE AdventureWorks;
GO
SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City, 
    SP.Name AS State, CR.Name AS CountryRegion
FROM Person.Contact AS C
    JOIN Sales.Individual AS I ON C.ContactID = I.ContactID
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY I.CustomerID ;
GO

C. Viewing store customers, either retail or wholesale stores

The following example returns the name of each customer that is categorized as a store (CustomerType = 'S').

USE AdventureWorks;
GO
SELECT Name
FROM Sales.Store AS S
    JOIN Sales.Customer AS C
        ON S.CustomerID = C.CustomerID
WHERE C.CustomerType = N'S'
ORDER BY Name ;
GO
GO

D. Viewing store contacts by store

The following example returns the name of all store customers and the names and titles of store employees who authorized to purchase Adventure Works Cycles products on behalf of their company.

USE AdventureWorks;
GO
SELECT S.Name AS Store, C.FirstName, C.LastName, CT.Name AS Title 
FROM Person.Contact AS C 
    JOIN Sales.StoreContact AS SC ON C.ContactID = SC.ContactID
    JOIN Person.ContactType AS CT ON 
        CT.ContactTypeID = SC.ContactTypeID
    JOIN Sales.Store AS S ON S.CustomerID = SC.CustomerID
ORDER BY S.Name ;
GO

E. Viewing sales by store

The following example lists store customers and their associated sales orders.

USE AdventureWorks;
GO
SELECT Name, SalesOrderNumber, OrderDate, TotalDue
FROM Sales.Store AS S
    JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
ORDER BY Name, OrderDate ;
GO

F. Viewing stores by locations

The following example prints the store-customer name, city, state and country/region.

USE AdventureWorks;
GO
SELECT S.CustomerID, S.Name AS Store, A.City, SP.Name AS State, CR.Name
    AS CountryRegion
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
    JOIN Person.CountryRegion CR ON 
        CR.CountryRegionCode = SP.CountryRegionCode
ORDER BY S.CustomerID ;
GO
GO