Het ultieme SEO Search Analytics dashboard – deel2

Handleiding voor onderbouwde metingen en verbetering van SEO-prestaties sites

Dit is het tweede deel uit de serie waarin we Search Analytics-data ontsluiten vanuit de Google Search Console en via een geavanceerd dashboard kunnen presenteren en analyseren. In onderhavig deel 2 zullen we een stevige stap verder gaan dan deel 1. In deel 1 hebben we voornamelijk inzicht gekregen in de mogelijkheden en beperkingen van de Search Console data en hebben we met hulp van de Analytics Edge Add-In, een eenvoudige dagrapportage gemaakt in Excel. In dit deel gaan we de rapportage flink uitbreiden: we gaan het aantal velden in de dataset vergroten, gaan ze koppelen aan Google Analytics en presenteren in Data Studio. Als u op voorhand meent dat het artikel (voor nu) wat te ingewikkeld wordt, lees dan in ieder geval even het Nawoord, onderaan de pagina.

1. De voorbereiding

Allereerst: Excel is mijns inziens onvoldoende geschikt voor een professioneel SEO dashboard. Excel is perfect voor het uitvoeren van allerlei berekeningen op grote hoeveelheden data, maar het is een slecht communicatiekanaal tussen de data-engineer (het SEO bedrijf of de SEO-marketeer) en de klant c.q. leidinggevende. 

Nu heeft Google het afgelopen jaar “Data Studio” (sinds februari j.l. ook in Nederland) uitgerold. Via dat pakket, werkzaam in de cloud, kan je op zeer eenvoudige manier complexe, interactieve datapresentaties maken. Dit is precies wat we nodig hebben. Door Data Studio te koppelen aan onze -wat zij noemen- “gegevensbron” kunnen we onze data naar eigen wens als dashboard vormgeven en via een simpele URL met derden communiceren. Als we die derden schrijfrechten geven, kunnen ze zelfs meewerken aan de verdere vormgeving van het dashboard.

Nu kent Data Studio verschillende connectoren voor gegevensbronnen. Er is een connector rechtstreeks voor Google Search Console data, voor Google Analytics data, voor Google sheets, voor AdWords, voor BigQuery, voor Youtube, etc. Maar er is geen connector voor Excel (behalve via een .csv-bestand, maar het steeds opslaan van .csv-bestanden vanuit Excel is in de praktijk te omslachtig). Er blijkt wel een connector voor MySQL. Als we de Excel-data kunnen koppelen aan een MySQL-database, zijn we dus klaar. En gelukkig kan dat: er is een addon voor Excel, namelijk Excel to MySQL, om de Excel-gegevens te koppelen met MySQL. Op die manier kan onze Excel-data dus worden gekoppeld met Data Studio.

Even een stapje terug: waarom hebben we Excel eigenlijk überhaupt nodig, waarom gebruiken we niet gewoon rechtstreeks de Google Search Console-connector binnen Data Studio? Dan hebben we toch rechtstreeks de gegevens in Data Studio? Dat klopt inderdaad, en ik raad u aan dat ook eens te gaan doen (met name voor het leren kennen van Data Studio), maar we hebben dan wel te maken met drie belangrijke beperkingen:

1. Onze data wordt maar 90 dagen door Google wordt bewaard, terwijl het voor SEO interessant is data over langere periodes te kunnen bekijken en analyseren.
2. Er kunnen in Data Studio weliswaar extra data-velden worden toegevoegd, maar daar kunnen slechts eenvoudige berekeningen aan worden gekoppeld. Binnen Excel kunnen we via veel geavanceerde functies, kolommen aan onze data toevoegen die we vervolgens via Data Studio in beeld kunnen brengen. Een voorbeeld van een extra kolom is “Brand” met de waarde 0 of 1. We kunnen de zoekwoorden op basis van een lijst varianten van ons merk of onze bedrijfsnaam (in mijn geval seoguru, seo guru, seo goeroe, etc.) markeren als brand=0 of brand=1. Zodoende kunnen we ook de verhouding in beeld brengen van de hoeveelheid zoekwoorden die gericht zijn op ons bedrijf/merk (ook wel “navigational” zoekopdrachten genoemd) en de overige zoekwoorden. Een andere mogelijkheid van extra data-velden zijn ‘opportunity’-velden. We kunnen via formules bepalen waar kansen liggen op bijvoorbeeld het gebied van ctr (te laag voor de positie, dus aanbeveling tot verbetering van snippet), op het gebied van scoringsposities (tussen de 10 en de 20: kleine seo verbeteringen kunnen maken dat de site opschuift naar de eerste pagina), etc. 
3. In Excel kunnen we data uit meerdere bronnen combineren. Denk aan Google Analytics, Google AdWords en de Google zoekwoordplanner, maar ook aan data uit backlink-analysetools zoals ahrefs, majestic of moz. Ook zouden we data vanuit de Screaming Frog SEO Spider tool kunnen betrekken bij de dataverwerking. Over dit derde punt meer na de afbeelding, hieronder.

