Sisu provides the Weighted Average Metric type to allow you to understand a column’s average when the occurrence of certain row “events” is unequal or unbalanced, as opposed to simply calculating the straight average of individual lines. This allows you to “weight” certain rows of your data table when some rows in their data are more important or significant than others.
Related Articles:
Weighted Average Metrics
Weighting provides a way to incorporate logic and reality beyond the raw data into Sisu to inform the Metric’s calculation.
Use the Weighted Average Metric type if you have an aggregated dataset where the event level data was perhaps trillions of rows and too large to manage. Defining an average Metric based on the aggregated dataset would not provide a realistic picture, so using a weighted average Metric will capture the additional context lost when the dataset was “rolled up”. Each value in the weight column corresponds to the number of observations of the given row in the Metric column.
Here are some typical examples:
- In marketing, a sample Metric might be cost per lead weighted by number of leads
- When investigating financial fraud, a sample Metric might be the fraudulent transaction amount per cohort weighted by number of transactions per cohort
- For customer success, a sample Metric might be average hours spent on customer per product/service weighted by number of customers per product/service
Let’s look at an example of a Metric using Weighted Average to determine the decrease in weighted average of cost per lead, using the sample data below.
Weighted average of cost per lead =
SUM(Cost per Lead * Number of Leads) / SUM(Number of Leads)
Dataset aggregated by lead |
|||
Campaign ID |
Cost per lead |
Number of leads |
Source |
Campaign 0001 |
20 |
20 |
Facebook Ads |
Campaign 0002 |
5 |
30 |
Facebook Ads |
Campaign 0003 |
2.5 |
10 |
Google Ads |
Campaign 0004 |
8 |
50 |
Google Ads |
Campaign 0005 |
5 |
10 |
Google Ads |
The overall Metric is calculated as follows:
Overall Metric = (20*20 + 5*30 + 2.5*10 + 8*50 + 5*10) / (20+30+10+50+10) = 8.54
An example Fact that Sisu might return with the Analysis is a negative impact on weighted average:
Source = Facebook Ads → Metric = (20*20 + 5*30) / (20+30) = 11
Configuring a Weighted Average Metric
To configure a weighted average Metric in Sisu:
- Create a new Metric, choose your data, and select “Weighted average” under the “Numerical” subheading of the “Metric calculation type” menu.
- Define the Metric column and the weight column. See the equation under the tooltip when hovering over the “i” button next to the calculation type menu to see how the Metric is calculated given the columns you select.
Sisu automatically performs the weighting calculation on the Metric column when a user specifies the weighted average Metric calculation type and a weight column.
There are a few constraints when using a weighted average Metric:
- The weight column should be the column with the unit of observations (e.g. transaction count, number of events, etc.) for the results to be statistically significant.
- For a given table with a fixed Metric, there will only be one best weight column.
- The weight column must be a non-negative number.
- Using other types of columns as weights (e.g. transaction amount on a dataset of financial transactions) is alright but might yield results that over-focus on weights at the expense of statistical significance (e.g focus on high-value transactions on a financial dataset).
Interpreting a Weighted Average Metric
The Metric value represents the weighted average of the Metric column weighted by the weight column. The other summary statistics represent the weighted sum and total weight of the Metric.
Refer to Understanding Impact for more details. |
For example, the fact “USER_GENDER = M” in the example below can be described as::
Where User Gender = Male,
The weighted average of Order Value weighted by Number of Items is
38.1 in the first time frame and 36.9 in the second time frame