Standardize URL Formats before Importing Data

In Magical Import, URL formatting becomes important when a website or URL column is used to match imported rows with CRM records. CRM data can be inconsistent when different representatives enter URLs in different formats, and external data sources often format website addresses differently, too.

The same company website might appear in any of these formats:

• https://www.acme.com
• http://acme.com
• acme.com
• www.acme.com

A CSV with mixed URL formats may look like this in the Magical Import Preview:

The image above shows the Preview section of the Magical Import module. The Preview table has four columns: CSV Column, Company name, Website, Country/Region, and Phone. The Website column is highlighted with a blue outline and contains URLs in inconsistent formats. (row 2) shows https://www.bandcamp.com,  (row 3) shows www.hexun.com, (row 4) shows https://www.bbb.org, and (row 5) shows www.biblegateway.com. A Filter drop-down set to "Show All Rows" appears at the top of the Preview.

In Magical Import, the Data Preparation step includes Functions that make bulk changes to CSV column values before the data is imported. These changes apply to the Preview, not directly to your CRM, and all cleanup happens on the Insycle side. This ensures the import contains standardized data.

1. In the Magical Import module, select your CSV and map your fields. See Selecting a CSV and Template for Import and Mapping CSV Columns to CRM Fields, Field Logic, and Matching Criteria for details.
2. Click the Data Preparation heading to expand it.
3. Under Column Name, select the website or URL column.
4. Under Function, select the URL function that produces the format you need: Extract: Domain from URL, Remove: Sub-domain, or Remove: Top-level domain. See How URL Functions Handle Protocols, Subdomains, and Paths for what each one returns.
5. (Optional) To apply more than one function to the same column, click the grey + (plus) button next to the first function, then select the next function. For example, to get "acme" from https://www.acme.com, apply Extract: Domain from URL first, then add Remove: Top-level domain.
6. (Optional) Because the order of functions affects the result, use the arrow buttons next to a function to move it up or down in the sequence.
7. Click Apply.
8. Review the column in the Preview to confirm the values match the format you need.

The image above shows the Data Preparation section of the Magical Import module. One Column Name and function pair is configured: Website is set to "Extract: Domain from URL". An "Add Field" button and a yellow "Apply" button appear below the configured fields.

In the Magical Import Data Preparation step, three domain functions return different parts of a URL. The function you choose determines whether the protocol (such as https://), the subdomain (such as ny.), and the path (the characters after the domain) are kept. 

• Extract: Domain from URL removes the protocol (https://), subdomain, and path, leaving the second-level and top-level domains.
• Remove: Sub-domain removes the subdomain and the path but keeps the protocol.
• Remove: Top-level domain removes the top-level domain and the path but keeps the protocol and subdomain.

Because the two Remove functions leave the protocol in place when applied alone to a full URL, apply Extract: Domain from URL first when you want a bare value such as acme.com or acme.

In Magical Import, the Preview shows how the column data will look after the functions are applied. After you click Apply, review the URL column in the Preview to confirm the values match the format you need. In this example, the domain has been extracted from each URL, leaving only the second-level and top-level domains. These are the values that will be imported into your CRM.

You can adjust the Functions and re-apply them as many times as needed to get the results you want. To revert to the original CSV values, click the grey plus button by each function, then click Apply again.

The image above shows the Preview section of the Magical Import module after Data Preparation functions have been applied. The Preview table has four columns: CSV Column, Company name, Website, Country/Region, and Phone. The Website column is highlighted with a blue outline and contains only the second-level and top-level domain for each row as defined in Data Preparation: bandcamp.com (row 2), hexun.com (row 3), bbb.org (row 4), and biblegateway.com (row 5). A Filter drop-down set to "Show All Rows" appears at the top of the Preview.

  Note: If you've set up formatting or standardization functions but the changes aren't reflected after importing, confirm that you clicked Apply for each function. Functions must be applied to the CSV data in the Preview before the import runs—they aren't applied automatically.