How to merge HubSpot and Google Ads data with ChatGPT?

When I joined CometChat as a Growth Marketing Manager, I was tasked with fixing the foundational mistakes and build Google Ads into a reliable pipeline engine. However, when i started working on it, i faced major issues when trying to build reports from Google ads and HubSpot in a single place. There was no easy way to bring both of those data together and even if there were, the solutions were paid. Being a very compulsive saver, I sat down thinking how I can save money on this?

There should be a little hard, yet cost effective way of achieving this, right? And there was! Taking help from ChatGPT i was able combine both Google Ads and HubSpot data into a single Google sheet which i then used to build reports and dashboards on LookerStudio. Here's the extensive process that I followed, along with the code snippets that i used.

Our requirements were simple, but a bit hard to execute:

1. Populate google ads performance data in one sheet and leads data from HubSpot in another sheet

2. Get the count of leads and count of business emails for a specific campaign on a specific date by checking the HubSpot data and adding the values in the campaign performance sheet for each campaign

3. Connect the raw data sheet with looker studio and build reports.

Step 1: Connect HubSpot data with Google Sheets

1. Create a Google sheet with the fields that you want to build reports on.

2. Setup a workflow in HubSpot with the required filters to send data to a Google Sheets file. Example: Original source is Paid search

3. Match the column names with the fields on HubSpot and activate the workflow.

4. Now the contacts matching the filter get added to a dedicated sheet (we'll call it "HubSpot paid leads daily") within the main raw data spreadsheet.

Video on workflows:

Note: The HubSpot to Sheets workflow is only available with any of the following subscriptions: Marketing Hub Professional, Enterprise Sales Hub Professional, Enterprise Service Hub Professional, Enterprise Operations Hub Professional, Enterprise

Step 2: Fix Date Formatting

Since the workflow sending data from HubSpot sends the date values in unix timestamp format, we will have to fix those values inside of Google sheets. But making any kind of changes to the original sheet where the workflow is configured on can break the workflow in ways you can't imagine :P.

So in this step, we will create 2 scripts,

1. To copy data form the workflow configured sheet to another sheet

2. To fix the date value in the new sheet

2.1 - Data Copying from "Hubspot paid leads daily" to "Hubspot paid leads daily fixed"

The script identifies two specific sheets within the spreadsheet:

'Hubspot paid leads daily' (source)

'Hubspot paid leads daily fixed' (target) - You will have to create this sheet.

The cell 'K2' in the 'Hubspot paid leads daily fixed' sheet is checked to determine the last row that was updated during the previous script run. Set this to '1' initially.

The script calculates the number of new rows to copy by subtracting the last updated row number from the total rows present in the 'Hubspot paid leads daily' sheet.

If there are new rows to be copied:

1. The script fetches the data from the 'Hubspot paid leads daily' sheet, starting from the row immediately after the last updated row.

2. It copies columns A to F (depending on how many columns you have) from the source sheet. This range can be adjusted as needed.

3. The copied data is appended to the 'Hubspot paid leads daily fixed' sheet, starting from its last row.

4. Finally, the 'K2' cell in 'Hubspot paid leads daily fixed' is updated with the last row number from the 'Hubspot paid leads daily' sheet, ensuring that during the next script run, only new data will be considered.

Script used:

function copyValuesFromRow() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    // Specify your source and target sheet names
    var sourceSheetName = "Hubspot paid leads daily"; // Change to your source sheet name
    var targetSheetName = "Hubspot paid leads daily fixed"; // Change to your target sheet name
    
    var sourceSheet = ss.getSheetByName(sourceSheetName);
    var targetSheet = ss.getSheetByName(targetSheetName);

    // Specify the range of columns you want to copy. For example: "A:C" will copy columns A, B, and C.
    var columnsToCopy = "A:F"; // Change this to your desired range

    // Get the last updated row from a specific cell in the target sheet, for example, "Z1".
    // If you want to use another cell, change "Z1" to your desired cell address.
    var lastUpdatedRowCell = targetSheet.getRange("K2");
    var lastUpdatedRow = lastUpdatedRowCell.getValue() || 1;

    // Calculate how many rows to copy
    var rowsToCopy = sourceSheet.getLastRow() - lastUpdatedRow;

    if (rowsToCopy > 0) {
        // Get the values from the source sheet starting from the last updated row
        var valuesToCopy = sourceSheet.getRange(lastUpdatedRow + 1, 1, rowsToCopy, sourceSheet.getLastColumn()).getValues();

        // Write the values to the target sheet
        targetSheet.getRange(targetSheet.getLastRow() + 1, 1, rowsToCopy, sourceSheet.getLastColumn()).setValues(valuesToCopy);

        // Update the last updated row cell
        lastUpdatedRowCell.setValue(sourceSheet.getLastRow());
    }
}

