Chapter 4. Statistics, analytic, OLAP functions in business scenarios 173
Figure 4-20 charts this relationship.
Figure 4-20 Standard deviations from the mean by revenue
From our previous example, and this one, Ankara makes almost twice the profit
of the Tokyo store on almost half the revenue, thus making Ankara the most
profitable store.
4.3 Finance
1. Identify the most profitable customers
2. Identify the profile of transactions concluded recently
3. Identify target groups for a campaign
4. Evaluate effectiveness of a marketing campaign
5. Identify potential fraud situations for investigation
6. Plot monthly stock prices movement with percentage change
7. Plot the average weekly stock price in September
8. Project growth rates of Web hits for capacity planning purposes
9. Relate sales revenues to advertising budget expenditures
4.3.1 Identify the most profitable customers
Determining customers that buy products/services that bring the most profit to
the company can be used to build stronger customer relationships and increase
profitability and customer satisfaction.
174 High-Function Business Intelligence in e-business
Data
We assume that there is a data warehouse containing information about
customers and products/services they subscribe to. And we assume that we
have already done an analysis of how much profit each product or service brings
to the company per year.
The main sources of our data are:
? Product information which lists the products/services offered
? Customer details
? Products/Services purchased by customers
Customer profitability is the price of the product minus the cost the company
incurs in providing the service, as shown in Figure 4-21.
Figure 4-21 Profit from a customer
BI functions showcased
RANK, DENSE_RANK, ROW_NUMBER, ORDER BY
Companies rank their customers based on their business rules. In this example,
we show ranking using RANK, DENSE_RANK and ROWNUMBER in order to
show the differences in the results of these functions.
Steps
The SQL shown in Example 4-17 provides the desired result.
Example 4-17 Most profitable customers
SELECT a.custid,SUM(c.profit) AS total_profit,
RANK() OVER (ORDER BY SUM(c.profit) DESC) AS rank,
DENSERANK() OVER(ORDER BY SUM(c.profit) DESC) AS denserank,
ROW_NUMBER() OVER(ORDER BY SUM(c.profit) DESC) AS rownum
FROM cust a, prod_owned b, prod c
WHERE a.custid=b.custid AND b.prodid=c.prodid
GROUP BY a.custid
Customer yearly profit is calculated:
Customer Total Profit (CTP) = (N1 x P1) + (N2 x P2) +...(Nn x Pn)
Where:
N = number of product/service a customer has per product type
P = profit for product type
Chapter 4. Statistics, analytic, OLAP functions in business scenarios 175
Figure 4-22 shows the results of this query.
Figure 4-22 Customer profitability ranking result
We ran the query in IBM QMF and saved the output in an MS-Excel spreadsheet
and used MS-Excel charting to create the bar chart shown in Figure 4-23.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset