Format your store sales data

Store sales data needs to be formatted correctly and comply with our customer data policies. This article provides guidelines to follow when formatting your store sales data file.

Basic formatting guidelines

  • Include your data in an Excel, CSV, or Google Sheets file. If you need help setting up your data, use these templates: 
  • To keep your customers' transaction information secure, you'll need to hash customer names, email addresses, and phone numbers in your store sales data. You can hash the information yourself, or let Google Ads hash your store sales data, using the SHA-256 algorithm (which is the industry standard for one-way hashing).
  • Be aware that file size limits may prevent your file from being uploaded.
    • If you’re doing a one-time upload directly into your Google Ads account: 50MB (or up to 100,000 rows of transactions)
    • If you’re uploading using the Google Ads API: 1,000,000 rows of transactions per upload job. You should use OfflineUserDataJobService to create the job and add transactions using the AddOfflineUserDataJobOperations method in batches of 10,000 rows at a time to the job for optimal processing. You may create multiple upload jobs in parallel to increase the rate at which you submit rows for processing.
       
      Note: If you’re uploading using a store sales data partner, check with your store sales data partner on their limits for uploading store sales data.

Column header formatting guidelines 

You’ll need two header rows in your store sales data file: a row for upload metadata and a row that includes customer transaction column names.

Note: Ensure that your column headers in your store sales data file use the exact names specified in English. The rest of your data may be in any language, but the column headers must be in English.

Upload metadata

The first row includes the upload metadata, including the time zone, loyalty rate, and transaction upload rate.

You should format the column header in this row as:

Parameters:TimeZone=<insert_TZ>;LoyaltyRate=<insert_rate>;TransactionUploadRate=<insert_rate>

In the brackets, you’ll add the requested information for each parameter. 

Customer transaction metadata

The second row includes information related to the customer transactions you want to upload, such as email addresses, phone numbers, and names. Here are the column headers you can add to the second row for customer transaction information:

  • Email
  • First Name
  • Last Name
  • City
  • State
  • Zip
  • Country
  • Phone Number
  • Conversion Name (required)
  • Conversion Time (required)
  • Conversion Value (required)
  • Conversion Currency (required)
You can add up to 3 email addresses and 3 phone numbers per customer. If you want to include multiple email addresses and phone numbers per customer, add 3 “Email” columns and 3 “Phone Number” columns to your store sales data file. 

In the next section, you’ll learn how to format your store sales data.

Data formatting guidelines

The customer transaction information you provide needs to be formatted correctly so that Google Ads can upload the data. Some customer transaction information needs to be hashed using the SHA-256 algorithm, which includes:

  • Customer email addresses
  • Customer phone numbers
  • Customer first and last names

Other customer transaction data--such as the country of residence, zip codes, and attribute data--doesn’t need to be hashed.

To get started, download the Excel, CSV, or Google Sheets template and enter your customer transaction information. If you’re unable to hash some of the customer transaction information, use the CSV template. Google Ads will automatically hash any data that requires hashing when you upload the completed CSV template.

Expand a section below to see guidelines for each column in the template. The column names should be exactly as written in the template.

TimeZone

  • Include in the first row if all conversion times are in the same time zone
    • If conversion times are in different time zones, add the time zone for each conversion time in the “Conversion Time” column (see the instructions and table in the “Conversion Time” section in this article)
    • If you enter a time zone in both the first row and the “Conversion Time” column, the “Conversion Time” column will be considered first in the upload (followed by the time zone in the first row if a conversion is missing a time zone)
  • Enter your time zone ID from this list to avoid errors during daylight savings time transitions
  • Enter your Greenwich Mean Time (GMT) offset by indicating “+” or “-” and then the 4 digit time difference (for GMT, enter +0000)

For example:

  • America/New_York-0500
  • Europe/Berlin+0100

LoyaltyRate

  • The percent of overall sales in your data file that you can associate with a customer
  • The Loyalty Rate needs to be between “0” and “1” (excluding “0”)

