Discovering mappers: A guide to effective data mapping

Mapping is a key process in data integration, aligning fields and formats to ensure seamless data flow across systems. Celigo provides user-friendly mapping tools that make it easy for users to connect fields between source and destination systems.

Designed for simplicity, mappers are intuitive for basic tasks yet robust enough to handle more complex scenarios, such as cases where conditional logic, multi-field mapping, or dynamic data transformations are required. They feature a clear visual interface that displays both the source and destination JSON structures, allowing users to easily reference sample input data from the source application. This simplifies the process of defining how data will be structured before being transmitted to the destination system.

Celigo provides two versions of its mapper tool, Mapper 1.0 and Mapper 2.0. This article covers best practices for data mapping and offers an overview of mapping options with practical use case examples.

Mappers overview

• Visual representation: Celigo mappers provide a clear visual representation of source and destination JSON structures, allowing users to reference input data while building the JSON structure for the destination application.
• Data type validation: Mappers validate data types to ensure accuracy and integrity.
• Advanced mapping features: Advanced features, such as handlebars expressions, dynamic lookups, static lookups, and additional settings, offer users greater flexibility and control.

Mapper use case example

A common use case for mapping is integrating an ecommerce platform like Shopify with an ERP system like NetSuite. The mapping tool ensures that order details (e.g., customer info, product data) from Shopify are accurately transferred to corresponding fields in NetSuite for seamless order fulfillment and inventory management. This automation reduces manual data entry and improves accuracy across systems.

Mapping enables the following:

• Data format conversion: Ensures that data from one system is automatically converted into a format compatible with the destination system, so it can be properly processed.
• Data transformation: Allows users to apply transformations, perform calculations, or set custom rules to tailor data as it moves through the integration.
• Data accuracy and validation: Celigo’s mapping tool includes built-in error handling and validation features, ensuring that only accurate and correctly formatted data flows through the integration.

Mapper 1.0

Mapper 1.0 is ideal for simple, 1:1 field-level mapping where fields have direct correspondence and no complex transformations are required. This structure is suitable for syncing basic customer information from Shopify to a CRM like Salesforce. Here, fields such as “First Name,””Last Name,” and “Email Address” can be directly mapped without additional configuration.

Let’s explore the field mapping types available in Mapper 1.0.

Field mapping types in Mapper 1.0

To begin editing mappings in a flow step, click on the integration tile from your homepage, open the flow containing the data you wish to map, and select the mapping button for the relevant step.

The mapping feature allows you to define how data is transferred between flow steps. Each mapping screen displays two columns of drop-down menus where you can choose the fields to be mapped.

Let’s examine the field mapping types available in Mapper 1.0. The first mapping type we’ll cover is Standard Mapping.

Standard mapping

Standard mapping refers to the straightforward, 1:1 mapping of fields from a source application to corresponding fields in a destination application. In this setup, each source field directly maps to a single destination field without any transformations, complex logic, or multi-field combinations.

This mapping type is typically used when data fields in the source and destination are identical in structure and format, making the mapping process simple and direct.

Standard mapping use case

You want to transfer customer details from an ecommerce platform like BigCommerce to an ERP system like Oracle NetSuite. Standard mapping allows the company to easily map fields like “Customer First Name,” “Email Address,” and other relevant customer data from BigCommerce to the corresponding fields in NetSuite.

Standard mapping allows for smooth and accurate data transfer since these fields have a direct 1:1 relationship and don’t require complex transformations. This ensures the ERP is always updated with the latest customer information, improving customer service and supporting more targeted marketing efforts.

To begin editing mappings in a flow step, click on the integration tile from your homepage, open the flow containing the data you wish to map, and select the mapping button for the relevant step. The mapping feature allows you to define how data is transferred between flow steps.

Each mapping screen displays two columns of drop-down menus where you can choose the fields to be mapped.

Hard-coded mapping

Hard-coded mapping is used when a field in the destination application will have the same value across all records. Hard-coded mapping assigns a fixed value to a field in the destination application, which applies to all records.

Hard-coded mapping use case

You need to record an employee’s Daily Status Report (DSR) description from a CRM system like Salesforce into a collaboration tool such as Slack. Using hard-coded mnel.

This ensures that the information is consistently delivered to the correct channel without needing dynamic mapping or additional configuration for each transfer, streamlining the process and maintaining accuracy.

Lookup mapping

Lookup Mapping dynamically references related data from external systems, which can be either Static or Dynamic.

