CODEWITHSUNDEEP
google-bigquerygoogle-analytics
Tom Wu
Tom Wu

Reputation: 11

How to extract GA4 traffic session from BigQuery instead of user acquisition traffic?

We had linked GA4 and Bigquery 6 months ago, and now successfully extracting data, which the GA4 was recording a website.

We are aiming to measure campaign performance for each channel of triggering users to come to website (i.e. EDM, App-push, google ads, adwords etc.)

The measurement was extracting traffic attribution from BigQuery(source, medium, campaign etc.), however, according to BigQuery schema, the traffic columns in BQ indicates the traffic source that first acquired the user. (https://support.google.com/firebase/answer/7029846?hl=en#zippy=%2Cold-export-schema)

Another post had also tried to extract utm code from page_location, but cannot match the result as GA4 report.(How to get traffic source data of 'current session' in GA4 - bigquery export?)

So, to summarized, is there any idea to get the session traffic instead of user acquisition traffic from BigQuery? any idea would be appreciated, thanks.

i.e. One user came to website via email one month ago, then came again via google. We would like to get google as session traffic, email as user acquisition traffic. However, BigQuery only shows email as traffic source which means the user acquisition traffic.

Upvotes: 1

Views: 2790

Answers (3)

Krisjan O.
Krisjan O.

Reputation: 21

You can, although there are some known bugs (as of writing) in both the GA4 interface as the Google Analytics 4 (GA4) BigQuery exports, related to session traffic attribution. For example:

  • Traffic from Google Ads (google / cpc) is not inserted correctly in the event campaign parameters (so you have to check for a gclid paramater in the page_location to identify this traffic)
  • In the GA4 interface, session attribution behaves weird when a user_id is attached mid-session.

You can find an excellent article here, providing example code:

And an in-depth analysis of how session source/medium is calculated in GA4:

Upvotes: 1

user19967747
user19967747

Reputation: 1

I don’t think you can do this reliably with the bigquery export. I think the more reliable way would be pulling the data via the GA4 API. You’d need to pass the clientid and sessionid as user properties.

Upvotes: -1

sigmaError
sigmaError

Reputation: 3

In order to get the traffic at the session level, something like this would work:

SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source' AS session_source

Upvotes: 0

Related Questions