The ability to turn a rolled-up measure inside a View into a new transaction-based measure is useful in many circumstances. At Spendata we call this "recursive segmentation", or "View-based measures". It’s the same as extracting the View to a file, then loading the file back into the system and linking it to Fact — except we do it in real time as the source View is filtered, and we accomplish it without external files or linkages.

Scenario: Sales Analysis

An organization has a number of products for sale. Because of acquisitions, legacy contracts, and time-varying incentives to sales staff, pricing for the company’s products did not always follow a logical pattern. Management wanted to understand the pricing patterns for each product in order to bring prices under better control.

The company’s transaction dataset contains Customer, Industry, Unit Price, Quantity, Product, Date, and Salesperson, among other fields. To begin the analysis process, we load the data and build rolled-up Views (or measures) based on each of the columns in the dataset — a process requiring two or three minutes.

The company had lost track of "list price" for many of the products, so the price was whatever had been negotiated for an individual deal. Therefore the first objective was to establish a rational list price for each of the products, which in turn allows an analysis of variation from list. To do this, we build a View of Product with measure average(Unit Price). Let’s call this View "P1". We then build a View-based Measure "Avg UP" based on P1 — done with a few mouse-clicks and processing time measured in seconds.

Armed with Avg UP at the transaction level, we can now build Views of Product (by Salesperson, by Date, by Customer) with the discount (or premium) from "Avg UP" as one of the measures.

However, Avg UP isn’t particularly useful as it stands, because the average is affected by outliers. There are Customers with whom the company has a special relationship or a special deal, Salespersons who were known to have cut especially bad or especially favorable deals, and sales to Customers in Industries with different pricing models that don’t fit into a generalized matrix.

To account for these outliers, we harness the implicit power of View-based measures. By simply filtering the outliers out of View P1, a curated Avg UP measure is automatically and instantly re-derived and re-displayed (with any dependent calculations re-calculated as well) in every View.

To calculate curated average price for Industries with different outlier definitions, we create additional Views and View-based measures as necessary.

Outcome

The relative pricing analysis identified which Customers were paying less than the curated average price for particular Products. This allowed the company (1) to identify Products and Customer segments (segmented by Industry and Quantity) where discounts might not need to be offered, and then (2) to build and map a qualitative determination of the likelihood of retaining business despite the price hike.

After field-testing a pricing model based on this analysis, the company lowered discounts to some Customer segments for certain Products, increasing its overall margins.