Confidence Level Example

The reports and report components in this example can be found in the following folder:

MicroStrategy Tutorial\Public Objects\Reports\ MicroStrategy Platform Capabilities\Advanced Analytics\ Statistics and Forecasting\Confidence Level

Confidence level is used to determine valuable customers in two slightly different ways:

• Who are my Valuable Customers? (Example 1): Determines valuable customers as those whose average spending is above an upper bound of sales orders.
• Who are my Valuable Customers? (Example 2): Determines valuable customers as those whose average spending is above an upper bound of sales orders. Additionally, valuable customers are compared for each Customer Region, rather than comparing customers in all regions.

Who are my Valuable Customers? (Example 1)

The basic goal is to define a cut-off value that represents the minimum requirement to be classified as a valuable customer. To identify the valuable customers in your customer base, you must determine the parameters that help differentiate those customers from the others. In this example, valuable customers are those whose average spending is above an upper bound of sales orders.

The ORDER_FACT table contains all orders received. Assume that the sales order amount is normally distributed with a certain mean and standard deviation. Based on the assumption of normal distribution and a confidence level of 99%, you can define valuable customers as those who have a spending average above an upper bound of sales orders.

To determine the valuable customers based on this criteria, you need to know several values. Use the following metrics to obtain these values.

1. You must know the average sales order from all orders in the ORDER_FACT table. This is the arithmetic mean of the normal distribution. You can get this value using the Avg function. The metric to compute the average of sales orders from the ORDER_FACT table can be defined as:

   M01 = Avg(Revenue) {![Call Center]+, !Year+, !Employee+, !Order+, !Day+}

   Since Day, Employee, and Order are the highest attributes that are parents of the keys in the ORDER_FACT table, include them as the level of aggregation to make sure that the ORDER_FACT table is used. Also, the average must be calculated over all sales order amounts, so you must set the group-by on the level attributes to none. This ensures that the metric does not group by any of these attributes.

2. You must know the standard deviation of sales orders from all orders. This number is the standard deviation of the normal distribution. You can get this value using the StDevP function (see StDevP (Standard Deviation of a Population)).

   The metric to compute the population standard deviation of sales orders from the ORDER_FACT table is defined as follows:

   M02 = StdevP(Revenue) {![Call Center]+, !Year+, !Employee+, !Order+, !Day+}

3. You must know the number of rows in the ORDER_FACT table. You need this value to calculate the cut-off value. You can retrieve this value by using the Count function, in particular, Count from the ORDER_FACT table. The metric to count the data in the ORDER_FACT table is defined as follows:

   M03 = Count(Revenue) {![Call Center]+, !Year+, !Employee+, !Order+, !Day+}

4. Under the assumption of normal distribution with the parameters given above, you must determine the cut-off value that represents the top 5% of sales order amounts. To do this, you can use the Confidence plug-in function. The metric to compute the cut-off number is defined as follows, using some of the metrics defined above:

   M04 = Confidence(0.01, [M02], [M03])

   The number 0.01 comes from a normal distribution with 99% confidence level.

5. The metric that calculates the upper bound uses some of the metrics defined above, and is defined as follows:

   M05 = ([M01] + [M04])

6. You must calculate the average sales for each customer to get a list of valuable customers based on the criteria. The metric is defined as follows:

   Average Sales = Avg(Revenue) {~+}

   You do not need to include the level of aggregation for Average Sales since the default {~+} notation is replaced by the attribute Customer.

