Format your shop sales data

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

In this article

• Add your shop sales data
• Format your shop sales data file
• Format your column headers
• Learn the customer transaction data formatting guidelines

Before you begin

Include your data in an Excel, CSV or Google Sheets file. If you need help setting up your data, use these templates:

• Excel
• CSV
• Google Sheets

Basic formatting guidelines

• To keep your customers' transaction information secure, you'll need to hash customer names, email addresses and phone numbers in your shop sales data. You can hash the information yourself, or let Google Ads hash your shop sales data, using the SHA-256 algorithm (which is the industry standard for one-way hashing).
• Be aware that file size limits can prevent your file from being uploaded.
  • One-time upload directly into your Google Ads account:
    • 150 MB: For CSV files uploaded manually in Google Ads only, with estimated 300,000 transaction rows.
    • 50 MB: For all other formats, including CSVs uploaded using a schedule, with estimated 100,000 transaction rows.
  • 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 can create multiple upload jobs in parallel to increase the rate at which you submit rows for processing.
    Note: If you’re uploading using a shop sales data partner, check with your shop sales data partner on their limits for uploading shop sales data.

If you have technical questions concerning the Google Ads API (for example, questions directly related to API or SDK usage, errors received from the API or SDK, etc.), please use the troubleshooting steps found on the API technical support page.

If you have questions concerning Google Ads API that relate to the Google Ads UI specifically, please click 'Contact us' at the bottom of the page.

Column header formatting guidelines

It's mandatory to have two header rows in your shop 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 shop sales data file use the exact names specified in English. The rest of your data can be in any language, but the column headers must be in English.

Upload metadata

The first row includes the upload metadata.

Note: If you're using the custom variables feature, you should format the column header in this row as:

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

Bear in mind that your custom variable should be active in order to use it.

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

• LoyaltyRate and TransactionUploadRate values should be set to 1.

Customer transaction metadata

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

• Email
• First name
• Last Name
• City
• County
• Postcode
• Country
• Phone Number
• Conversion Name (required)
• Conversion Time (required)
• Conversion Value (required)
• Conversion currency (required)
• Ad user data (if using shop sales data with Customer Match for EEA users)
• Ad Personalisation (if using shop sales data with Customer Match for EEA users)

At least one email, phone number or address is required with your upload. You can add up to three email addresses and three 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 shop sales data file.

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

Data formatting guidelines

The customer transaction information that 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, postcodes 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.

Time zone

• 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. Review 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 saving 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

Loyalty Rate

• The percent of overall sales in your data file that you can associate with a customer
• We recommend leaving this value at '1'

Transaction upload rate

• Currently, the value of your Transaction Upload Rate doesn’t impact shop sales reporting. We recommend that you upload all customer associated sales and set the Transaction Upload Rate to '1'.

For example:

• 0.5
• 0.2
• 0.8

Email

• Include a domain name for all email addresses, such as gmail.com or hotmail.co.jp.
• Remove any spaces in between the email addresses.
• All lowercase letters
• Can provide up to three email addresses per customer, which is separated into three 'Email' columns

For example:

• [e-mail]
• [e-mail]

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

Surname

• 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/Postcode

• US zip codes and international postcodes are allowed.
• For the US:
  • 5-digit zip codes are allowed.
  • 5 digits followed by 4-digit extension are also allowed and can improve your match rate.
• For all other countries:
  • Don’t include postcode 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:

• U.S.
• 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. This can include a national destination code or an area code.
• Can provide up to three phone numbers per customer, which is separated into three 'Phone number' columns

For example:

• [phone]
• [phone]

Conversion name

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

For example:

• SS_customer_signups
• SS_customer_purchases

Conversion time

• Use an acceptable date format. Review the table below.
  • To add a time zone to the conversion time, see one of the last four formats in the table below.
  • Replace '+z' with the GMT offset by indicating '+' or '-' and then the four-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

• A number representing the value that you place on the conversion
• Only positive numbers are accepted

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

Custom variable value

• The variable values associated with the custom variable mentioned in the parameter row.
• The variable values specified in the upload file must match the variable values defined in your Google Ads account.
• Use this column if you're using custom variables and have mentioned a custom variable in the parameters.
• Use this column to specify variable values associated with each transaction.
• If any transactions are uploaded with an unmatched variable value as defined in the Google Ads account, shop sales conversions associated with such transactions will be reported with a variable value as 'uncategorised'.

For example:

• Custom variable: 'Customer type'
  • Variable values: 'New' or 'Returning'
• Custom variable: 'Product category'
• Variable values: 'Jackets', 'Shoes', 'Sportswear' or 'Other'

Important: When creating or uploading custom variables and custom variable values, don’t include any personally identifiable information (PII) or any sensitive categories per our data policies. Custom variables and their values should only reflect non-sensitive and non-personal data.

Ad user data

• Pass consent signals for EEA users for ad measurement purposes.
• Required if using Customer Match with shop sales data.

Possible values:

• Granted: The consent for data to be used is granted
• Denied: The consent for data to be used is denied
• Unspecified: The consent is unspecified

For example:

• Granted
• Denied

Ad Personalisation

• Pass consent signals for EEA users for ad personalisation purposes.
• Required if using Customer Match with shop sales data.

Possible values:

• Granted: The consent for data to be used is granted
• Denied: The consent for data to be used is denied
• Unspecified: The consent is unspecified

For example:

• Granted
• Denied

RETURN TO SHOP SALES HOME