Go to TapClicks.com

Understanding Calculation Post Aggregation

  • Updated

Before you begin: In this document, only data sources are mentioned, but it applies to channels as well.

Pre-aggregation calculation means that the calculation is computed for each row of the database, whereas a post-aggregation calculation means that it’s computed on the already reduced data through the aggregate function. Depending on the calculation formula, you have created, TapClicks intuitively determines where the calculation should be computed in the pipeline, either before or after the data is reduce. However, there are times where you may want to override the default behavior.  This why the Post-aggregation option has been exposed when building a calculation. The options you can select are as follows:
  • "Auto" means that the calculation will behave based on the TapClicks default logic: based on the calculation formula, the code decides automatically if the calculation has to be computed after the aggregation (thus in a "post-aggregation" phase) or before.
  • "Post Aggregation" = "Yes" / "No" stands for bypassing this automatic behavior and let the user decides when the calculation is computed: after ("Yes") or before ("No") aggregation.
Note, if your calculation is defined already containing a custom aggregate function, the "Post Aggregation" option cannot be used.

 

Example #1

To illustrate the operation of the data pipeline described above, let’s create a simplified example with this database:

We want to create a widget grouped by campaign name, and add a calculation that computes the CTR value (100*clicks/impressions). The standard SUM aggregate function is used when grouping clicks and impressions.

 

Post-aggregation result

If the calculation is computed after the aggregation (i.e., it’s a post-aggregation calculation), then data is first grouped like this:

Then the calculation is computed as follows:

 

Pre-aggregation result

In this case, the calculation is computed for each database row:

In this case, the AVERAGE aggregate function is used when grouping calculation data and it gives this result:

The result is different, so the choice of computing the calculation before or after the aggregation matters. Here, obviously, the correct one is the post-aggregation calculation.

 

Example #2

To measure the performance of a campaign, we'd like to get the number of days where the number of clicks is > 100. We'll use the same database and the same widget configuration. This time, the calculation is defined using this formula:

IF (Clicks >= 100) THEN 1 ELSE 0 END

The SUM aggregation function is used in this case. With a post-aggregation calculation, we get this final result:

Definitely, this result isn’t what's expected. With pre-aggregation, the calculation is first computed at the database row level:

Then, when aggregated, the final result is now correct:

Related articles