Skip to main navigation Skip to main content Skip to social media links Skip to footer navigation
Sign In

Tips & Tricks: The COALESCE function

Matthew Dykeman

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.

For more information about the COALESE function, visit our knowledge base.

Make sure you stay up-to-date with all the latest AdTech, MarTech, Data and Datorama-related news by following us on TwitterFacebook and LinkedIn.

Marketing Intelligence Report

Marketing Intelligence Report

Explore the first edition the Marketing Intelligence Report for an inside look into how the new growth imperative is affecting marketers across every industry.

Download Now