Upload offline transactions

30/11/2019

Once you’ve set up Store sales (direct upload) as a conversion type, you’re ready to start uploading offline transactions to Google.

This article explains how to set up your offline transactions and event data, and upload them to Google Ads.

How it works

You’ll have to set up the offline transaction/events data to upload to Google. You can either upload transaction data directly in Google Ads or work with third-party partners. Ask your account representative about using a third-party data partner to track your offline conversions. 

Instructions

Follow the instructions below to set up your offline transaction/event data. You may upload private customer data that you’ve already hashed using the SHA256 algorithm, or, you may upload private customer data in plain text via one-time CSV uploads within the Google Ads interface and it will be automatically hashed for you using the same SHA256 algorithm, which is the industry standard for one-way hashing.

Keep in mind

You don’t have to provide all the data types related to customer information (4b-4h). But providing as many as possible can help you increase your match rates.

Upload unhashed plain text data

  1. Download the conversion upload CSV template (Note: Client-side hashing is not supported for Excel or Google Sheets uploads). Be sure not to remove the row that begins with "Email" or your import won’t work.
    1. Make sure your data doesn't include additional columns or any information other than required in the template.
    2. Make sure to provide both the parameters:
      • Loyalty Rate: This is the fraction of overall sales which you can associate with a customer loyalty program. The fraction needs to be between 0 and 1 (excluding 0).
      • Transaction Upload Rate: This is the ratio of sales you’re uploading to the overall sales that you can associate with a customer. The fraction needs to be between 0 and 1. For example if you upload half the sales that you are able to associate with a customer, your Transaction Upload Rate would be 0.5 or 50% (excluding 0).
  2. Choose how to enter your timezone in the file:
    1. If all of your conversion times are in the same time zone, set the time zone once in the "Parameters" row.
    2. If your conversion times are in different time zones, add the time zone to each conversion time using the “Conversion Time” column. See the instructions and table under “Conversion Time” in step 4 below. If you don’t enter a time zone in the “Parameters” row then you’ll just be providing Loyalty Rate and Transaction Upload Rate in the “Parameters” row.
    3. Or, you can enter a time zone in both the “Parameters” row and the “Conversion Time.” “Conversion Time” will be used first, and if any conversion is missing a time zone, the “Parameters” value will be used.
  3. Use one of the following formats to enter the time zone:
    1. Enter your time zone ID from this list. This method is recommended to avoid errors during daylight savings time transitions.
    2. Enter your GMT offset by indicating + or - and then the 4 digit time difference. (For example, New York's offset is -0500, and Berlin's is +0100). If you use Greenwich Mean Time, then simply enter +0000.
  4. Next, add a new row for each offline transaction, filling in the columns as follows:
    1. Email:
      • The email addresses will be automatically hashed in Google Ads using SHA-256 upon submission
      • You can provide up to 3 email addresses. Depending on how many email addresses you’re providing you can have up to 3 columns with the header name “Email”
      • Lowercase all characters
      • Remove any extra spaces before, after, or in between the email address
      • Remove periods before "@" sign
      • Examples: user1@gmail.com, user2@yahoo.com, user3@googlemail.com
    2. Phone Number:
      • The phone number will be automatically hashed in Google Ads using SHA-256 upon submission
      • You can provide up to 3 phone numbers. Depending on how many phone numbers (or different formats) you’re providing you can have up to 3 columns with the header name “Phone Number”
      • Phone numbers should be formatted according to the E.164 standard and consist of the "+" prefix, followed by country code and subscriber number (which may include a national destination code or area code)
      • Examples: +12038271234, +442071838750
    3. First Name:
      • The first name will be automatically hashed in Google Ads using SHA-256 upon submission
      • Lowercase all characters
      • Accents are allowed
      • Remove any extra spaces before, after, and in between
      • Examples: tom, renée, marie-astrid, john
    4. Last Name:
      • The last name will be automatically hashed in Google Ads using SHA-256 upon submission
      • Accents are allowed
      • Remove any extra spaces before, after, and in between
      • Examples: gupta, o'calloway, cox-cohen, mcdonnell
    5. City:
      • The city name will remain unhashed
      • Lowercase all characters
      • Accents are allowed
      • Examples: san francisco, new york, paris, münchen
    6. State:
      • The state names will remain unhashed
      • Lowercase all characters
      • We also accept 2-letter abbreviated state names for US (this can be in uppercase)
      • Example: CA, california, new york, maharastra
    7. Zip Code:
      • The zip code will remain unhashed
      • We accept US and international zip and postal codes
      • For United States:
        1. 5-digit codes are allowed
        2. 5 digits followed by 4-digit extension are also allowed and may improve your match rate
      • For all other countries:
        1. Leave out postal code extensions
      • Example: 94109, 94109-1234, 103-8011, W1J 0BH
    8. Country:
      • The country name will remain unhashed
      • Use ISO two-letter country codes
      • Include the country code even if all of your customer data is from the same country
      • Example: US, DE, FR, AU, JP
    9. Conversion Name: the name of the conversion action that you’d like to import this conversion for. It's important that you use the exact same spelling and capitalization that you did when you created this conversion action in your Google Ads account.
    10. Conversion Time: the date and time that the conversion occurred. See the table below for a list of acceptable date formats (e.g. MM/dd/yyyy HH:mm:ss). You can add a time zone to the conversion time by using one of the last 4 formats listed. Replace “+z” with the GMT offset by indicating + or - and then the 4 digit time difference. (For example, New York's offset is -0500, and Berlin's is +0100). Or, replace "zzzz" with the time zone ID from this list.
      Format Examples
      MM/dd/yyyy hh:mm:ss aa "08/14/2012 5:01:54 PM"
      MMM dd,yyyy hh:mm:ss aa "Aug 14, 2012 5:01:54 PM"
      MM/dd/yyyy HH:mm:ss "08/14/2012 17:01:54"
      yyyy-MM-dd HH:mm:ss "2012-08-14 13:00:00"
      yyyy-MM-ddTHH:mm:ss "2012-08-14T13:00:00"
      yyyy-MM-dd HH:mm:ss+z "2012-08-14 13:00:00+0500"
      yyyy-MM-ddTHH:mm:ss+z "2012-08-14T13:00:00-0100"
      yyyy-MM-dd HH:mm:ss zzzz "2012-08-14 13:00:00 America/Los_Angeles"
      yyyy-MM-ddTHH:mm:ss zzzz "2012-08-14T13:00:00 America/Los_Angeles"

       

    11. Conversion Value: a number representing the value that you place on the conversion (only positive numbers are accepted).
    12. Conversion Currency (optional field): the currency in which your conversion value is provided. You'll use this if you report conversion values in more than one currency, or have multiple accounts that are billed in different currencies. Use 3-character ISO 4217 currency codes, such as USD for United States dollars, and JPY for Japanese yen.

