Datorama Tips & Tricks: Calculated Measurements
This post is part of our Tips & Tricks series, highlighting some of Datorama’s most interesting or useful features. This series features guest authors from our Client Success team, who are hard at work every day helping Datorama customers with everything from data prep to visualization.
This post is by Alex Hauer, Client Success Manager.
You know the feeling – sometimes you get raw data and it doesn’t quite have the information you need. Your DCO gives you Impressions by Creative, but doesn’t have Media Cost. Conversely, your ad server has Media Cost, but it doesn’t have delivery by Creative! Your hands are tied when your client asks you to report out Media Cost by Creative.
Fortunately, we have a solution for that: Calculated Measurements. With a Calculated Measurement, you can write expressions that include all types of measurements – mapped, Calculated, Filtered – dimensions, logical operators, and/or functions to get your desired output. Datorama hosts a vast library of internal functions that are reminiscent of Excel. Some popular formulas are: IF(), SUM(), DATEDIFF(), VLOOKUP(); but the library expands from there to include STDEV(), FUZZYMATCH(), LOG_TREND(), etc. For those users who are more comfortable in other computing languages, Calculated Measurements also support Java, R, and C programming.
Calculated Measurements can be found in ‘Connect & Mix’ under the Measurements module:
But, let’s get back to that DCO spend distribution. How would we go about that? The answer is: window functions (not to be confused with window dressings). Window functions allow you to return a single value across a defined data range. Let’s look at the below example:
This is a simplified version of the initial DCO example – we have Media Cost at the Media Buy Key level and Impressions at the Creative level. We now want to see Media Cost at the Creative level.
To redistribute the Media Cost across the Creatives, we must first implement a window function. The window function we’ll use in this instance is SUM(). The formula syntax for SUM() is:
Following that syntax, in this example we would create a Calculated Measurement called ‘SUM Window Function’ using the below expression:
By SUMming Media Cost by Media Buy Key, we now can create the following view in the platform:
As you can see, the $100 Media Cost is now spread across all Creative Keys associated with the Media Buy Key. This is the first step to getting your DCO redistributed cost! The next step, creating a percentage of Impressions, also utilizes a window function:
In this step, the SUM formula creates the denominator of the Percentage of Impressions, by aggregating all Impressions at a Media Buy Key level. We now know how much of the Media Cost should be distributed to each Creative, based on delivery. We are now able to get our final output by creating a Calculated Measurement, ‘DCO Cost’:
This final output now solves your client’s question “How much are we spending per Creative” even though your DCO doesn’t report spend!
Through a series of three Calculated Measurements, we transformed our raw data into our desired output. Now the client is happy and you saved yourself time, as this solution can be applied across multiple use cases in your Workspace. For those of you willing to try, this solution can even be consolidated into one Calculated Measurement.
It’s not as hard as you might think. Why not give it a try?