Adding VAN IDs to a Text Campaign to Bulk Upload into VAN


This guide will walk you through matching 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.


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 mapping VAN ids in a group: Creating a Group from a VAN Imported List.


Step 1:

You will need to do a Campaign-level Messages export of the affected campaign that did not have its VAN ids mapped.

Screenshot of a campaign export page with a white table on a beige background. The expanded Export Type dropdown shows 'Messages' highlighted in bright pink on a dark grey menu..Step 2: 

Export a list of the contacts from your VAN instance that includes the applicable 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 ThruText campaign export on Sheet 1 and the VAN contact list on Sheet 2. To create this, open the Campaign’s export CSV (Sheet 1 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 Sheet 1 titled "Messages_Export." Sheet 1 contains the exported messages from our text campaign. We have Sheet 2 titled "VAN_IDs," which is being used as the contact list with each contact’s VAN ID and phone number.

Screenshot in Excel with green arrows pointing to the bottom of the page to add additional sheets to the file.Step 4:

Perform the following to pull the VAN id information from the VAN contact list ("VAN_IDs") over to a new column in your ThruText campaign export ("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:
    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 "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 (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:
    1. $P2 stands for P column in "Messages_Export", where the phone number data is being matched.
    2. The $ will make an exact cell reference for the function.
    3. "VAN_IDs" references Sheet 2 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 "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.Screenshot of an Excel sheet. The VLOOKUP formula is highlighted with a green rectangle.

Step 5:

Save your excel Sheet 1 as a CSV (when prompted, confirm you want to save the active worksheet). Screenshot of a light grey Save prompt with black text in Microsoft in Excel.

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.