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

 
  • Wouter de Jong

    Beste Andre,

    Bedankt voor deze handige tips.

    Vriendelijke groet,

    Wouter de Jong

  • Raoul

    Wat een gedoe.. 🙂

  • Kost wat, maar dan heb je ook wat 😉

  • Cool,
    Alleen jammer dat ik op zoek moet gaan naar de vertaling van al die Excel functies. Ned > Eng.

    DG

  • Daniel Borrego

    Thanks a lot, André. This will come in handy!

    Just one question from a non-Dutch-speaking guy: what's the English equivalent of the SOMMEN.ALS function? Seems like a SUMIFS function, but I'm not sure...

  • Hi Daniel: it's SUMIFS according to this page. 😉

  • Bedankt voor je uitgebreide uitleg André! Helemaal top.

  • Daniel Borrego

    Hey, nice page! Straight to my bookmarks 😉

    Thanks again, André

  • Kim

    Bedankt voor de tips! heb weer wat geleerd!

  • Roy

    Mooie post André!

    Alleen zou deze methode niet waardevoller zijn als we naar de exacte search queries zouden kijken in plaats van keywords (d.m.v. http://www.getelastic.com/exact-keywords-google-analytics/ )?

    Hoe denk jij hierover?

  • Dat artikel beschrijft dezelfde methode als ik eens beschreven heb en is feitelijk achterhaald. Zoals je in het 2de plaatje bij stap 1 kunt zien zijn de echte gezochte woorden tegenwoordig standaard op te vragen in Google Analytics zonder daar een speciaal filter voor aan te maken.

  • Lenen met uitkering

    Ik zal dit eens gaan uittesten, is iedergeval duidelijk uitgelegd dus het moet me vanavond nog wel even te lukken zijn.

    Maar gebruik je deze techniek om de slechtere keywords af te stoten of is het daarvoor te onbetrouwbaar?

  • Hoi Andre,

    Erg handig deze tip..
    Die gaat zeker in mijn bookmarks!
    Morgen maar eens toepassen op enkele sites 🙂

  • Ron

    Wow,
    Dit is absoluut een toptip!
    Bedankt en ik heb hem ook toegevoegd aan m'n favorieten.

  • Internet Marketing Fanaat

    Leuk stukje!

  • Dankje, leuke linkdrop 😉

  • Interessant om te volgen, maar de excel geeft fouten bij me (mac osx 10.6 met excel 2008 v12) op de 2e tab, net waar de magie begint. Blijkbaar komen de sommen als niet goed door.
    Straks nog eens opnieuw proberen op een nieuwe excel.

  • Wat een goedoe allemaal zeg. Ga het maar is proberen allemaal.

    Bedankt voor je artikel