GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration

GA4 Events & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration

Google Analytics 4 (GA4) is a Event based tool, and with Event based analytics tools, a measurement plan/documentation is important. This because you need to document which Events & Parameters are used, and what they are measuring. Another important part is to document which Parameters are allocated to the different Events.

There are many ways to do this documentation, but unless you are using a “special tool” for this, the documentation can be time consuming, and the possibility of errors in the documentation is a potential problem.

In addition, this is just documentation, it has no real influence of the GA4 Events & Parameters that are being collected.

I decided to see if could do something about this, so I made a Google Sheet powered with Google Apps Script with Google Cloud Firestore integration. Firestore again can be integrated with Server-side Google Tag Manager (SGTM), which means the GA4 documentation in Google Sheet is now integrated with the data collection. Here are some possible options:

  • Flag GA4 Events that are being collected and not documented.
  • Route GA4 Events to a different GA4 Property if Events aren’t documented.
  • Block GA4 Events that aren’t documented.

These examples aren’t randomly selected. This functionality can be found in some other Event based analytics tools, and also in third party solutions that handles Event documentation.

Getting started with the Google Sheet

GA4 Events & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration
  • First, open the Google Sheet, and make a copy.
  • Wait for the Parameter & Firestore Menu to appear, then click on Add/Edit Parameters. This will start the activation of the Apps Script called GA4 Event & Parameter functions.

The Google Sheet explained

The Google Sheet contains the following Sheets:

  • Events
  • Parameters
  • Item Parameters
  • Conversion Events
    • This Sheet is listing Events from the Events Sheet where the Event is marked as a Conversion Event.
  • User Properties
    • This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to User.
  • Event Parameters
    • This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to Event.
  • Session Parameters
    • This Sheet is listing Parameters from the Parameters Sheet where the Scope of the Parameter is set to Session.
    • This Sheet is hidden since Session Parameters don’t exist. Just trying to predict the future.
  • Settings
  • Helper
    • This Sheet is hidden, and has some helper functionality

Events Sheet

Here you document your Events. The most common GA4 Event Names are prefilled and described. The different columns in the Sheet are described below.

Column NameDescription
Event GroupGrouping Events together with a event_group parameter is a good thing to do. It can make it easier to understand your Events, and do queries in BigQuery. The Event Group parameter will also be used in the Firestore to Server-side GTM integration. How to group the Events is up to you.
Event NameType the Event Name here. If the Event Name is a duplicate, the cell will be highlighted with a red color.
If the Event Name is written in a format that isn’t snake_case, input will be rejected. Enforcing snake_case can be turned off in the Settings Sheet.
MethodSelect from a drop-down list how this Event is generated. This is just for your own documentation.
Event TypeSelect from a drop-down list what kind of Event this is. This is just for your own documentation.
Conversion EventConversion Event documentation.
= Conversion Event.
Event DescriptionWrite a description for the Event here.
ParametersParameters from the Parameters Sheet are added here. Do not Edit cells manually, since this may break some functionality. Only add Parameters to a cell by using the Apps Script application, or copy + paste from another Parameter cell.
Item ParametersParameters from the Item Parameters Sheet are added here. Do not Edit cells manually, since this may break some functionality. Only add Parameters to a cell by using the Apps Script application, or copy + paste from another Item Parameter cell. If Item Parameters isn’t relevant for your documentation, just hide the Item Parameters column.
CommentWrite a comment/description about your parameters here.
Autofilled TimeIf either Event Name, Parameters or Item Parameters are changed/added, this column will be autofilled with the time the change was made.
PlatformDocumentation of which platform this Event should be generated on. If you have more platforms, just add more columns.
Info related to Google Analytics & GTMDocumentation related to GA (Universal Analytics) and Google Tag Manager. Ex. what data in GA is similar to this Event, or which Tag in GTM is sending this Event.

Adding new Events

  • The quickest method for adding a new Event, is to copy a similar Event, and edit the information afterwords.

Editing/adding Parameters to a Parameter Cell

