21 april 2020

Efficiënter advertising campagnes bouwen met Excel: 7 tips

Heb je vragen?

Neem contact op met Bart. Hij helpt je graag verder.

bart@begradient.com

Daar is ‘ie dan! Deel 2 uit de reeks met slimmigheden voor het automatiseren van een campagne setup in meerdere kanalen. In deel 1 namen we je mee in de aanleiding tot en de werkwijze van het automatiseren. Nu wordt het pas echt interessant voor de techies en Excel liefhebbers onder ons.

En volgende week? In blog 3 (het sluitstuk) behandelen we de import van banners, Facebook targeting en werpen we een blik op de toekomst. Kun je niet wachten? Download dan hier de whitepaper.

In dit artikel

VLOOKUP (of VERT.ZOEKEN)

Weet je nog dat we de vorige post eindigde met onderstaande afbeelding en de vraag hoe je deze informatie omzet? Dus hoe zorg je ervoor dat “Utrecht” automatisch “UT” wordt en “Najaar” verkort wordt tot “NJ”?

Juist! Met de VLOOKUP (of VERTICAAL ZOEKEN) functie. Deze functie zoekt een waarde terug in een kolom en geeft vervolgens de waarde van een andere cel in dezelfde rij terug. Om hiervan gebruik te maken hebben we eerst een hulptabel aangemaakt waarin alle mogelijke opzoekwaarden (in dit voorbeeld plaatsnamen) corresponderen met de juiste resultaatwaarden (in ons geval zijn dit de afkortingen van de plaatsnamen).

De syntax voor deze functie is VERT.ZOEKEN(waarde, bereik, index, [is_gesorteerd]). Wanneer je hem invult voor ons specifieke geval krijg je VERT.ZOEKEN(C3,H2:I8,2,ONWAAR).

  1. Cel C3 is de waarde “Utrecht” die we gaan opzoeken in de tabel.
  2. Deze waarde wordt opgezocht in de tabel met bereik H2 tot en met I8. 
  3. Bij een match wordt vervolgens de waarde uit de tweede kolom (kolom I) teruggegeven. 
  4. De waarde [is_gesorteerd] kan ONWAAR (of 0) of WAAR (of 1) zijn. Wanneer je een exacte overeenkomst tussen opzoekwaarde en resultaatwaarde wilt zoeken vul je hier 0 of ONWAAR in.

Et voilà, met deze formule toveren we “UT” tevoorschijn. Wanneer je cel C3 verandert naar een andere plaatsnaam, bijvoorbeeld Amsterdam, verandert de output naar “AM”. Hetzelfde trucje kunnen we herhalen voor editie, zodat de input “Voorjaar” altijd als “VJ” wordt weergegeven en “Najaar” als “NJ”. 

Wanneer we deze formules allemaal met elkaar combineren krijgen we =C2&”_”&C4&”_”&VERT.ZOEKEN(C3,H2:I8,2,ONWAAR)&”_”&VERT.ZOEKEN(C5,!H11:I13,2,ONWAAR). Dit levert als output Dutch Heroes Comic Con_2019_UT_NJ op. Bij een andere beurs, een ander jaartal, een andere plaats en een andere editie zal de naam ook automatisch mee veranderen. Niet verkeerd, toch?

Naast dit ietwat technische handigheidje hebben we nog veel meer automatiseringen en slimmigheden in de spreadsheet ingebouwd. Daar nemen we je ook nog even doorheen.

VLOOKUP voor locatie-targeting

Te beginnen met de zojuist besproken VLOOKUP. Deze gebruiken we namelijk op veel meer plaatsen in het document. Het Zwitsers zakmes van de spreadsheet aficionados. Zo gebruiken we het ook om de locatie coördinaten van de campagnes te specificeren.

Deze coördinaten zijn vereist omdat de klant in een radius om de plaats van het evenement wilt adverteren. Dit kan je relatief eenvoudig via de Google Ads interface voor elkaar boksen, echter is dit voor een hoog volume aan campagnes nogal een tijdrovend karwei. 

Gelukkig is er ook een schaalbare oplossing; je kunt namelijk de desbetreffende lengte- en breedtegraad van een locatie opzoeken en deze via de Google Ads Editor importeren. Met behulp van onderstaande tabel kunnen we met deze truc de juiste radius targeting instellen voor beurzen op verschillende plekken in het land.

