[Sophia]

Gonna take a wild guess here – I reckon you’re using the good ‘ol Google Analytics Spreadsheet Add-on, right? Unfortunately, it was made to jibe with Universal Analytics. Don’t know for sure if it’s working with the reporting API or the core reporting API.

Now, about the GA4 issue, it definitely won’t work with GA4, mate. The GA4 APIs are in beta for the time being and all the niggly bits aren’t ironed out yet. And the kicker is, you can’t rummage back and downgrade a GA4 to a UA, and creating a new UA is a no-go.

Let me ask around to see if the folks are planning on giving the add-on a face-lift to match with GA4. I’ll keep you in the loop!

[Sophia]

You’re actually already on the right track! In fact, Firebase Analytics events are the same as GA4 events. So, you don’t even need to connect your GTM container for GA4 migration from GA3.

The main thing you need to do is integrate Firebase Analytics into your mobile app. That’s how you’ll be able to pass events data into GA4. The cool thing about using GTM with this is you can actually add, modify, or delete Firebase events.

Here is a useful link for you about Firebase Analytics: [https://firebase.google.com/docs/analytics](https://firebase.google.com/docs/analytics)

[Sophia]

Ah, gotcha! It seems like there’s a simple reason for your geographic data not appearing. It turns out that the city and country information can only be automatically collected by some specific tools. These are: gtag, Google Tag Manager, or Google Analytics for Firebase. That’s probably why your city and country data is showing up as “(not set)”.

You can find more information about this on Google’s own developer guide for the protocol you’re using. Here’s [the link](https://developers.google.com/analytics/devguides/collection/protocol/ga4) for the guide.

Hope this clears up the confusion!

[Sophia]

In general, it sounds like you’re trying to send a GA Session ID as a user property and also register it as a user-level dimension within GA4. GA has its own way of managing sessions, so injecting your own session ID might cause some discrepancies or confusion.

If you treat the session ID both as a user property and dimension, you wont necessarily mess up your data collection, but it may add complexity in understanding the information and you may retrieve some non-intuitive outcomes.

However, sending this session ID as a user property to GA4 could be useful for cross-referencing with data collected outside of GA, it won’t interact with GA’s already established measures of users and sessions.

In conclusion, while there doesn’t seem to be a data collection issue, you’ll want to make sure you fully understand how GA handles data in relation to users/sessions to ensure the most accurate and valuable analysis.

[Sophia]

Sure, I can help you with that. Here’s a good way to approach it by using an UPDATE-SET-FROM statement, which is pretty handy but isn’t explicitly listed in the Google documentation.

The basic pattern you’d use goes a little something like this:

`sql
update ds.targettable t
set t.targetfield = s.sourcefield
from (select keyfield, sourcefield
from ds.sourcetable
) s
where t.keyfield = s.keyfield
`
Now, like I said it’s super useful to be able to pull information from one table to update another table like this. For your specific situation, you’d probably need to tweak it a bit. I don’t have direct access to your GA events table, so I can only offer you a rough estimation, but hopefully this helps:

`sql
update project-name.analytics_299XXXXXX.events_* tgt
set page_location = src.page
from (SELECT event_name, key, _table_suffix (SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = ‘page_view’ AND key = ‘page_location’) AS page
FROM project-name.analytics_299XXXXXX.events_*
WHERE _table_suffix BETWEEN ‘20220322’
AND FORMAT_DATE(‘%Y%m%d’,DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = ‘page_view’
and page LIKE ‘%login%’) src
where farm_fingerprint(concat(tgt.key, tgt.event_name) = farm_fingerprint(concat(src.key, src.event_name)
and src._table_suffix = tgt._table_suffix
and tgt.page LIKE ‘%login%’
`
Table suffix comparison might be a bit tricky though, you’ll need to experiment with that to get it right.

But above all, let me know if you have any questions or need any clarification, I’d be happy to assist. Good luck!

[Sophia]

There could be a few potential issues causing your current query to not return the expected result. The most likely one is that the ‘product_id’ and ‘site_interaction’ keys may not be present in the same event parameter. Unnesting the event_params columns will create a separate row for every key-value pair, and trying to join them on the same row later using a WHERE condition might result in an empty dataset if the conditions are not met.

Here, you might want to consider utilizing a CASE WHEN in an aggregated function to help in isolating the respective keys. Doing so would mean unnesting the array only once but testing which specific key we’re handling in each situation.

For example:
`
SELECT
MAX(CASE WHEN ep.key = ‘product_id’ THEN ep.value.string_value END) as product_id,
COUNT(1) as share
FROM
‘dataset’,
UNNEST(event_params) as ep
WHERE
event_name = ‘listing’
GROUP BY
product_id
`

This will first find the ‘product_id’ for each ‘listing’ event, then count how many times each product_id appears in those events. This seems to align with your required output, but please adjust as needed based on your exact dataset and requirements.

[Sophia]

It appears that your website’s in-page links have started including additional, potentially unwanted parameters which may be causing the page to refresh with each click. While these parameters, usually used for tracking user behavior across multiple domains, seem to be related to your Google Analytics 4 (GA4) setup, you mentioned not having made any recent changes to your GA4 or Google Tag Manager (GTM) settings. Despite deactivating certain features in GA4 like enhanced measurement and in-page link events, these parameters persist. The exact cause remains unclear. It would likely require a more in-depth look into your website setup, and potentially consulting with a website developer or a Google Analytics expert to find a solution.