Before you begin: This article will guide you through the best practices for applying Calculations to Channels. By following these guidelines, you will avoid common issues and ensure that your data is displayed 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's important to understand when and how to use SUM and AVERAGE functions.
Best Practices
1. 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.
2. Apply SUM and AVERAGE Functions in Channel-Level Calculations
If it is necessary to apply calculations directly to a channel, ensure that SUM functions are used in your formulas. This ensures the correct aggregation of the data from all rows before the final calculation is performed.
- Avoid: ((Cost * 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.
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:
When this refined formula is applied, the widget now returns the correct value of 4.89%, as it correctly aggregates clicks and impressions across rows before applying the formula.
What's Next?
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 Calculations 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.