Adding VAN IDs to a campaign to bulk upload into VAN

This guide will walk you through the process of matching up VAN IDs to phone numbers in your export if VAN IDs were not mapped in your group. Often this needs to be done if you need the call data recorded in VAN. After matching up the data through the steps in this guide, you will need to bulk upload these results into VAN.

NOTE - All groups on a campaign need to have their VAN IDs mapped to be able to import survey questions from VAN. For details on how to have those mapped in a group and synced on group upload, please review our guide: Creating a Group from a VAN Imported List.

Step 1:

You will need to do an Account Campaign Dials export from the “Exports” page and download the CSV for the affected campaign that includes the group(s) that did not have VAN IDs mapped.

Step 2:

Export a list of the contacts from your VAN instance that includes the contacts’ phone numbers and VAN IDs. You can also create a new group of the external contact list with the correct VAN ID mapping and export that to use for this as well.

Step 3:

Create an Excel doc with two sheets. Include the ThruTalk campaign export on sheet 1 and the VAN contact list on sheet 2. To create this, open the Campaign’s export CSV (sheet1 will be named the same as the file name but you can click on that name to rename it, if desired) and then click on the plus sign to create a second sheet where you will copy and paste the contact list details that include the VAN IDs.

For this test we have sheet1 titled Campaign_Dials_Export, which is the exported dials from our talk campaign, and we have sheet2 titled VAN_IDs which is being used as the contact list with each contact’s VAN ID and phone number.

Step 4:

Perform the following to pull the VAN ID information from the VAN contact list (sheet 2 VAN_IDs) over to a new column in your ThruTalk campaign export (sheet 1 Campaign_Dials_Export).

• Use the empty column in the Account_Campaign_Dials sheet titled “Van_Id” in the next empty column (column J1 in the example below).
• We will be using the phone number for our VLOOKUP function, as it is unique per contact, which is needed for a VLOOKUP to work. 
• The function formula:
  1. =VLOOKUP($<cell with first user’s phone number>, <sheet 2’s name>!$<in sheet 2, first cell with users’ phone numbers>:$<last cell in column with the VAN IDs>, <the column whose values should be used in the new column ( starting from the leftmost column, which is 1)>, False)
• In this example, in the Campaign_Dials_Export sheet, in column J2, based on the formula mentioned above, we’re going to create this VLOOKUP function:         =VLOOKUP($A2,VAN_IDs!$C$2:$H$9,6,FALSE) to pull in the data from the VAN IDs sheet’s (which has column C for phone number and column H for the VAN IDs) that matches column A (phone numbers column) in the Campaign_Dials_Export sheet.
• Let’s break down each section of the formula in our example:
  1. $A2 stands for A column in Campaign_Dials_Export where the phone number data is being matched.
  2. The $ will make an exact cell reference for the function.
  3. VAN_IDs references sheet2 that you are pulling the matching data from.
  4. $C$2 stands for the starting C column and row in VAN_IDs where the phone number data is being matched.
  5. :$H$9 stands for the last row in column H in VAN_IDs that includes the VAN IDs and the 9 is the number of rows being searched for matching data in the example.
  6. 6 stands for the column number between C to H that we want to retrieve the value of.
  7. False means an exact match is being searched for.
• Next copy the formula down through all rows in the Campaign_Dials_Export sheet.
    

Once completed, this function will search phone numbers across the two lists to determine which dials exports’ contact matches each VAN ID. You should see the VAN IDs populate in the van_id column you created within your dials export sheet for the campaign if done successfully.

Step 5:

Save your Excel sheet 1 as a CSV (when prompted, confirm you want to save the active worksheet).

The saved campaign messages CSV will now include VAN IDs for the contacts.

Step 6:

Use the Bulk Upload Wizard feature in VAN to upload your newly created CSV to your VAN Instance.

And that’s it! You have now added the campaign data with VAN IDs to VAN.

Please write to [email protected] with any questions.