Ultimately, what goes in must come out, and that includes data. When manually importing leads into Prospector in order to receive hygiene, appends, or scores, it is not only important to make sure your file and its data are formatted correctly, but also that the data is as clean as possible. Often when you are grabbing data in a streamlined way from a particular source, such as a ticketing system, data warehouse, or CRM system, it will be quite raw and will largely benefit from some data prep before being uploaded directly into Prospector. This will help ensure high match, appends, and score rates. As an analyst, here are some common issues I see with uploaded files and how they can be fixed.
The fields accepted on a standard manual import are First Name, Last Name, Address 1, Address 2, City, State, Postal Code, Home Phone, Mobile Phone, and Email. In order to receive most of the append packages, First Name, Last Name, Address 1, and Postal Code are required. The more fields you include, the more likely you will be to receive an append. Also, the more fields you include, the more likely you are to create a contact data match in Prospector to an existing already-appended record. If the contact data matches, an append is not required. Only American addresses can receive appends.
One of the most common issues I see is that, in lieu of having blanks in cells when the data is unknown or doesn’t exist, is having a placeholder value in the cell instead, like “NULL” or a period. It is best to try to avoid unnecessary values like this, and placing values in the wrong column as well. For example, you wouldn’t want to place an address in the First Name column. It is best to eliminate as many of these instances as possible by simply clearing the cell to blank or fixing it. You can easily check for and clear the values in Excel if you apply a filter to all the columns and sort alphabetically, column by column. Incorrect values will tend to be at the top or bottom (since you wouldn’t expect a name to start with a number or a Street Address to start with a letter). For “NULL”s, if you know what your default value is, you can filter to that value in each column and manually clear it out. There can further be bad values if the columns are not split out. There may be a singular name column that has the full name in it, which is not valid for upload to Prospector. You will need to either pull specific First and Last Name columns, or use Excel to split out the column yourself. If it is in a format like “Smith, John”, you can use the Text-To-Columns tool and delimit on commas to split into columns separated by commas. I have also quite often seen this in Address 1 (“City, State, Zip” all in a single column) which should be separated.
Furthermore, you want to make sure all column headers are clearly labeled so both you and Prospector can understand it. A common error is having the Address Line 1 and 2 columns switched, so a line may say “123 Main Street” in Address 2 and “Apartment 5” in Address 1, consistently throughout the file. Or, you might have your columns named like “Address_City”, “Address_State”, and “Address_Zip”. However because of reasons like this, it is recommended that on each file you upload into Prospector, you click the “Edit” button before submitting and make sure your file fields match up correctly to the system’s contact fields.
Here are additional tips to keep in mind –
•Account ID – If you are using a template that matches to Account ID, make sure that the ID number you are matching is the correct one (this is the ID number used to associate ticket spends, so usually it is from your ticket provider, but not always). If you are unsure, please contact your account rep. If your provider is Ticketmaster, this will likely be the Archtics ID.
•One person per cell – make sure you’re uploading one person per line, instead of linked accounts (i.e., “John & Jane” “Smith”).
•Incorrect Address – If you have a record of a person at an old or incorrect address, they may still receive an append, but some fields will be attributed to the person, while some will be attributed to the household. For example, Age, Education, and Occupation relate to the person you’ve uploaded, but Presence of Children, Home Market Value, and Vehicle Indicator may pertain to the Address.
•PO Box numbers– If you have a PO Box number but not a Street Address, that is okay to include in Address 1 on your Prospector template. Address 2 can be left blank if you don’t have any data to include.
• Zip Code – It is best practice to make sure that your zip code is being stored as text (this can be set in Excel). If your zip code has a leading 0, Excel may otherwise cut it off. If you need to add it back, you can make a new column and use the concatenate formula in Excel (Concatenate “0” with the other digits, if the zip code is 4 digits long). Sorting the column can be helpful here.