SEO-automation met Google Sheets: een praktisch voorbeeld

Voordat je verder leest, moet je dit weten: dit zijn 2 verhalen in 1.

Aan de ene kant wilde ik laten zien dat je moet optimaliseren op basis van intenties i.p.v. zoekwoorden. Dat dat niet alleen goed mogelijk is, maar simpelweg beter werkt.

Aan de andere kant wilde ik inspireren door te laten zien hoe je Google Sheets kunt gebruiken als een soort superhandig Zwitsers zakmes dat in principe alles kan. En dat het je werk en centen scheelt.

Wil je de hele presentatie bekijken? Die staat hier.

Ik ga hieronder niet ALLE slides na, maar alleen de inhoudelijke. Uiteraard wel in dezelfde volgorde ;)

Ik begon met een lofzang op Google Sheets: een soort Zwitsers zakmes van data, API's, scrapers, en tekstgeneratie.

Het kan veel, heel veel. Maar realiseer je wel dat het, net als een Zwitsers zakmes, individueel nergens echt heel goed in is.

Het is juist de combinatie van allerlei tools die het in zich heeft wat het krachtig maakt. Een ideale werkomgeving voor snelle automatiseringen.

Er zitten meerdere handige functionaliteiten verstopt in Google Sheets, die heel nuttig zijn voor SEO.

Zo is daar de =IMPORTXML() functie, die je kunt gebruiken om te scrapen. Ik noem maar wat: een XML-sitemap van je concurrent, zodat je een lijstje hebt van al hun diensten en hoe ze die diensten noemen.

Data kun je verrijken, door bijvoorbeeld keywords te clusteren met Apps Script of de =REGEXMATCH() functie. Handig! En ook essentieel voor dit verhaal.

Die data kun je filteren, of sorteren (bijvoorbeeld op een bepaalde custom metric (kom ik nog op terug), zodat je de belangrijkste bovenaan hebt staan).

Of de mogelijkheid om vrij eenvoudig teksten te genereren (ik noem maar wat: meta-titles) op basis van waarden die je uit je dynamische data gehaald hebt. Dit stukje zou je ook 'programmatic SEO' moeten noemen trouwens.

En al die kolommen en sheets en dergelijke, kun je met =QUERY() of met Pivot tables vrij eenvoudig combineren tot een enkele tabel. Zodat je toe kunt werken naar 1 kick-ass master tabel waar alles in staat.

Deze artikelen gaan verder in op wat je met Google Apps Script kunt doen qua scraping. Best veel ...

Even voor alle duidelijkheid: dit is slechts een voorbeeld, een kapstok om het verhaal aan op te hangen. Deze site bestaat letterlijk niet echt (meer).

Waar het om gaat, is dat het een typische website is zoals er veel meer zijn.

En dat deze site uitdagingen had die niet met klassieke SEO opgelost konden worden.

Want wat is het geval?

Geen sinecure dus om dit om te draaien ;)

Nog even los van de specifieke genoemde uitdagingen, was het duidelijk dat klassieke SEO voor deze site niet werkte.

De standaard strategie is het om dan maar voor alle locaties in Nederland (dat zijn er een aantal duizend) landingspagina's te maken. Met alle duplicate content issues en onderhoudskosten als gevolg.

En natuurlijk om vanaf je homepage naar de 25 grootste steden te linken. Zeg maar, die plaatsen waar iedereen zich al op richt.

En tenslotte om je conversiepagina's helemaal vol te plempen met keywords die in nikszeggende teksten gefrot worden. Een feestje om te lezen. Converteert ook supergoed. Not.

Een tactiek die ik graag 'moderne SEO' noem. Waarbij we ons niet richten op keywords, maar op de intenties die erachter liggen.

Wat ik doe, is keywords gebruiken als data. Die data zegt iets over het probleem, de angst, de behoefte, de ergernis, die iemand heeft waarvoor die persoon een oplossing zoekt. De vraag achter de vraag. Het vertrekpunt achter de zoektocht. De intentie dus. De vraag waarop je antwoord moet geven, waarvan een keyword slechts een oppervlakkige uiting is.

En als je dit op enige schaal in een competitieve markt wil doen, dan schiet het niet op om dit hele psychologische onderzoek per pagina uit te voeren. In plaats daarvan moet je opschalen. Automatiseren. Data-driven werken.

Belangrijk om te vermelden, is dat die intentie-gebaseerde manier van werken begint bij de analyse, maar absoluut doorwerkt naar de uiteindelijke bouw van je content en je landingspagina's:

Wat we dus gaan doen, is het maken van een 'master tabel', die een overzicht geeft van alle te maken pagina's, van welk type die pagina's zijn (op welke soort intentie ze inhaken), welke woorden daarbij horen, welke meta-teksten, en in welke volgorde we die pagina's moeten gaan maken.

Bovenaan beginnen. Prioritering wordt verzorgd door de data.

De tabel bevat ook 'dynamische' data. Dat wil zeggen: we gaan een algoritme maken dat dit allemaal voor ons doet, en dat algoritme toetsen we dagelijks aan verse Search Console data. Onze prioriteitenlijst is dus altijd up to date, en kan dus ook veranderen. Dat is juist de bedoeling!

Dit is het meest interessante onderdeel uiteraard :)

Wat we nu willen weten, is ALLES wat in 'de markt' gezocht wordt.

Het gaat dus NIET om het vinden van de keywords waar wij op gevonden willen worden. Dat komt later.

We willen nu eerst leren vanuit welke soorten intenties ('intentie-typen' noem ik ze) er gezocht wordt:

Gebruik alle klassieke SEO-tactieken om deze data te verzamelen. Scrape concurrenten, praat met klanten, marketeers, en sales-mensen (vooral handig om de emotionele triggers te vinden!), gebruik keyword-data tools om alle woorden te verzamelen.

Nu gaan we al deze input gebruiken om een algoritme te maken dat onze eigen SEO-prestaties kan aflezen als een open boek.

Wij clusteren hier geen keywords, maar intenties. De techniek is echter hetzelfde: we bundelen groepen keywords bij elkaar en geven dat groepje een naam. Die naam is echter geen onderwerp, maar een intentie. Big difference.

De manier waarop je al die eerder verzamelende keyword data kunt bevragen, is door naar achterliggende patronen te zoeken. Daarvoor kun je beginnen met de 5W's: Wie, Wat, Waar, Wanneer, Waarom en Hoe. Hoe is ook een W ;)

Enkele voorbeelden:

Waarom nou zoveel data gebruiken?

Heeft meerdere doelen. Je leert de markt kennen. Dat moet je sowieso doen.

Daarnaast zal de markt veel meer data bevatten dan alleen de keywords waar je zelf (al) zichtbaar op bent. Dus dat is simpelweg beter onderzoeksmateriaal.

En het doel is hier niet om keyword research te doen voor onze website, maar om te bepalen welke intenties er zijn in de markt, en hoe we die kunnen koppelen aan onze eigen prestaties en kansen. Ik herhaal:

De verzamelde keyword data geeft inzicht in welke woorden mensen gebruiken als ze zoeken vanuit een bepaalde intentie. Die woorden gaan we nu clusteren.

Clusteren kan op allerlei manieren, maar wat je moet onthouden is dit: je hebt niet alleen een individueel keyword nodig, maar ook de context waarin dat keyword zich bevindt.

Dus als je alle keywords hebt verzameld van een markt, en iemand in die markt gebruikt het woord 'snel', dan weet je dat de context is dat iemand een snelle oplossing voor een probleem wil, of wil dat een expert snel langs komt om een probleem te fixen.

Of als iemand het woord 'koopwoning' gebruikt, dat die persoon niet op zoek is naar een koopwoning, maar dat die persoon waarschijnlijk een probleem heeft in zijn of haar koopwoning en wellicht wil weten wie verantwoordelijk is voor de kosten van de oplossing van het probleem ;)

Hoe bepaal je context van een woord? Dat doen wij door onze eigen inzichten te gebruiken. We zeggen letterlijk in de code: als iemand woord x gebruikt, dan vinden we dat dat hoort bij intentie y.

En zo moet je de onderstaande code-snippet ook lezen:

In de bovenstaande afbeelding zag je hoe de clusters gedefinieerd werden, en in de volgende afbeelding zie je hoe je met een standaard loop die configuratie kunt 'leggen' op een lijst keywords (in het geval van Sheets: alle woorden uit een bepaalde kolom):

In de vorige stappen hebben we eerste alle data verzameld, en vervolgens gebruikten we het om een intentie-clustering algoritme te maken in Google Apps Script.

Nu is het zaak om jouw eigen dynamische data (uit Google search Console) te verrijken met deze interpretaties. Dus: per woord voeg je een kolom toe in je Google Sheet die per intentie aangeeft of die erin zit, en zo ja, welke dan.