7. You must also apply a metric qualification in a filter to restrict the rows returned to those that meet the definition of valuable customers. The filter is defined as follows:

   F01 = Set of Customers where (Average Sales Greater than [M05]

   To generate a report listing valuable customers, create a report with the attribute Customer on the row axis and the attribute Customer Region on the page-by axis. Apply filter F01 to the report to produce a list of valuable customers for each attribute element in Customer Region. The Valuable Customers 01 report is shown below:

   

   The SQL generated for the report Valuable Customers 01 is as follows:

   Copy

   Pass0 -  Execution Duration: 0:00:00.07
   create table ZZTLZV82TJ0MD000 (
       CUSTOMER_ID LONG, 
       WJXBFS1 DOUBLE)
   Pass1 - Execution Duration: 0:00:00.20
   insert into ZZTLZV82TJ0MD000 
   select a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       avg(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
   from [CUSTOMER_SLS] a11
   group by a11.[CUSTOMER_ID]
   Pass2 -  Execution Duration: 0:00:00.04
   select a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       a11.[ORDER_ID] AS ORDER_ID,
       a11.[ITEM_ID] AS ITEM_ID,
       a11.[EMP_ID] AS EMP_ID,
       a11.[ORDER_DATE] AS DAY_DATE,
       a11.[CUSTOMER_ID] AS CUSTOMER_ID0,
       (a11.[QTY_SOLD] * (a11.[UNIT_PRICE] - a11.[DISCOUNT]))
       AS WJXBFS1
   from [ORDER_DETAIL] a11
   Pass3 -  Execution Duration: 0:00:00.10
   create table ZZTW4A12VECMD001 (
       CUSTOMER_ID LONG, 
       WJXBFS1 DOUBLE)
   Pass4 -  Execution Duration: 0:00:00.00
   [Analytical SQL calculated by the Analytical Engine:
       select CUSTOMER_ID0,
           (ISNULL(avg(WJXBFS1), 0) + Confidence(0.01,
           StdevP(WJXBFS1), count(WJXBFS1)))
       from [previous pass]
   ]
   Pass5 -  Execution Duration: 0:00:00.00
   insert into ZZTW4A12VECMD001 values (1, 33.4351602545)
   Pass6 - Execution Duration: 0:00:00.00
   create table ZZTPDKUSLWSMQ002 (
       CUSTOMER_ID LONG)
   Pass7 -  Execution Duration: 0:00:00.10
   insert into ZZTPDKUSLWSMQ002 
   select pa11.[CUSTOMER_ID] AS CUSTOMER_ID
   from [ZZTLZV82TJ0MD000] pa11, 
       [ZZTW4A12VECMD001] pa12
   where pa11.[CUSTOMER_ID] = pa12.[CUSTOMER_ID]
    and (pa11.[WJXBFS1] > pa12.[WJXBFS1])
   Pass8 -  Execution Duration: 0:00:01.53
   select a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       max(a13.[CUST_LAST_NAME]) AS CUST_LAST_NAME,
       max(a13.[CUST_FIRST_NAME]) AS CUST_FIRST_NAME,
       a15.[CUST_REGION_ID] AS CUST_REGION_ID,
       max(a16.[CUST_REGION_NAME]) AS CUST_REGION_NAME0,
       avg(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
   from [CUSTOMER_SLS] a11, 
       [ZZTPDKUSLWSMQ002] pa12, 
       [LU_CUSTOMER] a13, 
       [LU_CUST_CITY] a14, 
       [LU_CUST_STATE] a15, 
       [LU_CUST_REGION] a16
   where a11.[CUSTOMER_ID] = pa12.[CUSTOMER_ID] and 
       a11.[CUSTOMER_ID] = a13.[CUSTOMER_ID] and 
       a13.[CUST_CITY_ID] = a14.[CUST_CITY_ID] and 
       a14.[CUST_STATE_ID] = a15.[CUST_STATE_ID] and 
       a15.[CUST_REGION_ID] = a16.[CUST_REGION_ID]
   group by a11.[CUSTOMER_ID],
       a15.[CUST_REGION_ID]
   Pass9 -  Execution Duration: 0:00:00.00
   [Populate Report Data]
   Pass10 -  Execution Duration: 0:00:00.06
   drop table ZZTLZV82TJ0MD000
   Pass11 -  Execution Duration: 0:00:00.00
   drop table ZZTW4A12VECMD001
   Pass12 -  Execution Duration: 0:00:00.00
   drop table ZZTPDKUSLWSMQ002

• Pass0 and Pass1 compute the average sales per customer (Average Sales metric) and put the results in a temporary table.
• Pass2 retrieves ORDER_DETAIL data, puts the data into memory, and uses the data to compute M01, M02, and M03.
• Pass3 creates a temporary table used to store the results of the calculations.
• Pass4 calculates M02 using the Intelligence Server because the group-value function StDevP is not supported by the Access database. The values of M01, M02, and M03 are used to compute M05.
• Pass5 inserts the results of the calculations into the temporary table.
• Pass6 and Pass7 use the earlier two temporary tables to qualify on valuable customers based on the definition.
• Pass8 displays the report with attributes Customer and Customer Region.
• The remaining passes perform additional report preparation and drop the temporary tables.

Who are my Valuable Customers? (Example 2)

This example generates a list of valuable customers based on a different definition from the previous example.

Assume a normal distribution of sales orders is still valid, but the cut-off value is for each Customer Region. This means you must compare the average spending of each customer against the average spending of the Customer Region to which the customer belongs. To allow for more variation, take the average value from the current year data, but use sample standard deviations for all available years. Use a confidence level of 95%.

Valuable customers are defined as those customers who have total spending above the cut-off value. The cut-off value is calculated using the filters and metrics defined below:

1. The filter used to limit the calculation of average sales orders to only the data for the current year is defined as follows:

   Current year in sample DB = Year In list (2010)

2. The metric used to calculate the average sales order is defined as follows:

   M07= Avg(Revenue) {[Customer Region]+, !Year+, !Employee+, !Order+, !Day+} <[Current year in sample DB]; @2; ->

3. The metric used to calculate the standard deviation of sales orders is defined as follows:

   M08 = Stdev(Revenue) {[Customer Region]+, ![Call Center]+, !Year+, !Employee+, !Order+, !Day+}

   Notice that the function Stdev (standard deviation of a sample) is used in this metric because the amount of data is limited to each Customer Region. This means the data can be considered as more of a sample than a full population.

4. The metric used to determine the cut-off value is defined as follows, using the preceding metrics:

   M09 = ([M07] + (1.96 * [M08]))

   The number 1.96 comes from a normal distribution with 95% confidence level. In practice, this number is often rounded to 2 instead of 1.96.

5. The metric used to determine each customer's spending so that it can be compared to the cut-off value is defined as follows:

   M10 = Sum(Revenue){~+}

6. The metric qualification (filter) needed to restrict the report to a list of valuable customers based on Definition 2 is defined as follows:

   F03 = Set of Customer where (M10 Greater than [M09])

7. To view the last order sales from all valuable customers, use the metric defined as follows:

   Last Order Sales = Sum(Revenue) {~+, >|Day+}

   You must compute order sales for each customer at the Day level from the ORDER_FACT table, but must take it from the last Day on that fact table. The {~+} is replaced by any attribute on the report. In this example, it is the Customer attribute.

   Build a report putting the attribute Customer on the row axis, the attribute Customer Region on the page-by axis, and the Last Order Sales metric on the column axis, then applying the filter F03. The Valuable Customers 02 report is shown below.

   

   The SQL generated by the Valuable Customers 02 report is as follows:

   Copy

   Pass0 -  Execution Duration: 0:00:00.00
   create table ZZT3WSP7T8BMD000 (
       CUSTOMER_ID LONG, 
       WJXBFS1 DOUBLE)
   Pass1 -  Execution Duration: 0:00:00.06
   insert into ZZT3WSP7T8BMD000 
   select a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
   from [CUSTOMER_SLS] a11
   group by a11.[CUSTOMER_ID]
   Pass2 -  Execution Duration: 0:00:00.00
   create table ZZT36UF785VMD001 (
       CUST_REGION_ID SHORT, 
       WJXBFS1 DOUBLE)
   Pass3 -  Execution Duration: 0:00:02.18
   insert into ZZT36UF785VMD001 
   select a14.[CUST_REGION_ID] AS CUST_REGION_ID,
       avg((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] -
       a11.[DISCOUNT]))) AS WJXBFS1
   from [ORDER_DETAIL] a11, 
       [LU_CUSTOMER] a12, 
       [LU_CUST_CITY] a13, 
       [LU_CUST_STATE] a14, 
       [LU_DAY] a15
   where a11.[CUSTOMER_ID] = a12.[CUSTOMER_ID] and 
       a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and 
       a13.[CUST_STATE_ID] = a14.[CUST_STATE_ID] and 
       a11.[ORDER_DATE] = a15.[DAY_DATE]
    and a15.[YEAR_ID] in (2010)
   group by a14.[CUST_REGION_ID]
   Pass4 -  Execution Duration: 0:00:00.03
   select a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       a11.[ORDER_ID] AS ORDER_ID,
       a11.[ITEM_ID] AS ITEM_ID,
       a11.[EMP_ID] AS EMP_ID,
       a11.[ORDER_DATE] AS DAY_DATE,
       a14.[CUST_REGION_ID] AS CUST_REGION_ID,
       (a11.[QTY_SOLD] * (a11.[UNIT_PRICE] - a11.[DISCOUNT]))
       AS WJXBFS1
   from [ORDER_DETAIL] a11, 
       [LU_CUSTOMER] a12, 
       [LU_CUST_CITY] a13, 
       [LU_CUST_STATE] a14
   where a11.[CUSTOMER_ID] = a12.[CUSTOMER_ID] and 
       a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and 
       a13.[CUST_STATE_ID] = a14.[CUST_STATE_ID]
   Pass5 -  Execution Duration: 0:00:00.00
   create table ZZTS6MZ3N2BMD002 (
       CUST_REGION_ID SHORT, 
       WJXBFS1 DOUBLE)
   Pass6 -  Execution Duration: 0:00:00.00
   [Analytical SQL calculated by the Analytical Engine:
       select CUST_REGION_ID,
           ISNULL((1.96 * Stdev(WJXBFS1)), 0)
       from [previous pass]
   ]
   Pass7 -  Execution Duration: 0:00:00.00
   insert into ZZTS6MZ3N2BMD002 values (1, 127.4088118401)
   Pass8 -  Execution Duration: 0:00:00.00
   create table ZZTSMWZF9SRMQ003 (
       CUSTOMER_ID LONG)
   Pass9 -  Execution Duration: 0:00:00.36
   insert into ZZTSMWZF9SRMQ003 
   select distinct pa11.[CUSTOMER_ID] AS CUSTOMER_ID
   from [ZZT3WSP7T8BMD000] pa11, 
       [LU_CUSTOMER] a12, 
       [LU_CUST_CITY] a13, 
       [LU_CUST_STATE] a14, 
       [ZZT36UF785VMD001] pa15, 
       [ZZTS6MZ3N2BMD002] pa16
   where pa11.[CUSTOMER_ID] = a12.[CUSTOMER_ID] and 
       a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and 
       a13.[CUST_STATE_ID] = a14.[CUST_STATE_ID] and 
       a14.[CUST_REGION_ID] = pa15.[CUST_REGION_ID] and 
       pa15.[CUST_REGION_ID] = pa16.[CUST_REGION_ID]
    and (pa11.[WJXBFS1] > (IIF(ISNULL(pa15.[WJXBFS1]), 0,
       pa15.[WJXBFS1]) + IIF(ISNULL(pa16.[WJXBFS1]), 0,
       pa16.[WJXBFS1])))
   Pass10 -  Execution Duration: 0:00:00.00
   create table ZZTRX7EEWHNNB004 (
       DAY_DATE TIMESTAMP, 
       CUSTOMER_ID LONG, 
       WJXBFS1 DOUBLE)
   Pass11 -  Execution Duration: 0:00:07.89
   insert into ZZTRX7EEWHNNB004 
   select a11.[ORDER_DATE] AS DAY_DATE,
       a11.[CUSTOMER_ID] AS CUSTOMER_ID,
       sum((a11.[QTY_SOLD] * (a11.[UNIT_PRICE] -
       a11.[DISCOUNT]))) AS WJXBFS1
   from [ORDER_DETAIL] a11, 
       [ZZTSMWZF9SRMQ003] pa12
   where a11.[CUSTOMER_ID] = pa12.[CUSTOMER_ID]
   group by a11.[ORDER_DATE],
       a11.[CUSTOMER_ID]
   Pass12 -  Execution Duration: 0:00:00.00
   create table ZZTQFB0EMCJMB005 (
       CUSTOMER_ID LONG, 
       WJXBFS1 TIMESTAMP)
   Pass13 -  Execution Duration: 0:00:00.59
   insert into ZZTQFB0EMCJMB005 
   select pc11.[CUSTOMER_ID] AS CUSTOMER_ID,
       max(pc11.[DAY_DATE]) AS WJXBFS1
   from [ZZTRX7EEWHNNB004]    pc11
   group by    pc11.[CUSTOMER_ID]
   Pass14 -     Execution Duration:    0:00:00.78
   select    distinct pa11.[CUSTOMER_ID] AS CUSTOMER_ID,
       a13.[CUST_LAST_NAME] AS CUST_LAST_NAME,
       a13.[CUST_FIRST_NAME] AS CUST_FIRST_NAME,
       a15.[CUST_REGION_ID] AS CUST_REGION_ID,
       a16.[CUST_REGION_NAME] AS CUST_REGION_NAME0,
       pa11.[WJXBFS1] AS WJXBFS1
   from    [ZZTRX7EEWHNNB004]    pa11, 
       [ZZTQFB0EMCJMB005]    pa12, 
       [LU_CUSTOMER]    a13, 
       [LU_CUST_CITY]    a14, 
       [LU_CUST_STATE]    a15, 
       [LU_CUST_REGION]    a16
   where    pa11.[CUSTOMER_ID] = pa12.[CUSTOMER_ID] and 
       pa11.[DAY_DATE] = pa12.[WJXBFS1] and 
       pa11.[CUSTOMER_ID] = a13.[CUSTOMER_ID] and 
       a13.[CUST_CITY_ID] = a14.[CUST_CITY_ID] and 
       a14.[CUST_STATE_ID] = a15.[CUST_STATE_ID] and 
       a15.[CUST_REGION_ID] = a16.[CUST_REGION_ID]
   Pass15 -     Execution Duration:    0:00:00.00
   [Populate Report Data]
   Pass16 -     Execution Duration:    0:00:00.00
   drop table ZZT3WSP7T8BMD000
   Pass17 -     Execution Duration:    0:00:00.00
   drop table ZZT36UF785VMD001
   Pass18 -     Execution Duration:    0:00:00.01
   drop table ZZTS6MZ3N2BMD002
   Pass19 -     Execution Duration:    0:00:00.00
   drop table ZZTSMWZF9SRMQ003
   Pass20 -     Execution Duration:    0:00:00.00
   drop table ZZTRX7EEWHNNB004
   Pass21 -     Execution Duration:    0:00:00.00
   drop table ZZTQFB0EMCJMB005
   

• Pass0 and Pass1 compute metric M10.
• Pass2 and Pass3 compute metric M07.
• Pass4, Pass5, Pass6, and Pass7 compute (1.96 * M08). The group-value function for M08, StDev, is not supported by the database (Microsoft Access) so it is calculated by Intelligence Server.
• Pass8 and Pass9 obtain the list of valuable customers, evaluating the filter condition F03.
• Pass10, Pass11, Pass12, and Pass13 compute the Last Order Sales metric.
• Pass 14 displays the results for the report.
• All other passes drop the temporary tables.