SSAS for web sales: A cube model for click contribution assessment
Today it is typical to trace and protocol the web clicks that lead to the sale. You can extend your sale cube with click data collected to answer questions like “How big is the contribution of web resource X to our sales?” The answer can be for example directly used to quantify how much you are ready to pay to your web partners bringing you customers.
With Many-to-Many and MeasureExpression features of multidimensional Analysis Services it can be pretty simple.
Let’s suppose we have:
- Sales data featuring Dim Customer
- Data with click chains per customer
- A set of assessment rules for click contribution
An assessment rule is a percent distribution over the click chain. Here are some examples:
- Last click gets 100%
- Equal distribution over all clicks in the chain
- First and last get 50% each
- Some gradual uplift from first to last
Given we want to have these distribution rules in a form of a relational data. It means that each rule should be instantiated for different chain lengths we have in our data. For example for the “equal” rule:
Note that the weighting we want here can be generalized to scalar vector product and further to vector and matrix operations that many-to-many and measure expression of MSAS allow.
Now here is our cube extension model for AdventureWorks (blue are dimensions, yellow are facts):
And here is the DSV of the extention:
Some comments on the model:
- Web Clicks Facts: click chains per customer
- Web Media: the web resources we want to assess
- Web Count Rules Facts: the table of weights for rules
- Web Count Rule: the dimension of assessment rules (only key and name, IsAggregatable=false)
How to make it work in cube
Our ultimate goal is to apply Weight to the sale measures under selection of some certain Web Count Rule and in the context of Web Media.
First let’s bring Weight to the clicks for Web Media. For that we need a measure expression:
(Don’t forget to switch data type from integer to double.)
…and we need a many-to-many from Web Count Rule to Web Clicks Facts:
Let’s test it for one click chain. Here is the sample data for Web Clicks Facts:
And here is what we get in cube:
Now we have to bring this calculation further: through customers to sale measures.
We can chain a many-to-many through several measure groups, but we cannot do the same with measure expressions! So the last step should be done with the cube script in the same way we simulate the behavior of measure expression for the Standard Edition of MSAS.
Lets define the copy of “Internet Transaction Count”:
…and put an assignment in cube script:
- ([Measures].[Internet Transaction Count Weighted],
- [Measures].[Internet Transaction Count]
- [Measures].[Web Clicks Count Weighted];
We made this assignment at the bottom level of the Dim Customer. The aggregation to the upper levels will be made by MSAS itself since the measure “Internet Transaction Count Weighted” is not calculated.
Here is our target assessment in cube:
(our sample customer has only two transactions)
The big cardinality of Dim Customer (millions) can cause a poor performance at this point. For one of our projects with tens of millions of customers we had to join Sales and Click facts in one measure group. It costs us a bigger volume but now being able to use aggregations we ended up with an acceptable performance for our users.
Another small optimization you can consider is bringing Dim Web Length and Dim Web Position into one cross-joined dimension.