Rules are run at the data source level-of-detail (a row-level calculation). The results are aggregated using SUM to create the query result. This means that the value of a rule metric is calculated for each row of raw data in Funnel, after which the values are summarized.
Formulas are calculated at the visualization level-of-detail (an aggregate calculation), after summarizing. Formulas are used to calculate totals and can correctly calculate rates and ratios between other metrics.
If you want to calculate a rate or divide two metrics with each other, you will have to use the formula metric. Let's examine why this is, using cost per click (CPC) as an example. CPC is calculated by dividing Cost with Clicks.
The highlighted column is a rule metric called ‘CPC rule’. As CPC calculates cost/clicks we can, by dividing the values of cost for 2020-04-01 with the clicks for 2020-04-01, easily spot that the values in the red-marked column don’t look correct as 2 498.85 / 13 316 = 0.18 and not 52.65.
The reason for this is related to two things:
1. The calculation that happens behind the scenes is made per row of raw data in Funnel. This can be demonstrated by looking at the data with the highest granularity possible for that data set. Doing so will result in that the rule and formula metrics show the same value per row.
If you instead choose to look at the data set on a lower granularity, such as CPC per date, the values in the rule and formula metrics will differ.
The explanation to this is that the Total value for ‘CPC rule’ consists of an aggregated value based on numbers where the cost/clicks per row already have been calculated. To sum these values after the division has been calculated therefore returns the wrong value.
2. As mentioned, a rule metric is calculated on each row of raw data that in Funnel that matches the rule(s) in your custom metric. Most data sources that are connected to Funnel consist of more dimensions than just date (e.g. campaign, ad set etc.) which increases the number of rows. If that’s the case and the rule-based metric is greedy and applies to more than one row of data, then the calculation will be performed on every single row and then summed up which, again, will cause the values to be higher then what you likely would expect them to be.
When you want to calculate cost/clicks, we must base the division on the aggregated values for cost and clicks and then divide them by each other. This can be done by creating a custom metric based on formulas in Funnel or in your visualization tool/data warehouse.
The custom metric ‘cpc formula’ metric shows the correct values here, namely CPC per day:
The difference between this rule metric and the formula metric is furthermore in what stage of the data processing the division and addition take place (read more about this here). This is a crucial fact to take into consideration when you are planning on using the custom metrics outside of Funnel and exporting them to a data warehouse.
Funnel helps you on the way to getting the right numbers in your data warehouse in the way that we limit you from exporting formula metrics for the above-mentioned reasons. You can read more about this in more detail here.