Before you begin: This article covers best practices for applying calculations to channels. Following these guidelines helps you avoid common issues and ensures that your data displays accurately in dashboard widgets.
Channel Calculations Overview
When applying calculations to channels, you may encounter unexpected results due to how the data is surfaced to dashboard widgets. Channels allow you to create unions between data tables. When surfacing data to widgets, a channel data table is created from the source data tables. Calculations are then applied to this “initial data set” (i.e., each individual row) before widgets apply Group Bys and aggregate the data for display. To maintain better control over these calculations, it is important to understand when and how to use SUM and AVERAGE functions.
Best Practices
Create Calculations at the Data Source Level
In most cases, calculations should be created at the individual data source level, not at the channel level. Once calculations are defined in the data source, they can be applied to the channel field, ensuring accurate results across multiple data sources.
Channels are designed to handle aggregation logic automatically. If the fields in the data sources have the same underlying formula, the channel respects that formula and avoids erroneous sums or averages.
Use SUM and AVERAGE in Channel-Level Calculations
If it is necessary to apply calculations directly to a channel, use SUM functions in your formulas. This ensures the correct aggregation of the data from all rows before the final calculation is performed. SUM functions work in both the Basic Calculator and the Advanced Calculator.
- Avoid: ((Clicks * 1000) / Impressions)
- Use: ((SUM(Clicks) * 1000) / SUM(Impressions))
This approach ensures that the component values from all rows are aggregated correctly before calculating a percentage or rate.
Use AVERAGE functions when a field is already a score or rate in the source data and has no component fields to sum. Without AVERAGE, the calculated value for each row is summed when the widget aggregates the data, which inflates the result.
- Avoid: ((Quality Score / 10) * 100)
- Use: ((AVERAGE(Quality Score) / 10) * 100)
AVERAGE returns a simple average across rows and does not weight each row by volume. When component fields are available, use SUM functions on the components instead.
Example
Consider: Three widgets are pulling data from the same channel, and the “CTR (%)” field is calculated.
- In one widget, the formula (Clicks / Impressions) * 100 returns 12.96%, but the expected result is 4.89%.
- This discrepancy occurs because the system calculates the CTR for each row individually, and then sums those values, leading to incorrect results.
Solution: Add SUM functions to the formula as follows: (SUM(Clicks) / SUM(Impressions)) * 100
When this refined formula is applied, the widget returns the correct value of 4.89%, as it correctly aggregates clicks and impressions across rows before applying the formula.
Related Articles
For additional information on calculations, aggregation logic, and troubleshooting, see the following resources:
- To read more about aggregation logic, see Understanding Calculation Aggregation Options.
- To see a complete list of calculation functions, see the Advanced Calculation Function Reference.
- For more information about calculations in general, see FAQs: Calculations.
- To learn about how Audit Logs can help you track changes to your calculations, see Understanding Audit Logs.
By following these best practices, you can ensure that your data is accurately reflected in widgets, and that calculations on channels behave as expected.