There it is! Part 2 in the series featuring smarts for automating a multi-channel campaign setup. In part 1, we took you through the reason for and process of automating. Now it gets really interesting for the techies and Excel enthusiasts among us.
In this article
VLOOKUP (or VERT.ZOEKEN)
Remember how we ended the previous post with the image below and the question of how to convert this information? So how do you make sure that “Utrecht” automatically becomes “UT” and “Autumn” is shortened to “NJ”?
Right! Using the FLOOKUP (or VERTICAL SEARCH) function. This function retrieves a value in a column and then returns the value of another cell in the same row. To make use of this, we first created an auxiliary table in which all possible lookup values (in this example, place names) correspond to the appropriate result values (in our case, these are the abbreviations of the place names).
The syntax for this function is VERT.SEARCH(value, range, index, [is_gesorteerd]). When you enter it for our particular case, you get VERT.SEARCH(C3,H2:I8,2,False).
- Cell C3 is the value “Utrecht” that we are going to look up in the table.
- This value is looked up in the table with ranges H2 through I8.
- If there is a match, the value from the second column (column I) is then returned.
- The value [is_gesorteerd] can be TRUE (or 0) or TRUE (or 1) are. If you want to find an exact match between lookup value and result value enter 0 or FALSE here.
Et voilà, with this formula we conjure up “UT.” When you change cell C3 to another place name, for example, Amsterdam, the output changes to “AM.” We can repeat the same trick for edition, so that the input “Spring” is always displayed as “VJ” and “Autumn” is displayed as “NJ”.
When we combine these formulas all together, we get =C2&”_”&C4&”_”&VERT.SEARCH(C3,H2:I8,2,TRUE)&”_”&VERT.SEARCH(C5,!H11:I13,2,TRUE). This produces as output Dutch Heroes Comic Con_2019_UT_NJ. With another fair, another year, another place and another edition, the name will also automatically change with it. Not wrong, right?
In addition to this somewhat technical knack, we have many other automations and smarts built into the spreadsheet. We’ll take you through that, too.
VLOOKUP for location targeting.
Starting with the VLOOKUP just discussed. In fact, we use these in many more places in the document. The Swiss army knife of spreadsheet aficionados. Similarly, we use it to specify campaign location coordinates.
These coordinates are required because the client wants to advertise in a radius around the event location. You can pull this off relatively easily through the Google Ads interface, however, for high volume campaigns, this is quite a time-consuming task.
Fortunately, there is also a scalable solution; namely, you can look up the relevant latitude and longitude of a location and import it through the Google Ads Editor. Using the table below, we can use this trick to set the right radius targeting for trade shows in different places around the country.
With the VLOOKUP formula that you are now familiar with, this allows us to enter the correct coordinates for each place and transmit them linearly to the platform.
Data validation
Another gimmick we frequently use in our document is the data validation feature in Google Sheets. This functionality allows you to limit the input to a cell to a range of values you specify. Often this is in the form of a dropdown menu, as shown below in the example from our document.
How do you set this up? Select your cell and then navigate to Data > Data validation. In the section Criteria set which choices should appear in the dropdown menu. Here you can enter a list of options yourself or use a list you have put elsewhere in the document.
Tip: For cells where the date needs to be entered, you can use Date use as criteria. When setting the date, you can then double-click on the cell to easily select the desired date.
Like the VLOOKUP, we use the data validation function several times in the document. For the targeting of Display campaigns, where you can choose from over 3,000 (!) audiences, this is also a welcome addition to the document.
To get this working, we put all the possible target groups into separate columns. So you have columns for affinity and in-market audiences as well as topics you can advertise on. By pointing the cell to which you want to apply data validation to one of these columns, you make it very easy to pick out the desired target groups.
A nice functionality here is that the list is automatically refreshed based on the text you enter here (also known as autocomplete), so you don’t have to scour the entire list looking for the right audience.
Are you looking for Trekkies or persons who have an affinity for the Skywalker family? Then enter science fiction (or simply science) and autocomplete will complete it:
Conditional data validation
Now that we understand how data validation works, we can move on to advanced data validation; based on one or more conditions. Among other things, we use this in our video campaigns that we run on YouTube.
Creating a video campaign through a spreadsheet is basically quite simple, however, you need a trick to make the process of choosing campaign type and bidding strategy correct. For example, unlike Search and Display, not every bidding strategy can be linked to every campaign type.
For video campaigns, you can choose from several campaign types, including drive conversions, non-skippable and outstream. The tricky part is that just about every type requires the use of a different bidding strategy. For example, for drive conversions you can choose maximize conversions or target CPA, and for outstream you can deploy only manual CPM.
When picking the campaign type as well as the bidding strategy, you only want to show the bidding strategy options that are actually possible for the chosen campaign type. Fortunately, even this challenge can be solved with a formula. Only this time it is not a standard formula, but an array. A what? An array.
To start with this, it is useful to first record all possible combinations in the sheet.
Next, create the cell where you set data validation for the campaign type. You refer these to the cells where you have noted all possible campaign types (in the case above, H80 to L80).
Now you can start creating the array formula. In our case, it looks like this:
=ALS(C74=H80;H81:H82;ALS(C74=I80;I81;ALS(C74=J80;J81;ALS(C74=K80;K81;ALS(C74=L80;L81;””))))). After entering, remember not to simply press ENTER, but CTRL+SHIFT+ENTER. After all, Mr. array, like your troublesome sister or brother, is someone with a manual of his own. Alternatively, you could wrap the entire formula in an ARRAYFORMULA function. Or in a roll of toilet paper, see what you do.
The above formula may look a bit frightening, but it is actually just a simple nested ALS formula. The formula tries to match cell C74 (the cell containing the campaign type data validation) and H80 through L80 (the cells containing all possible campaign types). If a match is found here, the contents of the cell in row 81 (and in the case of drive conversions rows 81 and 82) become the output of the formula. And you can then use this output again in a second cell with data validation for the bidding strategy. Three cheers!
Data validation with multiple selections
When setting up data validation, you can only select one option from the list. But what if you want to check more than one? Normally this is impossible, but by using a script it can be done. Just say data validation on steroids.
The use case for this feature is to target certain counties in Facebook. How can we make sure that you can click on the counties you want to target and not have to type the counties piece by piece into the cell (since the latter is monk work and here, maybe you already figured it out, we don’t like it)?
Before we get going with the script, we first need to set up data validation, as normal via Data > Data Validation. In our case, we refer herein to a cell range containing all Dutch provinces.
Next, we can work on the scripting part. By the way, it’s actually two scripts, one that takes care of the pop-up window and one that takes care of the actual script. You can find them both at this link (free and for nothing, even we are not the lousy ones) and you install them via Tools > Scripteditor. Once you install them you will find a new menu button called Scripts and a Multi-select sub-button.
Now select the cell in which you set the data validation and click Scripts > Multi-select. A number of windows open in which you give the script access to your spreadsheet. A pop-up will then open on the right side of your screen:
The rest is self-explanatory; check the provinces you want to target and click Set. The selected cell is now automatically populated with all provinces checked, separated by commas. This cell can now be imported 1-to-1 to Facebook Ads. Still, you saved yourself some unnecessary mouse clicks! Your index finger will thank you.
Conditional formatting
Most of you may already be familiar with conditional formatting. Allows you to format cells (for example, by giving the cell a different color background) based on conditions you set. Google Sheets contains many standard terms that you can select and you can additionally use formulas here.
We find a practical example of this in our paper when entering the YouTube video URL. This is because it is important to enter the full URL (and not the shortened URL; i.e. https://www.youtube.com/watch?v=ENDQUZIX6jI and not https://www.youtu.be/ENDQUZIX6jI). By adding a formatting line like text does not contain watch make sure the input cell changes color as soon as the URL is entered incorrectly.
Thus, a cell with an incorrectly entered URL turns orange:
And a cell in which the URL did enter correctly remains yellow:
We find another example when entering ad copy for Google Ads. There are all restrictions and rules attached to this; for example, you may not go over a certain character limit and your description lines may not both contain an exclamation point. Faintly, Google.
To check that your text is not over the character limit, you can use the formula =30-LENGTH(A1), where you replace the number 30 with the appropriate character limit (30 for headers, 90 for descriptions) and have the text to be checked in cell A1. With a conditional formatting rule, you make sure that all outcomes that are less than 0, i.e., where you have gone over the character limit, are highlighted by a different cell color. The formatting rule then becomes less than or equal to -1.
In the example below, we went 4 characters over the limit:
Removing the word “Autumn” puts us back nicely within the limit:
A third practical example of conditional formatting is slightly more complex than the previous two. Indeed, in doing so, we use a formula to detect double exclamation points. In our document, this formula is = IF(ISGETAL(FIND.SPEC(“!”;B9));AND(IF(ISGETAL(FIND.SPEC(“!”;B10));1;0))).
The combination of ISGETAL and FIND.SPEC allows you to search for certain character or word, in our case the exclamation point. The function AND looks up this character in both cell B9 and B10. And should the exclamation point be found in both cells, the output will be 1, if not, it will be 0. With an output 1, you trigger the conditional formatting rule and the formatting style is applied (in our case, the cell gets an orange background).
With one exclamation point, nothing is wrong:
But using two exclamation points makes it clear that you are breaking a rule:
So do yourself a favor, make sure your ads are error-free and follow the (formatting) rules nicely. And remove your dt errors right away (am I right, my fellow language Nazis?).
Other example of ALS cell contains text
In addition to detecting double exclamation points, we use the trick in which we combine ISGETAL and VIND.SPEC even more often in the document. Similarly, to match keywords to the right campaign. With this feature, we separate branded from non-branded and trade show or event-related words from the rest, among other things:
To make this function a little more readable, we will dissect it:
- This section compares the name of the scholarship (entered in the “General” tab in cell C2) with the keyword in cell A1. Should a match be found here then the output of the cell becomes “Branded”
- Should the keyword “stock market” (with this you match on both stock market and exchanges) appear in the keyword, the output will be “Stock Market”
- Should the keyword “even” (for a match on both event and event) appear in the keyword, the output will also be “Scholarship”
- If no match is detected in any of the above cases then the output will be “General”
With such separation of keywords at the campaign level, you ensure that it is easier to keep track and make better decisions when managing your campaigns. For example, you probably want to set very different budgets and bids for your branded campaigns and your generic campaigns. With this formula, then, you ensure that keywords are assigned to the right campaigns.
What does blog 3 say?
In the next (and final) blog, we cover the latest technical gadgets and briefly look ahead to developments in the future.
- Importing display banners
- Finding out channel IDs
- Facebook Image Hash / Video ID
- Facebook interests API
