Data mapping

The Data mapping section is a form-based interface to guide you as you specify how your imported data is processed and stored in Medallia Experience Cloud. It provides instant error messaging and real-time previews, and it builds the underlying XML import specifications, providing a low-code/no-code interface to administrators.

Screen capture of blank Data Mapping form

When done, click Close to the Importers screen; you do not need to also save on that screen.

To create a data mapping:

Load a sample file

Load a sample feed-file with Browse to create the data mapping. The file must be in the same format (that is, the same columns and same types of data) as future imports.

Restriction: The maximum size of the sample file is 2 MB (2,097,152 bytes).

Screen capture highlighting the Browse File to upload a new sample file option

Restriction: Experience Cloud requires a flat, pretty-print JSON structure which does not contain nested nodes. Additionally, uploaded JSON sample files cannot contain null values. The attribute names need to match those agreed on in the data specification to ensure they can be correctly processed by Experience Cloud.

Warning: Avoid using Excel files for production imports because large Excel files can cause large performance problems. Better is to convert the file to CSV first; CSV does not have the same size issues as Excel.

Medallia Experience Cloud analyzes the uploaded sample file and reports the number of columns and rows available for mapping. The columns represent the “input” fields and the rows represent records.

Example of the mapping for a successfully loaded sample file

Choose the import type

Import types are selected when creating a new importer. Click Edit to modify the import type, or click the plus sign to the right of the tab to create a new mapping.

Screen capture showing the import types on the form

Import types represent the types of records or data objects to import to Experience Cloud:

Feedback Invitations — Client's customer events, transactions, or events.
Existing Feedback — Client's existing feedback in survey, conversations or audio format (audio transcription format should be considered as conversation feedback).
Opt Out — Existing customers opt out preferences.
Users — Client's User and Role data that define access and privileges within Experience Cloud.
Organization — Client's Units and Unit groups representing the Organization Chart that allows filters and aggregations in the consumption of VoC results.
Lookup Table — Client's enrichment data.
Goals — Client's CX Goals based on unit, and employee goals when used for Employee experience.

Screen capture showing the import type option dropdown menu

Choose the database update option

Screen capture highlighting the processing options on the form

Click Edit to define the processing options.

Screen capture showing processing-option editing form

Processing options


Import type	Field	Description
All	Processing mode	The processing operations are: Create only: creates new entities; ignores existing entities. Create and update: creates new entities; updates existing entities (with matching keys). Update only: updates existing entities (with matching keys). No new entities will be created.
All	Column evaluation	Determines how to handle imported files or API data when there is a difference between the columns and the mapping rows: Allow extra columns: the import will not fail if there are extra column headers in the import file or the API compared to the mapping rows. Any missing columns will fail the import. A difference in the order of the columns is allowed and will not cause the import to fail. Allow extra or missing columns: the import will not fail if there are extra or missing column headers in the import file or the API compared to the mapping rows. A difference in the order of the columns will not cause the import to fail. Allow missing columns: the import will not fail if there are missing column headers in the import file or the API compared to the mapping rows. Any missing columns will fail the import. A difference in the order of the columns will not cause the import to fail. Allow no column headers: the import file or the API can successfully import data without column headers. The system will compare the number of columns with the number of mapping rows (minus the constant values, processing date and processing date and time values) prior to processing. If there is a difference in the number of columns, the import will fail. Then, the system will map incoming data in the order of the columns (left to right) to the order of the mapping rows (top to bottom). Exact match: the column headers in the import file or the API match the mapping rows exactly. Any extra or missing columns will fail the import. A difference in the order of the columns will not cause the import to fail.
Existing feedback/Feedback invitations	Primary unit	The field to use as the primary unit ID for the created episodes/surveys. Some companies have more than one unit field in the record. This option identifies the field that identifies the Unit associated with the record. The unit ID is necessary for several reasons, including but not limited to: Identifying unit data and unit group data to associate with the invitation. Sampling steps, especially quota and quarantine. Design (look and feel) to apply to the invitation and survey. Alert assignments. Org hierarchy filtering.
	Invitations mode	Determines how the records that are sampled will be treated. Select the necessary sampling operation: None. Push to survey engine, do not generate invites. Determine at record level. Push to survey engine, then generate invites. Do not push to survey engine. For information about sampling operations, see Sampling operation.
	Use survey	Use Survey Spec: Auto-Detect: uses the default survey program. Select from a list of survey programs.
	Sampling for quotas	Attempts to reach a target count of responses for various segments of the company. Select from a list of quota rules.
	Program assignment	Select the Experience Program the importer supports.