Met de jullie inmiddels bekende VLOOKUP formule kunnen we hiermee voor elke plaats de juiste coördinaten invullen en linea recta doorzetten naar het platform.

Gegevensvalidatie

Een ander foefje waar we veelvuldig gebruik van maken in ons document is de gegevensvalidatie functie in Google Sheets. Met deze functionaliteit kan je de invoer in een cel beperken tot een reeks waarden die jij specificeert. Vaak is dit in de vorm van een dropdown menu, zoals hieronder weergegeven in het voorbeeld vanuit ons document.

Hoe stel je dit in? Selecteer je cel en navigeer vervolgens naar Gegevens > Gegevensvalidatie. Bij het onderdeel Criteria stel je in welke keuzemogelijkheden in het dropdown menu moeten voorkomen. Hierbij kan je zelf een lijst met opties invoeren of gebruik maken van een lijst die je elders in het document hebt neergezet.

Tip: Bij cellen waar de datum ingevuld dient te worden kan je Datum als criterium gebruiken. Bij het instellen van de datum kan je vervolgens op de cel dubbelklikken om eenvoudig de gewenste datum te selecteren.

De gegevensvalidatie functie gebruiken we, net als de VLOOKUP, meerdere malen in het document. Voor de targeting van de Display campagnes, waarbij je uit ruim 3.000 (!) doelgroepen kunt kiezen, is dit ook een welkome toevoeging aan het document. 

Om dit aan de praat te krijgen hebben wij alle mogelijke doelgroepen in aparte kolommen gezet. Zodoende heb je kolommen voor affinity en in-market doelgroepen alsook onderwerpen waarop je kunt adverteren. Door de cel waarop je gegevensvalidatie wilt toepassen te verwijzen naar één van deze kolommen, maak je het heel eenvoudig om de desgewenste doelgroepen uit te kiezen.

Een leuke functionaliteit hierbij is dat de lijst automatisch wordt ververst op basis van de tekst die je hier invoert (ook wel bekend als autocomplete), zodat je niet de gehele lijst af hoeft te struinen op zoek naar de juiste doelgroep.

Ben je op zoek naar Trekkies of personen die affiniteit hebben met de familie Skywalker? Vul dan sciencefiction (of simpelweg science) in en autocomplete vult het aan:

Voorwaardelijke gegevensvalidatie

Nu we begrijpen hoe gegevensvalidatie werkt, kunnen we door met gegevensvalidatie voor gevorderden; op basis van een of meerdere voorwaarden. Dit gebruiken we o.a. bij onze videocampagnes die we op YouTube hebben draaien.

Het aanmaken van een videocampagne via een spreadsheet is in principe vrij eenvoudig, echter heb je een trucje nodig om het proces van campagnetype en biedstrategie kiezen te laten kloppen. Zo is, in tegenstelling tot Search en Display, niet elke biedstrategie te koppelen aan elk campagnetype.

Voor videocampagnes kun je kiezen uit meerdere campagne types, waaronder drive conversions, non-skippable en outstream. Het lastige is dat bij zo ongeveer ieder type een andere biedstrategie gebruikt dient te worden. Zo kun je bij drive conversions kiezen uit maximize conversions of target CPA en bij outstream enkel manual CPM inzetten.

Als je het type campagne én de biedstrategie wilt uitkiezen, wil je enkel de biedstrategie opties tonen die ook daadwerkelijk mogelijk zijn voor het gekozen campagnetype. Gelukkig valt ook deze uitdaging met een formule op te lossen. Alleen is het deze keer geen standaard formule, maar een array. Een wat? Een array.

Om hiermee te beginnen is het handig om eerst alle mogelijke combinaties vast te leggen in de sheet.

Vervolgens maak je de cel aan waarin je gegevensvalidatie voor het campagnetype instelt. Deze verwijs je naar de cellen waarin je alle mogelijke campagnetypes hebt genoteerd (in bovenstaand geval H80 t/m L80).

Nu kan je de array formule aan gaan maken. In ons geval ziet deze er als volgt uit:

=ALS(C74=H80;H81:H82;ALS(C74=I80;I81;ALS(C74=J80;J81;ALS(C74=K80;K81;ALS(C74=L80;L81;””))))). Denk erom dat je na het invoeren niet simpelweg op ENTER drukt, maar CTRL+SHIFT+ENTER. Meneer array is net als je lastige zusje of broertje namelijk iemand met een eigen gebruiksaanwijzing. Als alternatief zou je de hele formule ook in een ARRAYFORMULA functie kunnen wikkelen. Of in een rol toiletpapier, kijk maar even wat je doet.

Bovenstaande formule ziet er misschien wat angstaanjagend uit, maar het is eigenlijk gewoon een simpele geneste ALS-formule. De formule probeert cel C74 (de cel waarin de gegevensvalidatie van het campagnetype staat) en H80 t/m L80 (de cellen met alle mogelijke campagnetypen) aan elkaar te matchen. Mocht hier een overeenkomst zijn gevonden, dan wordt de inhoud van de cel in rij 81 (en in het geval van drive conversions rijen 81 en 82) de output van de formule. En deze output kan je vervolgens weer gebruiken in een tweede cel met gegevensvalidatie voor de biedstrategie. Driewerf hoezee!

Gegevensvalidatie met meerdere selecties

Bij het instellen van gegevensvalidatie kan je maar één optie uit de lijst selecteren. Maar wat als je er nou meerdere wilt aanvinken? Normaliter is dit onmogelijk, maar door het gebruik van een script kan dit wel. Zeg maar gerust gegevensvalidatie on steroids.

De use case voor deze functie is het targeten van bepaalde provincies in Facebook. Hoe kunnen we ervoor zorgen dat je de provincies waarop je wilt targeten aan kunt klikken en niet de provincies stuk voor stuk in de cel hoeft te typen (dit laatste is namelijk monnikenwerk en hier, misschien had je het al door, houden wij niet van)?

Voordat we met het script aan de gang gaan dienen we eerst de gegevensvalidatie op te zetten, net als normaal via Gegevens > Gegevensvalidatie. In ons geval verwijzen we hierin naar een celbereik met alle Nederlandse provincies.

Vervolgens kunnen we met het scriptgedeelte aan de slag. Overigens zijn het eigenlijk twee scripts, eentje die zorgt voor het pop-up venster en eentje voor het daadwerkelijke script. Je vindt ze beiden via deze link (gratis en voor niets, ook wij zijn de beroerdste niet) en je installeert ze via Extra > Scripteditor. Zodra je ze hebt geïnstalleerd vind je een nieuwe menuknop genaamd Scripts en een subknop Multi-select.

Selecteer nu de cel waarin je de gegevensvalidatie hebt ingesteld en klik op Scripts > Multi-select. Een aantal vensters openen waarin je het script toegang geeft tot je spreadsheet. Vervolgens opent zich aan de rechterkant van je scherm een pop-up:

De rest wijst zichzelf; vink de provincies aan waarop je wilt targeten en klik op Set. De geselecteerde cel wordt nu automatisch gevuld met alle provincies die zijn aangevinkt, gescheiden door komma’s. Deze cel kan nu 1-op-1 geïmporteerd worden naar Facebook Ads. Heb je jezelf toch weer wat onnodige muisklikken bespaard! Je wijsvinger zal je dankbaar zijn.

Voorwaardelijke opmaak

De meesten van jullie zullen misschien al wel bekend zijn met voorwaardelijke opmaak. Hiermee kun je cellen opmaken (bijvoorbeeld door de cel een andere kleur achtergrond te geven) op basis van voorwaarden die je instelt. Google Sheets bevat veel standaard voorwaarden die je kunt selecteren en je kunt daarnaast ook hier formules gebruiken.