Excel is dus eigenlijk onze berekeningslaag. MySQL is daarbij onze opslaglaag. MySQL is erg geschikt als opslaglaag, omdat je in een MySQL-database gigantische hoeveelheden data kwijt kan. MySQL is bovendien het meest gebruikte database managementsysteem op het web. Bij iedere provider kan een MySQL-database worden aangevraagd.

Schematisch ziet de hiervoor beschreven aanpak er nu als volgt uit:

U ziet dat ik Google Analytics hier via een stippellijn heb gekoppeld aan Excel. Dat kan namelijk ook. Misschien willen we gegevens uit beide bronnen combineren alvorens we e.e.a. gaan koppelen aan Data Studio. Op basis van pagina-URL kunnen we dat doen, waardoor we de hele funnel van zoekopdracht->impressies->klikken->gebruikersgedrag (weigeringspercentage, etc)->doelen in beeld kunnen brengen.

We kunnen Google Analytics ook rechtstreeks koppelen aan de Data Studio. Zodoende kunnen we andere zaken die ook voor SEO van belang zijn (zoals weigeringspercentage, tijd op de pagina, laadsnelheden van pagina’s, etc.) ook in beeld brengen. Maar we kunnen die gegevens dan dus niet koppelen aan de URL’s uit de Search Console. Het worden dan afzonderlijke presentaties.

Maar in Google Analytics zelf (bij de gekoppelde Search Console gegevens) kunnen we tegenwoordig toch al precies die hele funnel zien? In antwoord hierop kan ik allereerst vertellen dat de Search Console-data niet via de Analytics connector op te halen is, daar de Analytics API hier nog geen ingang toe geeft. Ten tweede toont ook Analytics de Search Console gegevens maar voor een periode van 90 dagen. Tot slot is de weergave beperkt: we kunnen bijvoorbeeld niet in beeld brengen welke URL’s er allemaal impressies hebben gekregen, gegeven een zoekopdracht. Voor SEO is het wel belangrijk dit in beeld te hebben. Kortom: via de Excel-berekeningslaag kunnen we precies doen wat in Google Analytics ook al gebeurt, maar dan veel uitgebreider. Bovendien kunnen we via de hier beschreven methode ons eigen dashboard (in Data Studio) realiseren. In Google Analytics kan je ook eigen dashboards maken, maar ook daar zijn de Search Console gegevens (ook al zijn die gekoppeld aan Analytics) weer niet toegankelijk.

Voor ik overstap naar de presentatielaag wil ik nog één ding opmerken. In plaats van Excel kunnen we in principe ook gebruik maken van Google Sheets. Dat ligt zelfs meer voor de hand, daar Data Studio ook een rechtstreekse connector voor Google Sheets heeft. Google Sheets zou dan ook als opslaglaag moeten dienen. Er is zelfs een mooie addon voor Sheets, onder de toepasselijke naam “Search Analytics for Sheets”, om (net als Analytics Edge bij Excel) de Search Analytics data op te halen. Die addon kan worden gevonden op mijn seo tools pagina. Ik heb met die oplossing om deze goede redenen dan ook uitgebreid geëxperimenteerd, maar ook weer verworpen. Waarom? Omdat Google Spreadsheets voor deze oplossing een te lage datalimiet heeft. Bij een beetje site loop je al snel tegen de grenzen aan. De combinatie van Excel plus MySQL is veel krachtiger, zowel qua rekencapaciteit als opslagcapaciteit, dan Google Sheets alleen.

Nadeel van het gebruik van Excel is wel het feit dat we een Windows-machine nodig hebben voor de datavergaring. Op een Apple machine is het nogal lastig om de genoemde Addons draaiende te krijgen, tenzij er een virtuele Windows machine is geïnstalleerd op de Apple. Het eindproduct, het dashboard, is overigens wel altijd te zien op zowel Windows als Apple, daar die eenvoudigweg via iedere browser bereikbaar is. De data-engineer moet in dit proces dus een Windows-machine tot zijn/haar beschikking hebben, maar de klant of leidinggevende kan de resultaten op iedere machine, ook mobiel, prachtig inzien.

2. Het dashboard

Ik heb de methode toegepast op de data van een klant van mij. Het dashboard zelf is daarbij dus gerealiseerd via Google Data Studio. Hieronder heb ik een aantal screenshots opgenomen van verschillende pagina’s van het dashboard. Per pagina is het mogelijk filters in te stellen, waardoor de data op allerlei mogelijke manieren kan worden onderzocht. 

Merk hier boven op dat we hier inderdaad de gegevens over een veel langere periode zien, dan over de periode van 90 dagen. Nu volgt een voorbeeld van een tijdlijn met een overzicht van impressies en klikken, vergeleken met de vorige periode.

En hier volgt nu een voorbeeld met een overzicht van gevonden pagina’s (de landingspagina’s), met de zoekopdracht (query) als input. Er kan verder worden verfijnd via de month, country en device filters.