If you need help with implementing or customizing this setup, please feel free to reach out to me.

2.2 - Unix Timestamp Conversion Script

The script retrieves the last row that was updated during the previous run from the cell 'K1' in the 'Hubspot paid leads daily fixed' sheet.

It fetches the Unix timestamps from the 'A' column, starting from the row immediately after the last updated row.

The script also gathers data from the 'G' column of the 'Hubspot paid leads daily fixed' sheet to determine which dates have already been converted.

For each Unix timestamp:

1. It checks if the timestamp exists and if the corresponding date hasn't been converted yet.

2. The Unix timestamp is converted into a GMT/UTC formatted string.

3. The year, month, day, hours, and minutes are extracted from this string.

4. A new Date object is constructed using the extracted components.

5. To adjust for the GMT+5:30 time zone, the script adds 5 hours and 30 minutes to this Date object.

6. The final adjusted date is then formatted as 'DD/MM/YYYY'.

7. The converted dates are written back to the 'G' column in the 'Hubspot paid leads daily fixed' sheet, ensuring they align with their corresponding Unix timestamps.

8. Finally, the script updates the 'K1' cell in the 'Hubspot paid leads daily fixed' sheet with the new last updated row number. This ensures that the next run of the script will only consider new timestamps.

Script used:

function convertTimestampsFinal() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hubspot paid leads daily fixed");
    
    // Get the last updated row from cell K1
    var lastUpdatedRow = sheet.getRange("K1").getValue() || 1;

    // Get the range of the timestamps starting from the row after the last updated one
    var timestamps = sheet.getRange("A" + (lastUpdatedRow + 1) + ":A" + sheet.getLastRow()).getValues();
    
    // Get the range of dates in column G to check for existing converted dates
    var existingDates = sheet.getRange("G" + (lastUpdatedRow + 1) + ":G" + sheet.getLastRow()).getValues();

    for (var i = 0; i < timestamps.length; i++) {
        if (timestamps[i][0] && !existingDates[i][0]) {
            var timestampValue = parseInt(parseFloat(timestamps[i][0]));

            // Convert to a string in GMT/UTC format
            var gmtString = new Date(timestampValue).toISOString();
            var year = parseInt(gmtString.substring(0, 4));
            var month = parseInt(gmtString.substring(5, 7)) - 1; // Months are 0-indexed
            var day = parseInt(gmtString.substring(8, 10));
            var hours = parseInt(gmtString.substring(11, 13));
            var minutes = parseInt(gmtString.substring(14, 16));

            // Construct a new Date object
            var date = new Date(year, month, day, hours, minutes);

            // Adjust for GMT+5:30
            date.setHours(date.getHours() + 5);
            date.setMinutes(date.getMinutes() + 30);

            // Extract the date components
            var dayFinal = date.getDate().toString().padStart(2, '0');
            var monthFinal = (date.getMonth() + 1).toString().padStart(2, '0');
            var yearFinal = date.getFullYear();
            var formattedDate = dayFinal + '/' + monthFinal + '/' + yearFinal;

            existingDates[i][0] = formattedDate;
        }
    }
    
    // Write the converted dates back to column G
    sheet.getRange("G" + (lastUpdatedRow + 1) + ":G" + (lastUpdatedRow + timestamps.length)).setValues(existingDates);
    
    // Update cell K1 with the last row that was updated
    sheet.getRange("K1").setValue(lastUpdatedRow + timestamps.length);
}

