Formatting Column-Based Files

Column-based files are LiveRamp's preferred format, especially when there is more than one identifier field.

Although LiveRamp accepts data in both column-based and key-value file formats, LiveRamp prefers and recommends that you provide column-based files, particularly when there is more than one identifier field (this will typically be PII-based data with a name and postal address, multiple email addresses, or some combination thereof).

Caution

EU data can only be accepted in a column-based file format. Key-value files are not allowed for EU data.

Follow the guidelines listed below for the best results.

Include a Header Row in Every File

LiveRamp requires that you include a header row in every column-based file. This allows us to flexibly accept columns in any order, and automatically detect and map much of the data without human intervention.

  • Make sure each header is in all caps, with no spaces or underscores.

  • Make sure each header matches the contents of that particular column.

Caution

The recommended headers for PII-based files are somewhat different for U.S. data files and EU data files.

Caution

Avoid header issues: Header issues tend to be the most common cause of file upload failure. See "Avoiding File Upload Failure" for more information.

Keep Headers and Identifier Fields Consistent

Files for ingestion in the same audience should always have the same set of identifier fields, and the headers must be unique and consistent from file to file, including capitalization.

Do Not Include Data You Do Not Intend to Use

If a given column will not be used as an identifier or as a segment to be distributed, do not include it.

Each data column makes the file larger, and this size increases as a function of the number of rows in the file. We also need to inspect each column for privacy compliance. The smaller the file and the fewer analysis operations LiveRamp needs to perform, the faster the file can be processed.

Files Must be Rectangular

Every column-based file must be rectangular, meaning every row should contain the same number of delimiters and fields. If a given field has no value for an identifier, simply leave that value empty for that row.

Caution

LiveRamp does not accept fixed-width files. Make sure to use a delimited format.

In order to maximize reach and maintain accuracy, the following 13 standard headers and their associated fields are highly recommended for every column-based file of PII identifiers you upload to LiveRamp.

Click here to download a CSV file with these headers that you can use as a template. You can find other downloadable files in the sections below.

Don't have all identifier types? If you cannot supply a given identifier (such as name and postal, email, or phone), you may include the respective header(s) anyway and simply leave the column(s) blank. However, if including name and postal, make sure to include all required name and postal fields.

In order, the standard header labels are:

  • CCID (Client Customer ID - see below)

  • FIRSTNAME

  • LASTNAME

  • ADDRESS1 (street address line 1)

  • ADDRESS2 (street address line 2)

  • CITY

  • STATE

  • ZIP (ZIP Code)

  • EMAIL1

  • EMAIL2

  • EMAIL3

  • PHONE1

  • PHONE2

The first field ("Client Customer ID") is an optional field you can include, which can be used as the audience key, allowing us to de-duplicate rows in the uploaded file (in case a file has multiple rows related to the same person). If included, the entries in this field should be unique and persistent across all audiences. Contact your LiveRamp representative if you are not using a Client Customer ID to identify records.

Note

The ZIP Code field can include either standard 5-digit values or 9-digit ZIP+4 values. Do not include hyphens when sending 9-digit ZIP codes.

The bolded headers listed below are required, with the exact heading, for every file of PII identifiers you upload to LiveRamp. Additional headers are shown with their recommended header labels.

Note

For complete information on formatting EU data files, see Creating EU Data Files.

If you do not have data for a particular required column, leave those column entries blank.

Click here to download a .csv file with these headers that you can use as a template.

Note

Don't have all identifier types? If you cannot supply a given identifier (such as name and postal, email, or phone), you may include the respective header(s) anyway and simply leave the column(s) blank. However, if including name and postal, make sure to include all required name and postal fields.

In order, the standard header labels are:

  • CustomerID (Client Customer ID - see below)

  • FIRSTNAME

  • LASTNAME

  • ADDRESS1 (street address line 1)

  • ADDRESS2 (street address line 2)

  • ADDRESS3 (street address line 3)

  • ADDRESS4 (street address line 4)

  • TOWN

  • COUNTY (required for UK data files only)

  • POSTCODE

  • EMAIL1

  • EMAIL2

  • EMAIL3

  • MOBILE1

  • MOBILE2

  • LANDLINE1

  • MD5EMAIL1

  • MD5EMAIL2

  • MD5EMAIL3

  • SHA1EMAIL1

  • SHA1EMAIL2

  • SHA1EMAIL3

  • SHA256EMAIL1

  • SHA256EMAIL2

  • SHA256EMAIL3

