Data harmonization reference

This reference article provides information about the data types and measures used in data harmonization.

Data types used in data harmonization

Equivalence between Adverity and SQLAchemy data types

The table below provides information about the most common data types in Adverity and their equivalents in SQLAlchemy. For more information, see the SQLAchemy documentation.

Adverity

SQLAlchemy

Notes

String

Unicode

Adverity applies the maximum character length specified in the Length field.

JSON

String

Adverity applies the maximum character length specified in the Length field.

Long

BigInteger

 

Float

Numeric

Adverity uses a numeric precision is 13, and a scale of 4.

Date

Date

 

DateTime

DateTime

For the Snowflake Destination, Adverity uses the data type TIMESTAMP_NTZ.

Boolean

Boolean

 

Percentage

Float

 

Currency

Float

 

Formula

String

 

These data types may be displayed differently in the Destination depending on the data types the underlying database supports.

Universal data types

The table below provides information about the data types in Adverity that are universally used in data management.

Data type

Notes

Example

String

Use for dimensions, such as names, titles, and descriptors.

EN_Campaign_21

Long

Use for integer metrics without decimal points.

16

Float

Use for non-integer metrics with digits after a decimal point.

16.523

Date

Use for dates. Adverity recognizes common date formats.

(Recommended) Use the ISO 8601 format YYYY-MM-DD.

This data type works with all Destinations except Explore & Present. For more information, see Specifying Schema Mapping to transfer data to Explore & Present.

2021-03-11

DateTime

Use for date and time. Adverity recognizes common datetime formats.

(Recommended) Use the ISO 8601 format YYYY-MM-DDThh:mm:ss.

This data type works with all Destinations except Explore & Present. For more information, see Specifying Schema Mapping to transfer data to Explore & Present.

2021-03-11T16:23

Boolean

Use for binary metrics. The values are either 0 (false) or 1 (true).

0 or 1

Adverity-specific data types

The table below provides information about the data types that are specific to Adverity.

Data type

Notes

Example

Percentage

Use for rates and percentages. Adverity automatically multiplies the values by 100. For example, 0.1 is displayed as 10 %.

0.1

Currency

Use for metrics that express values in a currency.

10

JSON

Use for layered metrics with JSON-compatible formatting.

{"firstName": "Jane", "lastName": "Doe"}

Formula

Use to send information about formulas to the Google Sheets Destination.

=A1 + B1

Duration

Use for metrics that express a time duration.

(Recommended) Use the ISO 8601 format PnYnMnDTnHnMnS.

P2DT3H

Measures used in data harmonization

The table below provides information about measure types, the mathematical function underlying the values in a metric field.

Measure type

Notes

Sum

Use for metrics that display the sum of values. Applicable for most metrics with values that can be summed up across several data sets, such as clicks, impressions.

Average

Use for metrics that display the average of values by dividing the sum of values by the number of data sets.

Count

Use for metrics that display the number of data sets.

Min

Use for metrics that display the minimum value across all data sets.

Max

Use for metrics that display the maximum value across all data sets.

None (Metric)

Use for metrics that do not have an underlying mathematical function, such as campaign reach.

None (Dimension)

If you transfer data to Explore & Present, Adverity treats the data type of a field that you set up with this measure as a String regardless of the data type you select in the Type field.

Specifying Schema Mapping to transfer data to Explore & Present

To transfer data to Explore & Present from a Datastream, ensure that all of the following conditions are satisfied:

  • Ensure the length of field names in the Data Extract is no longer than 60 characters.

  • Ensure the length of dimension values in the Data Extract is no longer than 2,700 characters.

  • In Schema Mapping, perform the following actions:

    • Map at least one dimension to a target field.

    • Map at least one metric to a target field.

    • Map exactly one field with the data type Date to the target field day. Ensure the dates in this field use the ISO 8601 format YYYY-MM-DD. Ensure no other fields are mapped with the data type Date.

    • Ensure there are no fields mapped to the data type DateTime. Explore & Present does not support the data type DateTime. To transfer fields with date data to Explore & Present (for example, campaign start date), map these fields to the data type String.

    For more information on Schema Mapping, see Harmonizing data.

Troubleshooting: Mapped a field to the wrong data type and transferred it to Explore & Present

If you accidentally map a source field to a target field of the wrong data type (metric instead of dimension or vice versa) and transfer the data to Explore & Present, you cannot solve this issue by simply changing the data type of the target field.

To resolve this problem, follow these steps:

  1. Create a new target field with the correct data type and with a different name.

  2. Map the source field to this new target field.

  3. Transfer data to Explore & Present.