Sections in this category

Understanding & Defining Weighted Average Metrics

  • Updated

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:

  1. Create a new Metric, choose your data, and select “Weighted average” under the “Numerical” subheading of the “Metric calculation type” menu. 
  2. 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.

weighted_average_settings.png

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.

tip_icon_-_small.png 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

weighted_avg_example.png