AdWords individuele keyword analyse vanuit Google Analytics

Hoe kun je op een andere manier eens naar het effect van je AdWords zoekwoorden kijken vanuit Google Analytics? Hieronder beschrijf ik een manier die voor mij vaak tot goede inzichten leidt, maar het is wel wat bewerkelijk.

Allereerst de benodigdheden (het is net een knutselwerkje):

  • Een Google Analytics account vol data
  • Een AdWords campagne van een tijdje (maand tot enkele maanden) oud
  • Excel (of een soortgelijk pakket)
  • Een handige plain tekst editor (Notepad++)
  • Voor grote campagnes een snelle pc (rekenkracht in Excel)

Dan de theorie

Deze keywordanalyse gaat eigenlijk om het ombouwen van deze tabel:

Zoekwoord Bezoekers Omzet Omzet per bezoeker
Vakantie Ameland 100 € 120 € 0,83
Vakantie Texel 50 € 80 € 1,60
Weekendje Texel 20 € 200 € 10,00

Naar deze:

Zoekwoord Bezoekers Omzet Omzet per bezoeker
Vakantie 150 € 200 € 1,33
Ameland 100 € 120 € 1,20
Texel 70 € 280 € 4,00
Weekendje 20 € 200 € 10,00

Waarom? Zo kun je in dit voorbeeld heel snel zien dat de zoekopdrachten met het woord "weekendje" erg waardevol zijn. Ook zou je op deze manier heel snel woorden kunnen opsporen die helemaal niet bijdragen aan de omzet: deze kun je vervolgens weer uitsluiten.

Stap 1: De zoekwoorden exporteren

Ga in Google Analytics naar het rapport "Keywords" onder "Traffic Sources" -> "AdWords":

Kies in dat rapport in het pulldown menu "Keyword" voor "Matched Search Query". We willen namelijk de ingetypte woorden hebben en niet de woorden waarop geadverteerd word:

Wanneer de tabel geladen is kun je tabel exporteren. klik bovenin het scherm op "Export" en klik met de rechtermuisknop op de "TSV" link. Kopieer de URL van de link en zet deze in de adresbalk. Type er vervolgens "&limit=10000" achter, dat is de manier om meer dan de standaard aantal rijen te exporteren.

Vervolgens kun je het bestand openen of downloaden. Kies voor de laatste, want het bestand is nog niet klaar voor Excel. Open vervolgens het TSV bestand in de tekst editor en voer 2 zoek-en-vervang acties uit:

  • Zoek alle komma's (,) en vervang deze met niets ()
  • Zoek alle punten (.) en vervang deze door komma's (,)
  • Verwijder de eerste rijen tot aan waar de echte tabel begint

Nu is het bestand klaar om in Excel geopend te worden. Doe dat 😉

Stap 2: de bewerkingen in Excel uitvoeren

Je hebt nu een Excel werkblad met daarin alle data (we noemen dat werkblad "Data"). Als het goed is gegaan is de data ook al mooi gepresenteerd in kolommen. Kopieer kolom A (de zoekwoorden) van werkblad "Data" naar de text editor. Daar doe je de volgende zoek en vervang actie:

  • Zoek alle spaties ( ) en vervang deze met een break voor een nieuwe regel (\n):

Nu staan alle individuele woorden op een enkele regel. Kopieer deze woorden naar kolom A van een nieuw werkblad genaamd "Analyse" in Excel. Selecteer in dat werkblad kolom A en kies voor de volgende functie:

Hiermee worden alle dubbele woorden uit de kolom verwijderd en blijft er van elk woord maar 1 vermelding over. Ga terug naar werkblad "Data" en voeg daar 2 extra kolommen toe: "Bouncers" en "Pages". Deze kolommen moeten respectievelijk als formule bevatten: "=bouncerate*visits" en "=pages-visits*visits". Zo hebben we het absolute aantal bouncers en bekeken pagina's. Die zijn weer nodig in het werkblad "Analyse".

In werkblad "Analyse" kunnen we nu de extra kolommen gaan toevoegen. Eerst zoeken we het aantal visits per zoekwoord op. Dat gaat met de volgende formule die we in kolom B plaatsen:

=SOMMEN.ALS(Data!B:B; Data!A:A; Analyse!A2 & " *")+SOMMEN.ALS(Data!B:B; Data!A:A; "* " & Analyse!A2)+SOMMEN.ALS(Data!B:B; Data!A:A; Analyse!A2)+SOMMEN.ALS(Data!B:B; Data!A:A; "* " & Analyse!A2 & " *")

Kort gezegd zoekt deze formule in werkblad "Data" alle vermeldingen op van het zoekwoord uit kolom A van werkblad "Analyse" waar het zoekwoord geen onderdeel is van een langer woord. Dus bij het woord "goed" wordt "goedkoop" niet gematched maar "alles is goed gedaan" wel.

Van de rijen die matchen worden dan de waardes uit kolom B (de visits) opgeteld. Hetzelfde kun je doen om alle bouncers op te tellen:

=SOMMEN.ALS(Data!F:F; Data!A:A; Analyse!A2 & " *")+SOMMEN.ALS(Data!F:F; Data!A:A; "* " & Analyse!A2)+SOMMEN.ALS(Data!F:F; Data!A:A; Analyse!A2)+SOMMEN.ALS(Data!F:F; Data!A:A; "* " & Analyse!A2 & " *")

Zelfde formule, alleen worden nu de waardes uit kolom F (de bouncers) bij elkaar opgeteld. Zo kun je alle velden uit werkblad "Data" opvragen: revenue, time-on-site, enz.

De final touch

Het enige wat rest is in het nieuwe werkblad de kolom "bounce rate" aan te maken door het aantal bouncers door het aantal visits te delen.

Omdat het werkblad "Analyse" wel eens heel zwaar kan zijn bij grote aantallen zoekwoorden kun je voor het analyseren het beste een kopie maken. Je maakt een nieuw werkblad aan en kopieert werkblad "Analyse" daar naar toe. Bij het plakken kies je dan voor "Waarden plakken":

Zo ontstaat er een nieuw werkblad dat geen formules meer maar alleen waardes bevat. Hier kun je vervolgens je echte analyses op los laten. Welke woorden genereren veel omzet, welke niet. Waar zitten de bouncers, enz.

Een voorbeeld sheet

Ik kan me voorstellen dat het allemaal niet even duidelijk is. Ik heb daarom een voorbeeld sheet gemaakt met daarin de besproken tabbladen:

In de praktijk kom ik zo veel bijvoeglijke naamwoorden tegen die het erg goed doen. En zonder deze analyse was ik daar niet snel achter gekomen. Ook levert het vaak veel woorden op die uitgesloten moeten worden. Google's Extended Super Wide Huge Broad Matching algoritme wil nog wel eens voor rare zoekwoord matches zorgen.

In plaats van Excel en Notepad++ zijn er ook andere tools, maar die zullen wellicht niet alle opties aan boord hebben om de analyse op de beschreven manier te doen. Je zult dan creatief moeten zijn in het zoeken naar een andere oplossing.

Vragen? Leuk? handig? Hieronder 😉

Click to activate social bookmarks