Import Event-Data to BigQuery using Server Side Google Tagmanager | Part 2

In this guide I will continue to show, how to send data from any client or server to Server Side Google Tagmanager and handle the incoming data, so that it can be written to a BigQuery table immediately.
In the previous part of this post, I demonstrated how to create a generic REST-endpoint so receive data from any kind of source. Now let’s see how we can insert this data event by event into BigQuery to build an analytical database.

In general, there are two ways to insert your data into BigQuery. One would be to create a stringified object out of all the event data from the common event data model. This would require you to parse and manipulate the data afterwords. The obvious advantage is, that there is no data loss in case you need some data you previously weren’t aware of. The second possibility is to define the rows and values you want to populate in your target table. This way, the receiving table already has data to work with and there are no additional data processing steps necessary. In this case I will show, how to do the latter.

There are a few preparations one has take care of:

• You need a BigQuery dataset and a BigQuery table
• Your sGTM container needs permissions to write to BigQuery. If your sGTM Docker Image is hosted in the same project, that your BigQuery dataset is located in, this usually works without any configuration. In case it does not, you need to check the permissions of sGTMs service account (usually the default service account for AppEngine/CloudRun). For more info, see this: sGTM Deployment Docs
• The BigQuery table needs to have a matching schema to the tag-setup
• This includes matching data types.
• Optionally you can also add a Slack Webhook URL to automatically monitor API-Errors when trying to insert data. For more info, check out the Slack docs on this.

After taking care of the above, you can start by importing the following Tag template: Link to template file

//import APIs
const BigQuery = require("BigQuery");
const getAllEventData = require("getAllEventData");
const log = require("logToConsole");
const JSON = require("JSON");
const getTimestampMillis = require("getTimestampMillis");
const Object = require("Object");
const getEventData = require('getEventData');
const sendHttpRequest = require('sendHttpRequest');
const getCookieValues = require('getCookieValues');

//for slack messaging
const slackPostHeaders = {'Content-Type': 'application/json'};
const slackUrl = data.slackWebHookUrl;

const connection = getConnection(data.tableId);

let writeData = {};

// add timestamp
if (data.addTimestamp) {
  writeData[data.timestampFieldName] = getTimestampMillis();
}

//custom data
if (data.customData && data.customData.length) {
  for (let i = 0; i < data.customData.length; i += 1) {
    const elem = data.customData[i];
    if (elem.fieldValue || elem.fieldValue === 0) {
      writeData[elem.fieldName] = elem.fieldValue;
    } else {
      Object.delete(writeData, elem.fieldName);
    }
  }
}

//custom data nested fields
if (data.nestedFields && data.nestedFields.length) {
  for (let i = 0; i < data.nestedFields.length; i += 1) {
    const nestEl = data.nestedFields[i];
    const nestStr = nestEl.nestedFieldValue || "[{}]";
    const nestObj = JSON.parse(nestStr);
    writeData[nestEl.nestedFieldName] = nestObj;
  }
}

const rows = [writeData];

const options = {
  ignoreUnknownValues: true,
  skipInvalidRows: false,
};

//streaming insert
BigQuery.insert(connection, rows, options, data.gtmOnSuccess, (err) => {
  log("BigQuery insert error: ", JSON.stringify(err));
  log(err);
  if (data.sendErrorToSlack && data.slackWebHookUrl) {
    const postBody = '{"text": "BigQuery insert error: '+ data.slackMessage +'"}';
    sendHttpRequest(slackUrl, (statusCode, headers, body) => {
    }, {headers: slackPostHeaders, method: 'POST', timeout: 3000}, postBody);
  }
  data.gtmOnFailure();
});

function getConnection(tableId) {
  return {
    projectId: tableId.split(".")[0],
    datasetId: tableId.split(".")[1],
    tableId: tableId.split(".")[2],
  };
}

After importing the template and checking the required tag-permissions, you can start configuring the tag. Set the destination table and the data you want to insert. There is also an option to insert data to nested fields in BigQuery (similar to JSON and Array type). To do so, the columns value needs to be a stringified JavaScript Object. I recommend adding a timestamp as well (big thanks to TRKKN for adding this to their template).

And optionally you can add your Slack Webhook URL as well as an individual message.

And that’s it! If something does not work, you can see it in sGTM’s debug console and/or your Slack messages.

Have fun with setting it up and testing it!