{"id":10431,"date":"2026-08-15T14:39:31","date_gmt":"2026-08-15T12:39:31","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/blog\/toolbox\/ga4-bigquery-query-builder\/"},"modified":"2026-08-15T14:48:46","modified_gmt":"2026-08-15T12:48:46","slug":"ga4-bigquery-query-builder","status":"publish","type":"page","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/","title":{"rendered":"GA4 BigQuery Query Builder"},"content":{"rendered":"<div class=\"gtm-analyser-container\" style=\"background: var(--bg-panel, #1e1e24); padding: 25px; border-radius: 8px; border: 1px solid var(--border, #2a2a35);\">\n<p style=\"color: var(--text-secondary, #a0a0b0); margin-bottom: 20px;\">The GA4 export puts almost everything worth querying inside a nested array, and the difference between a correct query and an expensive wrong one is a few characters. This builds the statement, names how many daily tables it reads, and points at what would otherwise be found out later.<\/p>\n<div style=\"display: flex; gap: 12px; flex-wrap: wrap; margin-bottom: 16px;\">\n<div style=\"flex: 1; min-width: 200px;\">\n            <label for=\"bq-projekt\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Project<\/label><br \/>\n            <input id=\"bq-projekt\" type=\"text\" class=\"form-control\" value=\"mein-projekt\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<div style=\"flex: 1; min-width: 200px;\">\n            <label for=\"bq-datensatz\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Dataset<\/label><br \/>\n            <input id=\"bq-datensatz\" type=\"text\" class=\"form-control\" value=\"analytics_123456789\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<\/p><\/div>\n<div style=\"display: flex; gap: 12px; flex-wrap: wrap; margin-bottom: 16px;\">\n<div style=\"flex: 1; min-width: 150px;\">\n            <label for=\"bq-von\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">From (YYYYMMDD)<\/label><br \/>\n            <input id=\"bq-von\" type=\"text\" class=\"form-control\" value=\"20260801\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<div style=\"flex: 1; min-width: 150px;\">\n            <label for=\"bq-bis\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">To (YYYYMMDD)<\/label><br \/>\n            <input id=\"bq-bis\" type=\"text\" class=\"form-control\" value=\"20260831\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<div style=\"flex: 1; min-width: 180px;\">\n            <label for=\"bq-ereignis\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Event name (empty = all)<\/label><br \/>\n            <input id=\"bq-ereignis\" type=\"text\" class=\"form-control\" value=\"purchase\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<div style=\"flex: 1; min-width: 200px;\">\n            <label for=\"bq-kennzahl\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Metric<\/label><br \/>\n            <select id=\"bq-kennzahl\" class=\"form-control\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; box-sizing: border-box;\"><option value=\"ereignisse\">count events<\/option><option value=\"nutzer\">distinct users<\/option><option value=\"sitzungen\">sessions<\/option><option value=\"umsatz\">sum of value<\/option><\/select>\n        <\/div>\n<\/p><\/div>\n<div style=\"font-family: 'Nunito Sans', sans-serif; font-weight: 700; color: var(--text-primary, #e8e8ee); font-size: 0.92rem; margin-bottom: 8px;\">Break down by<\/div>\n<div id=\"bq-felder\" style=\"display: grid; grid-template-columns: repeat(auto-fill, minmax(240px, 1fr)); gap: 6px 16px; margin-bottom: 14px;\"><\/div>\n<div style=\"display: flex; gap: 12px; flex-wrap: wrap; align-items: flex-end; margin-bottom: 16px;\">\n<div style=\"flex: 1; min-width: 200px;\">\n            <label for=\"bq-eigen\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Own event parameter<\/label><br \/>\n            <input id=\"bq-eigen\" type=\"text\" class=\"form-control\" placeholder=\"z. B. checkout_step\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; font-family: monospace; box-sizing: border-box;\">\n        <\/div>\n<div style=\"min-width: 170px;\">\n            <label for=\"bq-eigen-typ\" style=\"color: var(--text-secondary, #a0a0b0); display: block; font-size: 0.85rem; margin-bottom: 5px;\">Its value field<\/label><br \/>\n            <select id=\"bq-eigen-typ\" class=\"form-control\" style=\"width: 100%; padding: 10px; background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); border-radius: 6px; box-sizing: border-box;\"><option value=\"string\">string_value<\/option><option value=\"int\">int_value<\/option><option value=\"double\">double_value<\/option><\/select>\n        <\/div>\n<\/p><\/div>\n<p>    <button id=\"bq-btn\" class=\"button\" style=\"background: var(--accent, #7ee787); color: #0b1114; border: none; padding: 12px 24px; border-radius: 6px; font-weight: 700; cursor: pointer;\">Build query<\/button><\/p>\n<div id=\"bq-ausgabe\" style=\"margin-top: 22px;\"><\/div>\n<\/div>\n<p><script>\n(function () {\n    'use strict';<\/p>\n<p>    const T = {\"einleitung\":\"The GA4 export puts almost everything worth querying inside a nested array, and the difference between a correct query and an expensive wrong one is a few characters. This builds the statement, names how many daily tables it reads, and points at what would otherwise be found out later.\",\"label_projekt\":\"Project\",\"label_datensatz\":\"Dataset\",\"label_von\":\"From (YYYYMMDD)\",\"label_bis\":\"To (YYYYMMDD)\",\"label_ereignis\":\"Event name (empty = all)\",\"label_kennzahl\":\"Metric\",\"kennzahl_ereignisse\":\"count events\",\"kennzahl_nutzer\":\"distinct users\",\"kennzahl_sitzungen\":\"sessions\",\"kennzahl_umsatz\":\"sum of value\",\"h_felder\":\"Break down by\",\"quelle_ereignis\":\"event_params\",\"quelle_spalte\":\"column\",\"quelle_artikel\":\"items\",\"label_eigen\":\"Own event parameter\",\"label_eigen_typ\":\"Its value field\",\"knopf\":\"Build query\",\"h_sql\":\"Query\",\"h_hinweise\":\"Worth knowing before running it\",\"hinweis_tabellen\":\"Reads %d daily tables. BigQuery bills the columns referenced across exactly these partitions - a wider range costs proportionally more.\",\"hinweis_intraday\":\"The events_* wildcard also matches events_intraday_*. The suffix filter excludes those, because intraday_ sorts after any eight-digit date. To include today, query the intraday table separately.\",\"hinweis_datum_ungueltig\":\"A date is not eight digits. The filter is written out anyway, but it will prune nothing and the query will read the whole dataset.\",\"hinweis_zeitraum_verdreht\":\"The end date lies before the start date. The query is valid and returns nothing.\",\"hinweis_eigen_name\":\"The own parameter name contains characters that cannot be a column alias, so it was left out.\",\"hinweis_naeherung\":\"Over more than ninety days COUNT(DISTINCT user_pseudo_id) becomes slow and expensive. APPROX_COUNT_DISTINCT gives the same answer within about one per cent.\",\"hinweis_artikel_vervielfacht\":\"The items array is unnested with a cross join, so one purchase with four articles becomes four rows. Counting events after that counts articles.\",\"hinweis_event_date_text\":\"event_date is a string in the export, not a date. Comparisons work lexically; TIMESTAMP_MICROS(event_timestamp) is the field to use for real date arithmetic.\",\"hinweis_umsatz_ohne_waehrung\":\"Summing value without grouping by currency adds euros to dollars.\"};<\/p>\n<p>    \/\/ --- Felder des Exportschemas -------------------------------------------\n    \/\/ quelle: 'ereignis' = event_params, 'nutzer' = user_properties,\n    \/\/         'spalte' = Spalte der Tabelle, 'artikel' = items-Array\n    const FELDER = [\n        { id: 'page_location',        quelle: 'ereignis', typ: 'string' },\n        { id: 'page_title',           quelle: 'ereignis', typ: 'string' },\n        { id: 'ga_session_id',        quelle: 'ereignis', typ: 'int' },\n        { id: 'ga_session_number',    quelle: 'ereignis', typ: 'int' },\n        { id: 'engagement_time_msec', quelle: 'ereignis', typ: 'int' },\n        { id: 'session_engaged',      quelle: 'ereignis', typ: 'string' },\n        { id: 'source',               quelle: 'ereignis', typ: 'string' },\n        { id: 'medium',               quelle: 'ereignis', typ: 'string' },\n        { id: 'campaign',             quelle: 'ereignis', typ: 'string' },\n        { id: 'currency',             quelle: 'ereignis', typ: 'string' },\n        { id: 'value',                quelle: 'ereignis', typ: 'double' },\n        { id: 'transaction_id',       quelle: 'ereignis', typ: 'string' },\n        { id: 'event_date',           quelle: 'spalte',   typ: 'string' },\n        { id: 'user_pseudo_id',       quelle: 'spalte',   typ: 'string' },\n        { id: 'device.category',      quelle: 'spalte',   typ: 'string' },\n        { id: 'geo.country',          quelle: 'spalte',   typ: 'string' },\n        { id: 'traffic_source.medium', quelle: 'spalte',  typ: 'string' },\n        { id: 'items.item_id',        quelle: 'artikel',  typ: 'string' },\n        { id: 'items.item_name',      quelle: 'artikel',  typ: 'string' },\n        { id: 'items.price',          quelle: 'artikel',  typ: 'double' }\n    ];<\/p>\n<p>    const WERTFELD = { string: 'string_value', int: 'int_value', double: 'double_value' };<\/p>\n<p>    function feld(id) {\n        for (let i = 0; i < FELDER.length; i++) { if (FELDER[i].id === id) { return FELDER[i]; } }\n        return null;\n    }\n\n    function wertfeld(typ) { return WERTFELD[typ] || 'string_value'; }\n\n    \/\/ --- Datumsrechnung ------------------------------------------------------\n\n    function alsDatum(text) {\n        const s = String(text || '').trim();\n        if (!\/^\\d{8}$\/.test(s)) { return null; }\n        const j = Number(s.slice(0, 4)), m = Number(s.slice(4, 6)), t = Number(s.slice(6, 8));\n        if (m < 1 || m > 12 || t < 1 || t > 31) { return null; }\n        const d = new Date(Date.UTC(j, m - 1, t));\n        if (d.getUTCFullYear() !== j || d.getUTCMonth() !== m - 1 || d.getUTCDate() !== t) { return null; }\n        return d;\n    }<\/p>\n<p>    function tagesanzahl(von, bis) {\n        const a = alsDatum(von), b = alsDatum(bis);\n        if (!a || !b) { return 0; }\n        const tage = Math.round((b.getTime() - a.getTime()) \/ 86400000) + 1;\n        return tage > 0 ? tage : 0;\n    }<\/p>\n<p>    \/\/ --- Abfrage bauen -------------------------------------------------------<\/p>\n<p>    function bauen(e) {\n        const ein = e || {};\n        const projekt   = String(ein.projekt || 'mein-projekt').trim();\n        const datensatz = String(ein.datensatz || 'analytics_123456789').trim();\n        const von = String(ein.von || '').trim();\n        const bis = String(ein.bis || '').trim();\n        const ereignis  = String(ein.ereignis || '').trim();\n        const kennzahl  = ein.kennzahl || 'ereignisse';\n        const auswahl   = (ein.felder || []).slice();\n        const eigen     = String(ein.eigen || '').trim();\n        const eigenTyp  = ein.eigenTyp || 'string';<\/p>\n<p>        const hinweise = [];\n        const tage = tagesanzahl(von, bis);\n        if (!alsDatum(von) || !alsDatum(bis)) { hinweise.push('datum_ungueltig'); }\n        else if (tage === 0) { hinweise.push('zeitraum_verdreht'); }<\/p>\n<p>        \/\/ Spalten der Auswahl\n        const spalten = [];\n        let artikelJoin = false;<\/p>\n<p>        auswahl.forEach(function (id) {\n            const f = feld(id);\n            if (!f) { return; }\n            if (f.quelle === 'ereignis') {\n                spalten.push('  (SELECT value.' + wertfeld(f.typ) + ' FROM UNNEST(event_params) WHERE key = \\'' + f.id + '\\') AS ' + f.id);\n            } else if (f.quelle === 'spalte') {\n                spalten.push('  ' + f.id + ' AS ' + f.id.replace(\/\\.\/g, '_'));\n            } else if (f.quelle === 'artikel') {\n                artikelJoin = true;\n                spalten.push('  ' + f.id.replace('items.', 'artikel.') + ' AS ' + f.id.replace(\/\\.\/g, '_'));\n            }\n        });<\/p>\n<p>        if (eigen !== '') {\n            if (!\/^[A-Za-z_][A-Za-z0-9_]*$\/.test(eigen)) { hinweise.push('eigen_name'); }\n            else { spalten.push('  (SELECT value.' + wertfeld(eigenTyp) + ' FROM UNNEST(event_params) WHERE key = \\'' + eigen + '\\') AS ' + eigen); }\n        }<\/p>\n<p>        \/\/ Kennzahl\n        let kennzahlZeile = '  COUNT(*) AS ereignisse';\n        if (kennzahl === 'nutzer')     { kennzahlZeile = '  COUNT(DISTINCT user_pseudo_id) AS nutzer'; }\n        if (kennzahl === 'sitzungen')  { kennzahlZeile = '  COUNT(DISTINCT CONCAT(user_pseudo_id, \\'-\\', (SELECT value.int_value FROM UNNEST(event_params) WHERE key = \\'ga_session_id\\'))) AS sitzungen'; }\n        if (kennzahl === 'umsatz')     { kennzahlZeile = '  SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = \\'value\\')) AS umsatz'; }<\/p>\n<p>        \/\/ Gruppierung: alles ausser der Kennzahl\n        const gruppen = [];\n        for (let i = 0; i < spalten.length; i++) { gruppen.push(String(i + 1)); }\n\n        const zeilen = [];\n        zeilen.push('SELECT');\n        spalten.forEach(function (s) { zeilen.push(s + ','); });\n        zeilen.push(kennzahlZeile);\n        zeilen.push('FROM `' + projekt + '.' + datensatz + '.events_*`');\n        if (artikelJoin) { zeilen.push('CROSS JOIN UNNEST(items) AS artikel'); }\n        zeilen.push('WHERE _TABLE_SUFFIX BETWEEN \\'' + von + '\\' AND \\'' + bis + '\\'');\n        if (ereignis !== '') { zeilen.push('  AND event_name = \\'' + ereignis.replace(\/'\/g, '') + '\\''); }\n        if (gruppen.length) { zeilen.push('GROUP BY ' + gruppen.join(', ')); }\n        zeilen.push('ORDER BY ' + (gruppen.length ? String(gruppen.length + 1) : '1') + ' DESC');\n        zeilen.push('LIMIT 100');\n\n        \/\/ Hinweise zur Abrechnung und zu bekannten Fallen\n        if (tage > 0) { hinweise.push('tabellen'); }\n        if (kennzahl === 'nutzer' && tage > 90) { hinweise.push('naeherung'); }\n        if (artikelJoin && kennzahl === 'ereignisse') { hinweise.push('artikel_vervielfacht'); }\n        if (auswahl.indexOf('event_date') !== -1) { hinweise.push('event_date_text'); }\n        if (kennzahl === 'umsatz' && auswahl.indexOf('currency') === -1) { hinweise.push('umsatz_ohne_waehrung'); }\n        hinweise.push('intraday');<\/p>\n<p>        return {\n            sql: zeilen.join('\\n'),\n            tage: tage,\n            spalten: spalten.length,\n            artikelJoin: artikelJoin,\n            hinweise: hinweise\n        };\n    }<\/p>\n<p>    \/\/ --- Pruefpunkt ----------------------------------------------------------\n    window.LW_TEST = window.LW_TEST || {};\n    window.LW_TEST.BQ = { bauen: bauen, tagesanzahl: tagesanzahl, wertfeld: wertfeld, felder: FELDER };<\/p>\n<p>    \/\/ --- Oberflaeche ---------------------------------------------------------<\/p>\n<p>    const felderBox = document.getElementById('bq-felder');\n    const btn = document.getElementById('bq-btn');\n    const out = document.getElementById('bq-ausgabe');\n    if (!felderBox || !btn || !out) { return; }<\/p>\n<p>    function el(tag, stil, text) {\n        const e = document.createElement(tag);\n        if (stil) { e.setAttribute('style', stil); }\n        if (text !== undefined) { e.textContent = text; }\n        return e;\n    }\n    function wert(id) { const e = document.getElementById(id); return e ? e.value : ''; }<\/p>\n<p>    const VORGEWAEHLT = { 'page_location': true, 'device.category': true, 'transaction_id': true };<\/p>\n<p>    FELDER.forEach(function (f) {\n        const zeile = el('label', 'display: flex; align-items: baseline; gap: 8px; color: var(--text-secondary, #a0a0b0); font-size: 0.85rem; cursor: pointer;');\n        const kasten = document.createElement('input');\n        kasten.type = 'checkbox';\n        kasten.id = 'bq-f-' + f.id.replace(\/\\.\/g, '_');\n        kasten.checked = !!VORGEWAEHLT[f.id];\n        kasten.style.cssText = 'accent-color: var(--accent, #7ee787); flex-shrink: 0;';\n        zeile.appendChild(kasten);\n        zeile.appendChild(el('span', 'font-family: monospace; color: var(--text-primary, #e8e8ee);', f.id));\n        zeile.appendChild(el('span', 'font-size: 0.76rem; color: var(--text-secondary, #a0a0b0);', T['quelle_' + f.quelle]));\n        felderBox.appendChild(zeile);\n    });<\/p>\n<p>    const UEBERSCHRIFT = 'font-family: \\'Nunito Sans\\', sans-serif; font-weight: 700; color: var(--text-primary, #e8e8ee); font-size: 0.92rem; margin: 18px 0 8px;';<\/p>\n<p>    btn.addEventListener('click', function () {\n        const auswahl = [];\n        FELDER.forEach(function (f) {\n            const k = document.getElementById('bq-f-' + f.id.replace(\/\\.\/g, '_'));\n            if (k && k.checked) { auswahl.push(f.id); }\n        });<\/p>\n<p>        const erg = bauen({\n            projekt: wert('bq-projekt'),\n            datensatz: wert('bq-datensatz'),\n            von: wert('bq-von'),\n            bis: wert('bq-bis'),\n            ereignis: wert('bq-ereignis'),\n            kennzahl: wert('bq-kennzahl'),\n            felder: auswahl,\n            eigen: wert('bq-eigen'),\n            eigenTyp: wert('bq-eigen-typ')\n        });<\/p>\n<p>        out.innerHTML = '';\n        out.appendChild(el('div', UEBERSCHRIFT, T.h_sql));\n        out.appendChild(el('pre', 'background: var(--bg-body, #14141a); border: 1px solid var(--border, #2a2a35); color: var(--text-primary, #e8e8ee); padding: 16px; border-radius: 6px; overflow-x: auto; font-family: monospace; font-size: 0.8rem; line-height: 1.55; white-space: pre; margin: 0;', erg.sql));<\/p>\n<p>        out.appendChild(el('div', UEBERSCHRIFT, T.h_hinweise));\n        const liste = el('ul', 'margin: 0 0 0 18px; padding: 0; color: var(--text-secondary, #a0a0b0); font-size: 0.87rem; line-height: 1.65;');\n        erg.hinweise.forEach(function (h) {\n            let text = T['hinweis_' + h] || h;\n            if (h === 'tabellen') { text = text.replace('%d', String(erg.tage)); }\n            liste.appendChild(el('li', '', text));\n        });\n        out.appendChild(liste);\n    });\n})();\n<\/script><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Builds a query against the GA4 export schema with the correct UNNEST form per parameter, a date filter that actually prunes tables, and the traps that quietly change the result.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":38,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"template-tool-base.php","meta":{"footnotes":""},"tags":[91333,91258],"class_list":["post-10431","page","type-page","status-publish","hentry","tag-bigquery","tag-google-analytics-4"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>GA4 BigQuery Query Builder - Lukas Wojcik - Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"GA4 BigQuery Query Builder - Lukas Wojcik - Blog\" \/>\n<meta property=\"og:description\" content=\"Builds a query against the GA4 export schema with the correct UNNEST form per parameter, a date filter that actually prunes tables, and the traps that quietly change the result.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/\" \/>\n<meta property=\"og:site_name\" content=\"Lukas Wojcik - Blog\" \/>\n<meta property=\"article:modified_time\" content=\"2026-08-15T12:48:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/08\/og-default.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/ga4-bigquery-query-builder\\\/\",\"url\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/ga4-bigquery-query-builder\\\/\",\"name\":\"GA4 BigQuery Query Builder - Lukas Wojcik - Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/#website\"},\"datePublished\":\"2026-08-15T12:39:31+00:00\",\"dateModified\":\"2026-08-15T12:48:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/ga4-bigquery-query-builder\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/ga4-bigquery-query-builder\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/ga4-bigquery-query-builder\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Toolbox\",\"item\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/en\\\/toolbox\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"GA4 BigQuery Query Builder\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/\",\"name\":\"Lukas Wojcik - Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/#\\\/schema\\\/person\\\/895f7604f9b6b71aad9bba33af28d0f9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/#\\\/schema\\\/person\\\/895f7604f9b6b71aad9bba33af28d0f9\",\"name\":\"luky\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/07\\\/lw-x2.jpg\",\"url\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/07\\\/lw-x2.jpg\",\"contentUrl\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/07\\\/lw-x2.jpg\",\"width\":424,\"height\":636,\"caption\":\"luky\"},\"logo\":{\"@id\":\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/07\\\/lw-x2.jpg\"},\"sameAs\":[\"https:\\\/\\\/www.lukaswojcik.com\\\/blog\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"GA4 BigQuery Query Builder - Lukas Wojcik - Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/","og_locale":"en_US","og_type":"article","og_title":"GA4 BigQuery Query Builder - Lukas Wojcik - Blog","og_description":"Builds a query against the GA4 export schema with the correct UNNEST form per parameter, a date filter that actually prunes tables, and the traps that quietly change the result.","og_url":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/","og_site_name":"Lukas Wojcik - Blog","article_modified_time":"2026-08-15T12:48:46+00:00","og_image":[{"width":1200,"height":630,"url":"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/08\/og-default.jpg","type":"image\/jpeg"}],"twitter_card":"summary_large_image","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/","url":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/","name":"GA4 BigQuery Query Builder - Lukas Wojcik - Blog","isPartOf":{"@id":"https:\/\/www.lukaswojcik.com\/blog\/#website"},"datePublished":"2026-08-15T12:39:31+00:00","dateModified":"2026-08-15T12:48:46+00:00","breadcrumb":{"@id":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/ga4-bigquery-query-builder\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.lukaswojcik.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Toolbox","item":"https:\/\/www.lukaswojcik.com\/blog\/en\/toolbox\/"},{"@type":"ListItem","position":3,"name":"GA4 BigQuery Query Builder"}]},{"@type":"WebSite","@id":"https:\/\/www.lukaswojcik.com\/blog\/#website","url":"https:\/\/www.lukaswojcik.com\/blog\/","name":"Lukas Wojcik - Blog","description":"","publisher":{"@id":"https:\/\/www.lukaswojcik.com\/blog\/#\/schema\/person\/895f7604f9b6b71aad9bba33af28d0f9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.lukaswojcik.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.lukaswojcik.com\/blog\/#\/schema\/person\/895f7604f9b6b71aad9bba33af28d0f9","name":"luky","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/07\/lw-x2.jpg","url":"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/07\/lw-x2.jpg","contentUrl":"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/07\/lw-x2.jpg","width":424,"height":636,"caption":"luky"},"logo":{"@id":"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/2026\/07\/lw-x2.jpg"},"sameAs":["https:\/\/www.lukaswojcik.com\/blog"]}]}},"_links":{"self":[{"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/pages\/10431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/comments?post=10431"}],"version-history":[{"count":1,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/pages\/10431\/revisions"}],"predecessor-version":[{"id":10449,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/pages\/10431\/revisions\/10449"}],"up":[{"embeddable":true,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/pages\/38"}],"wp:attachment":[{"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/media?parent=10431"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.lukaswojcik.com\/blog\/wp-json\/wp\/v2\/tags?post=10431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}