Boost Your Google Ads with Offline Conversions: A Smart Fix for Quality Lead Targeting

Picture this: a potential customer hits your site, zips through a quick lead form, and then their info goes off to get processed in your CRM. Sounds simple, right? But here's the snag — what if you want to focus your ad on the qualified leads, but your CRM is locked down tight (the security is tight and your team is busy), so exporting data isn’t an option?

Well, there’s a neat workaround that could just be the ticket. You’ll need to get one thing squared away with your client first: make sure your CRM tech team can send out a web-hook when a conversion happens.

Here’s the lowdown on how to set it all up:

Catch that Web-hook: First up, set up a receiver using SGTM with a Client Custom Template. This will grab and store whatever data the CRM sends over into Google BigQuery (GBQ) just as it comes.
Tidy up the Data: Create a view in GBQ that shapes this data into the format Google Ads need.
Link to Google Ads: Finally, add this as an offline conversion in Google Ads, pointing it to your GBQ and the view you just crafted.

Create SGTM receiver

You have to create your own Custom Client Template in SGTM because it is not possible to import it

Click "New" in the Client Templates

Define two fields:
requestPath - is where you webhook receiver client would respond on requests
bigQueryPath - path to GBQ table you want to store the conversions

Paste the code:

const claimRequest = require('claimRequest');
const getRequestPath = require('getRequestPath');
const requestPath = getRequestPath();
const logToConsole = require('logToConsole');
const setResponseStatus = require('setResponseStatus');
const setResponseHeader = require('setResponseHeader');
const setResponseBody = require('setResponseBody');

const returnResponse = require('returnResponse');

const getRequestQueryParameters = require('getRequestQueryParameters');
const BigQuery = require('BigQuery');
const makeInteger = require('makeInteger');
const makeString = require('makeString');
const makeNumber = require('makeNumber');

const JSON = require('JSON');
const getType = require('getType');
const getTimestampMillis = require('getTimestampMillis');

// convert identifiers to strings
const convertToString = (fieldValue) => {
  if (getType(fieldValue) === 'null' || getType(fieldValue) === 'undefined') {
    return;
  }
  return makeString(fieldValue);
};


logToConsole(requestPath,  data.requestPath, getRequestQueryParameters());
if (requestPath === data.requestPath) {
  claimRequest();  
  const params = getRequestQueryParameters();
  const row = {
    'timestamp': getTimestampMillis(),
    'gclid': convertToString(params.gclid),
    'user_id': convertToString(params.user_id),
    'email': convertToString(params.email),
    'value': makeNumber(params.value),
    'currency': convertToString(params.currency),
    'type': convertToString(params.type),
    'lead_id': convertToString(params.lead_id),
    'transaction_id': convertToString(params.order_id)
  };

  BigQuery.insert(
    {
      projectId: data.bigQueryPath.split(".")[0],
      datasetId: data.bigQueryPath.split(".")[1],
      tableId: data.bigQueryPath.split(".")[2]
    }, 
    [row], 
    {ignoreUnknownValues: true}, 
    (res)=>{ 
      setResponseStatus(200); returnResponse(); 
    }, 
    (err) => {
      logToConsole("BigQuery insert error: ", JSON.stringify(err), row);
      setResponseStatus(500); returnResponse(); 
    });
 
}

Create a new Client based on the template you just created and set values for requestPath and bigQueryPath

Create a table and view in GBQ

Perform the following SQL query in your BigQuery, change YOUR-PROJECT-ID.SGTM.conversions accordingly

CREATE TABLE `YOUR-PROJECT-ID.SGTM.conversions`
(
  timestamp INT64,
  gclid STRING,
  user_id STRING,
  email STRING,
  value FLOAT64,
  currency STRING,
  transaction_id STRING,
  type STRING
)
PARTITION BY DATE(_PARTITIONTIME)
OPTIONS(
  require_partition_filter=true
);

The the view which will format the raw data

SELECT
  CAST(timestamp/1000 as INT64) as time_seconds,
  TO_HEX(SHA256(email)) as email_sha256_hex,
  *
FROM `YOU-PROJECT-ID.SGTM.conversions`
WHERE
  (TIMESTAMP_TRUNC(_PARTITIONTIME, DAY) >= TIMESTAMP_TRUNC(
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) ,
    DAY
    )
  OR _PARTITIONTIME IS NULL
  ) and type="YOU_CONVERION_TYPE"
AND email IS NOT NULL
ORDER BY timestamp DESC

Create offline conversion in Google Ads

Choose project, dataset and view you just created

Configure the mapping between fields