• Static lookup: Uses fixed, predefined mappings. It’s simple, fast, and best for consistent values (e.g., mapping “CA” to “California”). It doesn’t use API calls or require real-time lookups, making it ideal for stable, unchanging mappings.
• Dynamic lookup: This retrieves data in real-time from a secondary system using API calls. It’s more complex to set up but ensures up-to-date, accurate information. It’s suitable for scenarios where data changes frequently (e.g., looking up a customer ID based on an email). Dynamic lookup also allows for real-time error handling if no match is found.

Let’s expand on each.

Static lookup mapping

Static Lookup Mapping is a type of field mapping that allows you to assign a fixed, predefined value from a source application to a corresponding value in a destination application.

It is useful when you need to map specific values directly without relying on dynamic data retrieval. For example, you can set up a static lookup table to map the state code “CA” from the source to “California” in the destination system.

In practice, you configure this by selecting Static: Value to value in the mapping settings and defining each pair of values you want to map. If a value is not found in the source data, you can set a default action, such as using an empty string, a null value, or a custom default value. This approach ensures consistency and accuracy when mapping predefined fields across systems.

Static lookup use case

You want to synchronize product information from an ERP system, like Oracle NetSuite, to an ecommerce platform, like  BigCommerce. However, the product codes in NetSuite do not directly match the product identifiers in BigCommerce.

To resolve this, Static lookup mapping is used. In this scenario, a predefined mapping table is created that associates each NetSuite product category with the corresponding BigCommerce product category code.

This static mapping ensures that the correct product information is matched and updated accurately whenever product data is transferred from NetSuite to BigCommerce. This approach maintains consistency across both systems, streamlining the product management process.

Dynamic lookup

Dynamic Lookup Mapping is a field mapping technique that dynamically retrieves data from a secondary source during import. Instead of mapping predefined static values, Dynamic Lookup Mapping performs real-time searches in the destination system or another application to locate a matching record based on certain criteria, such as an email address or ID.

This mapping method helps ensure data accuracy by dynamically referencing current records in the destination system.

Dynamic lookups are commonly used when:

• Data changes frequently, requiring real-time data retrieval to ensure accuracy.
• A source application must pull related data from a destination system, such as retrieving customer information based on an order ID.

Dynamic lookup use case

You need to accurately adjust inventory for three items simultaneously. The process involves utilizing subrecords, specifically through an Inventory Detail subrecord in NetSuite. You will export inventory changes from Celigo’s “mirror” API to an ERP system like Oracle NetSuite.

By using Dynamic Lookup Mapping, the integration can dynamically reference and map each inventory item’s details between both systems. This setup enables the creation of Inventory Detail subrecords in NetSuite from the exported data, supporting precise and synchronized inventory adjustments for all items concurrently.

Multi-field mapping

Multifield mapping combines multiple fields from a source application into a single target field in a destination application. This technique is particularly useful for integrations where data needs to be consolidated into one cohesive field. For instance, “First Name” and “Last Name” fields from a CRM system can be merged into a “Full Name” field in an ERP system.

Multifield Mapping simplifies complex data structures, enabling accurate data consolidation and enhancing data consistency in the destination application, ultimately streamlining the integration process.

Multi-field mapping use case

To synchronize product information from Oracle NetSuite to BigCommerce, this integration use case focuses on accurately calculating a customer’s store credit by combining base store credit data with conditional adjustments based on company affiliation. Here’s how it works:

The ERP system, NetSuite, manages a base store credit amount in the field {{store_credit}}. The loyalty rewards platform then applies additional adjustments to this store credit based on the customer’s company. Through Multifield Mapping, the integration combines the base store credit with conditional bonus amounts, as follows:

For customers affiliated with company “B”: The system adds a bonus of 10 to the existing store credit, resulting in {{store_credit}} + 10.
For customers affiliated with any other company: The system retains the base store credit value as {{store_credit}}.

This Multifield Mapping approach ensures that the loyalty platform’s store credit accurately represents both the base amount and any company-based adjustments. This method provides a customized and precise reward calculation for each customer, aligning with specific business rules and enhancing the customer experience.

Mapper 2.0

Mapper 2.0 is designed to handle all the simple mapping types and use cases of Mapper 1.0 and more. Mapper 2.0 can handle complex scenarios requiring advanced data transformations, such as conditional logic, array processing, or nested field mapping. For example, integrating Salesforce with Oracle NetSuite for sales order management may require merging multiple fields or applying specific conditional logic based on customer type.

