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:
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
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