SEO-automation met Google Sheets: een praktisch voorbeeld

De verdere uitwerking van mijn presentatie hierover op #SEObenelux 30 Nov. 2023

46 min.

Plaatjes zeggen meer dan duizend woorden.

Daarom dus 1000 woorden bij mijn plaatjes ;)

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.

Uitleg bij de inhoudelijke slides van de presentatie

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 ;)

Google Sheets is als een Zwitsers zakmes

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.

Over de capaciteiten van Google Sheets

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.

P.s.: wat leestips voor verdere verdieping

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

De case

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.

Een typische database gedreven website

Want wat is het geval?

  • -

    We hebben hier te maken met een nieuwe website in een gevestigde markt.

  • -

    En die markt wordt gedomineerd door lokale zoekopdrachten zoals 'loodgieter Lutjebroek' of 'schilder Bemmel'. En dat dan voor ALLE plaatsen in Nederland.

  • -

    En om het feest compleet te maken: deze website had SEO gekopieerd van de concurrenten, die het allemaal op de verkeerde manier deden.

Geen sinecure dus om dit om te draaien ;)

De strategie

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

Hoe het NIET moet

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.

Dit werkt allemaal niet

Hoe dan wel?

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.

Intent-based en data-driven: dat is de strategie

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:

  • -

    Per intentie-type ontwerp je een aparte template.

  • -

    Op basis van data kies je welke specifieke intentie (onderdeel van een type, en daarbij horend keywords die die intentie verwoorden) als eerste gemaakt en geschreven wordt

  • -

    Die data herbergt een zekere voorspelling, voor zover mogelijk, over het effect dat je hiermee gaat halen. Richt je niet op 'loodgieter Amsterdam', maar op 'loodgieter Lutjebroek', als uit de data blijkt dat je daar meer kans hebt

  • -

    En tenslotte zorgt deze inkadering ervoor dat je maar, bijvoorbeeld, 100 landingspagina's hoeft te maken, in plaats van een paar duizend. Dat scheelt enorm veel onderhoudskosten, en duplicate content, en verspilde linkwaarde.

Het plan

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.

Dit is wat we gaan doen

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!

De executie

Dit is het meest interessante onderdeel uiteraard :)

Stap 1. Data-collectie

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:

  • -

    Welke locaties er in de markt zijn, en waarop de competitie zich richt. Hiervoor kunnen we de scrape-mogelijkheden van Google Sheets heel goed gebruiken

  • -

    De markt is veel groter dan alleen ons productaanbod. Ons aanbod zou nog kunnen groeien uiteraard. Maar dat is het punt niet. Waar het om gaat, is dat we willen leren wat voor soort producten er in deze markt aangeboden worden. Hoe heten ze? Hoe noemen onze concurrenten ze? Waar wordt naar gezocht? Ook hier kunnen we de sites van concurrenten voor scrapen.

  • -

    Hetzelfde geldt voor alle aanbieders. Hoe worden ze genoemd?

  • -

    Welke fasen kennen de consumer journeys in deze markt? Welke problemen zijn er waarvoor het aanbod een oplossing is?

  • -

    En wat zijn typische emotionele triggers die belangrijk zijn? Moet iets urgent gefixt worden? Hoe belangrijk is de prijs? Is er wantrouwen dat we moeten adresseren? Hoe wordt dat genoemd?

  • -

    Welke acties worden er in de markt ondernomen? In dit geval moet je denken aan werkwoorden als fixen, repareren, installeren, plaatsen, etc.

Alle data's verzamelen!

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.

Stap 2. Intentie-clustering

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:

  • -

    Wie: experts of bedrijven. Loodgieter, elektricien

  • -

    Wat en hoe: ding + actie. Een 'ding' is bijvoorbeeld een 'wasmachine' en een actie is 'repareren'

  • -

    Wanneer en waarom: kijk bijvoorbeeld naar voor en na, oftewel in dit geval: oorzaak en gevolg. Een oorzaak kan zijn: 'wasmachine kapot' en een gevolg kan zijn: 'waterschade'

  • -

    Waar: welke locatie, al dan niet in combinatie (meestal) met een expert, zoals 'loodgieter Nijmegen'