This is the most timesaving functionality in this Google Sheet.

  • Select the Parameter Cell that you want to Edit.
    • If the Apps Script application isn’t active, select Add/Edit Parameters from the Parameter & Firestore Menu.
    • If the Apps Script application is active, click on the Get Parameters from Cell button.
  • Select relevant Parameters in the Apps Script application, click the Fill Current Cell button.
    • Searching for parameters is also possible.
Google Sheet Select Parameter Apps Script Application

Parameters & Item Parameters Sheets

The most common Parameters and Item Parameters are prefilled. The different columns are described below. If you need more columns in your documentation, just add the columns. It will not break any functionality.

If Item Parameters aren’t relevant for your documentation, just hide the Item Parameters Sheet.

Column NameDescription
Group NameThis is just for internal grouping of Parameters in the Sheet. How to group Parameters is up to you.
Parameter NameThe Parameter Name
ParameterType the Parameter here. If the Parameter is a duplicate, the cell will be highlighted with a red color.
If the Parameter is written in a format that isn’t snake_case, input will be rejected. This can be turned off in the Settings Tab.
ScopeCan be selected from dropdown. Available choises: Event, User & Session (session doesn’t exist yet in GA4, so I try to predict the future here). Scope isn’t relevant in the Item Parameters Tab.
TypeSelect Parameter Type from dropdown (Event Standard Parameter, Custom Dimension etc.)
FormatSelect Format from dropdown (Array, Boolean, Number or String)
Info related to Google Analytics & GTMDocumentation related to GA (Universal Analytics) and Google Tag Manager. Ex. what data in GA is similar to this parameter, or what is the Variable in GTM called.

Settings Sheet

Settings for the Google Sheet and the Firestore integration can be set here. Firestore settings is explained in the Firestore section.

There is only one setting for the Google Sheet, and that is to enforce snake_case for Event & Parameters.

Syncing GA4 Events & Parameters to Firestore

Google Cloud Firestore is a NoSQL document database built for automatic scaling, high performance, and ease of application development.

To access Firestore with Apps Script, the Firestore library has been installed in this Google Sheet.

Firestore free quota per day is 50,000 document Reads, 20,000 document Writes and 20,000 document Deletes. See Firestore pricing for more information.

Syncing GA4 Events & Parameters to Firestore works like this:

  • If the Event Name isn’t in Firestore, the Event Name & Parameters will be added.
  • If the Event Name is in Firestore & Google Sheet, the Event Name & Parameters will be updated.
  • If the Event Name is in Firestore, but not Google Sheet, the Event Name & Parameters will be deleted from Firestore.

The following data is synchronized with Firestore:

ParameterDescription
change_statusadded or updated (or deleted if you activate logging of data synchronized to Firestore)
date_editedDate event_name, parameters or items was edited. Date comes from Autofilled Time column.
event_groupEvent Group from Events Sheet.
event_nameEvent Name from Events Sheet.
parametersArray. Parameters from Events Sheet.
itemsArray. Item Parameters from Events Sheet.
GA4 Events & Parameters stored in Firestore
Firestore Event Data

Google Cloud & Firestore Setup

I recommend that you create a new Google Cloud Project for the Firestore setup.

Firestore Setup

Follow the steps below to set up Firestore.

  1. Select a Cloud Firestore mode
    1. Select Native Mode
  2. Choose where to store your data
    1. Create Database

Create a Google Service Account

To connect this Google Sheet to Firestore, the easiest way is to create a Google Service Account with read/write access to your Firestore database. Giving a service account access to your datastore is like giving access to a user’s account, but this account is strictly used by your script, not by a person.

  • Open the Google Service Accounts page.
  • Select the Firestore project, and then click “Create Service Account“.
  • For your service account’s role, choose Datastore > Cloud Datastore Owner.
  • Check the “Furnish a new private key” box and select JSON as your key type.
  • When you press “Create“, your browser will download a .json file with your private key.
  • Save the .json file locally.