Users	Import as account	The import is run using the permissions (visible accounts and units) from the account with this username.
Users	Additional settings	Select the checkbox next to the option you want to activate: Restrict permissions based on primary role of account being processed: when setting the access for a role, the unit group visibility check will only use the role's permissions in the importing account instead of all its roles. Send new account emails: a post-processing action that indicates that new Account e-mails need to be sent after an account import finishes.
Lookup table	Lookup table	If the name is not specified, then a new lookup table is created using the imported file's name.
Organization/Users	Processing mode	The processing operation is: Create, update and delete/deactivate: new entities are created; existing entities (with matching keys) are updated; existing entities without a match in the feed-file are deleted.
Organization/Users	Delete mode	Determines whether to delete a data record or simply deactivate it. Delete: Existing entities without a match in the feed-file are deleted. Deactivate do not delete: existing entities without a match in the feed-file are deactivated.
Organization/Users/Goals	Update mode	Determines how data will be updated when a change to that data is imported: Update all matched records: all of the matched entities are updated. Update only if one data record matches: if a single entity matches, it is updated; if multiple entities match, none are updated.
Organization	Sync root	Overwrite all the persistent data tied to a record with org data in the feed file or the API.

Map input data to data fields in Experience Cloud

After choosing a processor, the Importer automatically:

Maps input fields to data fields in Experience Cloud.
Suggests new fields to be created in Experience Cloud to accommodate the mappings.

When Experience Cloud cannot automatically map a field, it does not show it in the data mapping rows and it has to be mapped manually clicking Add a mapping row on the Settings and Info menu.

Important: Review the details of each mapping to verify the automatic choices are correct.

The mapping details include:

The Input is the column in the incoming file, while the Medallia Field is the field in Experience Cloud to which the data will be imported.
Transforms alter the Input data to the format required for the Output. For example, a transform might be a JavaScript that converts state names to the state's abbreviation (such as California to CA).

Screen capture showing data mapping rows

Click on the field menu of a mapping entry to edit the field settings for each mapping row:

Mapping row option menu

Field Validation Settings provide additional control and conditions to apply to the data import.

Screen capture showing field setting options


Validation option	Description
Value is required	The value must be provided and must be successfully parsed, otherwise the whole row is discarded. For multi-valued fields, the entire record is dropped, if a single part is incorrect. For example, when parsing e-mails with `\|` as delimiter and the input is `a@example.com\|foo\|b@example.com` the record/row is dropped because `foo` is not a valid email format.
Use for duplicate checking	Same as Value is required, but the value is also used as a key to identify duplicates by comparing the input value against the values for the output field already in the database. If the Processing Option is: Creating only and there is no duplicate, the record is created; otherwise (duplicate exists), the record is dropped. Updating only and there is a duplicate, the record is updated; otherwise (no duplicate), the record is dropped. The Survey processor looks in the Survey table, while the Episode processor looks in the Episode table. For the Account processor only one field can be used, and it must be either Username or Company Account ID. Note that neither of these fields are updatable, and attempting to update one when the other is the required duplicate check just ignores the new value.
Convert invalid to null	The value must be provided and must be (at least partially) successfully parsed, otherwise the whole row is discarded. For multi-valued fields, the parts that are not correct are dropped, while the good data are kept. For example, when parsing e-mails with `\|` as delimiter and the input is `a@example.com\|foo\|b@example.com`, the `a@example.com` and `b@example.com` are kept but the invalid `foo` address is dropped. The Detailed Processing Report notes the dropped value, but the record is still processed.
Ignore missing values	If selected and the value is not provided on a record, the missing value is ignored during an update. For example, when updating an account and the input record is missing a value for an email field: When this option is selected, the existing email address in the database remains untouched. When this option is omitted, the existing value is replaced by a blank value. This option cannot be used on fields that are used for duplicate checking. Important: Fields might shown as modified on the detailed processing report even if they are not modified by Experience Cloud.
Default Value	Value to use when this field is missing a value in the input. Furthermore, when Convert invalid to null is selected, this value is used if the one in the input data fails to be properly parsed.
Date Format	Available for Date/Time fields. Date format you are planning to import within this field.
Time Zone	Available for Date/Time fields. Set time zone for incoming data.
Map by	Available for Multi-valued fields, which can store multiple values. When using multi-valued fields you can reference: The field name. The sequence number of enumerated fields. The field's numeric value.
Handle multi-values by	Available for Multi-valued fields. When using multi-valued fields you can: Replace existing values — The values specified in the input are set as the current ones, discarding the previous values. If a field is mapped multiple times (even from different rows with the same duplicate checking key), then the last mapping from the last row is stored. Remove from the existing values — The values specified in the input are removed from the ones currently stored in Experience Cloud. Those values to be removed, which are not currently stored in Experience Cloud, are skipped. Append to existing values — The values specified in the input are added together from multiple mappings even from different rows (as long as the duplicate checking key is the same), and then added to the ones currently stored in Experience Cloud. Sync — The values specified in the input are added together from multiple mappings even from different rows (as long as the duplicate checking key is the same), and then stored in the system. Notice that, unlike the append option, the values currently stored in Experience Cloud are discarded and not included. Note: By default, Experience Cloud discards the values stored in Experience Cloud and sets the ones from the input file.

