Skip to main content

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

Data from EMEA countries can only be accepted in a column-based file format. Key-value files are not allowed for EMEA data. For more information, see "Creating Data Files for EMEA Countries".

Formatting Guidelines Highlights for Column-Based Files

For the best results, follow these requirements and guidelines.

Include a Header Row in Every File

  • Include a header row in every column-based file.

  • Make sure each header matches the contents of its column.

  • Subsequent uploads must be consistent with the original file's formatting, including header order and syntax. We recommend that you format headers using all capital letters with no spaces or underscores so that you can reliably format subsequent files the same way.

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.

Note

The recommended headers for PII-based files are somewhat different for U.S. data files and EMEA data files. See "Headers to Use for EMEA PII Data Files" 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. This is so that we can ingest the files automatically and so there are no delays in ingestion process. For more information, see "The Ingestion Automation Process for File Uploads".

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.

Headers to Use for U.S. PII Data Files

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 with 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.

Headers to Use for EMEA PII Data Files

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

Note

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

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.

Examples of Column-Based Segment Data Files

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 quotation marks.

File Example Good Column-PeE.jpg

Download the Column-Based Single Identifier example file template

Single Identifier Column-Based File Example Enclosed with Quotation Marks

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 quotation marks. Note that the delimiter commas remain outside the quotation marks.

File Example Good Column Quotes-nAU.jpg

Download the Column-Based File With Quotation Marks example file template (open with a text editor to see the quotation marks)

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 quotation marks.

  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 quotation marks, 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

Examples of Column-Based Consumer Request Files

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

  • RampIDs

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

Files with RampIDs

Consent Files IDL Example-KPw.jpg

Subject Access Request Consent File Example with RampIDs

Download the consent files RampID header row template with request ID

In this section:

A type of file where every row has the same number of fields and the field names are contained in a header row, as opposed to a key/value file.

A type of data file where each row contains key/value pairs in the form of "key=value" (for example, "gender=female").

Data that are used to identify something, such as a person, mobile device, computer browser, or household.

Personally-identifiable information (such as name and postal, phone number, or email address) which, on its own or when combined with some other data, can be used to identify an individual.

The first row of a column-based file, where the entries of that row indicate the field name for each column.

Personally-identifiable information (such as name and postal, phone number, or email address) which, on its own or when combined with some other data, can be used to identify an individual.

The process through which customer files are uploaded and properly formatted in LiveRamp's system.

In the context of LiveRamp Connect, a way to group your consumers within LiveRamp.

A field name entry for a particular column of data in a column-based file.

A group of your records that are defined by a specific attribute (a field/value pairing, such as "Gender=female"). For example, a field for "Gender" might contain segments for "Gender=Female" and for "Gender=Male."

A column-based file where every row has the exact same number of delimiters and columns of data.

A character (such as a comma or pipe) that acts as a separator between two values in a data file.

A set of segment data that holds a specific category of values, such as "Car_Owned."

Within a column of segment data in a data file, the entry for a given record that indicates a trait or attribute value, such as "BMW" in a field named "Car_Owned." A group of users with a specific field/value pairing creates a segment.

Fixed width text files are special cases of text files, which are not supported by LiveRamp, where the format is specified by column widths, pad character, and left/right alignment. Column widths are measured in units of characters. For example, if you have data in a text file where the first column always has exactly 10 characters, and the second column has exactly 5, the third has exactly 12 (and so on), this would be categorized as a fixed-width text file.

A field in your files that acts as a unique ID for all of the records in a given LiveRamp audience.

A field in your files that acts as a unique ID for all of the records in a given LiveRamp audience.

A category of identifiers where the identifiers consist of PII (personally identifiable information). PII identifiers are sometimes also referred to as "offline identifiers."

The first row of a column-based file, where the entries of that row indicate the field name for each column.

rectangular

A column-based file where every row has the exact same number of delimiters and columns of data.

Personally-identifiable information (such as name and postal, phone number, or email address) which, on its own or when combined with some other data, can be used to identify an individual.

A cryptographic operation that is performed on variable-length data strings, such as email addresses or phone numbers, to convert them from plaintext to bit strings of a fixed length through the use of a specific algorithm. Hashing of a particular data string with a particular algorithm always produces the same result. Hashing is designed to be a one-way function so that it is very difficult to reverse. Common hashing algorithms include MD5, SHA-1, and SHA-256.

A packet of data sent by an Internet server to a browser, which is returned by the browser each time it subsequently accesses the same server and used to identify the user or track their access to the server.

LiveRamp's universal, pseudonymous identifier that is tied to devices in the LiveRamp Identity Graph.