[Charlotte]

What you’re doing here is calculating the engagement rate as the ratio of engaged sessions to total sessions. For those unfamiliar with SQL language:

You’re using the safe_divide function to divide the first argument by the second argument. This function will not cause an error if you try to divide by zero; instead, it will return NULL.

The first argument is the count of distinct sessions where ‘session_engaged’ = ‘1’. The ‘case when’ statement is used to decide when a session is considered engaged. According to GA4, a session is considered engaged when it lasts at least 10 seconds or had a conversion event or 2 screen or page views.

For the ‘distinct’ condition, you’re using a combination of ‘user_pseudo_id’ and ‘ga_session_id’ to recognize distinct sessions. ‘user_pseudo_id’ is a unique identifier for a user and ‘ga_session_id’ is a unique identifier for a session. Therefore, the combination of these two keys will give you distinct sessions.

The second argument is the total count of distinct sessions. It’s calculated in the same way as above but it includes all sessions, not just engaged ones.

Then you’re aliasing the output of the division as ‘engagement_rate’. This will be the name of the column in the output of your query.

In summary, the ‘engagement rate’ is calculated as ‘the count of distinct engaged sessions divided by the count of all distinct sessions’.

It’s worth mentioning that this is likely a simplification of how engagement rate is calculated in GA4. Google may be using additional or different criteria to determine whether a session is considered engaged.

I hope this helps! Let me know if you have any other questions!

[Charlotte]

Yes, it’s absolutely possible. You can indeed utilize the same event tags in Google Tag Manager (GTM) for different instances of Google Analytics 4 (GA4). In different environments, you can have another GA4 for the same app. By setting up a new GA4 config tag, GTM would be able to send data to both GA4s accurately, based on the mapping variable you set in your tags.

[Charlotte]

It is not necessarily a glitch. Discrepancies in data between Google Analytics and Azure SQL could arise due to several factors. Sometimes it can be due to the way Google Analytics and Azure SQL process and filter data. If you are using any sampling or filters in Google Analytics, this can result in different numbers in reports than what is pulled into Azure SQL. Or, the methodology behind how each platform calculates metrics can be different. In addition, data in Google Analytics can undergo processing delay, which can cause variation in real-time data. Lastly, it’s important to make sure the time zone settings match in both platforms. If these settings are different, the data between the two platforms won’t align. You should re-check your pipeline and transformation rules and your configuration in both Google Analytics and Azure SQL.

[Charlotte]

Since you’ve already tried recreating your data streams, the issues you’re encountering may be due to some other factors. One possibility might be related to permissions: ensure that each property is properly linked to BigQuery and that the BigQuery service account has permission to access and write data.

Another potential culprit might be how your datasets are organized in BigQuery. Google Analytics 4 (GA4) exports raw event data to BigQuery once a day under the dataset name that you’ve specified. If these datasets are not set up correctly or are inconsistent in some way, it may be causing complications.

Sometimes, data can be delayed for a few hours even under ideal conditions, but as you’ve waited a few weeks already, it seems unlikely to be a simple delay issue. You may want to reach out to GA4 or BigQuery’s support services or consult an expert in these areas.

[Charlotte]

Sure, let me simplify those for you!

1. Regarding the possibility of issues with Google Analytics 4 if different events share the same name, there are no specific issues. The key is how you plan to analyze the data. Google Analytics 4 will group these events together since they have the same name, which could be useful if you wish to see all your button clicks in one place. However, if you need to separate data based on different buttons or properties, it might be more difficult.

2. In reality, with a lot more groups and “common columns” to manually enter into Google Tag Manager, it can become a burdensome task. Fortunately, Google Tag Manager provides foldering and other organizational tools that could help make the task of managing numerous tags a little less tedious. Another suggestion is to use templates and variable references to avoid rewriting common constants. Alternatively, you might consider using more advanced methods, like creating scripts to automate the work, if it’s an option.

[Charlotte]

In your function, you do have a ‘host’ parameter but it appears you’re not using it. If you want to track the hostname in Google Analytics, you can do so by including it in the ‘page_location’ field of the event. This field is meant to track the full URL including the domain ( hostname ). You can combine the host and the page path like this – host + pagePath, which gives you the full URL. Your code would look like this:

function pageViewGa(pagePath, pageUrl, host) {
    gtag("event", "page_view", {
        "page_title": pagePath,
        "page_location": host + pagePath,
        "page_path": pagePath
    });
}

This way, you will be tracking the hostname on Google Analytics.