Upload hashed data

  1. Download the conversion upload template (download: Excel, CSV, or Google Sheets). Be sure not to remove the row that begins with "Email" or your import won’t work.
    1. Make sure your data doesn't include additional columns or any information other than required in the template.
    2. Make sure to provide both the parameters:
      • Loyalty Rate: This is the fraction of overall sales which you can associate with a customer loyalty program. The fraction needs to be between 0 and 1 (excluding 0).
      • Transaction Upload Rate: This is the ratio of sales you’re uploading to the overall sales that you can associate with a customer. The fraction needs to be between 0 and 1. For example, if you upload half of the sales that you are able to associate with a customer, your Transaction Upload Rate would be 0.5 or 50%.
  2. Choose how to enter your timezone in the file:
    1. If all of your conversion times are in the same time zone, set the time zone once in the "Parameters" row.
    2. If your conversion times are in different time zones, add the time zone to each conversion time using the “Conversion Time” column. See the instructions and table under “Conversion Time” in step 4 below. If you don’t enter a time zone in the “Parameters” row then you’ll just be providing Loyalty Rate and Transaction Upload Rate in the “Parameters” row.
    3. Or, you can enter a time zone in both the “Parameters” row and the “Conversion Time.” “Conversion Time” will be used first, and if any conversion is missing a time zone, the “Parameters” value will be used.
  3. Use one of the following formats to enter the time zone:
    1. Enter your time zone ID from this list. This method is recommended to avoid errors during daylight savings time transitions.
    2. Enter your GMT offset by indicating + or - and then the 4 digit time difference. (For example, New York's offset is -0500, and Berlin's is +0100). If you use Greenwich Mean Time, then simply enter +0000.
  4. Next, add a new row for each offline transaction, filling in the columns as follows:
    1. Email:
      • The email addresses should be hashed using SHA-256
      • You can provide up to 3 email addresses. Depending on how many email addresses you’re providing you can have up to 3 columns with the header name “Email”
      • Lowercase all characters
      • Remove any extra spaces before, after, or in between the email address
      • Remove periods before "@" sign
      • Examples: user1@gmail.com, user2@yahoo.com, user3@googlemail.com
    2. Phone Number:
      • The phone number should be hashed using SHA-256
      • You can provide up to 3 phone numbers. Depending on how many phone numbers (or different formats) you’re providing you can have up to 3 columns with the header name “Phone Number”
      • Phone numbers should be formatted according to the E.164 standard and consist of the "+" prefix, followed by country code and subscriber number (which may include a national destination code or area code)
      • Examples: +12038271234, +442071838750
    3. First Name:
      • The first name should be hashed using SHA-256
      • Lowercase all characters
      • Accents are allowed
      • Remove any extra spaces before, after, and in between
      • Examples: tom, renée, marie-astrid, john
    4. Last Name:
      • The last name should be hashed using SHA-256
      • Accents are allowed
      • Remove any extra spaces before, after, and in between
      • Examples: gupta, o'calloway, cox-cohen, mcdonnell
    5. City:
      • The city name shouldn't be hashed
      • Lowercase all characters
      • Accents are allowed
      • Examples: san francisco, new york, paris, münchen
    6. State:
      • The state name shouldn't be hashed
      • Lowercase all characters
      • We also accept 2-letter abbreviated state names for US (this can be in uppercase)
      • Example: CA, california, new york, maharastra
    7. Zip Code:
      • The zip code shouldn't be hashed
      • We accept US and international zip and postal codes
      • For United States:
        1. 5-digit codes are allowed
        2. 5 digits followed by 4-digit extension are also allowed and may improve your match rate
      • For all other countries:
        1. Leave out postal code extensions
      • Example: 94109, 94109-1234, 103-8011, W1J 0BH
    8. Country:
      • The country name shouldn't be hashed
      • Use ISO two-letter country codes
      • Include the country code even if all of your customer data is from the same country
      • Example: US, DE, FR, AU, JP
    9. Conversion Name: the name of the conversion action that you’d like to import this conversion for. It's important that you use the exact same spelling and capitalization that you did when you created this conversion action in your Google Ads account.
    10. Conversion Time: the date and time that the conversion occurred. See the table below for a list of acceptable date formats (e.g. MM/dd/yyyy HH:mm:ss). You can add a time zone to the conversion time by using one of the last 4 formats listed. Replace “+z” with the GMT offset by indicating + or - and then the 4 digit time difference. (For example, New York's offset is -0500, and Berlin's is +0100). Or, replace "zzzz" with the time zone ID from this list.
      Format Examples
      MM/dd/yyyy hh:mm:ss aa "08/14/2012 5:01:54 PM"
      MMM dd,yyyy hh:mm:ss aa "Aug 14, 2012 5:01:54 PM"
      MM/dd/yyyy HH:mm:ss "08/14/2012 17:01:54"
      yyyy-MM-dd HH:mm:ss "2012-08-14 13:00:00"
      yyyy-MM-ddTHH:mm:ss "2012-08-14T13:00:00"
      yyyy-MM-dd HH:mm:ss+z "2012-08-14 13:00:00+0500"
      yyyy-MM-ddTHH:mm:ss+z "2012-08-14T13:00:00-0100"
      yyyy-MM-dd HH:mm:ss zzzz "2012-08-14 13:00:00 America/Los_Angeles"
      yyyy-MM-ddTHH:mm:ss zzzz "2012-08-14T13:00:00 America/Los_Angeles"

       

    11. Conversion Value: a number representing the value that you place on the conversion (only positive numbers are accepted).
    12. Conversion Currency (optional field): the currency in which your conversion value is provided. You'll use this if you report conversion values in more than one currency, or have multiple accounts that are billed in different currencies. Use 3-character ISO 4217 currency codes, such as USD for United States dollars, and JPY for Japanese yen.

