Mailchimp Transform Data Overview

Insycle's Transform Data module supports Mailchimp Contact records.

Navigate to Data Management > Transform Data. Select the Mailchimp database and Contacts in 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, select the Field to work with. Use the Condition to set the rule that the data in the selected field must meet. With this filter, you're telling Insycle what records you would like to update.

In this example, we want to fill in missing country values based on the two-character top-level domain. 

The filter finds records that have:

• A missing or empty Country/Region field
• A non-empty Email field
• An Email Domain that has at least one character (.) followed by a period (.), and then two lowercase letters ([a-z]{2})
• An Email Domain that does not end with common top-level domains like .com, .net, .io, etc

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. Look through the results to see if there's anything else you want to filter out. If you change the filter, click the Search button again to reload the Viewer.

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

Select the Field that contains the value you want to start with, then select the Function and enter the parameters. Click the plus at the end of the row to add an additional function for this field.

Each function will use the value output from the previous function, so the sequence of them matters.

Dozens of functions are available to help you properly format data, fix typos, split values into multiple fields, add information from other fields, and more. View the Function Catalog for a breakdown of each available function.

In the example below, the top-level domain (TLD) from the email address is being used to populate the Country value. Once the TLD is identified, if it's a two-character country TLD, it's converted to the full country name and then entered into the Country field. Because the value is copied, the email value remains unchanged.

Preview Changes in CSV Report

Data transformation is a bit more difficult to visualize than basic value-A-to-value-B changes, so before applying changes to your Mailchimp data or when you're developing your new template, previewing the results is an important step.

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

Click the Next button to go to the Notify screen, where you can select recipients and add context to the CSV report email.

On the When tab, click the Run Now tab, 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 values for each row.

You can see the (Before) and (After) values side-by-side. The Deal Name (After) values have been rewritten to concatenate the customer name, deal close date, and product line. 

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

Apply Changes to Your Mailchimp Records

When you're satisfied with the results in your preview, you can apply the changes to your Mailchimp data.

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

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

After you've seen the results in Mailchimp and are satisfied with how the operation runs, you can save your configuration as a template and set up automation so this transform operation runs on a set schedule. If you have several templates you'd like to run together automatically, you can create a Recipe.

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.

You can view all your scheduled automations at any time on the Operations > Automations page.

When you have a solid set of templates that reliably transform your records, you can put them together into a longer, ordered sequence as a Recipe. Then, you can schedule that Recipe to run on a consistent, set schedule. Your templates will run one after another in the order that you set.

With the Activity Tracker, you have a complete audit trail and history of changes made through Insycle, including processes run in Preview mode or data syncs. At any time, you can download a CSV report that lets you see all of the changes that were made in a given run of the operation.

Navigate to Operations > Activity Tracker, search by module, app, or template name, then click the Run ID for the operation.

If there isn’t a single function under Step 2 that makes the change you’re looking for, you can still complete the step in one template.

Functions can be layered to make changes in a series of steps. They will cumulatively apply to the field value, executing in order from top to bottom.

For example, you can use two functions to clean up Website URL values with multiple directories. Instead of “https://app.insycle.com/data/bulk/contact/,” you just want to keep the main domain value, “insycle.com":

1. The Extract: Domain from URL function will remove the https:// and subdomain, but the directory values after the domain, '/data/bulk/contact/' would remain. Leaving the Target Field blank, will write the value back to the original field.
2. To remove those directory values, add the function Split: By any delimiter and keep Nth term to remove everything after the forward slash, “/”, and keep the value in the first section.

For another example, you can combine values from different fields (Deal Name, Close Date, Amount, Next step) into a single formatted string to create a concise and standardized deal or opportunity identifier or description.

Here's a breakdown of what each function is doing:

• Append: Concatenate concatenates (combines) the value in the "Deal Name" field with the value in the "Close Date" field, using the "/" character as the separator.
• Split: By any delimiter and pick the Nth term, splits the concatenated value from the previous step by the character "T" (representing a date/time delimiter) and selects the 1st term (N=1).
• Append: Concatenate appends (adds) the value in the "Amount" field to the end of the value from the previous step, using the "/" character as the separator.
• The second Append: Concatenate function adds the value in the "Next step" field to the end of the value from the previous step, using the "/" character as the separator.
• Format: Proper case company formats the concatenated string from the previous steps by capitalizing the first letter of each word, following a typical company name formatting convention.

You may need to do some experimentation. Use the Preview mode and CSV report to see if you get the desired result. If not, adjust your functions, maybe try reordering them, and then Preview again.

With your filter and layout set, Insycle can now export your data segment. 

In the Transform Data module, you can export your data one time immediately or set up automated exports. This will export the current data without the transform updates.

Under Step 1, click the Export button.

In the Export popup, select New File and click Next.

On the Email tab, enter additional email addresses (hit Enter after each), a Subject, and Description. The CSV export will be attached to this email whenever it is generated.

To export the data once:

On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.

To set up automation for recurring exports:

On the When tab under Automate, set up the frequency your CSV should automatically be exported--hourly, daily, weekly, or monthly.

Then, data that matches your set filter will be automatically exported and emailed to you or your team on a set schedule, improving data collaboration and letting you keep an eye on important customer data.

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.

The Transform Data Preview CSV includes a fixed set of fields: record identifiers, a deeplink to the record in Mailchimp, 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. It is best practice to build separate templates for formatting each specific field. This helps you to keep things organized and troubleshoot issues if they arise. If you wind up with several related templates you'd like to automatically run together, you can create a Recipe.

Yes. If you are having trouble figuring out how to format a specific field, contact customer support via the live chat to receive help with building your template.

Filtering data before running an operation 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 Mailchimp data. 

There are a few reasons filtering is important:

1. You only want to change 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.