The first field ("Customer ID") is an optional field you can include, which can be used as the audience key, allowing us to de-duplicate rows in the uploaded file (in case a file has multiple rows related to the same person). If included, the entries in this field should be unique and persistent across all audiences. Contact your LiveRamp representative if you are not using a Customer ID to identify records.

LiveRamp prefers and recommends that you provide column-based files, particularly when there is more than one identifier field (this will typically be PII-based data with a name and postal address, multiple email addresses, or some combination thereof). See "Formatting Column-Based Files" for more information.

Single Identifier File

Simple column-based file with one PII identifier (for example, name and postal). Some fields are intentionally left empty to illustrate proper no-fill of those values. No values contain punctuation so there is no need to use double quotes.

File Example Good Column-PeE.jpg

Download the Column-Based Single Identifier Example File

Single Identifier Column-Based File Example Enclosed with Quotes

Similar to the above example, but with an additional identifier (email) and a new field, "PreviousCity", that contains a comma within the values and so is enclosed with double quotes. Note that the delimiter commas remain outside the quotes.

File Example Good Column Quotes-nAU.jpg

Download the Column-Based File With Quotes Example File (open with a text editor to see the quotes)

Common Column-Based File Errors Example

This file contains a number of typical file formatting errors:

File Example Bad Columns-kEc.jpg
  1. The header row contains two columns with the same label ("Email").

  2. The first field of the postal address is simply "Name", rather than having individual fields for "FirstName" and "LastName".

  3. There is an extra comma after what should be the final field ("San Francisco, CA"), making the file non-rectangular.

  4. The email address "john.dough@email.com" incorrectly has the comma delimiter inside the double quotes.

  5. The address "555 New Rd Apt 2" has a newline character after "Rd", which has caused this row to abruptly end partway through and pick up on the next row.

  6. "Rochester, NY" is not enclosed in double quotes, adding an extra field of " NY" due to delimiter collision and making the file non-rectangular.

  7. The "Under25" field value is "NULL" rather than being left empty.

Download the Column-Based File Errors Example File

If you’re a LiveRamp customer or partner who needs to send consumer request data to LiveRamp (such as opt out requests, deletion requests, or subject access requests), create column-based files that include only one identifier type per file:

  • PII (such as name and postal, phone number, email address)

  • Plaintext mobile device IDs (IDFAs and AAIDs)

  • SHA-1-hashed IDFAs

  • SHA-1-hashed AAIDs

  • Cookies

  • IdentityLinks

Note

For subject access request files, add a column to the beginning of the file with a unique alphanumeric request ID for each consumer.

Note

For opt-outs and deletion requests, only include identifier fields in your request files. Do not include any other fields. If you use one of the downloadable templates below, delete the “REQUESTID” field.

Each file example comes with a header row template .csv file that you can download.

Once your files are properly formatted, upload them to the appropriate folders or subfolders on the LiveRamp SFTP (as provided by LiveRamp). See “Upload a File via LiveRamp's SFTP” for uploading instructions. See “Consumer Requests for Opt-Outs, Data Access, or Data Deletions” for more information.

Files with PII Identifiers

You can include all PII columns and simply leave any unused columns blank, or you can send only the columns that you have values for.

Consent Files PII Example-H1U.jpg

Subject Access Request Consent File Example with PII Identifiers

Download the consent files PII header row template with request ID

Files with Mobile Device IDs

If you’re sending plaintext mobile device IDs (MAIDs), you can send both IDFAs and AAIDs in the same file.

Consent Files MAID Example-IRA.jpg

Subject Access Request Consent File Example with Mobile Device IDs

If you’re sending SHA-1 hashed MAIDs, put the IDFAs and AAIDs into separate files.

Download the consent files mobile device ID header row template with request ID

Files with Cookies

Consent Files Cookie Example-QGw.jpg

Subject Access Request Consent File Example with Cookies

Download the consent files cookie header row template with request ID