Formatting File Data

Once you’ve determined what data you’ll be including, and which audience you’ll be uploading the data to (if necessary), review the information in the sections below (including the sections pertaining to your file type) to make sure that the data is formatted in a way that LiveRamp can accept.

Once you’ve made sure that all of the data is formatted correctly, finalize the file for uploading.

Note

See "Uploading Data" for an overview of the file creation and formatting process.

For both column-based files and key-value files, follow the guidelines listed below (in addition to the appropriate guidelines for formatting column-based files and for formatting key-value files).

Use Double Quotes When the Data Contains the Delimiter

Data containing punctuation characters is at risk of delimiter collision and thus data bleed, where the delimiter chosen (such as a comma) also appears as part of the data values. This can cause LiveRamp to interpret data in a particular row as belonging to the wrong field.

To avoid this, enclose each field, key, and value in double quotes, following the guidelines listed below:

  • Equals signs and delimiters (such as commas or pipes) should be outside the quotes.

  • Make sure that all quotes are closed with an even number of double quotes characters per data row.

  • If using quotes, best practice (but not required) is to quote all fields rather than only those with delimiter collision potential.

  • It is not necessary to quote empty/null fields.

  • If a particular field value itself contains double quotes characters, they should be properly escaped and maintain the "even number of quotes" rule:

    • LCD TV,50" becomes "LCD TV","50"""

    • "early-bird" special becomes ""early-bird" special"

    • 5'8" becomes "5'8""

Put All Data Related to a Given Identifier on a Single Row

For example, if you have three different segments, put them all on one row, either as three columns or three key-value pairs, rather than listing the same identifier on three rows with one segment per row. The latter approach will make file processing take significantly longer.

Caution

Multi-valued data is an exception. If you have a column where there are multiple values for the same header, each column value should be on a separate row, along with the identifier. Contact your LiveRamp representative to make sure this data is ingested properly.

Do Not Use Placeholders for Empty Values

If a given field entry for a particular row of data has no value, leave it blank. Do not use a placeholder such as "NULL" or "N/A".

Use UTF-8 Encoding

UTF-8 is in wide adoption and ensures maximum compatibility across different systems. ASCII encoding is accepted as it is included in the UTF-8 standard. When you're saving your file, make sure not to save it as something other than a UTF-8 encoded file type.

Formatting Dates

You can include segment data fields that contain calendar date values (such as date of most recent purchase) in the files you upload.

If you want to use a date field to create derived segments, use one of our preferred date formats: YYYYMM or YYYYMMDD. Do not include forward slashes (/) or hyphens (-).

This creates a numeric field that LiveRamp can map into segment ID codes that you can later modify into segment ranges using LiveRamp’s Segment Creator.

Note

Privacy issues: Keep in mind that LiveRamp will not distribute any data that could easily identify a group of fewer than 25 individuals (or fewer than two individuals for measurement files). Using the YYYYMM format can help avoid this situation.

Formatting Segment List Data

Not commonly used, these are single-field identifier files, typically device-based identifiers such as cookies tied to single PII identifier.

LiveRamp accepts non-rectangular segment list-based data. These are usually files with a single identifier field (typically a device-based identifier, such as a cookie ID, or PII-based data tied only to a single email address or phone number).

  • The identifier should be the first field of each row:

    <identifier1>,seg1,seg2

    <identifier2>,seg2

  • Separate the identifier and each segment with one of the allowed delimiters.

  • Segments should be unique per row. That is, do not include the same segment multiple times on a single row.

The file types listed below can be accepted, as long as they use one of the allowed file delimiters and meet all other file formatting requirements:

  • Comma-separated values files (.csv)

  • Tab-separated values files (.tsv)

  • Pipe (|)-separated values files (.psv)

  • Semicolon-separated values files (.scsv)

  • Text files (.txt)

Caution

EU data cannot be sent in txt files. See "Creating EU Data Files" for complete information on formatting EU data files.

When creating files for upload, keep in mind that files must be provided in a flat, text-based format. As a general rule of thumb, if the file contents cannot be previewed within a command-line terminal or simple text editor (like WordPad or TextEdit), we cannot accept it.

Files can be column-based with delimiters or in key-value format (except for EU data files, which can only be sent as column-based files). Files can also be compressed, which will result in a different file extension.

Caution

No Excel or Word files: Non-text-based file types, such as Microsoft Excel (.xls or .xlsx) or Word (.doc or .docx) files, cannot be accepted.

Caution

For Mac Excel files: If exporting data from Microsoft Excel for Mac, choose the "Windows Comma Separated (.csv)" option (do not use the MS-DOS or Macintosh CSV versions, or any non-UTF-8 option). Make sure that extra columns haven't been added during the export process before uploading.

Use one of the delimiters listed below to separate values when creating a file for uploading:

  • Commas (comma-separated values files, or .csv files)

  • Tabs (tab-separated values files, or .tsv files)

  • Pipes (|) (pipe-separated values files, or .psv files)

  • Semicolons (semicolon-separated values files, or .scsv files)

Make sure to follow all other guidelines for allowed file types and for formatting file data.

Note

Use double quotes when the data contains the delimiter: Data containing punctuation characters is at risk of delimiter collision and thus data bleed, where the delimiter chosen (such as a comma) also appears as part of the data values. This can cause LiveRamp to interpret data in a particular row as belonging to the wrong field. See "Formatting Guidelines for All Files" for more information.

There are many possible considerations to keep in mind when creating files for upload, but the cause of file upload failure or delay often falls into one of the categories listed below.

Overall, keep in mind that files for ingestion in the same audience should always have the same set of identifier fields, and the field names (headers in a column-based file and keys in a key-value file) and audience key should be consistent from file to file.

Note

Files uploaded for a particular audience are put into a queue for that audience, so a delay in the processing of a file containing issues will delay all subsequent files that have been uploaded to that audience.

For examples of properly formatted files (including downloadable files that can be used as templates), as well as files containing common errors, see "File Formatting Examples".

Header Issues

Header issues are the most common cause of file upload failure. The most common header issues are:

  • A header that was in the original file uploaded for that audience is missing, or is named differently (including being capitalized differently).

  • The file contains two or more headers that are exactly the same (sometimes extra headers and columns are added when saving from Excel).

Make sure that:

  • Every column-based file you upload has a header row.

  • Make sure that the headers are unique and match the headers in the original file uploaded for that audience.

See "Formatting Column-Based Files" for more information.

Audience Key Issues

Keep in mind that every file uploaded to a particular audience must use the same audience key as the original file uploaded for that audience so that we can effectively consolidate all rows within that audience.

The most common audience key issues are:

  • The audience key is different from the one used for the first file uploaded for that audience.

  • An identifier that was used originally as the audience key is not included in the file, such as “city” when the audience key is name and postal address.

Identifier Issues

  • An identifier is formatted incorrectly, such as including both the first and last name in the same field instead of having separate “first name” and “last name” fields.

See "Formatting Identifiers" for more information.

Delimiter Issues

  • There is an extra delimiter (such as a comma) in a row, making it appear as though the row has more fields in it than it should. When this happens, our system gets confused while trying to parse your file. Watch out for this, especially when creating CSV files from Excel files.

  • The data contains the delimiter, but is not enclosed in double quotes. Data containing punctuation characters is at risk of delimiter collision and thus data bleed, where the delimiter chosen (such as a comma) also appears as part of the data values. This can cause LiveRamp to interpret data in a particular row as belonging to the wrong field. See "Formatting Guidelines for All Files" for more information.

test