How to Use SHA-256 Hashing in Google Sheets for Secure Conversion Uploads to Google Ads

Are you looking to upload customer conversion data to Google Ads while ensuring all personal information remains secure and private? You’re in the right place! In this guide, I’ll show you how to hash your customers’ emails and phone numbers using SHA-256 in Google Sheets. We’ll also cover how to include essential identifiers like the Google Click ID (GCLID) to meet Google’s requirements for offline conversion tracking. This process keeps you compliant with Google’s policies and protects your customers’ sensitive data, all while enabling accurate conversion tracking in your ad campaigns.

Why Hash Your Data Before Uploading to Google Ads?

Privacy is paramount when uploading customer data for conversion tracking in Google Ads. Hashing transforms readable data into a fixed-length string of characters, making it nearly impossible to reverse-engineer. This ensures:

• Customer privacy is protected: Sensitive information remains confidential.
• Your uploads comply with Google’s policies: Google requires data to be hashed using SHA-256 for customer match lists.
• Data integrity is maintained without compromising security: You can track conversions accurately while adhering to privacy standards.

By hashing your data, you not only safeguard your customers’ information but also build trust and credibility with your audience.

Understanding Google’s Requirements for Offline Conversion Tracking

When importing offline conversions into Google Ads, it’s essential to include specific identifiers to match your conversions to the correct ad clicks. Google offers two primary methods:

• Google Click ID (GCLID): A unique identifier that tracks each click on your ad.
• Customer Data: Providing at least two out of three personal identifiers—email, phone number, or name and address.

Including the GCLID with your conversion data is the most precise way to match offline conversions to ad clicks. However, if the GCLID isn’t available, supplying two out of the three customer data points allows Google to associate conversions with the right ads.

What if You Don’t Have the GCLID?

If you can’t capture the GCLID, you can still upload offline conversions by providing hashed customer data. According to Google, you need to include at least two of the following three pieces of hashed information:

• Email address
• Phone number
• Name and address

This method allows Google to match your conversion data with user profiles, attributing conversions to the appropriate ads.

Getting Started: Setting Up Your Google Sheet

Follow these steps to set up your spreadsheet:

1. Open Google Sheets: Create a new sheet or use an existing one.
2. Organize your data with the following columns:
   • Column A: Invoice or Total Dollar Amount.
   • Column B: Type of Conversion (e.g., Purchase, Sign-Up).
   • Column C: Customer emails (Header: Email).
   • Column D: Customer phone numbers (Header: Phone).
3. Ensure the first row contains headers: This helps in identifying data fields clearly.

Here’s how your sheet should look:

Automating the Hashing Process with Google Apps Script

Instead of manually hashing your data, let’s automate it using Google Apps Script. This not only saves time but also reduces the risk of errors.

Access the Script Editor

In your Google Sheet, click on Extensions in the top menu and select Apps Script. This will open a new tab where you can input the hashing script.

Insert the Script

Delete any existing code in the script editor and paste the following script:

function copyAndHashToSheet2() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();

    // Get Sheet1
    const sheet1 = ss.getSheetByName("Sheet1");
    if (!sheet1) {
        Logger.log("Sheet1 does not exist.");
        return;
    }

    // Get data from columns A-D
    const range1 = sheet1.getRange(1, 1, sheet1.getLastRow(), 4); // Columns A-D
    const data = range1.getValues();

    // Create or get Sheet2
    let sheet2 = ss.getSheetByName("Sheet2");
    if (!sheet2) {
        sheet2 = ss.insertSheet("Sheet2");
    } else {
        sheet2.clear(); // Clear existing data in Sheet2
    }

    // Paste data into Sheet2
    const range2 = sheet2.getRange(1, 1, data.length, data[0].length);
    range2.setValues(data);

    // Hash columns C and D in Sheet2 (starting from row 2 to avoid headers)
    const rangeToHash = sheet2.getRange(2, 3, sheet2.getLastRow() - 1, 2); // Columns C-D
    const valuesToHash = rangeToHash.getValues();

    // Generate hashed values
    const hashedValues = valuesToHash.map(row => {
        const email = row[0] ? sha256(row[0].toString().trim().toLowerCase()) : "";
        const phone = row[1] ? sha256(row[1].toString().trim()) : "";
        return [email, phone];
    });

    // Replace the original values with hashed values in Sheet2
    rangeToHash.setValues(hashedValues);

    // Confirm completion
    Logger.log("Hashing complete and data replaced in Sheet2");
}

function sha256(data) {
    if (!data) return "";
    const hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, data);
    return hash.map(function (byte) {
        return ('0' + (byte & 0xFF).toString(16)).slice(-2);
    }).join("");
}

  