For example:

  • 0.5
  • 0.2
  • 0.8

TransactionUploadRate

  • The ratio of sales you’re uploading to the overall sales that you can associate with a customer
  • While the Transaction Upload Rate can be between “0” and “1,” we recommend uploading all customer associated sales and setting the Transaction Upload Rate to “1”.

For example:

  • 0.5
  • 0.2
  • 0.8

Email

  • Include a domain name for all email addresses (for example, gmail.com or hotmail.co.jp)
  • Remove any spaces in between the email address
  • All lowercase letters
  • Can provide up to 3 email addresses per customer (separated into 3 “Email” columns)

For example:

  • user1@gmail.com
  • user2@yahoo.com

First Name

  • All lowercase letters
  • Accents are allowed
  • Remove any unnecessary spaces before, after, and in between the names

For example:

  • tom
  • renée
  • marie-astrid

Last Name

  • All lowercase letters
  • Accents are allowed
  • Remove any unnecessary spaces before, after, and in between the names

For example

  • gupta
  • o'calloway
  • cox-cohen
  • mcdonnell

City

  • All lowercase letters
  • Accents are allowed

For example:

  • san francisco
  • new york city
  • paris
  • munich

State

  • All lowercase letters
    • Exception: 2-letter abbreviated state names for the US

For example:

  • CA
  • california
  • new york
  • maharastra

Zip

  • US zip codes and international postal codes are allowed 
  • For the US:
    • 5-digit zip codes are allowed
    • 5 digits followed by 4-digit extension are also allowed and may improve your match rate
  • For all other countries:
    • Don’t include postal code extensions

For example:

  • 94109
  • 94109-1234
  • W1J 0BH

Country

  • Use ISO two-letter country codes
  • Include the country code even if all of your customer data is from the same country

For example:

  • US
  • DE
  • FR
  • AU
  • JP

Phone Number

  • Phone numbers should be formatted according to the E.164 standard and include the "+" prefix, followed by the country code and the subscriber number (which may include a national destination code or an area code)
  • Can provide up to 3 phone numbers per customer (separated into 3 “Phone Number” columns)

For example:

  • +12038271234
  • +442071838750

Conversion Name

  • The name of the conversion action that you’d like to import
  • Must match the same spelling and capitalization of the conversion action in your Google Ads account

For example:

  • SS_customer_signups
  • SS_customer_purchases

Conversion Time

  • Use an acceptable date format (see the table below)
    • To add a time zone to the conversion time, see one of the last 4 formats in the table below
    • Replace “+z” with the GMT offset by indicating “+” or “-” and then the 4-digit time difference
    • Or, replace "zzzz" with the time zone ID
Format Examples
MM/dd/yyyy hh:mm:ss aa "08/14/2020 5:01:54 PM"
MMM dd,yyyy hh:mm:ss aa "Aug 14, 2020 5:01:54 PM"
MM/dd/yyyy HH:mm:ss "08/14/2020 17:01:54"
yyyy-MM-dd HH:mm:ss "2020-08-14 13:00:00"
yyyy-MM-ddTHH:mm:ss "2020-08-14T13:00:00"
yyyy-MM-dd HH:mm:ss+z "2020-08-14 13:00:00+0500"
yyyy-MM-ddTHH:mm:ss+z "2020-08-14T13:00:00-0100"
yyyy-MM-dd HH:mm:ss zzzz "2020-08-14 13:00:00 America/Los_Angeles"
yyyy-MM-ddTHH:mm:ss zzzz "2020-08-14T13:00:00 America/Los_Angeles"

Conversion Value

Conversion Currency

  • The currency in which your conversion value is provided
  • Use this column if you report conversion values in more than one currency, or you have multiple customer accounts that are billed in different currencies
  • Use 3-character ISO 4217 currency codes

For example:

  • USD
  • JPY
  • EUR

RETURN TO STORE SALES HOME

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue