Google Spreadsheets: het ultieme overzicht van tools voor top-analyses

Kijk eens om je heen, de kans is groot dat je op je beeldscherm (en waarschijnlijk ook op die van je collega’s) een duizelingwekkend spreadsheet aantreft. De reden? Spreadsheets zijn nog steeds ongeëvenaard als het aankomt op het – al dan niet geautomatiseerd – verzamelen, bewerken en visualiseren van data. Met deze 10 (gratis) hulpmiddelen voor Google Spreadsheets, ga je hier optimaal van profiteren.

Als je solliciteert in de wereld van digitale marketing, wordt je vaak voorgespiegeld dat je met top notch tools mag gaan werken. Voor een deel is dit zeker waar. Als marketeer, webanalist of search-specialist ben je dagelijks bezig om je weg te leren vinden in tools als Google Analytics, AdWords, Data Studio en Search Console. Alle technologische ontwikkelingen ten spijt: de good old spreadsheet is niet weg te slaan van de werkvloer en gaat met zijn tijd mee.

Zoals voetballiefhebbers zelden fan zijn van Ajax én Feyenoord, zijn er ook meerdere kampen te onderscheiden in de spreadsheet-wereld. De meest gebruikte tools zijn Microsoft Excel en Google Spreadsheets. Excel is vooral in de traditionele logistieke en financiële sector nog altijd de onbetwiste nummer één, maar in digitale marketing is Google Spreadsheets steeds meer in zwang.

Dit komt niet alleen doordat je eenvoudig met meerdere mensen tegelijk kunt samenwerken in één document en je nooit meer een bestand hoeft op te slaan. Deze functies biedt Excel Online inmiddels ook. Veel belangrijker is de gebruiksvriendelijkheid van Google Sheets in combinatie met het automatiseren van data-import, krachtige visualisaties en de voortschrijdende toepassing van machine learning en kunstmatige intelligentie. Google Spreadsheets heeft als web-based tool vanaf het eerste begin in 2006 een natuurlijke voorsprong op Excel, dat al sinds de jaren tachtig van de vorige eeuw als software draait op desktops en pas sinds 2010 online aan de weg timmert.

De volgende 10 hulpmiddelen en tips voor Google Sheets zullen je tijd besparen en kunnen je helpen om efficiënter te werken en de kwaliteit van je analyses te verhogen.

1. Google Analytics Spreadsheet Add-on

Met deze add-on van Google kun je Analytics-data ophalen en bekijken in Google Spreadsheets. Als je regelmatig handmatig data exporteert uit Analytics, kan deze add-on je veel werk uit handen nemen. De add-on maakt gebruik van de Google Analytics API en kan meerdere rapporten tegelijk opvragen, zo nodig uit verschillende dataweergaven en profielen. Omdat de data volgens een bepaald stramien in Spreadsheets binnenkomt en zo nodig maandelijks, wekelijks, dagelijks of ieder uur automatisch wordt bijgewerkt, kun je er meteen berekeningen en grafieken op los laten en het document bijvoorbeeld als bron voor een dashboard gebruiken.

Zoals je gewend bent in de user interface van Google Analytics kun je dimensies en metrics gebruiken, maar ook filters en segmenten toepassen. Bovenaan de rapporten staat vermeld of er sampling (een steekproef op basis van een beperkt deel van je data) wordt toegepast op je dataverzoek. Heb je Analytics gekoppeld met AdWords? Dan kun je via de Google Analytics Spreadsheet Add-on ook data uit je AdWords-campagnes ophalen.

2. Big Analytics Data

Is je dataverzoek aan Google Analytics zo specifiek (bijvoorbeeld doordat je een segment gebruikt) of heb je zoveel verkeer op je website dat je onverhoopt toch telkens met sampling wordt geconfronteerd? Dan is de Big Analytics Data add-on een goede tweede optie. Deze add-on werkt precies hetzelfde als de Google Analytics Spreadsheet add-on, maar is nét wat slimmer. Big Analytics Data probeert sampling automatisch te vermijden door de queries alvast voor je op te splitsen. Dit heeft als voordeel dat je eenvoudiger uitdraaien zonder sampling kunt maken, maar levert soms wel dubbelingen op die je zelf moet corrigeren.

Omdat de add-on door een onafhankelijke developer is gemaakt, is deze helaas niet altijd even stabiel. Nog een belangrijk extraatje: met deze add-on kun je custom metrics maken, door berekeningen in je query op te nemen waarvan de resultaten in een aparte kolom worden geplaatst. Zo kun je bijvoorbeeld naast kolom A (maand), kolom B (sessies), kolom C (opbrengst) een kolom D toevoegen waarbij kolom C gedeeld wordt door kolom B en bereken je dus automatisch de ‘opbrengst per sessie’. Big Analytics Data is zo gebouwd dat de add-on overweg kan met queries die je gemaakt hebt in de Google Analytics Spreadsheet Add-on.

3. Search Analytics for Sheets

Zoals je misschien wel weet is de toegang tot historische data over zoekresultaten in Google Search Console beperkt tot 90 dagen. Als je resultaten op langere termijn wil opvragen, bijvoorbeeld om te kijken in hoeverre je inspanningen tot het gewenste resultaat hebben geleid, loop je tegen deze beperking aan. Gelukkig is er een add-on die uitkomst biedt: Search Analytics for Sheets kan op commando allerlei analytische data over zoekgedrag ophalen en weergeven in Google Spreadsheets.

Via de zijbalk kun je heel eenvoudig je zoekopdracht aanpassen tot je resultaten precies weerspiegelen wat je wil zien. Wil je voortaan altijd de beschikking hebben over je historische data? Laat Search Analytics for Sheets dan automatisch iedere dag of iedere maand een back-up maken, zodat je je eigen databestand opbouwt.

4. Smart Autofill

De add-on Smart Autofill maakt gebruik van machine learning om ontbrekende waarden in je spreadsheet te kunnen ‘voorspellen’. Dit kunnen getallen zijn, maar ook teksten, sentimenten (positief, neutraal, negatief) of categorieën.

Stel je bijvoorbeeld de situatie voor dat er vanwege een meetfout een bepaalde periode ontbreekt in je e-commerce-transactiedata. Om de ontbrekende gegevens te berekenen maakt deze add-on gebruik van Google’s Prediction API. De add-on geeft ook meteen aan wat de mate van waarschijnlijkheid is dat de ingevulde waarden overeenstemmen met de werkelijkheid. Uiteraard is de kwaliteit van de berekende output erg afhankelijk van de beschikbare input. Hoe meer data en patronen er bekend zijn, hoe beter de add-on zijn werk kan doen.

Let op: deze add-on en de onderliggende Google Prediction API worden na 30 april 2018 helaas niet meer ondersteund.

5. Explore

Google past steeds meer machine learning toe in haar producten. Zo verscheen er enige tijd geleden opeens een knop ‘Explore’ (Nederlands: ‘Verkennen’) rechts onderin je Google Spreadsheets. Als je deze nieuwe functie inschakelt doet Google suggesties voor visualisaties en analyses, gebaseerd op de dataset die je voor je hebt.

In de Engelse versie kun je zelfs met de functie ‘praten’, door een vraag te stellen over je dataset. Je ziet dan meteen je antwoord gevisualiseerd in een grafiek, een tabel en – als je wil – de formule die tot het resultaat heeft geleid. Deze formule kun je vervolgens weer als basis gebruiken voor het bewerken van je eigen dataset (zie tip 9).

Ben je statistisch goed onderlegd en wil je specifieke modellen loslaten op je data? Dan zul je veel baat hebben bij de add-on XLMiner Analysis Toolpak. Deze add-on bevat grotendeels dezelfde tools als de bekende Analysis ToolPak voor Excel.

6. Conditionele opmaak

Met conditionele opmaak kun je grote hoeveelheden data op een visuele manier ordenen, zodat je snel met het blote oog inzicht kunt krijgen in eventuele patronen en (cor)relaties. Zo kun je cellen, rijen of kolommen zo opmaken dat de tekst- of achtergrondkleur verandert als aan bepaalde voorwaarden wordt voldaan.

Een voorwaarde kan bijvoorbeeld zijn dat een cel een bepaald woord of getal bevat. Je kunt kiezen uit twee soorten conditionele opmaak:

• het toepassen van één kleur als er aan de voorwaarde wordt voldaan.
• het toepassen van een kleurenschaal, waarbij de schaal van de reeks automatisch of handmatig gegenereerd kan worden. Een waarde die aan de onderkant van de reeks ligt kan dan bijvoorbeeld dieprood kleuren, een waarde in het midden van de reeks wit, terwijl een waard die er positief uitspringt donkergroen is.

Als je echt los wil gaan met conditionele opmaak kun je ook aan de slag met aangepaste formules, zodat je de opmaak van een bepaalde cel kunt laten reageren op de waarde van een andere cel. Dit doe je door bij ‘Cellen opmaken als…’ te kiezen voor ‘Aangepaste formule is’ en in het veld daaronder je formule te plaatsen. Als je wil dat je bereik (bijv. cel A1) rood kleurt als een ander bereik (bijv. cel B1) een negatief getal bevat, dan vul je ‘A1’ in bij ‘Toepassen op bereik’, is je aangepaste formule ‘=B1<0’ en je opmaakstijl de kleur rood.

7. Data importeren

Soms wil je in je sheet data verzamelen uit andere bronnen dan de tabbladen van je document. Google Spreadsheets kan dankzij de volgende formules geautomatiseerd data importeren uit andere spreadsheets, maar ook uit CSV’s, lijsten of tabellen op webpagina’s en informatie uit RSS-feeds en XML-bestanden.

Als je data uit andere spreadsheets importeert met de IMPORTRANGE-functie, zal dit niet meteen werken. Dit komt omdat je eerst toestemming moet geven om de data te importeren uit de andere sheet. Je lost dit op door op de foutmelding ‘#REF!’ te klikken en toestemming te geven via de blauwe knop die dan verschijnt. Daarna zie je de gewenste informatie verschijnen en wordt het geselecteerde bereik zo goed als real-time bijgewerkt zodra er een wijziging is in het brondocument.

8. Formules

Afhankelijk van de taalinstellingen van je spreadsheet kun je in de documentatie van Google een overzicht (Engels, Nederlands) van alle formules terugvinden. Hier vind je uitleg over alle formules. Daarnaast is het een handig naslagwerk om formules te ontdekken die je nog niet gebruikt, maar je werk wel efficiënter zouden kunnen maken. Gebruik een zoekwoord of selecteer een categorie, bijvoorbeeld ‘Datum’, ‘Wiskunde’ of ‘Statistiek’. Als je veel werkt met data uit allerlei bronnen en deze informatie wil bewerken, dan kunnen de volgende formules je veel tijdwinst opleveren:

• VLOOKUP: verticaal zoeken. Zoekt omlaag in de eerste kolom van een bereik naar een sleutel en retourneert de waarde van een opgegeven cel in de gevonden rij. Handig als je bijvoorbeeld data uit twee verschillende sheets automatisch wil samenvoegen op basis van een ‘sleutel’: een waarde die in beide bestanden voorkomt.
• SUMIF(S): retourneert de som van een bereik op basis van één of meerdere criteria. Deze formule gebruik je als je automatisch waarden wil laten optellen als ze aan de juiste criteria voldoen.
• EOMONTH: retourneert een datum voor de laatste dag van een maand die een bepaald aantal maanden vóór of na een andere datum valt. Hiermee kun je bijvoorbeeld het probleem omzeilen dat de ene kalendermaand meer dagen bevat dan de andere.
• SPARKLINE: maakt een miniatuurdiagram binnen één cel. Handig als je in je spreadsheet direct een trendlijn wil laten zien.
• GOOGLETRANSLATE: vertaalt tekst tussen twee talen. Dus ‘Hallo’ (Nederlands) in A1 wordt ‘Hello’ (Engels) in A2. Kan vertalen tussen tientallen talen.

Als je aan de slag gaat met formules, is de kans groot dat je een keer geconfronteerd wordt met een foutmelding. Daarom hier nog even een overzicht met alle voorkomende errors en wat je eraan kunt doen.

9. EZ Query

Als je goed uit de voeten kunt met formules in Google Spreadsheets, maar af en toe toch behoefte hebt aan meer vrijheid wat betreft het bewerken van data, is het tijd om je te gaan verdiepen in de ‘query-functie’. Dit is een functie die de kracht van SQL (de taal waarin je kunt communiceren met een database) combineert met de rekenkracht van spreadsheets. Dit uitstekende artikel legt je uit wat je zoal met de query-functie kunt doen en hoe dit taaltje in elkaar steekt.

Om je vast op weg te helpen met een basis-query kun je gebruik maken van de add-on EZ Query. Hier kun je redelijk eenvoudig je databestand bewerken door enkele voorwaarden op te geven, zonder dat je zelf een query hoeft te schrijven. Daarna kun je de automatisch gegenereerde formule naar eigen inzicht aanpassen. Zoals eerder vermeld kun je ook de ‘Explore’-functie (zie tip 5) gebruiken om een query te maken.

10. Apps Script voor Sheets

Loop je na het lezen en toepassen van bovenstaande tips nog steeds tegen beperkingen aan? Dan is het tijd om je in Apps Script te verdiepen. Met deze programmeertaal kun je zelf allerlei functies, custom formules, menu’s en add-ons bouwen voor Google Spreadsheets.

Met Apps Script is het bijvoorbeeld mogelijk om automatisch op een bepaald tijdstip een externe API (van bijvoorbeeld AdWords, Facebook Ads of MailChimp) te benaderen met een zoekopdracht en de resultaten die je terugkrijgt weg te schrijven in een spreadsheet. Dit geeft je de mogelijkheid om zonder tussenkomst van derde partijen je eigen data te verzamelen uit allerlei bronnen en deze – in het format dat voor jou handig werkt – weer te geven.

Om met Apps Script aan de slag te gaan heb je wel de nodige technische kennis nodig. Beheers je JavaScript? Dan zul je direct aan de slag kunnen, want Apps Script lijkt erg op JavaScript. Maar als je over enige logische vaardigheden beschikt, goed kunt googelen en de beschikbare documentatie en tutorials door ploegt, kun je ook een heel eind komen. Tip: op GitHub zijn er veel scripts te vinden die je direct kunt gebruiken voor je eigen project.

Heb je al ervaring met deze hulpmiddelen? Of mis je nog iets? Laat het weten in de reacties hieronder.