How to Join Outside Data into Existing System Data

While Avid integrates with many primary fundraising tools to connect all your data, sometimes you have data that lives offline. Avid provides a way to join data from an outside source.

Why would you want to join data to an Avid data set?

First, there is a lot of room for error with joining data.

The safest way to get data into Avid is via an integration. If there is an existing integration with your data source, use it! It will save you lots of time and potentially many headaches.

The primary reason that fundraisers join data in Avid is for direct mail match back reporting. This allows you to upload a mailing list and see how your direct mail campaign influenced giving in other channels.

A few other common reasons you may want to manually join data to an Avid data set include:

 

1. You’d like to add data that doesn’t exist in your CRM.
    
2. You’ve manually appended data from the CRM, and would like to add it into the system.
    
3. You’re migrating CRMs or other platforms and want to join your historical data.
    
4. You need to fix or reconcile data, but the CRM won’t allow it.
    
5. You don’t have permissions to add certain data in your CRM but want to see it in your Avid reporting.

There could be a variety of other reasons as well. But keep in mind, if there is an existing integration with your data source, you should use it!

How to Join Data in Avid

Part 1: Upload Your Data File

Before you can join your data, you need a file to join. Uploading your data is the first step to joining it to a data set in Avid.

1. Head to the “Connections” page.
2. Click the “Add” button.
3. Select File » Upload and upload your data file.
    

After you’ve uploaded your file, move on to part 2.

Part 2: Add a Data Set

Uploading a file gets you started, but to join your data to the rest of Avid, you’ll need create a data set. This help tell Avid what kind of data you’re uploading and how Avid should use it.

1. Head back to the “Connections” page.
2. Click on “Files” toggle.
3. Find your newly uploaded file and click the “+ Add” button underneath “Data Set.”
4. Create a name for your new data set.
5. Choose your data set category.
   
   The most common and flexible option for joining data is “Other.” Click here for more info on which data set category to use »
    
6. Set your “Refresh Type”
   1. “Replace” will overwrite previous file uploads to the same data set.
   2. “Append” will add data to the existing data set.
      1. If appending data, choose which columns you’d like to use to match up your records (usually a contact ID).

1. Set your file name pattern.
   To replace the file in the data set later, your files must have exactly matching names.
2. “Ignore Unused Column Changes” is turned on by default.
   If you turn this off, your data append must have a matching column for each record.
3. Click “Next.” On this screen, you can add filters to control what data gets included in your data set.
4. After your filters are set, then you can map your new data set’s columns.
   1. Click “Add All Source Columns” to add each column from your newly uploaded file. Or use “+Add Column” to add them individually.
   2. Choose the appropriate columns in Avid to match to, or create a new custom column.
5. Hit “Save” to finish your data set.

Part 3: Joining the Data to a System

To join your new data set to the data set from one of your other integrations, head back to the “Connections” page.

1. Click “View Connection” on the integration you’d like to join data to.
2. Click on “Join Additional Data.”
3. Choose which data set you’d like to join from the drop down.
4. If needed, customize the display name for your data set. (Keep in mind, display names cannot include any special characters)
5. Choose your “Join Method:”
   1. Matching Records Only: This option will remove all of your original record that do not have a match in your new data set.
   2. Match Where Possible (Most Common): This option will leave all of your original data intact and will add new data as it is able to find a matching records.
   3. All Records Regardless of Match: This option will add every record from your new data set, increasing the total number of records in the original data set.
6. Select your “Join Keys.”
   These are the fields that will be used to find a match (usually an account ID).
   
   Keep in mind, each individual key you add must find a match. The more keys you add, the stricter the matching criteria.
7. Hit the “Save” button to finish the join.

Part 4: Mapping Your Additional Columns

Lastly, Avid needs to know how to map any new columns from your joined data set to Avid.

1. Hit “Refresh” in your browser.
2. Then click on “Column Mapping.”
3. Click the “+Add Column” button to add any new columns from your joined data set.
4. Use the dropdowns on the right side to choose the corresponding column in Avid to match.
5. When you’re finished, hit “Save.”

Pro Tips & Troubleshooting

• Consistency in Field Names:
  Ensure that field names in the supplemental file match or are mapped correctly to avoid errors.
• Incremental Updates:
  Use the "Append" refresh type for datasets that need to grow over time, such as donor data or appeal data.
• Flexible Joins: The “Match Where Possible” join method is ideal for supplemental data that only covers part of your records.

I Joined My Data and Something Looks Wrong

Joining data to Avid is not a task to be taken lightly. If you're worried that something has gone wrong, just click on "Support" within the application and let us know.

Our team of Avid Pros will reach out to help.