{"id":5716,"date":"2023-08-09T14:21:05","date_gmt":"2023-08-09T11:21:05","guid":{"rendered":"https:\/\/trackingchef.com\/?p=5716"},"modified":"2024-10-28T19:05:59","modified_gmt":"2024-10-28T16:05:59","slug":"how-to-get-count-of-session-in-bigquery-for-google-analytics-4","status":"publish","type":"post","link":"https:\/\/trackingchef.com\/google-analytics\/how-to-get-count-of-session-in-bigquery-for-google-analytics-4\/","title":{"rendered":"How to Get Count of Session in Bigquery for Google Analytics 4"},"content":{"rendered":"\n

Back in the old days of universal analytics, there was a report that not that many users knew about or used, but could hold some interesting insights, i.e. the “Frequency & Recency” report which is absent from google analytics 4.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

This report used to show the “Count of Sessions” dimension , i.e. “buckets” of the nth number of session of a unique user. What I liked about this report is that it would give you a view on engagement and stickiness that other metrics didn’t show, what I didn’t like about it is that it showed this dimension with a metrics of sessions, instead of users which would have been more accurate and helpful. <\/p>\n\n\n\n

As of now, this dimension is not supported in GA4 and I wouldn’t hold my breath waiting for Google to add it, so it’s up to us to recreate it. Since there is no way to build it in the GA4 interface itself, you’ll need to use BigQuery and run the following query:<\/p>\n\n\n\n

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

SELECT\nuser_pseudo_id,\nCOUNTIF(event_name = \"session_start\") as num_of_sessions\nFROM 'myproject.analytics_343884170.events_*' \/\/change to your project and table id<\/span><\/span>\n where\n_table_suffix between '20230805' and '20230805' \/\/change to <\/span><\/span>the date frame you want\ngroup by 1,2\norder by num_of_sessions desc\n<\/code><\/pre>\n\n\n\n
The output of this query will look like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now, you can improve the query and create a more elaborate table (for example use a dynamic date range<\/a> or adding the event_date etc.), but in this case I choose to keep it simple and to the point, since this gives you the raw data you need in order to recreate the count of sessions report and we only need to style it via Looker Studio.

First you need to connect the data to Looker Studio. There are 2 ways you can do this:
1) Custom Query<\/strong> – In Looker Studio you can add a data source which is built on a custom query, simply add a new data source, choose BigQuery and there in the left side menu you will see the option of “Custom Query”, here in the query window you’ll need to paste the query from above (don’t forget to modify to your needs and change to your specific project)<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
2) Table in BigQuery<\/strong> – You can also create a table in BigQuery to hold this data and that will refresh daily. After you run your query, above the results pane you will see a button for “Save Results” which will open a drop down menu, there choose the option of “BigQuery Table”. this will open a setting panel for your saved table.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
After this is done you’ll need to create a daily scheduled update for this new table, in order to get the updated data each day. Once this is done, you can pull this into Looker Studio via the same BigQuery connector only choose “My Projects” and your specific table.<\/p>\n\n\n\n
No matter which method you used to get the data in Looker Studio, now you will need to create a table with “num_of_sessions” as dimension (yes, a dimension), and number the metrics is the count of pseudo_user_id. You can also add a pie graph if you want a visualization of the % breakdown per number of sessions.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Now, if you want to go the extra mile, I would suggest changing the values to something more literal in order to avoid confusion by the reader. You can either adjust the original query or manipulate the data in Looker Studio, in any case I would use a simple format. In Looker Studio create a new field with this formula:<\/p>\n\n\n\n
concat(num_of_sessions, ' Sessions')<\/code><\/pre>\n\n\n\n
In BigQuery adjust the “num_of_sessions” line in the query as so:<\/p>\n\n\n\n
concat(COUNTIF(event_name = \"session_start\"),' Sessions') as num_of_sessions<\/code><\/pre>\n\n\n\n
This will change the output from number of “2”, “3” etc. to “2 Sessions”, “3 Sessions” etc. And that’s it, you now have reconstructed the count of sessions report in google analytics 4. <\/p>\n","protected":false},"excerpt":{"rendered":"
Back in the old days of universal analytics, there was a report that not that many users knew about or used, but could hold some interesting insights, i.e. the “Frequency & Recency” report which is absent from google analytics 4. This report used to show the “Count of Sessions” dimension , i.e. “buckets” of the […]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-5716","post","type-post","status-publish","format-standard","hentry","category-google-analytics"],"_links":{"self":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/5716","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/comments?post=5716"}],"version-history":[{"count":11,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/5716\/revisions"}],"predecessor-version":[{"id":6090,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/posts\/5716\/revisions\/6090"}],"wp:attachment":[{"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/media?parent=5716"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/categories?post=5716"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trackingchef.com\/wp-json\/wp\/v2\/tags?post=5716"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}