Advanced Record-Level Business Intelligence with Inner Queries
- by gt0084e1
While business intelligence is generally applied at an aggregate level to large data sets, it's often useful to provide a more streamlined insight into an individual records or to be able to sort and rank them. For instance, a salesperson looking at a specific customer could benefit from basic stats on that account. A marketer trying to define an ideal customer could pull the top entries and look for insights or patterns. Inner queries let you do sophisticated analysis without the overhead of traditional BI or OLAP technologies like Analysis Services.
Example - Order History Constancy
Let's assume that management has realized that the best thing for our business is to have customers ordering every month. We'll need to identify and rank customers based on how consistently they buy and when their last purchase was so sales & marketing can respond accordingly. Our current application may not be able to provide this and adding an OLAP server like SSAS may be overkill for our needs. Luckily, SQL Server provides the ability to do relatively sophisticated analytics via inner queries. Here's the kind of output we'd like to see.
Creating the Queries
Before you create a view, you need to create the SQL query that does the calculations. Here we are calculating the total number of orders as well as the number of months since the last order. These fields might be very useful to sort by but may not be available in the app. This approach provides a very streamlined and high performance method of delivering actionable information without radically changing the application. It's also works very well with self-service reporting tools like Izenda.
SELECT CustomerID,CompanyName,
(
SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
) As Orders,
DATEDIFF(mm,
(
SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID)
,getdate()
) AS MonthsSinceLastOrder
FROM Customers
Creating Views
To turn this or any query into a view, just put CREATE VIEW AS before it. If you want to change it use the statement ALTER VIEW AS.
Creating Computed Columns
If you'd prefer not to create a view, inner queries can also be applied by using computed columns. Place you SQL in the (Formula) field of the Computed Column Specification or check out this article here.
Advanced Scoring and Ranking
One of the best uses for this approach is to score leads based on multiple fields. For instance, you may be in a business where customers that don't order every month require more persistent follow up. You could devise a simple formula that shows the continuity of an account. If they ordered every month since their first order, they would be at 100 indicating that they have been ordering 100% of the time. Here's the query that would calculate that. It uses a few SQL tricks to make this happen. We are extracting the count of unique months and then dividing by the months since initial order. This query will give you the following information which can be used to help sales and marketing now where to focus. You could sort by this percentage to know where to start calling or to find patterns describing your best customers.
Number of orders
First Order Date
Last Order Date
Percentage of months order was placed since last order.
SELECT CustomerID,
(SELECT COUNT(OrderID) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) As Orders,
(SELECT Max(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS LastOrder,
(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID) AS FirstOrder,
DATEDIFF(mm,(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID),getdate()) AS MonthsSinceFirstOrder,
100*(SELECT COUNT(DISTINCT 100*DATEPART(yy,OrderDate) + DATEPART(mm,OrderDate))
FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) /
DATEDIFF(mm,(SELECT Min(OrderDate) FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID),getdate()) As OrderPercent
FROM Customers