En natuurlijk een voorbeeld van filters die in Excel zijn gedefinieerd, en de zoekwoorden indeelt in vooraf gedefinieerde categorieën. Hierdoor kan heel mooi het aandeel van de verschillende categorieën van zoekopdrachten op het totaal van zoekopdrachten worden ingezien.

3. Nog een klein, maar belangrijk, puntje op de ‘i’

Tot slot van dit artikel wil ik nog een laatste beperking van de Google Search Console data, oplossen. In deel 1 hebben we gezien dat de data (clicks, impressions, etc.) binnen de Search Analytics van de Google Search Console niet volledig is voor de zoekwoorden (queries). Google geeft zelf aan dat niet alle zoekwoorden kunnen worden getoond om privacy-redenen. Het effect hiervan is dat als we in Excel totalen gaan berekenen, die totalen niet kloppen. Ik heb wat experimenten bij verschillende sites uitgevoerd en gemiddeld blijkt ongeveer 25% van de zoekopdrachten onbekend. 

Dit probleem kan relatief eenvoudig worden opgelost omdat we de totalen wél kennen. Stel dat we in Excel (via de Analytics Edge connector) records met de volgende structuur, binnen een opgegeven tijdsperiode (bijvoorbeeld een maand) willen ophalen:

Input: Page, Query, Device en Country
Output: Clicks, Impressions, CTR en Position

We krijgen dan een hele lijst, vaak duizenden, records in ons Excel-bestand. Als we Excel de totalen laten bepalen van Clicks, Impressions, etc. dan kloppen die totalen dus niet. Door een tweede tabel op te halen, namelijk zonder het Query-veld, krijgen we een bestand waarvan de totalen wél kloppen. We kunnen dus precies berekenen wat de clicks, impressions, ctr en position geweest is voor de onbekende queries. Door nu een record met Query=”(unknown)” voor iedere Page, Device, Country-combinatie toe te voegen aan de eerste tabel, en aan te vullen met de berekende waarden, worden de totalen alsnog correct.

NB1. Als we de Search Console hebben gekoppeld met Google Analytics, dan zijn de Search Console gegevens te zien onder de menu-optie Acquisitie-Search Console. Als we vervolgens op ‘Zoekopdrachten’ klikken, dan zien we dat Google Analytics exact hetzelfde doet als wat ik hiervoor voorstel en noemt die onbekende zoekopdrachten “(other)”. Dan rijst de vraag waarom we die gegevens dan niet meteen uit Google Analytics halen. Het antwoord is dat de Google Analytics API die Search Console data vooralsnog niet aanbiedt. Dit nog los van het feit dat ook in Google Analytics de Search Analytics data maar 90 dagen bewaard blijft. Die beperking wilden we met met ons dashboard juist óók oplossen.

NB2. De wijze van berekenen van clicks en impressions voor het (unknown)-record zullen duidelijk zijn. De ctr, vervolgens, is eenvoudigweg de berekende clicks gedeeld door de berekende impressions. De berekening van de position is echter wat complexer. Daar moet rekening worden gehouden met het feit dat de gemiddelde positie over het totaal van alle posities (inclusief die van het unknown-record) niet een gemiddelde is, maar een gewogen gemiddelde. Die weging vindt plaats over de impressies. In Excel hebben we daar de formule SUMPRODUCT voor.

NB3. Bij wat grotere sites resulteert het weglaten van alleen het Query-veld in te veel records. In dat geval kan ook worden besloten om alleen de totaalgegevens (via Analytics Edge) over de betreffende periode op te halen, d.w.z. de totale Impressions, Clicks, etc. onafhankelijk van Page, Device en Country. Op basis daarvan kunnen we één (unknown)-record bepalen waarbij niet alleen de Query, maar ook de Page, Device en Country de waarde (unknown) krijgen.

Nawoord

Tot zover deel 2 van deze serie. Ik kan mij voorstellen dat het u, als u dit zo allemaal heeft doorgenomen, wat duizelt en dat er wat vrees is ontstaan om dit zelf zo te gaan doen. Ik zit er daarom over te denken een training te organiseren waar we met een groepje van maximaal 8 personen gezamenlijk in één dag ons eigen dashboard via de hier beschreven methode in elkaar gaan zetten. Op het eind van de dag heeft u dan uw eigen, ultieme SEO Search Analytics dashboard. U kunt het dashboard met de opgedane kennis desgewenst zelf nog verder uitbreiden met gegevens, bijvoorbeeld vanuit AdWords of andere bronnen.

Een beetje affiniteit met tools, is daarbij wel van belang. Bovendien moet u de beschikking hebben over een Windows-machine, Excel geïnstalleerd hebben en toegang hebben tot een MySQL-database. Als u hier interesse in heeft, laat mij dat dan even weten van een mailtje! Als u liever heeft dat SEOguru het dashboard voor u opzet en eventueel onderhoudt, dan kan dat ook. 

Vragen en opmerkingen kunt u hieronder plaatsen en worden zeer gewaardeerd!

Toevoeging 10 augustus 2017: vanwege de grote belangstelling voor een SEO training Search Analytics, heb ik maar meteen een training ingepland. U kunt hier meer lezen over die training.