Denk vanuit de 5W's

En meestal vind je dan al vrij snel allerlei intenties en de manier waarop ze geformuleerd worden

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:

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

Intentie-clustering: de code

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:

Google-Apps script code: definieer clusters

We zien hier: een intentie-type ('case: intentie-type naam'); de onderliggende intenties (bijvoorbeeld verschillende groepen vragen, wie, wat, hoe etc.) en de reguliere expressies die ik gebruikt heb om ze te herkennen. In dit geval de 5Ws met word-boundaries (\b).

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):

Match cluster met array van keywords

Stap 3: enrich, mix, match, combine: op naar de 'master-table'

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.

Dit gaan we allemaal doen:

Enrich

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.

Keywords en intenties

Kolom 'Query': jouw GSC-data. De andere kolommen zijn de interpretatie resultaten van je eerder gemaakte clustering-algoritme.

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.

Mix & match

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:

Pivot-tables

Gefilterd op matchende keywords per een of meerdere intenties

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).

Combine

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.

Hoe combineer je meerdere draaifabelen in 1?

Dat doe je door =QUERY() met array-syntax te gebruiken.

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

=unique(query({'Per intentie'!C4:F;'Per intentie'!J4:M;'Per intentie'!Q4:T;'Per intentie'!X4:AA};"select Col2,Col1,Col4,Col3 where Col2 != '' AND Col1 > 25 ORDER BY Col1 desc";0))

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.

Twee extraatjes

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

Het genereren van dynamische title-tags

Op basis van de GSC-data

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

Prioriteit van een keyword

Quick & dirty, maar heel bruikbaar

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.

Moving forward: 1 intent = 1 template

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:

Intent > template > page

Intent-based templating

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:

... 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.

Het resultaat

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:

  1. 1.

    Het zorgt ervoor dat een compete roadmap in 1 makkelijk deelbaar overzicht staat. Is makkelijk te delen met stakeholders

  2. 2.

    De prioritering zorgt voor een eenvoudig te volgen stappenplan: bovenaan beginnen. Testen hoe lang het duurt om het live te zetten. Daarna nog strakker inplannen en herhalen

  3. 3.

    Zeer meetbaar: alles was sowieso al data-driven, en dus ook makkelijk te koppelen aan monitoring

De geijkte positieve lijngrafiek

Voor alle duidelijkheid: deze grafiek is niet echt, althans, niet van de site uit mijn case. Maar het was wel zoiets ongeveer. Maar dat is niet relevant vind ik. waar het om gaat, is dat je dit toepast op JOUW case!

Samenvatting

  • -

    Google Sheets is de ultieme all-in-one SEO-automation tool. Alleen niet zo heel erg goed.

  • -

    Ik leg dat uit aan de hand van een case: een offertesite met landelijke dekking

  • -

    Klassieke SEO gaat niet werken

  • -

    In plaats daarvan moet 'intent-based' en 'data-driven' gaan werken

  • -

    We gaan een 'master-tabel' maken die precies op een rijtje zet: welke type pagina moeten we maken, in welke volgorde, op wat voor manier

  • -

    Dat begint met het verzamelen van alle mogelijke problemen, uitdagingen, oplossingen, aanbieders, locaties, emotionele triggers, acties, etc. in de markt

  • -

    Vervolgens maken we een algoritme dat op basis van keyword-data al die achterliggende intenties kan herkennen

  • -

    Dat algoritme passen we vervolgens toe op onze EIGEN keyword-data, zodat we kunnen voorspellen wat voor ons (waarschijnlijk) het beste gaat werken

  • -

    En dat slaan we uiteindelijk dus plat in de genoemde 'master-tabel'

  • -

    Op basis van die 'master-table' kunnen we gaan bouwen. Belangrijk is dat het daarbij dat we per intentie ook een template ontwerpen dat ook precies aan dat type intentie tegemoet komt

  • -

    En dat werkt heel goed, op allerlei niveau's ;)

→ Call to action ←

Klinkt interessant?

Laten we dan de virtuele afstand tussen ons 1 stap kleiner maken!