Tips & Tricks: The COALESCE function
One of the most useful functions available for data stream mapping in Datorama is the COALESCE function. This function allows you to seamlessly integrate data and prevent headaches, even as data providers evolve and alter their reporting.
Let’s dive right into an example. Here’s a simple daily advertising report, received from a provider:
It comes into Datorama via a data stream, mapped like this:
Tomorrow, the provider’s mappings will change, and your data comes in looking like this:
The data stream won’t be able to ingest this file. It’s going to be looking for Ad_Set_ID and Ad_Set_Name, and won’t know to look for the new names: Media_Buy_External_ID and Media_Buy_Name, respectively.
This is where the COALESCE function comes in! You can easily set your mapping for Media Buy Key to:
Using the COALESCE function, you can tell the mapping to take either of these two fields from the file– whichever one it can find. This function can be added ahead of time if you know that a provider’s field name is going to change, preventing even a hiccup in the data. Or, it can be added after the fact and the last file can be reprocessed so the historical and future data is clean.
Maintaining historical data is key. If you ever needed to reprocess the files from before the change, the new mapping wouldn’t be able to handle them. The COALESCE function allows Datorama to take files, old and new, and unify them into one cohesive story.