Mapper 2.0 provides the flexibility to handle sophisticated data transformations, ensuring that complex business requirements are met.

Key features of Mapper 2.0

Intuitive drag-and-drop interface: Mapper 2.0 provides a user-friendly, drag-and-drop interface that simplifies the mapping process. You can easily match fields between source and destination systems without needing complex coding, making it accessible to both technical and non-technical users.

Automatic field suggestions: Mapper 2.0 offers smart field suggestions, automatically identifying potential mappings based on field names and types. This feature helps you complete mappings faster and reduces the risk of manual errors.

Advanced transformation capabilities: For complex data transformations, Mapper 2.0 includes tools like formulas, functions, and lookups. You can create custom logic directly within the mapping interface, allowing for greater flexibility in handling unique data requirements or business rules.

Real-time validation and testing: With real-time validation, Mapper 2.0 helps you spot potential mapping errors immediately. The testing feature allows you to validate mappings by running test data, ensuring data is transformed and transferred correctly before going live.

Support for nested data structures: Mapper 2.0 can handle complex and nested data structures, making it suitable for advanced use cases, such as working with JSON and XML files. You can map data across multiple levels, ensuring even the most complex data sets are accurately aligned.

Version control and reusability: Mapper 2.0 supports version control, enabling you to save, modify, or roll back to previous versions of mappings. Additionally, you can reuse mappings across multiple integration flows, making it easy to maintain consistency and save time on future projects.

Best practices for using Mapper 2.0

• Leverage auto-match for efficiency: Start by using the automatic field-matching feature to quickly set up basic mappings, then refine the details as needed.
• Use test data frequently: Regularly test mappings with sample data to validate transformations in real time and catch errors early in the process.
• Document custom transformations: Keep track of any custom formulas or transformation rules for future reference, especially in complex mappings.
• Save reusable mappings: When creating mappings that will be used in multiple flows, save them for easy application elsewhere, helping streamline future integrations.

Mapper 2.0 vs. Mapper 1.0

Mapper 2.0 is preferable to Mapper 1.0 for integrations that require complex logic, handling of nested data structures, or conditional data transformations. This advanced tool enables field mapping beyond simple 1:1 mappings and is particularly suited for scenarios such as:

• JSONPath notation: Mapper 2.0 supports JSONPath notation, enabling flexible and powerful mapping expressions. This lets you specify precise paths within complex JSON structures, making mappings more accurate and efficient.
• Complex JSON structures: Mapper 2.0 allows users to build intricate JSON structures with nested arrays and objects. This capability is a key improvement over Mapper 1.0, which does not support such advanced JSON configurations.
• Nested arrays: Mapper 2.0 supports creating and visualizing nested arrays, making it possible to work with deeply nested or hierarchical data. This feature is especially useful for mapping complex data structures commonly seen in API integrations.
• Filtering and navigation: Mapper 2.0 provides tools to filter by required or mapped fields, simplifying the management and navigation of large data structures. This feature lets users focus on relevant fields, streamlining the mapping process in complex integrations.

Mapper 2.0: Field mapping types

To explore the field mapping types in Mapper 2.0, follow these steps:

1. Accessing Mapper 2.0: Click the mappings button on an import flow step to open Mapper 2.0. If you need to switch between Mapper 1.0 and Mapper 2.0, use the toggle switch within the interface.
2. Default mapping view: Mapper 2.0 is set as the default interface for new mappings or imports without previously saved mappings.
3. Automatic selection of Mapper 2.0: If an import flow has any mapping configurations saved in Mapper 2.0, it will always open in Mapper 2.0—even if there are additional mappings configured in Mapper 1.0.
4. Handling dual configurations: When mappings exist in both Mapper 1.0 and Mapper 2.0, clicking Save will save configurations from both versions. However, during flow execution, only the mappings configured in Mapper 2.0 will be used. To use Mapper 1.0 mappings, you must delete all configurations in Mapper 2.0.

Celigo mapping types

The different types of mapping including standard mapping, lookup mapping (static and dynamic), multi-field mapping, and advanced Mapper 2.0 mapping—offer a comprehensive toolkit for handling data transformations across simple and complex integration scenarios.

Selecting the appropriate mapping type enhances data accuracy, simplifies transformation processes, and enables seamless integration between systems tailored to meet specific business requirements.