MDP Transpose Transformers

Use a transpose transformer when you want to reconstruct a flat-file format by translating columns of data into rows. For example, if a file has three conversion types in three separate columns that you want to aggregate into “total conversions,” they must be transposed into rows from columns. A column of data in a flat-file corresponds to a field that could be mapped in the Marketing Cloud Intelligence Data Pipelines data model. So anytime you can streamline the number of potential mapped fields, you free up fields that can be used for other dimensions or measurements. After transpose settings are set for a data stream, new files uploaded to the data stream automatically inherit the predefined pattern that was specified upon initial integration.

The transpose process is based on column indexes rather than names. For example, you select a column named “Click Conversion” to be the first column, and its position in the first file is #10. In the next uploaded file, the platform starts the transpose on column #10, no matter its name, which is helpful when your column order changes between files.

If you must transpose dates, such as 01-01-2020 and 02-01-2020, use the Keep Header Value As Is option when defining the transpose settings. This way, the year, which is a repeating word, isn’t separated from the rest of the dates.

Note Don’t map non-transposed measurements in the same data stream as the transposed measurements because their values are multiplied by the number of dimension values under the transposed object. If you want to map non-transposed measurements, create a second data stream using the same file. In the first transposed data stream, only include the measurements to be transposed. Include all the other measurements in the second data stream.

Examples of Files in Need of Transpose

Example 1 — Headers in your file look like this:

This file truly only has two measurements: View Through Conversions and Click Through Conversions. Activity type is the only distinction between these two measurements, which results in six total columns. Streamlining the number of columns results in some multiplier of the rows being consumed. In this case, three activities are consolidated into two measurement columns, so you now have three rows of data, or three times the number of rows that you started with.

Example 2 — Headers in your file look like this:

After transpose:

Before transpose:

After transpose:

If your file includes transposed measurements and non-transposed measurements, map the file twice. In one data stream, apply the transpose transformer, and then map the transposed measurements. In the other data stream, don’t perform the transposed transformer, but map the non-transposed measurements.

• MDP Apply Transpose Transformers
  Apply a transpose transformer when you want to reconstruct a flat-file format by translating columns of data into rows.