Een praktisch voorbeeld hiervan vinden we in ons document bij de invoer van de YouTube video URL. Hierbij is het namelijk van belang dat de volledige URL ingevoerd wordt (en niet de verkorte URL; dus https://www.youtube.com/watch?v=ENDQUZIX6jI en niet https://www.youtu.be/ENDQUZIX6jI). Door een opmaakregel toe te voegen als tekst bevat niet watch zorg je ervoor dat de invoercel van kleur verandert zodra de URL niet correct wordt ingevoerd.

Zodoende wordt een cel met een foutief ingevoerde URL oranje:

En blijft een cel waarin de URL wel correct is ingevoerd geel:

Een ander voorbeeld vinden we bij de invoer van de advertentieteksten voor Google Ads. Hieraan zijn allemaal restricties en regels verbonden; zo mag je niet over een bepaalde karakterlimiet heengaan en mogen je beschrijvingsregels niet beiden een uitroepteken bevatten. Flauw hoor, Google.

Om te checken of je tekst niet over de karakterlimiet heengaat kan je de formule =30-LENGTE(A1) gebruiken, waarbij je het getal 30 vervangt met de desbetreffende karakterlimiet (30 voor kopteksten, 90 voor beschrijvingen) en de te controleren tekst in cel A1 hebt staan. Met een conditionele opmaakregel zorg je ervoor dat je alle uitkomsten die lager dan 0 uitvallen, waarbij je dus over de tekenlimiet bent gegaan, extra opvallen door een afwijkende celkleur. De opmaakregel wordt dan ook kleiner dan of gelijk aan -1.

In onderstaand voorbeeld zijn we 4 tekens over de limiet heengegaan:

Door het verwijderen van het woord ‘Najaar’ zitten we weer mooi binnen de limiet:

Een derde praktisch voorbeeld van voorwaardelijke opmaak is iets complexer dan de vorige twee. Hierbij maken we namelijk gebruik van een formule om dubbele uitroeptekens op te sporen. Deze formule is in ons document =ALS(ISGETAL(VIND.SPEC(“!”;B9));EN(ALS(ISGETAL(VIND.SPEC(“!”;B10));1;0))). 

Met de combinatie van ISGETAL en VIND.SPEC kun je zoeken naar bepaald karakter of woord, in ons geval het uitroepteken. Door de functie EN wordt dit teken in zowel cel B9 als B10 opgezocht. En mocht het uitroepteken in beide cellen worden gevonden, dan wordt de output 1, indien dit niet het geval is, dan is het 0. Bij een output 1 trigger je de conditionele opmaakregel en wordt de opmaakstijl toegepast (in ons geval krijgt de cel een oranje achtergrond).

Met één uitroepteken is er niets aan de hand:

Maar met twee uitroeptekens wordt er duidelijk gemaakt dat je een regel overtreedt:

Dus doe jezelf een lol, zorg voor foutloze advertenties en houd je netjes aan de (opmaak)regels. En haal gelijk je dt-foutjes uit de tekst (am I right, my fellow taalnazi’s?).

Ander voorbeeld van ALS cel bevat tekst

Naast het opsporen van dubbele uitroeptekens gebruiken we de truc waarin we ISGETAL en VIND.SPEC combineren nog vaker in het document. Zo ook om de zoekwoorden aan de juiste campagne te koppelen. Met deze functie scheiden we o.a. branded van non-branded en beurs of evenement gerelateerde woorden van de rest:

Om deze functie wat leesbaarder te maken zullen we hem ontleden:

  1. In dit gedeelte wordt de naam van de beurs (die in het tabje “Algemeen” in cel C2 staat ingevuld) vergeleken met het zoekwoord in cel A1. Mocht hier een overeenkomst worden gevonden dan wordt de output van de cel “Branded”
  2. Mocht het zoekwoord “beur” (hiermee match je op zowel beurs als beurzen) in het zoekwoord voorkomen, dan wordt de output “Beurs”
  3. Mocht het zoekwoord “even” (voor een match op zowel event als evenement) in het zoekwoord voorkomen, dan wordt de output eveneens “Beurs”
  4. Indien er in geen van bovenstaande gevallen een match wordt geconstateerd dan wordt de output “Algemeen”

Met een dergelijke scheiding van zoekwoorden op campagneniveau zorg je ervoor dat je makkelijker het overzicht bewaart en betere beslissingen kunt nemen bij het managen van je campagnes. Zo wil je waarschijnlijk hele andere budgetten en biedingen instellen voor je merknaam campagnes en je generieke campagnes. Met deze formule zorg je er dan ook voor dat de zoekwoorden aan de juiste campagnes worden toegewezen.

Wat staat er in blog 3?

In de volgende (en laatste) blog behandelen we de laatste technische snufjes en blikken we kort vooruit naar ontwikkelingen in de toekomst.

  • Displaybanners importeren
  • Kanaal-ID’s achterhalen
  • Facebook Image Hash / Video ID
  • Facebook interesses API

sparren over dit topic

Verder praten over wat een geautomatiseerde campagne setup voor jouw bedrijf kan doen? 

zelf doen?

Snappen we helemaal. Daarom download je hier de whitepaper Campagne setups automatiseren doe je zo. Succes!

inspiratie van het huis

meer sterke verhalen