Warning: Experience Cloud supports field names that contain multiple underscores and it treats them as different fields. The field validation process does not support this: if you use multiple underscores in the middle of an output target field, they are replaced by a single underscore when the specification is validated. This mismatching in how field names are used causes unexpected issues that are difficult to detect. For example, if there is an existing Event field by the name of e_email (with one underscore) and, by mistake, in the definition you use e__mail ( with two underscores) without noticing it, there is no validation exception; the validation process treats both fields as the same field. This is only an issue when there are different fields whose only difference is the number of underscores, because when updated on an importer, only the field with a single underscore is updated.

Use the Notes field to describe mapping.

Click on the left of the Input to use special input types, including ones for a constant value (constant), the processing date (today), and the processing date and time (now).

Screen capture showing special input type selection

Warning: Field validation settings do not apply when special input types have been selected.

Select Create a transformation to alter the input data to the format required for the output. For example, a custom formula that converts agents' full name into agents' first and last name.

Screen capture showing the custom formula editor for importers

When viewing an importer, hover over transforms to see the full formula transform being done for mappings. You can select the formula for analysis.

Screen capture showing tooltip with full formula transform being done for the mapping

If a field is marked as "JavaScript" to indicate that it has a JavaScript mapping transform in Medallia Setup, contact your Medallia expert for assistance.

Advanced mappings cannot be modified or switched to regular custom formulas in Admin Suite, but you can hover over the JavaScript modal to see the transform.

Image showing field marked as having a JavaScript mapping transform in classic setup.

Formulas used by Importers are the same as used in K- fields, except for those that belong to org hierarchy functions.

You can also define a transformation with the custom formula editor by clicking Add on the Transform column for the desired row, and selecting Formula Transform.

Screen capture showing transform options for a row

Select Mapping Transform to define a set of one-to-one mapping between input data and output values. Maintain unmapped input values for the output or set a default value.

Screen capture showing Mapping Transform screen

Tip: Note that this option is used when the value of input column does not match a key in the mapping, while the default value configuration in Field settings should be used when the field is missing a value in the input.

Create and sync new fields

During data mapping, you have the option to map an input to a new field by eliminating the default mapping, and clicking Create a new Field on the Output dropdown.

Screen capture highlighting the Create a new Field button

When creating a new field you provide the Name, Field ID, and select the data type. For more information, see Field categories and Program fields.

Example of the Create a New Field form, with a new field named 'Code number' of type Integer

After mapping to a new field, a blue bar indicates the need to sync fields to add the new fields to Experience Cloud database.

Important: Employ caution when creating fields from this page. A new Data type will be created for each new field even if the data type already matches a data type already in Experience Cloud, and also when more than one field being synced have the same data type.

Save the data mapping

Click Save (frequently) to save changes to the specification.

When there are validation issues with the mappings, the screen will show a warning message. Click Expand to review the details of each mapping.

Screen capture highlighting the validation issue warning message.

When done, click Close to go back to the Importers screen; you do not need to also save on that screen.

Test the data mapping

Click Test at the bottom of the screen to upload and process a file in pretend mode: the file is processed but the records are not added to the database.

Screen capture showing the Test button and the file upload tool

Drag a test file or click Browse files to upload and process a file.

Restriction: The maximum size of the sample file is 2 MB (2,097,152 bytes).

Upload a file to validate the importer and its mapping. Testing this file will provide a reference for how the importer will work with the incoming data.

Note: Large files can take longer to process, Experience Cloud indicates when the file upload is complete.

Test report showing duplicate, discarded, and processed records and also showing test output results

The test-result report shows the total number of records, and also duplicates, discarded, and processed records. The test-output report shows records created, updated, and deleted.

Click Download report to download the report or Download test file to download the processed file.

Click the test menu to see the pre-processing options:

Pre-processing option menu

Reprocess last test file: Reprocesses the uploaded file and adds the records to the database. This option is only available after a file processed in pretend mode was processed without error.

Important: To comply with GDPR policies, files are deleted after 30 days.
View last report: View last pretend processing report.
Download last report: Download the last pretend processing report as an XLSX file.
Download last test file: Copy the file to your computer for examination.