SUM
This is the most commonly used metric definition for summing fields in a table. In the report engine metric definition section simply type SUM( Field ).
Example:
Common mistakes:
Forgetting to close brackets - all formulas need to have a closing bracket to complete the function
Starting the function with an '=' sign. Unlike Excel, the Report Engine only accepts mySQL syntax which does not require an '=' sign at the start of the function.
SUM IF
This is used when you want to sum a field in a table based on certain conditions. For example, you may want to create a metric that sums spend or conversions based on an attribute of the data like campaign or conversion floodlight.
In the report engine you will need to type: SUM(IF(condition, result if condition true, result if condition is false))
Example:
In the above example, we are writing a SUM IF statement which will sum the ct_conversions (click through conversions) and vt_conversions (view through conversions) from the Campaign Manager activity table if the activity ID (floodlight) is 1360445 or 1360446, if it does not meet this condition it will use 0.
Example 2:
In the above example we are writing a SUM IF which will sum media cost from the Campaign Manager delivery table if the campaigns have been categorised as 'Prospecting', if it does not meet this condition it will use 0.
This is a more complex definition as it relies on values from two separate tables. The condition in this equation relies on the Campaign Manager Campaign 'Meta' table where we have categorised campaigns by 'Strategy' (this has been mapped in the table field 'meta_9').
The result, if condition is true, in this equation relies on data in the Campaign Manager delivery table (dcm_data_aggregate) where the field Media Cost sits.
Common Mistakes:
Forgetting brackets between the SUM IF - the syntax is SUM(IF(
Forgetting to close brackets - this requires two closing brackets.
Forgetting to input a result if condition is false.
AVERAGES
Bright Analytics handles averages within the platform using composite metrics. This is a metric that is built using two already existing metrics which will give correct average subtotals in dashboards.
We advise to not to use AVG(field) within the Report Engine as this will attempt to aggregate and average which will result in incorrect subtotals in the platform.
Similarly we advise not to sum fields in a table that are already averages e.g. If you have a field in your table which is 'average time on site', you should not SUM this column as this would result in the sum of averages which is also incorrect.