You are here:
Mapping Formulas in Marketing Intelligence
Mapping formulas transform source data during ingestion, before it’s stored in Marketing Intelligence. Use them to clean, standardize, and enrich incomplete, inconsistent, or incorrectly formatted data without editing the source files. Unlike calculated fields in the semantic layer, which apply logic at runtime, mapping formulas permanently change the stored values.
Why Use Mapping Formulas?
Use mapping formulas when you need to prepare data without editing the original source file. Mapping formulas help reduce manual cleanup, fix mapping validation issues, and improve downstream reporting. Mapping formulas are especially useful when: A required target field doesn’t exist in the source file. A source value needs cleanup before it can be mapped. Multiple source fields must be combined into one target value. A value must be standardized across every row. A calculation should be stored for better reporting performance.
Important Considerations
- Mapping formulas change the data that's ingested into Marketing Intelligence. If a formula is incorrect, you must update the formula and reprocess the affected data. For complex or risky logic, test the approach carefully before applying it broadly.
- Mapping formulas use the supported formula functions available in the mapping formula editor. If you need a specific function, confirm that it's supported before relying on it in your mapping.
Mapping Formula Examples
Remove Unwanted Characters from Source Data
A source file includes a spend column with values such as $10, $25, and $100. The target spend field expects numeric values only, so the dollar sign can prevent the value from being processed correctly. Use a mapping formula to remove the dollar sign before the value is stored in Marketing Intelligence. This lets you keep the original source file unchanged while still mapping the cleaned value to a numeric measurement.
For
example, remove $ from $100 so the value is ingested as
100.
Add a Value That Doesn’t Exist in the Source File
A source file contains spend values in USD, but it doesn’t include a currency column. If the target data model requires a currency value, the file can’t be mapped completely without adding that information. Instead of editing the source file and filling every row with USD, use a mapping formula to provide a static value during ingestion. Marketing Intelligence applies that value to each ingested row.
For example, use a mapping formula with
USD as a value, and map it to the currency target field.
Create a Required ID from Multiple Fields
A source file includes fields such as ad_size and ad_format, but it doesn’t include a required ad_id field. If the target model requires an ID, you need a consistent way to create one from the data that’s available.Use a mapping formula to combine multiple source fields into a single value. The combined value can act as an ID, as long as the fields together create a unique and stable identifier.
For example, combine ad_format, an underscore,
and ad_size to create values such as
video_300x250.
Calculate a Missing Measurement
A source file includes video_views and
spend but it doesn’t include a cost_per_view field. If
you need cost per view for reporting, use a mapping formula to calculate it from the
available data.
For example, divide spend by
video_views to calculate
cost_per_view.
Standardize Values for Reporting
A source file might omit a channel name or use inconsistent values, such as google, Google, and Google Ads. These inconsistencies can split data across different categories in reports. Use a mapping formula to assign or standardize the values during ingestion so that reports group them consistently.
For example, map google, Google, and Google Ads to Google.
When to Use Mapping Formulas or Semantic Calculations
| Use a mapping formula | Use a semantic calculation |
|---|---|
| Data must be fixed before storage | Testing logic or exploring analysis |
| Value is needed by multiple reports | Calculation applies to one report |
| Source data has validation issues | You want flexibility to change later |
