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."
De winst zit niet in het uitbesteden van SQL. Die schrijf ik zelf net zo snel. De winst zit erin dat ik pas hoef te kiezen welke vraag de moeite waard is nadat ik tien uitwerkingen voor me heb. 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: hoe je de kanalen zo aan elkaar knoopt dat er iets te vragen valt, welke sleutels dat zijn, welke vier dingen 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 precies het moment waarop je het niet meer eenvoudig kunt terugdraaien, want een Search Console-export begint opnieuw op nul zodra je hem verplaatst.
Dus: één GCP-project, alle datasets in dezelfde locatie, en die keuze maak je 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. Dat maakt hem tot de enige bron waarbij uitstel je daadwerkelijk 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 is niet decoratief, daarin zie je of een dag daadwerkelijk geleverd is.
Google Ads via de Data Transfer Service
Deze heeft wel backfill, dus die kan later. Reken op één ding: het aanmaken van de transfer via de CLI vraagt om een interactieve OAuth-stap 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 unioneert ze later in een 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.
De export heeft wel een historisch gat van maximaal een dag maar geen echte backfill, dus ook hier geldt: eerder aanzetten is beter dan later.
De drie sleutels waarop kanalen aan elkaar zitten
Dit is het deel waar het warehouse zijn geld verdient. Drie bronnen naast elkaar in hetzelfde project leveren nog steeds drie losse dashboards op. Er zijn precies drie sleutels die ze zonder aannames aan elkaar knopen.
1. Datum
De grofste sleutel, en de enige die tussen alle drie de bronnen werkt. De valkuil zit in het formaat en de tijdzone. Ads-transfertabellen zijn gepartitioneerd op _DATA_DATE in de tijdzone van het Ads-account. GA4 gebruikt event_date als string in de vorm 20260818, plus event_timestamp in UTC-microseconden. Search Console gebruikt data_date als echte DATE.
Voor een Nederlandse klant scheelt dat in de zomer twee uur, wat betekent dat de laatste twee uur van elke dag in GA4 aan de volgende dag hangt in Ads. 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 de gclid in de event-parameters van de sessie. 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.
Dit is de enige manier om te zien hoeveel omzet je aan een campagne toeschrijft terwijl GA4 er geen bron bij heeft.
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. Draai 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
Zeldzame zoektermen worden weggelaten en gemarkeerd met is_anonymized_query. De som van je zoektermen is daardoor lager dan je totaal aantal vertoningen. 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 is er iets te vragen. En dat is het moment waarop het loont 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. Zonder die krijg je tien grafieken in tien stijlen, en dan 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: de conversielag maakt van elke verse grafiek een neergaande lijn.
De review is het echte werk
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 je zelf naar moet blijven kijken: klopt het getal. 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
Ter afsluiting, want 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:
- 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.
Er zit geen nieuw dashboardproduct achter. Wat het vraagt is dat de kanalen op dezelfde sleutels aan elkaar zitten, en dat er iemand tien voorstellen doet waarvan jij er vier houdt.