Nadat al die intentie-interpretaties uitgevoerd zijn, gebruiken we de data-merging en sortering methodes van Google Sheets om alle data samen te voegen tot iets behapbaars.

Uiteindelijk toewerkend naar '1 master-table' die alles bevat wat we nodig hebben.

Zoals gezegd: eerst voegen we kolommen toe aan onze keyword-data uit Search Console. Dit is dus onze EIGEN keyword data. Dat is dus een andere set data dan de data die in de markt zit. Ons doel is het hier namelijk om te gaan herkennen welke intenties WIJ kunnen gaan najagen.

Daarna ga je spelen met pivot tables / draaitabellen om de keywords en de gevonden intenties bij elkaar te zetten. Juist met pivot-tables kun je meerdere kolommen met elkaar combineren en filteren.

Zo heb ik hier een pivot-table die alle keywords bevat die zowel een expert als een locatie noemen. En de andere pivot table bevat alle keywords die specifieke 'dingen' noemen waarop de klussen uitgevoerd moeten worden:

Je ziet ook dat er nu hele mooie lijstjes van te gebruiken keywords naar boven komen drijven: dit zijn de best-presterende keywords binnen die specifieke intenties. Deze woorden gebruik ik vervolgens weer om bijvoorbeeld URLs en meta-titles te genereren. De URLs kun je in deze afbeelding ook zien (gebruik daar zgn. 'Calculated columns' voor).

Hoe dan ook, iedere draaitabel correspondeert met een intentie-type dat interessant genoeg is om er een aparte template voor te gaan ontwerpen.

Maar je wil nog een stap verder, want sommige intenties zijn belangrijker dan andere. En je wil ook een overzicht hebben met een sortering die zegt: 'hier moet je beginnen'.

Daarom moet je alles op 1 hoop gooien: de master-tabel.

Ik zal hieronder speciaal voor jou letterlijk de door mij in dit voorbeeld gebruikte formule tonen:

Waarschijnlijk heb je geen zak aan deze code. Snap je meteen waarom ik bovenin dit artikel zei dat de code op jouw situatie niet van toepassing is ;)

Maar hoe dan ook: precies deze bovenstaande code voegt alle pivot-tables samen in 1 master tabel.

Tijdens de presentatie heb ik - heel kort - ook de volgende 2 slides getoond. Het waren wat extraatjes die je helpen met:

En een eenvoudige formule die ik hier gebruikt heb om de 'prioriteit' van keywords en intenties uit te drukken:

Ik heb 'impressies' gebruikt als een uiting van 'relevant zoekvolume' en 'clicks' als uiting van 'in hoeverre ik hierop al presteer'. Die twee samen geven een mooie combinatie van potentieel en kans van slagen. Clicks wegen zwaarder in dit verband, vind ik. Een prima metric om op te sorteren.

Goed, de master tabel hebben we nu dus. Met daarin een overzicht van alle pagina's die we zouden moeten maken en welke keywords we daarbij kunnen gebruiken.

Maar belangrijker nog dan die keywords zijn de intenties. Een intentie geeft de richting aan: op welke soort vraag geven we antwoord op deze pagina?

Dus als iemand zoekt naar een probleem. Dan moet je een pagina aanbieden die gericht is op een probleem. dat probleem is onderdeel van dezelfde consumer journey als mensen die direct al zoeken naar 'loodgieter nijmegen', maar dat is niet wat ze zoeken: ze zoeken naar dat probleem, dus jouw antwoord gaat ook over dat probleem:

Als iemand zoekt naar een probleem, dan is het het doel van die pagina om mensen 1 stap verder te helpen in hun consumer journey. Bijvoorbeeld door te wijzen op experts die die persoon kunnen helpen met dat specifieke probleem.

Maar dat doe je niet bovenaan in je hero-image met een dikke call-to-action. Dat doe je onderaan, nadat je hebt bewezen dat je iemands probleem begrijpt.

Dat is dus ook het (meetbare!) doel van een template-type: het gaat er niet om hoeveel verkeer je daarmee naar binnen trekt, maar of mensen de content op die pagina daadwerkelijk consumeren. En of ze doorklikken naar de volgende stap in hun consumer journey. Als ze dat namelijk doen, heb je ze (1 stap verder) geholpen met hun vraag. Dat is quality content.

Ik besluit graag met deze quote, omdat die belangrijk is:

Neem gerust van me aan dat dit goeie SEO-resultaten oplevert. Maar dat is niet het enige. Het is een zeer gestructureerde manier van werken: