{"id":72371,"date":"2025-02-06T22:47:13","date_gmt":"2025-02-06T22:47:13","guid":{"rendered":"https:\/\/peraltafinancing.com\/analytics\/write-to-google-bigquery-from-a-gtm-server-container\/"},"modified":"2025-02-06T22:47:13","modified_gmt":"2025-02-06T22:47:13","slug":"write-to-google-bigquery-from-a-gtm-server-container","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=72371","title":{"rendered":"Write To Google BigQuery From A GTM Server Container"},"content":{"rendered":"


\n<\/p>\n

\n

Ever since it was released that server-side tagging in Google Tag Manager<\/a> would run on the Google Cloud Platform<\/strong> stack, my imagination has been running wild.<\/p>\n

By running on GCP, the potential for integrations with other<\/em> GCP components is limitless. The output to Cloud Logging<\/a> already introduces interesting pipeline opportunities, but now it gets even better.<\/p>\n

It\u2019s finally possible to write directly to Google BigQuery from a Client or tag template<\/a>!<\/strong><\/p>\n

\n

<\/p>\n

\"BigQuery<\/p>\n

<\/a><\/p>\n<\/div>\n

This means that instead of treating server-side tagging as a \u201csimple\u201d proxy<\/strong>, whose purpose is mainly to replace data streams that would otherwise connect a client directly with an endpoint, it\u2019s now possible to use a Server container as a data collection machine itself.<\/p>\n

In this article, I\u2019ll show you how to use the BigQuery API<\/strong> with a use case you might already be familiar with<\/a>. We\u2019re going to build a monitoring system<\/em> that writes tag execution data into a Google BigQuery table.<\/p>\n

This won\u2019t be the last I\u2019ll write about this integration \u2013\u00a0there are a lot<\/strong> of possibilities available now that a way to natively write data to a data warehouse from the Server container has become available.<\/p>\n

We\u2019ll start with a walkthrough<\/strong> before diving into the API specs<\/strong><\/a>.<\/p>\n


\n X<\/span><\/p>\n

\n
\n The Simmer Newsletter<\/strong>\n <\/p>\n

\n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n

<\/span><\/p>\n

Official documentation<\/h2>\n

You can find the official documentation for the BigQuery template API by following this link::<\/p>\n

Server-side Tagging APIS: BigQuery<\/a><\/p>\n

Full example: build a monitoring system<\/h2>\n

To get started, we\u2019ll build a Google Tag Manager monitor<\/a> for your server-side tags.<\/p>\n

\n

<\/p>\n

\"BigQuery<\/p>\n

<\/a><\/p>\n<\/div>\n

The schema<\/strong> is very simple. It\u2019s designed to collect information about the Client that caused the tag(s) to fire, whether they fired successfully (or failed), and how long the execution time of the tag was.<\/p>\n

You can use this to proactively monitor for issues in your Server container setup.<\/p>\n

Create the table<\/h3>\n

Before we get started, we need to create a table<\/strong> in BigQuery. I recommend using the same project as the one where your Server container is running. This way you don\u2019t have to worry about setting up authentication, as the Server container will already have full access to all cloud components running in the same project.<\/p>\n

First, Go to BigQuery<\/a>.<\/p>\n

Then, find your project ID in the navigation, and select the project.<\/p>\n

Finally, click CREATE DATASET<\/strong> to create a new dataset.<\/p>\n

\n

<\/p>\n

\"Create<\/p>\n

<\/a><\/p>\n<\/div>\n

Give dataset an ID, such as gtm_monitoring<\/code>, and set the data location, if you wish. Once ready, click the Create dataset<\/strong> button.<\/p>\n

Once the dataset is created, make sure it\u2019s selected in the navigation, and then click CREATE TABLE<\/strong>.<\/p>\n

\n

<\/p>\n

\"Create<\/p>\n

<\/a><\/p>\n<\/div>\n

The three settings you\u2019ll want to edit are Table name<\/strong>, Schema<\/strong>, and Partitioning<\/strong>.<\/p>\n

Set the Table name<\/strong> to whatever you want to name the table as, e.g. event_data<\/code>.<\/p>\n

Next, click +Add field<\/strong> to start creating the schema<\/strong>.<\/p>\n

The schema is essentially a blueprint for what type of data the table expects to be inserted into it. You need to define the fields<\/strong> (or columns) of the table, whether they are required<\/strong> or something else, and what the expected data type<\/strong> is.<\/p>\n

See the BigQuery documentation<\/a> for more details on what options you have at your disposal.<\/p>\n

This is the schema I\u2019ve chosen for the monitoring data:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n
Field name<\/th>\nType<\/th>\nMode<\/th>\n<\/tr>\n<\/thead>\n
event_name<\/code><\/td>\nSTRING<\/td>\nREQUIRED<\/td>\n<\/tr>\n
event_timestamp<\/code><\/td>\nINTEGER<\/td>\nREQUIRED<\/td>\n<\/tr>\n
client_name<\/code><\/td>\nSTRING<\/td>\nREQUIRED<\/td>\n<\/tr>\n
tag<\/code><\/td>\nRECORD<\/td>\nREPEATABLE<\/td>\n<\/tr>\n
tag.id<\/code><\/td>\nSTRING<\/td>\nNULLABLE<\/td>\n<\/tr>\n
tag.name<\/code><\/td>\nSTRING<\/td>\nNULLABLE<\/td>\n<\/tr>\n
tag.status<\/code><\/td>\nSTRING<\/td>\nNULLABLE<\/td>\n<\/tr>\n
tag.execution_time<\/code><\/td>\nINTEGER<\/td>\nNULLABLE<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

For Partitioning<\/strong>, I\u2019ve chosen to partition by day<\/strong>, which means that a table suffix is added to each row with the date of when the data was written to the table. This makes analysis much easier because you can scope your queries by day very easily.<\/p>\n

This is what the final configuration for the table looks like:<\/p>\n

\n

<\/p>\n

\"Table<\/p>\n

<\/a><\/p>\n<\/div>\n

The tag template<\/h3>\n

You can find the template soon in the Community Template Gallery. For now, you can also download the template.tpl<\/code> file by saving this page<\/a> with that filename.<\/p>\n

To load the template in the container, go to the Server container, select Templates<\/strong>, and then create a new tag template<\/strong>.<\/p>\n

\n

<\/p>\n

\"New<\/p>\n

<\/a><\/p>\n<\/div>\n

Next, in the overflow menu, choose Import<\/strong>.<\/p>\n

\n

<\/p>\n

\"Import<\/p>\n

<\/a><\/p>\n<\/div>\n

Locate the template.tpl<\/code> file you downloaded and import that using the system dialog.<\/p>\n

Click Save<\/strong> to save the template.<\/p>\n

To understand how the template works, read on\u2026<\/p>\n

Create the monitoring tag<\/h3>\n

Go to Tags<\/strong> in the Server container, and click NEW<\/strong> to create a new tag.<\/p>\n

\n

<\/p>\n

\"New<\/p>\n

<\/a><\/p>\n<\/div>\n

Now you need to configure<\/strong> the tag. There are three fields you need to set:<\/p>\n