Vorige week zette ik voor een klant met vijf landenwebshops een BigQuery-warehouse op. Google Ads, vijf GA4-properties en zes Search Console-properties, allemaal in hetzelfde project. Toen dat stond, deed ik iets wat ik een jaar geleden nog zelf zat uit te tikken:
Hier staat de dataset. Maak tien visualisaties die je zelf de moeite waard vindt, volg de viz-skill, en zet onder elke grafiek de query die je hebt gedraaid.
Daarna ging ik iets anders doen. Twintig minuten later stonden er tien. Vier waren raak, drie waren aardig, drie kon ik meteen weggooien. Mijn antwoord was: "1, 5, 7 en 9 hou ik. Maak nu ook de verdeling van organische posities voor de zoektermen waar we op bieden."
Het gaat me niet om het uitbesteden van SQL, die schrijf ik zelf net zo snel. Het gaat erom dat ik pas hoef te kiezen welke vraag de moeite waard is nadat ik tien uitwerkingen voor me heb liggen. Moet je die keuze maken voordat je iets ziet, dan kies je bijna altijd de vraag die je vorige maand ook al stelde.
Hieronder de hele werkwijze: welke bron je als eerste aanzet, op welke drie sleutels de kanalen aan elkaar zitten, welke vier fouten je cijfers stukmaken, en wat er in die viz-skill hoort te staan.
Eerst de harde regel: één project, één locatie
BigQuery joint niet over locaties heen. Een dataset in EU en een dataset in US zijn voor een query twee verschillende werelden, en je merkt het pas op het moment dat je ze wilt combineren. Dat is ook het moment waarop je het niet meer eenvoudig kunt terugdraaien, want een Search Console-export begint opnieuw op nul zodra je hem verplaatst.
Kies dus één GCP-project met alle datasets in dezelfde locatie, en maak die keuze op dag één. Voor Europese klanten is dat EU. Let op dat de transfer-CLI daar een eigen naam voor gebruikt: bij bq ls --transfer_config heet de locatie europe, niet eu. Zoek je op eu, dan krijg je een lege lijst terug en denk je dat je transfer niet bestaat.
Wat je aanzet, en in welke volgorde
Search Console als eerste, altijd
De bulk-export van Search Console kent geen historische backfill. Hij begint te vullen op de dag dat je hem aanzet en niet eerder. Daarmee is het de enige bron waarbij uitstel je echt data kost. Zet hem aan op de dag dat je het project aanmaakt, ook als je pas over drie maanden aan analyse toekomt.
Wat vaak misgaat: de export vraagt om IAM-rechten die je zelf moet toekennen voordat Search Console iets mag wegschrijven. Het serviceaccount search-console-data-export@system.gserviceaccount.com heeft roles/bigquery.jobUser en roles/bigquery.dataEditor op het project nodig. Vergeet je dat, dan geeft de Search Console-interface een foutmelding die niet vertelt welk recht ontbreekt.
Je krijgt drie tabellen: searchdata_site_impression (geaggregeerd per property), searchdata_url_impression (per URL) en ExportLog. Die laatste gebruik je om te controleren of een dag daadwerkelijk geleverd is.
Google Ads via de Data Transfer Service
Deze bron heeft wel backfill, dus die kun je later aanzetten. Houd er rekening mee dat het aanmaken van de transfer via de CLI om een interactieve OAuth-stap vraagt, waarbij je een version_info-string moet plakken. Dat werkt niet in een script en niet in een agent-sessie, alleen in een echte terminal. Het is de enige stap in deze hele setup die je niet kunt automatiseren.
Je krijgt tientallen tabellen, allemaal met je klant-id als achtervoegsel. De vier die je in de praktijk gebruikt:
p_ads_Campaign_<klantid>, de dagelijkse snapshot van campagnenamen en instellingenp_ads_CampaignBasicStats_<klantid>, de cijfers per campagne per dagp_ads_AdGroupBasicStats_<klantid>, hetzelfde een niveau lagerp_ads_ClickStats_<klantid>, de tabel met gclid per klik, en daarmee de brug naar GA4
GA4, per property apart
Elke property krijgt een eigen dataset analytics_<propertyid>. Bij vijf landen zijn dat vijf datasets, en dat is prima: je voegt ze later samen in één view. De datasets verschijnen pas na de eerste dagelijkse export, dus ongeveer een etmaal na het koppelen. Tot die tijd zie je in bq ls niets en lijkt de koppeling mislukt.
Ook GA4 vult niet met terugwerkende kracht. De export begint op de dag dat je koppelt, dus ook hier is eerder aanzetten beter dan later.
De drie sleutels waarop kanalen aan elkaar zitten
Dit is het deel dat bepaalt of het warehouse iets oplevert. Drie bronnen naast elkaar in hetzelfde project blijven anders gewoon drie losse dashboards. Er zijn precies drie sleutels die ze zonder aannames aan elkaar knopen.
1. Datum
De grofste sleutel, en de enige die in alle drie de bronnen zit. De valkuil is het formaat en de tijdzone. De Ads-transfertabellen zijn gepartitioneerd op _DATA_DATE, in de tijdzone van het Ads-account. GA4 heeft event_date als string in de vorm 20260818, in de tijdzone van de property, en daarnaast event_timestamp in UTC-microseconden. Search Console gebruikt data_date, een echte DATE, en die dagindeling loopt op Pacific Time.
Staan het Ads-account en de GA4-property in dezelfde tijdzone, dan sluiten _DATA_DATE en event_date op elkaar aan. Het gaat mis zodra je de datum uit event_timestamp afleidt: bij een Nederlandse klant schuiven in de zomer de laatste twee uur van elke dag dan naar de dag erna. Op maandcijfers valt dat weg. Op dagcijfers rond een campagnestart zie je het meteen, en dan zoek je een fout die er niet is.
2. Campagne-id
De koppeling tussen Ads en GA4 zonder gedoe. GA4 zet de Ads-campagne in een eigen struct: session_traffic_source_last_click.google_ads_campaign, met daarin campaign_id en campaign_name. Die waarde komt uit de gclid-koppeling tussen de twee platforms, dus hij is er alleen als de accounts gelinkt zijn en auto-tagging aanstaat.
Gebruik campaign_id voor de join en campaign_name alleen voor het label. Namen veranderen, ids niet.
3. Gclid
De fijnmazigste sleutel. p_ads_ClickStats_<klantid> bevat per klik de gclid met het bijbehorende campagne-, advertentiegroep- en zoekwoord-id. In GA4 staat diezelfde gclid in collected_traffic_source.gclid. Koppel je die twee, dan heb je klik, sessie en order in één rij, ook voor de klikken die GA4 zelf als "direct" wegschrijft omdat de gebruiker later via een bookmark terugkwam.
Dat is de enige route naar omzet die wel uit een campagne komt, maar waar GA4 geen bron bij heeft staan.
En Search Console dan
Search Console heeft geen campagne en geen gclid. Die koppel je op zoekterm en op landingspagina-URL. Dat is minder precies, maar het is de enige brug tussen betaald en organisch die geen aannames vereist. Op URL-niveau werkt hij het beste, omdat zoektermen aan de Ads-kant al door het matchtype heen zijn gehaald en aan de Search Console-kant deels geanonimiseerd zijn.
Vier dingen die je cijfers stukmaken
Kosten staan in micros
Cost in de Ads-transfertabellen is de werkelijke kost maal een miljoen. Deel door 1e6, altijd. Werk je met meerdere accounts in verschillende valuta, dan tel je bovendien euro's bij ponden op zonder dat iets je waarschuwt.
De campagnetabel is een dagelijkse snapshot
Join je p_ads_Campaign_ aan p_ads_CampaignBasicStats_ alleen op CampaignId, dan vermenigvuldig je je kosten met het aantal dagen dat de campagne bestaat. Je moet ook op de datumpartitie joinen. Dit is de meest gemaakte fout in dit soort warehouses, en hij valt niet op omdat het resultaat er plausibel uitziet.
SELECT
s._DATA_DATE AS datum,
c.CampaignName,
SUM(s.Cost) / 1e6 AS kosten,
SUM(s.Clicks) AS klikken,
SUM(s.Conversions) AS conversies
FROM `project.google_ads.p_ads_CampaignBasicStats_1234567890` s
JOIN `project.google_ads.p_ads_Campaign_1234567890` c
ON c.CampaignId = s.CampaignId
AND c._DATA_DATE = s._DATA_DATE -- zonder deze regel klopt niets
WHERE s._DATA_DATE BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND CURRENT_DATE()
GROUP BY 1, 2Ads herschrijft historie, GA4 niet
Conversies komen dagen na de klik binnen en worden teruggeschreven naar de klikdatum. De ROAS van vorige week is vandaag een ander getal dan gisteren, precies zoals het systeem bedoeld is. GA4 telt op de dag van het event en herschrijft niets.
Gevolg: een grafiek die Ads-conversies en GA4-conversies over dezelfde recente periode naast elkaar zet, laat een gat zien dat vanzelf kleiner wordt. Sluit de laatste zeven tot veertien dagen uit als je die twee vergelijkt, of vergelijk alleen perioden die volledig afgesloten zijn.
Search Console anonimiseert zoektermen met te weinig volume
Zoektermen die maar door een handvol mensen zijn ingetypt, houdt Google om privacyredenen achter. Die vertoningen en klikken komen wel binnen, maar met is_anonymized_query = true en een lege query. Het volume telt dus mee in je totaal, de zoekterm erachter niet. De som van je zoektermen is daardoor altijd lager dan je totaal aantal vertoningen, en behandel je die som als honderd procent, dan kloppen al je aandelen niet.
Nog een detail dat vaak fout gaat: de gemiddelde positie zit er niet als kolom in. Je rekent hem uit als SUM(sum_top_position) / SUM(impressions) + 1 in de site-tabel, en met sum_position in de URL-tabel. Die plus één staat er omdat de posities nul-geïndexeerd worden geëxporteerd.
SELECT
query,
SUM(impressions) AS vertoningen,
SUM(clicks) AS klikken,
SUM(sum_top_position) / SUM(impressions) + 1 AS gemiddelde_positie
FROM `project.searchconsole_nl.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND is_anonymized_query = FALSE
GROUP BY query
HAVING gemiddelde_positie <= 3
ORDER BY vertoningen DESCDan pas: tien visualisaties
Met die drie bronnen aan elkaar valt er iets te vragen. Vanaf dat punt loont het om niet zelf te bedenken wat je wilt zien.
Wat ik de agent meegeef, in deze volgorde:
- Lees eerst de structuur, raad niets.
INFORMATION_SCHEMA.COLUMNSper dataset, plus éénLIMIT 10per tabel die hij wil gebruiken. Zonder die instructie verzint een model kolomnamen die logisch klinken en niet bestaan. - Een kostenplafond.
maximum_bytes_billedop de queries. Een GA4-export van vijf properties is groot genoeg om een verkeerdeSELECT *zichtbaar te maken op je factuur. - De query onder elke grafiek. Zonder die query kun je het getal niet controleren, en dan weet je bij een afwijking niet of de grafiek fout is of je aanname.
- De viz-skill. Laat je die weg, dan krijg je tien grafieken in tien stijlen en zit je alsnog een uur te uniformeren.
Wat er in de viz-skill hoort
Een skill is niet meer dan een map met een instructiebestand dat de agent automatisch oppakt. Voor visualisaties zijn dit de regels die het meeste opleveren:
- De titel van elke grafiek is de vraag die hij beantwoordt, niet de naam van de metriek. "Waar betalen we voor posities die we al hebben" in plaats van "CPC per zoekterm".
- Vaste kleuren per kanaal, over alle grafieken heen. Betaald is altijd dezelfde kleur, organisch ook.
- Geen dubbele y-assen. Twee schalen in één beeld nodigen uit tot verbanden die er niet zijn.
- Geen taartdiagrammen boven de vier segmenten.
- Bij elke grafiek het aantal rijen en de periode waarop hij gebaseerd is, in het bijschrift.
- Onvolledige laatste periode altijd apart markeren of weglaten. Anders lijkt elke reeks te eindigen in een daling.
Die laatste regel is de belangrijkste, en de reden staat hierboven: door de conversielag wordt elke verse grafiek een neergaande lijn.
Wat je zelf moet nakijken
Van de tien houd ik er meestal drie tot vijf. Die verhouding hoort erbij. De eerste ronde gebruik je om te ontdekken welke vragen deze dataset aankan, de tweede ronde om te krijgen wat je nodig hebt.
Waar het uiteindelijk om gaat is of het getal klopt. Pak van de grafieken die je houdt de query erbij en controleer één cijfer tegen de interface van het bronsysteem. Wijkt het meer dan een paar procent af, dan zit er bijna altijd een van de vier bovenstaande fouten in. In mijn ervaring is het meestal de snapshot-join of de conversielag.
De vragen die dit mogelijk maakt
Dit is waar het om begonnen was: vragen die je met drie losse interfaces niet kunt stellen, en met één gekoppelde dataset in een paar minuten wel.
- Op welke zoektermen betalen we terwijl we organisch al in de top drie staan, en wat kost dat per maand?
- Welke campagnes leveren sessies die GA4 als direct wegschrijft, en hoeveel omzet hangt daaraan?
- Welke landingspagina's krijgen betaald verkeer maar hebben nauwelijks organische vertoningen? Dat is meestal een indexatie- of contentprobleem, geen biedprobleem.
- Waar lopen de conversieratio's tussen de landenwebshops uiteen bij vergelijkbaar verkeer, en zit het verschil in de campagne of in de site?
- Hoe lang is de werkelijke conversielag per markt? Die meet je één keer en gebruik je daarna in elke rapportage.
Daar is geen nieuw dashboardproduct voor nodig. Het vraagt dat de kanalen op dezelfde sleutels aan elkaar zitten, en dat iemand tien voorstellen doet waarvan jij er vier houdt.