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 1:
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, if it does not meet this condition it will use 0.
The result is that when using this metric it will only sum conversions from floodlight
1360445.
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.
Summing fields from one table based on a condition from another without a Utility Join.
For more information on Utility Joins please follow the link.
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_1').
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.
For more information on Meta tables please read the article here.
Create a utility join to join the data and meta table together.
Go to Datasources and choose the one want to use
Click 'Joins'
Add a new Join and select the table you wish to join to.
Choose the columns to match.
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.
Not creating a Utility Join if you bring in data from two tables.
Forgetting to put quotation marks around strings (words). This is not necessary if your condition is a number.
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.
For more information on composite metrics read here.