Feel free to reach out to me in case of any doubts.

Step 3: Creating a Script in Google Ads to Send Performance Data to Google Sheets

The script calculates the date for "yesterday" to use as both the start and end date. This means the script is designed to fetch only the previous day's data from Google Ads. To bulk populate data for a period of time change the following lines in the script to manually enter the dates.

From:

 // Calculate the date for yesterday
  var endDate = new Date();
  endDate.setDate(endDate.getDate() - 1); // Set endDate to yesterday
  var startDate = new Date();
  startDate.setDate(startDate.getDate() - 1); // Set startDate to yesterday

To:

 // Calculate the date for yesterday
  var endDate = new Date(YYYY, MM, DD);
  var startDate = new Date(YYYY, MM, DD);

The calculated dates are then formatted to 'DD/MM/YYYY' using the time zone of the AdWords account, ensuring that data retrieval aligns with the account's set timezone.

The script requests a report from Google Ads with the following fields. You can add or remove the fields as per your requirements:

• Date

• CampaignName

• Clicks

• Impressions

• Ctr (Click Through Rate)

• Cost

• AbsoluteTopImpressionPercentage

• TopImpressionPercentage

• ConversionRate

• Conversions

• AverageCpc (Average Cost Per Click)

• CostPerConversion

• SearchImpressionShare

The data from the report is filtered to only include metrics for "yesterday" and specifically for campaigns that had a impressions greater than 0.

All this is done by the script everyday, between 12 a.m. and 1 a.m as scheduled in the Google Ads script library.

Script used:

