Merge multiple value custom field data when updating subscribers from a file
When you update custom field data for existing subscribers by importing a file, the custom fields in your account are overwritten with the custom field values in the file.
This is fine for most custom fields, as they only accept one value. However, "multiple option (can select many)" fields can contain many values, potentially leading to a situation where multiple values are overwritten by a single, new value imported from your file.
Below, we'll explain how you can import new custom field values without overwriting existing values. You can use Excel or Google sheets to do this, and you’ll need a medium knowledge of formulas.
- Back up your list by exporting it. You can restore from this backup if you make an error.
- Click Lists & subscribers, then select the list you want to update.
- Click Custom fields in the right sidebar.
- Next to the name of the "multiple option (can select many)" custom field you want to append values to, click Edit.
- Write down the "Options available", then click Save changes.
- Below "Add a new field", enter a name like "Imported", set the data type to Multiple Options (can select many), then add the same options you just wrote down.
- Click Add custom field.
Upload the new list
- Next to "Manage custom fields" in the breadcrumb navigation, click the list name.
- Click Add new subscribers and import your subscriber file with the new data.
- On the next page, instead of assigning the new "multiple option (can select many)" values to your original custom field, assign them to the "Imported" custom field you just created. Click Finish adding subscribers.
Edit the data
- After you finish adding the subscribers, the subscriber list page will load. Click Segments in the right sidebar.
- Click Create new segment, and set the first rule to the "Imported" custom field you previously created. Set the next rule to is provided.
- Name the segment something like "Merge", then click Save and preview.
- You will see a preview of the new segment after saving. Click the Export segment button to download a CSV file.
- Open the downloaded CSV file in Excel or Google sheets, and create a new column in the spreadsheet.
- In the first row of the new column, enter a name like "New merge".
- In the second row of the new column, use the formula
=D2&"||"&E2, where D is the column with the original custom field values, and E is the column that contains the new values imported from your file. This will merge the values together. Don't worry if you get duplicate values, they will be stripped out when the file is imported.
- Fill the formula to the remaining rows.
- Save your changes as a CSV file, then upload it to your subscriber list.
- After it has uploaded, assign your "New merge" column to the original "multiple options (can select many)" custom field, then set the other columns you worked with in step 7 to "Nothing (skip)".
- To clean up, delete the temporary "Imported" custom field and "Merge" segment from your list.