How to Incorporate Outside Data into Existing System Data

Sometimes, your CRM or other systems might lack certain critical information—such as detailed costs, appeal-level data, or additional segmentation—that can enhance your reporting and decision-making. Avid AI allows you to upload and append supplemental data sets, giving you the flexibility to enrich your existing data for more powerful insights.

This guide provides a step-by-step walkthrough on how to add supplemental data and join it with existing data in Avid AI.

Step 1: Upload Supplemental Data

Step 1: Navigate to the Data Upload Section

1. Log in to Avid AI.
2. Go to the Settings menu.
3. Locate the Add File button. This section allows you to upload files directly or use other methods like SFTP or a public link.

Step 2: Upload Your File

1. Choose Your Upload Method:
   • Set up an SFTP drive that will automatically process any data files uploaded to it. (This is helpful if you'll be regularly updating this file.)
   • Drag and drop your file into the upload area.
2. Prepare Your File:
   • Create a CSV or Excel file containing the data you want to supplement.
   • Give the columns names that will make it easy to understand the data contained.
   • Include both the data you want to add and one or more columns that will allow you to join it to the desired data set.
3. Once ready, click Upload, and select your file. After uploading, you can view it in the Files tab. You can upload multiple files and they will all be added into the system for reporting.

Step 3: Create a Data Set from the Uploaded File

Below is a simple step-by-step. For more detailed instructions, you can check out the article: "+ Add" a Data Set.

1. Create a Data Set:
   1. Select the "+ Add" button next to your uploaded file name. This is found on the Files section of the settings.
   2. Give your dataset a descriptive name (e.g., “Supplemental Appeal Data”)
   3. Set the Category to “Other” or a category that fits your use case.
   4. Choose a Refresh Type:
      • Replace: Replaces the dataset each time you upload a new file.
      • Append: Adds new data to the existing dataset, ideal for building cumulative records.
   5. Map the columns. Use the buttons at the bottom to add the various columns into the data set. Don't worry about setting the data types to anything other than "Text". That can be done in a future step.
      • "Add All Source Columns" - This is the easiest method and will add all columns from the file into the data set for use.
      • "Add Column" - Will allow you to add individual columns if you want to be selective about what is imported into the system.

Step 4: Join Supplemental Data with Existing Data

1. Navigate back to the Connection Settings.
2. Select the System (e.g., CRM, email platform, or ad platform) where you want to integrate the supplemental data.
3. Make sure the data set to add the data is selected from the various tabbed options.
   1. Select the dataset you uploaded (e.g., “Supplemental Appeal Data”). 
   2. (Optional) Rename the joined dataset (e.g., “Appeal Data”).
   3. Choose a Join Method:
      • Match Only: Includes only records that exist in both data sets.
      • Match Where Possible: Retains all records from the original data, adding supplemental information where matches exist (ideal for incomplete supplemental data).
      • Add All Records: Performs a union of the data sets, combining all records regardless of matching fields.
   4. Define Matching Keys:
      • Map a field from the supplemental data (e.g., segment code) to a corresponding field in the existing data (e.g., segment code in your CRM).
      • You can map multiple fields if they are all required to match.
      • You can also use "Custom Logic" to modify the supplemental data columns if necessary to get better mapping results.
   5. Press the Save button. The screen will refresh.Click on "Join Additional Data."

Step 5: Map the New Columns

Once the screen has refreshed, the new supplemental data will be available in the list of fields used for Column Mapping. Go to the Column Mapping section to finish the process. Once saved, the new fields will be available for filters and charts.

Pro Tips:

• 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 or appeal data.

• Flexible Joins: The “Match Where Possible” join method is ideal for supplemental data that only covers part of your records.

By following these steps, you can easily enrich your existing systems with additional information.