How to Create a Facebook Custom Audience from a Customer File

in Best Practices

I was recently asked to create a custom audience for a Facebook ad campaign, and was given an Excel spreadsheet filled with names and contact information. As I began trying to understand how to get that spreadsheet into Facebook Ad Manager, I realized there was a lot of information online about why custom audiences work, but not how to do it. You may create custom audiences as part of your daily job, or you might be like me and have never come across the need to learn.

So, for anyone who has thousands of cells filled with information and limited experience with Excel, I’ve outlined exactly what you need to do to optimize that information for Facebook.

Customer information accepted by Facebook

Facebook provides a file template to reference when creating your own spreadsheet, and clarifies what information they accept (and how they accept it). These include:

  • email: up to two different email addresses
  • phone: up to three different phone numbers
  • madid: Mobile advertising ID
  • fn: first name
  • ln: last name
  • zip: zip code
  • ct: city
  • st: state
  • country
  • dob: Date of birth
  • doby: Date of birth year
  • gen: gender
  • age
  • uid: user ID number

(You can download a file template provided from Facebook here.)

Screen shot of Facebook's file download, and includes the fields email, email, email, phone,, phone, phone, madid, first name, last name, zip, city, state, country, date of birth, date of birth year
Facebook’s audience file example (click to expand)

Formatting customer information

Now that you know what information is accepted, here is how Facebook prefers that content to be formatted in your spreadsheet:

Note: Be sure to format each column header as shown in the picture above (email, phone, fn, ln, zip, ct, st, etc.)

  • Email: Lowercase
    • Example: username@gmail.com
  • Phone number: Must include a country code (including U.S. numbers), even if all your data is from the same country
    • Example: 1-222-555-5678, or 12225555678, or +44 222 555 5678
  • Mobile advertising ID: Facebook accepts two types of IDs: Android and Apple
  • First name: First name or first name initial, with or without accents, with or without period (for initials)
    • Example: Mark, or M., or José
  • Last name: Last name with or without accents
    • Example: Johnson, or Johnson-Smith
  • Zip code: Facebook accepts US and international zip and postal codes. Four-digit extensions are accepted (but not required) if separated by a hyphen
    • Example: 80523, or 80523-1001
  • City: Full city names
    • Example: Denver, New York
  • State/Province: Full names of states and provinces, abbreviated versions of U.S. states
    • Example: CO or Colorado
  • Country: Must be an ISO two-letter country code. Always include the country code for every contact, even if they’re all the same country
    • Example: US
  • Date of birth: Facebook accepts 18 different date formats.
  •  Year of birth: Enter the year as a 4-digit number
    • Example: 1991
  • Gender: Use an initial to indicate gender
    • Example: M or F
  • Age: Numerical
    • Example: 28
  • Facebook app user ID: More information
  • Facebook page user ID: More information

Formatting customer information…efficiently

This is where it gets interesting. When someone forwards you a spreadsheet full of information, odds are the information will not be formatted to Facebook’s standards. For example, first and last names might be combined into one cell, rather than separated into two separate cells. But who has time to manually separate 1,000 (or more) names? Nobody, that’s who. Here are some helpful tips to quickly format a lot of cells all at once.

First, be sure to organize your columns and delete all unnecessary information that Facebook does not accept, like middle names, for example.

Changing email addresses to lowercase

  • Create a new column next to the email addresses
  • Insert Function (under the Formulas tab)
  • In the cell next to the cell you would like to change, insert this function: =LOWER( )
  • Inside the parenthesis, enter the cell number you would like to change, for example: =LOWER(A2)
  • Press enter
  • The function should create a lowercase version of the text in the new column
  • Copy the new (correct) cell, and paste it into the remaining empty cells you would like to change
  • You can also change text to all uppercase (UPPER) or just capitalize the first letter (PROPER)
  • Confused by my poor directions? See photo examples below (click to enlarge each)

Step 1 (enter the formula in the empty cell next to the email address)

Step 2 (press enter)

Step 3 (copy the cell with the new lowercase email address)

Step 4 (paste it into the remaining empty cells next to the email addresses you would like to change, then delete the column with the original data)

Adding a “1” to the beginning of a phone number

  • Highlight all the phone numbers
  • Right click, select “Format Cells”
  • Click on “Custom” from the Category list
  • Enter in the “Type” field: 1-###-###-####
  • Click “OK”
  • See photo examples below (click to enlarge)

Original Data

Excel screen shot of step one to add a 1 before a phone number

Step 1 (select all the cells you would like to format, right click and select “Format cells)

Excel screen shot of step two to add a 1 before a phone number

Step 2 (Select “Custom” from the category list and type: 1-###-###-#### and then click “OK”

Excel screen shot of step three to add a 1 before a phone number

Final data

Excel screen shot of step four to add a 1 before a phone number

Pro tip if the phone numbers won’t format: 

When I first tried to format the phone numbers in Excel, the cells wouldn’t re-format no matter what I did. After some digging, I realized the phone numbers were entered into Excel as text instead of numbers. Luckily, there are very smart people on the Internet who have a solution. If this is happening to you, try this:

  • Type a “0” in a blank cell anywhere in the spreadsheet
  • Copy the cell
  • Select all the phone numbers you want to be numeric
  • Right click
  • “Paste Special”
  • Click on “Add” under “Operation”
  • Click “OK”
  • Now that the cells are numeric, try to format the cells using the steps in the previous section

Separating first and last names into two different cells

  • Make sure there is an empty column next to the names column (the empty column is where the last names will move to)
  • Highlight all the names you would like to separate
  • Under the “Data” tab, click on “Text to Columns”
  • Make sure “Delimited” is selected, and click “Next”
  • Under Delimiters, un-click “Tab” and select “Space,” and click “Next”
  • Make sure “General” is selected under “Column data format” and click “Finish”
  • See photo examples below (click to enlarge photos)

Step 1 (Highlight all the names you would like to separate, and click on “Text to Columns” under the Data column (see upper righthand corner in screenshot)

Step 2 (Select “Delimited” and click “Next”)

Step 3 (Deselect “Tab” and select “Space,” then click “Next”)

Step 4 (Make sure “General” is selected and click “Finish”)

Final data (First and last names should be separated)

Saving the Spreadsheet for Facebook

When you are ready to upload your file to Facebook, you can upload it as a .txt or .csv file. When you save your file, be sure to change the file type from an Excel Workbook (.xlsx) — which is likely the default — to .csv from the dropdown menu (File –> Save As). You can also copy and paste your data into Facebook.

After you upload your file, Facebook will data map it, meaning they will show you a preview of your data and how they classified it. As long as you format your data correctly from the get-go, Facebook shouldn’t have any trouble data mapping your file.

Screen shot of Facebook Custom Audience upload

Resources

Since my blog is primarily about formatting customer data and not so much about what you can do with it once it’s uploaded, here are some additional resources: