How to Populate Blank Field Based on Another Value
It's important to know where the businesses and contacts you work with are located for effective targeted outreach and to meet regional compliance requirements. You know there are quite a few records with blank country values in your CRM, however many of these have email addresses or website URLs that include a two-character country code. You just need an efficient way to map that data to the country field.
With Insyle's Transform Data module, you can easily isolate part of a field value, and use it to populate another field. If a record has an email value "email@example.com," or the website URL field contains, “www.acme.ns.ca,” you can use the two-character country from the top-level domain to populate the country field for the record in bulk.
- Use the filter to find records with blank values.
- Setup functions to use the two-digit country value from email addresses or domains to populate the country fields.
- Preview then apply the changes to the CRM records.
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.
- Select the email, domain, or website URL field from the dropdown.
- Select the Split: By any delimiter and pick the last term function. (Learn more about the "Split" functions in the Advanced How-Tos below.)
- In the Parameter field, enter a "." to use as the delimiter.
- Click the plus at the end of the row to add an additional function for this field.
- Now that you've isolated the two-character country code, select the Standardize: Country Code2 to Name function. Click the plus.
- 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.
Tips for Transforming Values
If you aren't sure what variations you have, you should do a little exploratory work to identify what non-standard values exist.
Use the Cleanse Data module to get a summary of all the data variations that are in a specific field in your database. This makes it easy to review the values in use and decide what needs to be cleaned up.
The Split functions are used to separate the values or characters in a field.
Split: By space and pick just the 1st term*
In fields where there are spaces between words or characters, only the first value before the space is kept or used for the next step.
Example: "John M. Doe" >> "John"
Split: By space and pick just the 2nd term*
In fields where there are spaces between words or characters, only the second value, after the space, is kept or used for the next step.
Example: "John Doe" >> "Doe"
Split: By any delimiter and pick the Nth term
Split up field content by a specified delimiter, this could be a period, space, dash, etc. Then specify which section of the field content should be kept or used for the next step.
Example: "524 Broadway, New York, NY 10012" with "," for the delimiter, and "2" for the section >> "New York"
Split: By any delimiter and pick the last term
Split up field content by a specified delimiter, and keep or use the last section of the field content for the next step.
Example: "http://wwwnews.live.bbc.co.uk" >> "uk"
|Split: By space into two fields*
In a field where there are spaces between words or characters, keep the value before the first space in the current field, then move the values after the space into a separate field.
Example: "Mary Evans Smith" >> "Mary" in original field, "Evans Smith" in other 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.
Frequently Asked Questions
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:
- You only want to make changes to a segment of records that meet certain criteria.
- 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.
- 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.
Related Help Articles
- Format Phone Numbers for Country Codes
- Format Names, Phone Numbers, Addresses
- Copy or Move Values Between Fields
- Module Overview: Transform Data
- Standardize Job Title, Industry, State, Country, or Any Other Free-Text Field
Related Blog Posts