Skip to main content

How to Split Metrics Downwards in Data Source?

Scenario Introduction

When two metrics in a data source are not on the same statistical dimension but need to be calculated together (e.g., arithmetic operations), we need to split one metric downwards or aggregate the other upwards so that both metrics are on the same dimension.

For example, in the sample data source, the "Order Increase" metric is on the dimension of date, restaurant, platform, and promoted product, while the "Valid Orders" metric is on the dimension of date, restaurant, and platform. "Valid Orders" is repeated across multiple promoted products. When calculating the order increase rate, we need to unify the numerator (order increase) and denominator (valid orders) to the same dimension.

A common approach is to aggregate the order increase up to the date, restaurant, and platform level. However, after aggregation, you cannot analyze by promoted product. Sometimes, the business requires splitting valid orders down to the date, restaurant, platform, and promoted product level according to certain logic. The splitting logic can be average or weighted, depending on business needs.

This case introduces the average splitting method.

image.png

Steps:

  • Process the data source in ETL;

  • Add a calculated column: Data Count. Calculate how many rows exist for each date, restaurant, and platform (i.e., how many promoted products).

COUNT([Promoted Product]) OVER(PARTITION BY [Store Code],[Date],[Platform])
  • Add a calculated column: Valid Orders_Split. Split valid orders equally by the number of promoted products.
[Valid Orders]/[Data Count]

image.png

  • Use the ETL output dataset to create a card;

  • Add [Valid Orders_Split] to the card and rename it to Valid Orders.

  • Calculate the order increase rate. After splitting valid orders, you can calculate the order increase rate at the finest granularity (date, restaurant, platform, promoted product) or aggregate upwards as needed.

SUM([Order Increase])/SUM([Valid Orders_Split])

image.png