Firestore Setup in Google Sheet

  • Go to the Settings Sheet and Firestore settings.
  • Insert the following values from the .json file:
    • Client Email -> client_email from the .json file
    • Project ID -> project_id from the .json file
    • Private Key -> private_key from the .json file
  • First Collection -> Name of first collection. Suggested name event_data.
  • Date Format -> Date format stored in Firestore. Dropdown menu.
    • Date stored comes from Autofilled Time in the Event Sheet.
  • Log data submitted to Firestore -> Logs all data synched to Firestore in a different collection (event_data_history).
    • Yes/No dropdown menu.

Firestore Settings will be stored as Apps Script Script Properties, since I think it’s better to reference a Script Property in the Apps Script, than a Google Sheet Cell or Named Range.

Server-side Google Tag Manager Setup

The last part of the puzzle is to either flag, block or route undocumented Events in Server-side GTM. In the example setup here, I’m going to flag undocumented Events.

Firestore Lookup Variable

To do that we are going to use a Firestore Lookup Variable. For detailed information about this variable, see the Enrich Server-side Data With Cloud Firestore by Simo Ahava. Do especially read the Override Project ID part, because most likely Server-side GTM and Firestore are in 2 different Google Cloud projects.

SettingsValueDescription
Lookup typeDocument pathHelp text from the Firestore Lookup Variable: “Look up a document by specifying its components (collection, document, subcollection)“.
Document pathevent_data/{{Event Name}}If you have chosen a different First Collection in the Firestore Setting in Google Sheet, replace event_data with your choice.
Key Pathevent_groupWill return Event Group from the Google Sheet.
Override Project IDyour-firestore-project-idIf Firestore lives in a different Google Cloud Project, you have to override Project ID.
Convert undefinedmissingUndocumented Events will be flagged as missing Event Group. See settings in the Firestore Lookup image below.
Server-side GTM Firestore Lookup Variable
Firestore Lookup Variable

Now edit your GA4 Tag in SGTM. In the Parameters to Add / Edit section, add event_group as a parameter, and your Firestore Lookup Variable as Value.

Then add Event Group as a Parameter to the GA4 Tag, and the Firestore Lookup Variable as Value.

GA4 Tag in Server-side GTM with "event_group" & Firestore Lookup Variable setup
GA4 Tag in Server-side GTM with “event_group” & Firestore Lookup Variable setup

The image from the GA4 Explore report shows the result of this setup. The Events scroll and clipboard haven’t been documented, and are flagged as missing.

Do notice that session_start and first_visit has a (not set) Event Group. This is because these Events don’t pass throug Server-side GTM.

GA4 Event Group example
GA4 Explore report showing Event Group & Event Name

Some final words

In this blog post I have shared how Event and Parameter documentation in Google Sheet can be integrated with Server-side GTM data collection, by synchronizing the documentation to Firestore. There could be several other use cases for a Google Sheet + Firestore + SGTM integration, ex. rewriting utm_id campaign tagging to regular campaign tagging instead of uploading the data to Google Analytics etc.

Note that this (for the moment) only works with GA4 Web Data Streams. Apps (Firebase) can’t send data to Server-side GTM. I hope Google will fix this.

Apps Script Privacy Policy & Terms of Service

To be able to read and edit data in the Google Sheet, showing the Apps Script application, and syncing data to Firestore, the Apps Script needs the following authorizations:

  • See, edit, create, and delete all your Google Sheets spreadsheets
  • Display and run third-party web content in prompts and sidebars inside Google applications
  • Connect to an external service
  • See, edit, share, and permanently delete all the calendars you can access using Google Calendar

You can at any time withdraw access given to this application.

You have full overview over the code and what the code is doing. The code is documented below, and you can also see it in the Google Sheet by through the menu Extensions -> Apps Script.

Google Apps Script

Below are the scripts used in this Google Sheet. If I make any changes, I will update this blog post.

Code.gs

Page.html

Be the first to comment on "GA4 Event & Parameter Documentation in Google Sheet with Firestore & Server-side GTM integration"

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.