Upload offline event data

To import your offline conversions to Google Ads, you'll need to upload your conversion file into your Google Ads or manager (MCC) account.

Important: After creating a new conversion action, wait 4-6 hours before uploading conversions for that conversion action. If you upload conversions during the first 4-6 hours, you’ll receive an error and you’ll need to upload your conversions again.

One-time upload

  1. Sign in to your Google Ads account.
  2. In the upper right corner of your account, click the tools icon .
  3. Under “Measurement”, click Conversions.
  4. Click Uploads from the page menu.
  5. Click the plus button .
  6. To download the template that fits with the type of conversions you’d like to import, click View templates. You can choose Excel, CSV, or Google Sheets to upload conversions from clicks, calls, or Store sales.
  7. Click Done.
  8. Select the “Source” you want to upload from: Upload a file, Google Sheets, HTTPS, or SFTP.
    1. If you choose Upload a file or Google Sheets, click Choose file to select the file you want to upload.
    2. If you choose HTTPS or SFTP, enter the URL for the file, and the username and password to access the file.
  9. Click the Upload and preview or Upload and apply button.
    1. If you click “Upload and preview”, you’ll see a preview of how many rows were uploaded successfully, and how many errors occurred. Once you’re happy with your data, click Apply file to start processing after all rows are uploaded. (Keep in mind, there's no "undo" option after this point.)
    2. If you click “Upload and apply”, the file will start processing after all the rows are uploaded.
  10. To see more details about the upload status (including any errors with the upload), click the links in the “Results” column.

Scheduled Upload

  1. Sign in to your Google Ads account.
  2. In the upper right corner of your account, click the tools icon .
  3. Under "Measurement", click Conversions.
  4. Click Uploads from the page menu.
  5. Click Schedules.
  6. Click the plus button .
  7. Select the “Source” you want to upload from: Google Sheets, HTTPS, or SFTP.
    1. If you choose Google Sheets, click Choose file to select the file you want to upload.
    2. If you choose HTTPS or SFTP, enter the URL for the file, and the username and password to access the file.
  8. Set a schedule by selecting the "Frequency" and "Time" of day you'd like your uploads to occur. You can schedule weekly or daily uploads for any time of day.
  9. Click Save & preview to save your schedule.
  10. You'll see your saved schedule in the table. To edit, pause, or remove a schedule, go to the "Actions" column and click Options. Select Edit, Pause, or Remove.
  11. After making any edits, click Save & preview. If you remove a schedule, confirm that you'd like to remove it by clicking Remove.

Use the Google Ads API (AdWords API)

Account owners or administrators can also use the Google Ads API (AdWords API) to upload data files and manage their Store sales (direct upload) conversions. Advertisers can create conversion actions, upload a data file, or edit configurations through the API. Learn more about the Google Ads API (AdWords API).

Additional guidelines

Click the sections below for guidelines to follow while importing your transactions.

Timing your uploads

Offline transactions uploaded more than 90 days after the transaction date won't be imported into Google Ads, and this means it won't show up in your conversion statistics.

Uploading the same transaction multiple times

The same transaction won't be imported more than once. So, if you try to upload a transaction with the same combination of Customer information, “Conversion Name”, ”Conversion Value”, date, and time, Google Ads will ignore it to avoid duplicating conversions.

Viewing the status of your upload

Once your file is uploaded, you'll see a new entry for that file at the top of the table. Check the "Status" column to see if your job is complete. Importing the file may take several minutes. Once it's complete, you'll see a summary in the status column with the following updates:

Status Details
Previewing... If you click Upload and preview, you’ll see a preview of uploaded records. To start processing, click the Action dropdown menu and select Apply. (Keep in mind, there's no "undo" option after this point)
Processing... If you click Apply or Upload and apply, we'll start processing your uploaded records.
Ready to apply The file has been uploaded and you can click on Apply to start processing it.
Applied Your file has been processed successfully and conversions have been added to your account.
Failed Your file either had pre-processing issues or the number of matched records post-processing were low.

What to do if there are errors

Sometimes there might be a problem that prevents the entire file from processing. If this happens, follow these steps to troubleshoot your file.

  1. Click the links in the “Results” column to see more details about the upload status (including any errors with the upload). These links will be available for 3 days from the time of upload.
  2. Scan the new "Status" column to find all the rows that are marked with an error. The “Change” column will have the row number, which you can use to identify the corresponding row in your transaction file.
  3. Make the necessary corrections in your file, save the file, and then re-upload it. It's OK to upload the whole file again, including the previous changes (since Google Ads will ignore those rows that have already been imported). 

Viewing your imported conversions in Google Ads

It can take up to 48 hours for your conversions statistics to show up in your Google Ads account. Store sales (direct upload) conversions will be added to the "All conversions" column in your campaign reports. If you haven't already, you'll need to add this column to your reports:

  1. Sign in to your Google Ads account.
  2. Click Campaigns in the page menu on the left.
  3. Click the columns icon Columns above the statistics table.
  4. Select Modify columns from the drop-down.
  5. Under "Select metrics," click Conversions.
  6. Select All conv. by clicking the arrow icon.

The "All conversions" column will now appear in your reports. Store sales (direct upload) are available at the campaign, ad group, and keyword level and can be segmented by device. To see your store sales separately from the rest of your other conversions, such as website actions or calls, follow the instructions below to segment your columns. Before you begin, make sure you've already added the "All conversions" column to your reports.

  1. Sign in to your Google Ads account.
  2. Click Campaigns in the page menu on the left.
  3. Click the segment icon Segment. Select Conversions, then Conversion name.
  4. You'll now see rows listing data by each conversion action. Look for the row for your Store sales (direct upload) conversion action to see your store sales data.

To view store sales in a separate column without segmenting your campaigns, you can also add a custom column by following the instructions below. Before you begin, make sure you've already added the "All conversions" column to your reports.

  1. Sign in to your Google Ads account.
  2. Click Campaigns in the page menu on the left.
  3. Click the columns icon Columns, then select Modify columns.
  4. Select the "Custom Columns" category on the left.
    1. If you've never created a custom column, you'll see a dialog box.
    2. If you've already created a custom column, click + Column.
  5. In the "Column name" field, enter the name of your new column. This name will be displayed in the header.
  6. In the "Column description" field, enter a description of your column. The text you enter here will be displayed whenever you hover over the ? next to the column name.
  7. Click the Metric menu, and select Conversions, then select All conv. 
  8. In the list below, click Conversions, then select Conversion source. 
  9. Under "Selected segment," select the source Store sales (direct upload). 
  10. Click Save. The new custom column is automatically added to your columns.
  11. Click Apply to apply your column customization to your data table.

Note: If you have multiple Store sales (direct upload) conversion actions then you can segment by conversion names after you have created the custom column.

How to upload transactions for multiple accounts from a single file

To do this, all of the relevant accounts need to be linked from a shared manager (MCC) account. This manager account is where you will upload your files.

  • If cross-account conversion tracking is enabled: You'll need to create your imported conversion actions at the manager account level, and ensure all individual managed accounts use the manager as the "conversion account." All the accounts under the manger who use the manager as the “conversion account” are used for matching purposes when you upload transactions to the manager.
  • If some accounts are not using the manager account as the conversion account: You'll need to create your Store sales conversion actions for each of these accounts and import transactions separately to these accounts. Note that in this case Store sales conversions won't be deduped across the accounts that don’t use cross-account conversion tracking.

* Nguồn: Google