Adding VAN IDs to a Text 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, but you need the data in VAN. After matching up the data through the steps in this guide, you will need to bulk upload these results into VAN.
Reminder: 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.
You will need to do a Campaign messages export of the affected campaign that includes the group(s) that did not have VAN IDs mapped.
Export a list of the contacts from your VAN instance that includes the contacts’ phone numbers and VAN IDs.
Create an Excel doc with two sheets. Include the ThruText 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 Messages_Export which is the exported messages from our text 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.
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 ThruText campaign export (sheet 1 Messages_Export).
- Create an additional column in the Messages_Export sheet titled “Van_Id” in the next empty column (column Q1 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:
- =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 Messages_Export sheet, in column Q2, based on the formula mentioned above, we’re going to create this VLOOKUP function: =VLOOKUP($P2,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 P (phone numbers column) in the Messages_Export sheet.
- Let’s break down each section of the formula in our example:
- $P2 stands for P column in Messages_Export where the phone number data is being matched.
- The $ will make an exact cell reference for the function.
- VAN_IDs references sheet2 that you are pulling the matching data from.
- $C$2 stands for the starting C column and row in VAN_IDs where the phone number data is being matched.
- :$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 stands for the column number between C to H that we want to retrieve the value of.
- False means an exact match is being searched for.
- Next copy the formula down through all rows in the Messages_Export sheet.
Once completed, this function will search phone numbers across the two lists to determine which messages exports’ contact matches each VAN ID. You should see the VAN IDs populate in the van_id column you created within your messages export sheet for the campaign if done successfully.
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.
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.