function main() {
  // Create or open a spreadsheet
  const spreadsheetUrl = 'link to sheet'; // Replace with your Google Sheet URL
  const spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  const sheet = spreadsheet.getSheetByName('Google ads daily'); // Replace with your sheet name

  // Calculate the date for yesterday
  var endDate = new Date();
  endDate.setDate(endDate.getDate() - 1); // Set endDate to yesterday
  var startDate = new Date();
  startDate.setDate(startDate.getDate() - 1); // Set startDate to yesterday
  
  var formattedStartDate = Utilities.formatDate(startDate, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');
  var formattedEndDate = Utilities.formatDate(endDate, AdWordsApp.currentAccount().getTimeZone(), 'yyyyMMdd');

  // Fetch the report with the Date column at the beginning
  const report = AdsApp.report(
      'SELECT Date, CampaignName, Clicks, Impressions, Ctr, Cost, AbsoluteTopImpressionPercentage, TopImpressionPercentage, ConversionRate, Conversions, AverageCpc, CostPerConversion, SearchImpressionShare ' +
      'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
      'WHERE Date >= "' + formattedStartDate + '" AND Date <= "' + formattedEndDate + '" AND Impressions > 0');

  // Get the rows from the report
  const rows = report.rows();
  
  // Iterate over the rows and append them to the sheet
  while (rows.hasNext()) {
    const row = rows.next();
    const rowData = [
      row["Date"], row["CampaignName"], row["Clicks"], row["Impressions"], row["Ctr"], row["Cost"], 
      row["AbsoluteTopImpressionPercentage"], row["TopImpressionPercentage"], row["ConversionRate"], 
      row["Conversions"], row["AverageCpc"], row["CostPerConversion"], row["SearchImpressionShare"]
    ];
    sheet.appendRow(rowData);
    
    // Format the date in the newly appended row
    const lastRow = sheet.getLastRow();
    sheet.getRange(lastRow, 1).setNumberFormat("DD/MM/YYYY");
  }
}

Step 4: Update Leads and Business Emails Data in the Google ads daily sheet

The script specifically opens two sheets within this spreadsheet: 'Google ads daily' (where our Google Ads data is stored) and 'Hubspot paid leads daily fixed' (where our HubSpot data, after Unix Timestamp conversion, is stored).

The script reads the column titles from both sheets. This allows it to find specific columns like 'Date', 'Campaign Name', 'Leads', and 'Business Email' among others.

The script checks a special cell (S2) in the 'Google ads daily' sheet to find out which was the last row it worked on in a previous run. This should be set to "1", when you start fresh.

For every new row in the 'Google ads daily' sheet (rows it hasn't processed before):

• It identifies the specific date and the campaign name.

• It sets up two counters: one for counting leads and another for counting business emails.

• Then, the script goes through each row in the 'Hubspot paid leads daily fixed' sheet to look for matching data:

• If it finds a row with the same date and campaign name:

• The leads counter increases by one.

• If the email type for that row is a 'Business Email', the business email counter increases as well.

• After checking all rows in the 'Hubspot paid leads daily fixed' sheet for a specific date and campaign combination from 'Google ads daily', the script updates the leads and business email counts in the 'Google ads daily' sheet.

Once all new rows in the 'Google ads daily' sheet have been processed, the script updates its memory (cell S2) about the last row it worked on.

Script used:

function updateGoogleAdsData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Access sheets
  var googleAdsSheet = ss.getSheetByName('Google ads daily');
  var hubspotSheet = ss.getSheetByName('Hubspot paid leads daily fixed');
  
  // Get data
  var googleAdsData = googleAdsSheet.getDataRange().getValues();
  var hubspotData = hubspotSheet.getDataRange().getValues();
  
  // Headers
  var googleAdsHeaders = googleAdsData[0];
  var hubspotHeaders = hubspotData[0];
  
  // Find necessary columns in each sheet
  var dateColGoogle = googleAdsHeaders.indexOf('Date');
  var campaignColGoogle = googleAdsHeaders.indexOf('CampaignName');
  var leadsColGoogle = googleAdsHeaders.indexOf('Leads');
  var businessEmailColGoogle = googleAdsHeaders.indexOf('Business Email');
  
  var dateColHubspot = hubspotHeaders.indexOf('Converted date');
  var campaignColHubspot = hubspotHeaders.indexOf('Marketing Campaign');
  var emailTypeColHubspot = hubspotHeaders.indexOf('Email Type');
  
  // Get the last updated row from cell S2
  var lastUpdatedRow = googleAdsSheet.getRange('S2').getValue() || 1;
  
  // Iterate over google ads data (skip header and previously processed rows)
  for (var i = lastUpdatedRow; i < googleAdsData.length; i++) {
    var dateGoogle = new Date(googleAdsData[i][dateColGoogle]);
    var campaignGoogle = googleAdsData[i][campaignColGoogle].toLowerCase();
    
    var leadsCount = 0;
    var businessEmailCount = 0;
    
    // Iterate over hubspot data (skip header)
    for (var j = 1; j < hubspotData.length; j++) {
      var dateHubspot = new Date(hubspotData[j][dateColHubspot]);
      var campaignHubspot = hubspotData[j][campaignColHubspot];
      var emailType = hubspotData[j][emailTypeColHubspot];
      
      if (dateGoogle.getTime() === dateHubspot.getTime() && campaignGoogle === campaignHubspot) {
        leadsCount++;
        if (emailType === 'Business Email') {
          businessEmailCount++;
        }
      }
    }
    
    // Update google ads data
    googleAdsData[i][leadsColGoogle] = leadsCount;
    googleAdsData[i][businessEmailColGoogle] = businessEmailCount;
  }
  
  // Write updated data back to google ads sheet
  googleAdsSheet.getRange(1, 1, googleAdsData.length, googleAdsHeaders.length).setValues(googleAdsData);
  
  // Update the last processed row in cell P1
  googleAdsSheet.getRange('S2').setValue(googleAdsData.length);
}

Finally in the Appscripts manager, set a time based trigger for the following scripts:

Step 2.1: 1AM to 2AM (To first copy the values into the 'Hubspot paid leads daily fixed' sheet)

Step 2.2: 2AM to 3AM (To then fix the timestamp values so that we can take the count of leads correctly)

Step 4: 3AM to 4AM (To then count the number of leads and Business Emails from the 'Hubspot paid leads daily fixed' into the 'Google ads daily' sheet.

From here on, you can connect the Google ads daily sheet with Looker studio and build reports as you want.

Have any specific requirements that you want to build for? Feel free to reach out to me.