--- title: "How to Write Your First Query on GA4 Data in BigQuery" description: "How Google Analytics 4 data is structured in BigQuery and how to write your first SQL queries: events, pages, sources. How to avoid overpaying for queries." url: "https://it-deweloper.pl/en/blog/ga4-bigquery-first-query" date: "2026-10-17T23:41:09+00:00" language: "en-GB" --- [Skip to main content](#tm-main) - [ Home](https://it-deweloper.pl/en "IT Deweloper web studio home page") - [Contact](https://it-deweloper.pl/en/contact "Contact the IT Deweloper web studio") - [ +48 731 190 041](tel:+48731190041 "Phone") - [ office@it-deweloper.pl](mailto:office@it-deweloper.pl "E-mail") - [ Nowogródzka 4A, Białystok, Poland](https://maps.app.goo.gl/BNjvryytwRyv4omA7 "Address") - [ WhatsApp](https://wa.me/48731190041 "WhatsApp") - [ Telegram](https://t.me/+48731190041 "Telegram") - [Solutions](https://it-deweloper.pl/en/web "IT Deweloper's web development solutions") - [How we workStages of work](https://it-deweloper.pl/en/web/process "Website development stages and timelines") - [SEOSearch engine optimization](https://it-deweloper.pl/en/web/seo "SEO promotion: optimization, links, positions in Google") - [GEOWebsite promotion in AI](https://it-deweloper.pl/en/web/geo "GEO optimization: website promotion in ChatGPT, Gemini and Perplexity answers") - [AIArtificial Intelligence on the website](https://it-deweloper.pl/en/web/ai) - [JoomlaWebsite Management System](https://it-deweloper.pl/en/web/joomla "Joomla website development") - [GoogleGoogle services setup](https://it-deweloper.pl/en/web/google "Google services setup for your website") - [Cost](https://it-deweloper.pl/en/cost "Rates and cost of services") - [PricingIndicative website development prices](https://it-deweloper.pl/en/cost/pricing "Website development prices") - [CalculatorSelf-service online estimate](https://it-deweloper.pl/en/cost/calculator "Calculate your website cost online") - [BriefGet an accurate project quote](https://it-deweloper.pl/en/cost/brief "Fill out the website development brief") - [Portfolio](https://it-deweloper.pl/en/portfolio "Portfolio and examples of our work") - [About us](https://it-deweloper.pl/en/about "About the founder — Victor Parhimchik") - [FAQ](https://it-deweloper.pl/en/faq "Frequently asked questions about website development") - [Blog](https://it-deweloper.pl/en/blog "Articles on website development and promotion") - [ Русский ](https://it-deweloper.pl/ru/blog/ga4-bigquery-first-query) - [ Polski ](https://it-deweloper.pl/pl/blog/ga4-bigquery-first-query) - [ Беларуская ](https://it-deweloper.pl/be/blog/ga4-bigquery-first-query) - [ English ](https://it-deweloper.pl/en/blog/ga4-bigquery-first-query) - ![](https://it-deweloper.pl/templates/yootheme/assets/images/language.svg) English - [ Русский ](https://it-deweloper.pl/ru/blog/ga4-bigquery-first-query) - [ Polski ](https://it-deweloper.pl/pl/blog/ga4-bigquery-first-query) - [ Беларуская ](https://it-deweloper.pl/be/blog/ga4-bigquery-first-query) - [ English ](https://it-deweloper.pl/en/blog/ga4-bigquery-first-query) [ ![IT-Deweloper](https://it-deweloper.pl/images/Faviocon.svg)![IT-Deweloper](https://it-deweloper.pl/images/Logo-light.svg)](https://it-deweloper.pl/en) [ Menu ](#tm-dialog) - [ Home](https://it-deweloper.pl/en "IT Deweloper web studio home page") - [Contact](https://it-deweloper.pl/en/contact "Contact the IT Deweloper web studio") - [ +48 731 190 041](tel:+48731190041 "Phone") - [ office@it-deweloper.pl](mailto:office@it-deweloper.pl "E-mail") - [ Nowogródzka 4A, Białystok, Poland](https://maps.app.goo.gl/BNjvryytwRyv4omA7 "Address") - [ WhatsApp](https://wa.me/48731190041 "WhatsApp") - [ Telegram](https://t.me/+48731190041 "Telegram") - [Solutions](https://it-deweloper.pl/en/web "IT Deweloper's web development solutions") - [How we workStages of work](https://it-deweloper.pl/en/web/process "Website development stages and timelines") - [SEOSearch engine optimization](https://it-deweloper.pl/en/web/seo "SEO promotion: optimization, links, positions in Google") - [GEOWebsite promotion in AI](https://it-deweloper.pl/en/web/geo "GEO optimization: website promotion in ChatGPT, Gemini and Perplexity answers") - [AIArtificial Intelligence on the website](https://it-deweloper.pl/en/web/ai) - [JoomlaWebsite Management System](https://it-deweloper.pl/en/web/joomla "Joomla website development") - [GoogleGoogle services setup](https://it-deweloper.pl/en/web/google "Google services setup for your website") - [Cost](https://it-deweloper.pl/en/cost "Rates and cost of services") - [PricingIndicative website development prices](https://it-deweloper.pl/en/cost/pricing "Website development prices") - [CalculatorSelf-service online estimate](https://it-deweloper.pl/en/cost/calculator "Calculate your website cost online") - [BriefGet an accurate project quote](https://it-deweloper.pl/en/cost/brief "Fill out the website development brief") - [Portfolio](https://it-deweloper.pl/en/portfolio "Portfolio and examples of our work") - [About us](https://it-deweloper.pl/en/about "About the founder — Victor Parhimchik") - [FAQ](https://it-deweloper.pl/en/faq "Frequently asked questions about website development") - [Blog](https://it-deweloper.pl/en/blog "Articles on website development and promotion") [ IT Deweloper Blog ](https://it-deweloper.pl/index.php?option=com_content&view=category&id=29&lang=en-GB "IT Deweloper Blog") # How to Write Your First Query on GA4 Data in BigQuery Once Google Analytics 4 is linked to BigQuery, raw events appear in your warehouse every day: every page view, click and enquiry with all its parameters. That gives you freedom the Analytics interface doesn’t, but the data is structured unusually, and first queries often leave people stuck. Here’s how GA4 tables in BigQuery are structured, three first SQL queries — by day, by page and by source — and how to avoid overpaying for data processing. --- ![A database icon in a browser window, a step checklist and a question mark — a guide to first queries on GA4 data in BigQuery](https://it-deweloper.pl/images/blog/ga4-bigquery-first-query-cover-2x.webp) ## What needs to be in place Your GA4 property is linked to a Google Cloud project via BigQuery links in Admin, and the export has been running for at least a day. Note: the export starts from the moment you link, and past data won’t appear retroactively. ## How the data is structured - The dataset is named `analytics_` plus your property number, e.g. `analytics_123456789`. - The daily export creates `events_YYYYMMDD` tables, one per day. If streaming export is on, the current day is in `events_intraday_YYYYMMDD`. - One row is one event: `page_view`, `first_visit`, `generate_lead` and so on. - Event parameters — page URL, title and so on — aren’t in separate columns but in a nested `event_params` list of key–value pairs. To get them out you need `UNNEST`. ## Query 1. Events and users by day ``` SELECT event_date, COUNT(*) AS events, COUNT(DISTINCT user_pseudo_id) AS users FROM `your-project.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007' GROUP BY event_date ORDER BY event_date; ``` `events_*` takes all the daily tables at once, and the `_TABLE_SUFFIX` condition keeps only the dates you need. Without it, the query reads your entire history — and you pay for that. Note that `user_pseudo_id` is a browser or device, not a person. ## Query 2. Most viewed pages ``` SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page, COUNT(*) AS views FROM `your-project.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007' AND event_name = 'page_view' GROUP BY page ORDER BY views DESC LIMIT 20; ``` The subquery with `UNNEST` pulls the value with the `page_location` key out of the parameter list. Other parameters work the same way: `page_title`, `page_referrer`. Numeric values are in `value.int_value`, not `string_value`. ## Query 3. Where new users came from ``` SELECT traffic_source.source AS source, traffic_source.medium AS medium, COUNT(DISTINCT user_pseudo_id) AS new_users FROM `your-project.analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007' AND event_name = 'first_visit' GROUP BY source, medium ORDER BY new_users DESC; ``` The `traffic_source` field stores the source of the user’s first visit, not the current session. It suits new users; for session analysis, other fields are used, such as `collected_traffic_source`. ## Why the numbers differ from the GA4 interface - The Analytics interface uses modelling and data thresholds, while BigQuery holds only the events actually collected. - Users in BigQuery are counted by device and browser, while GA4 reports may combine them differently. - `event_date` is in the property’s time zone, while `event_timestamp` is in UTC. ## How to avoid overpaying - Always limit dates with `_TABLE_SUFFIX`. - Select only the columns you need. BigQuery charges for the amount of data read, not for the number of rows returned, so `LIMIT` doesn’t save money and `SELECT *` is the most expensive query. - Check the estimate in the editor before running: BigQuery shows how much data a query will process. - BigQuery’s free tier includes 1 TB of query processing per month and 10 GB of storage. That’s usually enough for a small site. - In the BigQuery sandbox without billing, tables are deleted after 60 days. For long-term storage you need a project with a billing account. - Standard GA4 properties have a daily export limit of one million events per day; if it’s consistently exceeded, the export is paused. ## In short 1. One row is one event; parameters are in the nested `event_params`. 2. Query tables via `events_*` and always filter on `_TABLE_SUFFIX`. 3. Parameters come out with an `UNNEST` subquery. 4. `traffic_source` is the first-visit source, not the session source. 5. You pay for data read: select only the columns you need. If you still need to set up the export or want ready-made reports on raw data, we can [set up the Google Analytics 4 export to BigQuery](https://it-deweloper.pl/en/web/google/bigquery): create the project, link it to GA4, check the data and set up cost controls. Query results are easy to present in a [Data Studio report](https://it-deweloper.pl/en/blog/data-studio-first-report). 17 October 2026 | [Victor Parhimchik](https://it-deweloper.pl/index.php?option=com_contact&view=contact&id=1&catid=4) | [IT Deweloper Blog](https://it-deweloper.pl/index.php?option=com_content&view=category&id=29) [ ![A database icon in a browser window, a step checklist and a question mark — a guide to first queries on GA4 data in BigQuery](https://it-deweloper.pl/images/blog/ga4-bigquery-first-query-cover.webp) ### How to Write Your First Query on GA4 Data in BigQuery Once Google Analytics 4 is linked to BigQuery, raw events appear in your warehouse every day: every page view, click and enquiry with all its paramet… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=300:ga4-bigquery-first-query&catid=29&lang=en-GB) [ ![A gateway arch with an arrow in a browser window, a step checklist and a question mark — an explanation of how Google tag gateway works](https://it-deweloper.pl/images/blog/how-google-tag-gateway-works-cover.webp) ### How Google Tag Gateway Works and Whether to Turn It On Google tag gateway is a feature that lets you load Google tags from your own domain instead of Google’s servers. It’s often described as a way to “by… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=299:how-google-tag-gateway-works&catid=29&lang=en-GB) [ ![A speed gauge with a needle in a browser window, a step checklist and a question mark — a guide to checking Core Web Vitals](https://it-deweloper.pl/images/blog/check-core-web-vitals-cover.webp) ### How to Check Your Core Web Vitals and Read the Numbers Core Web Vitals are three metrics Google uses to judge how comfortable a page is to use: how quickly the main content appears, how quickly the page r… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=298:check-core-web-vitals&catid=29&lang=en-GB) [ ![An envelope in a browser window, a step checklist and a question mark — a guide to setting up SPF, DKIM and DMARC for Google Workspace](https://it-deweloper.pl/images/blog/google-workspace-spf-dkim-dmarc-cover.webp) ### How to Set Up SPF, DKIM and DMARC for Google Workspace If emails from your domain land in spam or don’t arrive at all, the first thing to check is three DNS records: SPF, DKIM and DMARC. Since 2024, Gmail… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=297:google-workspace-spf-dkim-dmarc&catid=29&lang=en-GB) [ ![A shield with a padlock in a browser window, a step checklist and a question mark — a guide to choosing reCAPTCHA and getting keys](https://it-deweloper.pl/images/blog/choose-recaptcha-version-cover.webp) ### How to Choose reCAPTCHA for a Form and Get the Keys reCAPTCHA protects your website forms from spam bots. During 2025, Google moved all reCAPTCHA keys to Google Cloud, so many older guides are out of d… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=296:choose-recaptcha-version&catid=29&lang=en-GB) [ ![A map pin in a browser window, a step checklist and a question mark — a guide to embedding a Google map on a website](https://it-deweloper.pl/images/blog/embed-google-map-on-website-cover.webp) ### How to Embed a Google Map on Your Website Without an API Key A map on your Contact page helps customers see where you are and get directions straight away. You don’t need an API key or a payment card in Google… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=295:embed-google-map-on-website&catid=29&lang=en-GB) [ ![A target with an arrow in a browser window, a step checklist and a question mark — a guide to checking Google Ads conversions](https://it-deweloper.pl/images/blog/check-google-ads-conversions-cover.webp) ### How to Check That Google Ads Conversions Are Tracked Google Ads learns from conversions: enquiries, calls, purchases. If they’re counted wrongly, the system optimises your campaigns blind — spending bud… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=294:check-google-ads-conversions&catid=29&lang=en-GB) [ ![Pie and bar charts in a browser window, a step checklist and a question mark — a guide to building a Data Studio report](https://it-deweloper.pl/images/blog/data-studio-first-report-cover.webp) ### How to Build a Data Studio Report from Analytics and GSC Data Studio is Google’s free tool for reports and dashboards. In April 2026 Google brought back this name; for several years before that it was calle… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=293:data-studio-first-report&catid=29&lang=en-GB) [ ![A cookie icon in a browser window, a step checklist and a question mark — a guide to checking Google Consent Mode](https://it-deweloper.pl/images/blog/check-consent-mode-v2-cover.webp) ### How to Check If Consent Mode v2 Works on Your Website Google Consent Mode v2 passes the choice a visitor made in your cookie banner to Google Analytics and Google Ads. A banner can look perfect and still… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=292:check-consent-mode-v2&catid=29&lang=en-GB) [ ![A Tag Manager tag icon in a browser window, a step checklist and a question mark — a guide to installing Google Tag Manager](https://it-deweloper.pl/images/blog/install-google-tag-manager-cover.webp) ### How to Install Google Tag Manager on Your Website Google Tag Manager is a “box” for all the tracking codes on your site: Google Analytics, Google Ads conversions, social media pixels. You install the… ](https://it-deweloper.pl/index.php?option=com_content&view=article&id=291:install-google-tag-manager&catid=29&lang=en-GB) [](#) [](#) ## Build your website Find out what your website will cost [ Pricing ](https://it-deweloper.pl/index.php?Itemid=262&lang=en-GB) ![](https://it-deweloper.pl/media/yootheme/cache/3c/background-square-01-3c44c74f.png?src=images/yootheme/demo/background-square-01.png&thumbnail=900,900,&hash=72c31958) ![](https://it-deweloper.pl/media/yootheme/cache/cb/background-square-03-cbb341a0.png?src=images/yootheme/demo/background-square-03.png&thumbnail=600,600,&hash=6e9dde93) ### [ Contact ](https://it-deweloper.pl/index.php?Itemid=277&lang=en-GB) Web studio IT Deweloper website development, Poland. 5423493188 SEOAIAdsWebSMM ### Address Nowogródzka 4A Białystok, 15-490, PL [ ### Phone +48 731 190 041 ](tel:+48731190041) [ ### Email This email address is being protected from spambots. You need JavaScript enabled to view it. ](mailto:office@it-deweloper.pl) [ ### WhatsApp ](https://wa.me/48731190041) [ ### Telegram ](https://t.me/+48731190041) Opening hours: - Monday 0900–1800 - Tuesday 0900–1800 - Wednesday 0900–1800 - Thursday 0900–1800 - Friday 0900–1800 - Saturday 0900–1600 - Sunday closed ![IT Deweloper](https://it-deweloper.pl/images/Logo.webp) Web development can be interesting! Our social media, with content about building websites. - [](https://www.facebook.com/ITdeweloper) - [](https://www.linkedin.com/company/it-deweloper) - [](https://t.me/IT_deweloper) - [](https://www.instagram.com/it_deweloper/) - [Portfolio](https://it-deweloper.pl/index.php?Itemid=264&lang=en-GB) - [Contact](https://it-deweloper.pl/index.php?Itemid=277&lang=en-GB) - [About us](https://it-deweloper.pl/index.php?Itemid=261&lang=en-GB) **it-deweloper** — a web development studio. We design and build modern websites and online stores. © [it-deweloper](https://it-deweloper.pl/en). All rights reserved. [Privacy Policy](https://it-deweloper.pl/index.php?Itemid=313&lang=en)