Note: I’ve included .toLowerCase() for the email hashing to comply with Google’s requirement of using lowercase emails.

Save and Run the Script

1. Click the Save icon and name your script (e.g., “Hashing Script”).
2. Authorize the script when prompted by clicking Run ▶️ for the first time.
3. After authorizing, click Run ▶️ again to execute the script.

If everything goes smoothly, you’ll see a confirmation message in the Logs stating that the hashing is complete.

Checking the Results

Once the script completes, navigate to “Sheet2” in your Google Sheet. Here’s what you’ll see:

• Columns A & B: Copied exactly from “Sheet1”.
• Columns C & D: Emails and phone numbers replaced with their hashed versions.

This automated process ensures consistency and accuracy across your entire dataset.

Adding Conversion Event and Timestamp

For Google Ads to track conversions accurately, you need to include the conversion name and timestamp. Add the following columns to your spreadsheet:

• Column E: Conversion Name (e.g., “Purchase” or “Sign-Up”).
• Column F: Conversion Time in the format YYYY-MM-DD HH:MM:SS.

Ensure that the Conversion Name matches exactly with the conversion action name in your Google Ads account. The Conversion Time should reflect when the conversion event occurred.

Preparing Your Data for Upload

Here’s how to finalize your data for upload:

1. Review your data: Check for any discrepancies or missing information in “Sheet2”.
2. Ensure proper formatting: Dates and times should be in the correct format, and there should be no extra spaces or characters.
3. Download as CSV: Click on File > Download > Comma Separated Values (.csv, current sheet) to save it as a CSV file.

Saving your file in CSV format ensures compatibility with Google Ads’ upload requirements.

Uploading to Google Ads

Follow these steps to upload your file:

1. Log in to your Google Ads account: Navigate to your dashboard.
2. Go to Conversions: Click on Tools & Settings > Conversions under the Measurement section.
3. Initiate the upload: Under Uploads, click the + button to upload your CSV file.
4. Map your data fields: Ensure that the fields in your CSV file match Google’s required fields.
5. Submit for processing: Review your data and click Apply to start the processing.

Google Ads will process your data and notify you once the upload is complete. You can then monitor the performance of your conversions within your campaigns.

Understanding Google’s Matching Process

Google uses the identifiers you provide to match your offline conversions to the correct ad interactions. Since we’re not using GCLID in this example, it’s crucial to supply at least two of the following hashed customer data points:

• Email address
• Phone number
• Name and address

By supplying these identifiers, Google can match your offline conversions to user interactions with your ads, attributing conversions to the appropriate campaigns and keywords.

Troubleshooting Common Issues

If you encounter any issues during the process, here are some common problems and solutions:

• Formatting Errors: Ensure all fields, like dates and times, are in the correct format (YYYY-MM-DD HH:MM:SS).
• Missing Data: Make sure required fields are not empty. Double-check for any blank cells in your spreadsheet.
• Column Headers: Double-check that headers match Google’s requirements exactly. Even slight variations can cause errors.
• Authorization Issues: If the script isn’t running, ensure you’ve granted the necessary permissions.
• Incorrect Hashing: Verify that emails are converted to lowercase before hashing to match Google’s matching process.

If problems persist, refer to Google Ads’ Offline Conversion Tracking Guide for detailed guidelines.

Best Practices for Data Handling

To maximize the effectiveness of your conversion tracking and maintain data security, consider these best practices:

• Regularly Update Your Data: Keep your customer data fresh to improve match rates.
• Secure Your Files: Store hashed data securely and limit access to authorized personnel only.
• Stay Compliant: Ensure you have the necessary permissions to use customer data for advertising purposes and comply with privacy laws like GDPR or CCPA.
• Monitor Your Campaigns: Regularly check the performance of your conversions to optimize your campaigns.
• Test with Small Batches: Before a full-scale upload, test the process with a small set of data to ensure everything works smoothly.

Adhering to these practices not only enhances your marketing efforts but also strengthens customer trust.

Wrapping It Up

By hashing your data, including essential conversion details, and preparing it properly, you’re ensuring:

• Customer trust through data privacy: Customers feel secure knowing their data is protected.
• Compliance with Google’s policies: Avoid potential account issues by following guidelines.
• Accurate conversion tracking for campaign optimization: Gain valuable insights to improve your marketing strategies.

With this guide, you can confidently upload secure customer data to Google Ads and make the most of your advertising campaigns. Happy hashing!

Additional Resources

For further reading and tools to help you along the way, check out these resources:

• Google Ads Offline Conversion Tracking Guide
• Uploading Offline Conversions without GCLID
• Google Apps Script Guide

Share Your Experience

Have you tried this method? I’d love to hear about your experience. Share your thoughts and any tips you might have in the comments below.