Populate Country Based on Domain

Navigate to Data Management > Transform Data, and select the database and record type from the top menu. Then explore the templates for an existing solution that may be close to what you need.

In Step 1 under the Filter tab, choose fields and criteria the field values must meet. With this filter, you're telling Insycle what records you would like to update.

In the example below, we only want to change records that don't have a Country/Region value, do have an email address, and the email doesn't end with ".com," ".net, ".inc," ".io," or ".co."

Click the Search button, and a preview of the records that match this filter will load in the Record Viewer at the bottom of the page. Have a look through the results to see if there's anything else you want to filter out. If you make changes to the filter, click the Search button again to reload the Viewer.

Under Step 2, you give Insycle instructions on what changes to make to the identified records.

To populate the country value you can use the top-level domain (TLD), which is everything that follows the final dot of a domain name. For example, in the domain name "about.us", ".us" is the TLD. In the domain name, "bbc.co.uk", ".uk" is the TLD. You can look at email addresses or website URLs for the TLD.

Then you convert this abbreviated value to a full country name and copy it into the country field.

1. Select the email, domain, or website URL field from the dropdown.  
2. Select the Split: By any delimiter and pick the last term function. (Learn more about the "Split" functions in the Advanced How-Tos below.)
3. In the Parameter field, enter a "." to use as the delimiter.
4. Click the plus at the end of the row to add an additional function for this field.
5. Now that you've isolated the two-character country code, select the Standardize: Country Code2 to Name function. Click the plus.
6. Select the Copy: Value function and the Target Field the country name should be written into.

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes in a CSV file. It's important to verify that your changes are working as expected before they are pushed to your live database.

Under Step 3, click the Review button, then select Preview in the popup.

On the Notify tab, you can select recipients for the email report and add additional context to the message. (Make sure to hit Enter after each email address.)

On the When tab, click Run Now and select which records to apply the change to (you could do All, but if you have a large number of records, you may just want to do a chunk for your preview), then click the Run Now button.

Insycle will generate a preview CSV and send it to your email. Open the CSV file from your email in a spreadsheet application and review the (After) values for your country field. 

You may discover that some of the two-character countries from the email domains didn't map to the full country names. If this is the case, see the Troubleshooting section below for a solution.

If your results don't look the way you expected, go back to your filters in Step 1 and functions in Step 2 and try making some adjustments, then preview again.

Apply Changes to the CRM

If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.

Under Step 3, click the Review button again, this time select Update mode.

On the When tab, you should use Run Now the first time you apply these changes to the CRM. If you have a large number of records, you may want to do a smaller batch to review the results in your CRM.

After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save your configuration as a template and set up automation so the country values are added on a regular schedule. By automating with a template, you'll save time and ensure that your fields are consistently updated on an ongoing basis. If you have several templates you'd like to automatically run together, you can create a Recipe. Additionally, HubSpot users can integrate Insycle Recipes into HubSpot Workflows.

Return to the Template menu at the top of the page and click Copy to save your configurations as a new version of the template you started with. Then click the pencil to edit your new template name.

Under Step 3, click the Review button, and select Update mode.

On the Notify tab, select the send option appropriate for your automation: Always send, Send when errors, or Do not email.

Add any additional recipients who should receive the CSV (and make sure to hit Enter after each address). You can also provide additional context in the message subject or body.

On the When tab, select Automate, and configure the frequency you'd like the template to run. When finished, click Schedule.

The Split functions are used to separate the values or characters in a field.

*Note that the "split by space" functions apply only to the first space, i.e., if there are more values separated by spaces, only the first space is taken into consideration.

If you'd like to see additional fields in the Record Viewer or your export, click the Layout tab under Step 1, find the desired field in the right column, and drag it left to the Visible Fields column. Drag and drop fields to reorder them in the Visible Fields column. 

Note that adding additional fields to the Record Viewer does not affect what is in the Step 3 review CSV report, only the Record Viewer and export from Step 1.

In the preview CSV you may see that most of the country data from the domains are being transformed to full country names successfully, but there are a couple of outliers. In this example, the "uk" value from the "nike.co.uk" email domain is copying to the Country/Region field as is. This is because some countries' top-level domains don't align with the ISO two-letter standard Insycle uses for the "Standardize Country Code2 to Name" function. In this case, the top-level domain is "uk", but the ISO code is "gb".

To address these cases, you can add a "Map" function under Step 2 that specifically tells Insycle to change all instances of "uk" found to "gb" before moving on to the next step. Make sure you insert the additional function before the "Standardize Country Code2 to Name" and "Copy" functions.

When you re-run your preview, you should now see the correct country name value in the (After) column.

The Transform Data preview CSV includes a fixed set of fields: record identifiers, a deeplink to the record in the CRM, and the Before and After values for the transformed fields. Other fields cannot be added.

To see all the fields that are in your Layout and Record Viewer, use the Export button under Step 1. This will export the current data without the Transform updates. For more detail, see the Advanced How-Tos above.

Yes, with the Transform Data module, you can save your settings as a template and schedule automation. You can also bundle templates together and automate them using Recipes.

See the Save Templates and Setup Automation section above for more details.

Filtering data before exporting ensures that Insycle will only analyze the records that you want to focus on. Otherwise, Insycle will look at all records of the selected record type in your CRM. 

There can be a few reasons filtering is important:

1. You only want to make changes to a segment of records that meet certain criteria. 
2. If you have thousands of records, the operations can take a very long time. It is more efficient and manageable to filter your data down into small segments and use multiple templates rather than running operations for your entire database.
3. There may be an overwhelming number of results. If you are trying to analyze your data, adding a filter or narrowing filter criteria lets you work with a relevant, reasonably sized subset of data.