How Excel has saved my PPC life
It’s undeniable, PPC is a fast paced marketing channel; waste your time and you waste your money.
In PPC, juggling loads of data found within giant spreadsheets and trying to find sense in the numbers can be time consuming. That’s why I am going to talk about a few Excel formulas that have saved me precious hours, finger ache and sore eyes.
VLOOKUP Formula
The VLOOKUP excel function is a precious ace in your PPC hand.
This amazing in-built formula is extremely powerful when juggling with data between different tables and arrays. VLOOKUP searches for a specific value within a table, gets the meaning of this value and fetches it back to where you want it.
Confused?
Let’s look at an example:
On this spreadsheet, we’ve got access to another tab called “Status Info”:
This Status Info tab shows the meaning of each letter within my train table, as you can see:
For sure, I don’t want to miss my train. So I want to see all data within the main train timetable and fast! This is where VLOOKUP is amazing.
If I write the following in the G4 cell of my train table =VLOOKUP and click the “FX” function button…
A new window, including various fields, shows up:
The VLOOKUP works with 3 components:
- Lookup_value = The cell where the mysterious value is located
- Table_array = The location of the array within which you want the VLOOKUP to look for the data
- Col_index_num = The column you want VLOOKUP to look for within the array.
Back to our example, we want to know what “O” stands for in the F3 cell = that’s our Lookup_value. We know that the information we want is located in the array “Status Info” = that’s our Table_array. We know that the data we want sits within the 2nd column of this array, that’s our Col_index_num.
The VLOOKUP has all it needs to operate its magic. Once you fill those 3 fields in, press OK. The formatted G4 cell will show us the value we wanted. Drag the cell down to the bottom of your table (while holding the CTRL key) and that’s it!
Ta-daaaaaaa!! Oh. Wait. What’s this strange #N/A symbol?
Not to worry folks! As we’ve dragged down the G4 cell, it logically copies the formula – adding an extra row each time.
The formula should look like this =VLOOKUP(F3,’Status Info’!B2:C5,2) and should be the same in G4, G5,G6…G11. In G11 you should see something like =VLOOKUP(F11,’Status Info’!B10:C13,2). That’s the problem.
We want our VLOOKUP to focus on the array B2:C5 . Solution? Simply insert a dollar sign ($) within the formula. The $ locks the cells of the formula.
Your tweaked formula should now be: =VLOOKUP(F3,’Status Info’!B$2:C$5,2)
Start again and drag it down.
Magic!
CONCATENATE Formula
The CONCATENATE function of Excel is your best friend when it comes to building a PPC campaign. It joins character strings end-to-end. For example, the concatenation of “Cheap” and “Blue Widgets” is “Cheap Blue Widgets”.
When a PPC campaign requires keyword variations, the concatenate formula is a time saver.
Let’s take our “Blue Widgets” example back and see how we can make the CONCATENATE work.
Let’s face it, coming up with keyword variations is not the most exciting task on earth and takes time.
So if we manually start to look at the possible variations of the main keyword “Blue Widgets”.
We have:
- Variation 1: “Cheap Blue Widgets”
- Variation 2: “Expensive Blue Widgets”
- Variation 3: “Great Blue Widgets”
- Variation 4: “Best Blue Widgets”
What if you have 100 variations for one keyword, and you have 100 keywords? CONCATENATE is here for you.
Try the following in any blank cell, type =CONCATENATE(A1,” “,B1)
Hit “Enter” and you’ll see your concatenated keyword: “Cheap Blue Widgets”.
Note that if we’ve inserted the quote symbol with a space in-between (“ “) within the formula it is because we want a space between “Cheap” and “Blue Widgets”. Otherwise it would look like “CheapBlue Widgets”.
Drag the new CONCATENATED cell down and this should associate variations to the main keyword of column A (“Blue Widgets”). Don’t forget to modify your formula when dragging it down by adding a $ sign. The dragged down formula should be =CONCATENATE(A1,” “,B$1)
The process is the same for the other widgets. Simply copy and paste your formula within a blank cell and remember to check your target keyword. For “Red Widgets”, that is =CONCATENATE(A1,” “,B$2) Etc…
Copy and paste the formula, change the column letter to trigger the right keyword and repeat the process.
TRIM Formula
The TRIM could be used as a complementary function of CONCATENATE.
As a quick PPC reminder:
- The sign [ ] means “Exact Match”, it’s one the most used matching variation Google and Bing use in paid search to display an advert (e.g: The keyword [Cheap Blue Widgets] will be triggered if it 100% matches the user’s search term of “Cheap Blue Widgets”).
Now that we have CONCATENATED our main keywords (“Blue Widgets”) with their variations (“Cheap”), we now have to insert our symbol [] to create an exact match variation of a keyword so search engines recognise it to display our ads.
If you have 100 keywords and you’ve got to go through this process manually, it can be a time waster, but not if you know about the TRIM function.
Let’s take our Blue Widgets example back.
In a new blank cell, type the following function: =”[“&TRIM(B7)&”]”
This formula says “Take the keyword in B7, insert an opening bracket before the keyword and insert a closing bracket after the keyword, and display the result.”
Drag down your TRIM cell formatted cells and the exact match variations (AKA with brackets) of each keyword to create it.
Clever stuff!
SUBSTITUTE Formula
This is the SUBSTITUTE function that Excel suggests is used in conjunction with the TRIM function.
This function helps you to insert a symbol in front of each word of your cell, not only before, but also after the keyword.
Let’s get back to our Widgets example once again.
For this, I need to insert a “+” symbol in front of each word.
(The symbol “+” is used in PPC to indicate that Google should broadly match a user’s search query). If for example someone types “Blue Cheap Widgets” or “Widgets Blue Cheap”, the keyword variation +Cheap +Blue +Widgets would trigger my ad and show it to users whatever the order of the words they entered within the search query. The exact match in this case would not work because the users’ search query is not 100% matching my keyword.
In order to create these broad modifier variations I need to keep adding a “+” symbol in front of Cheap, Blue and Widgets. This is where the TRIM function is not powerful enough, though it can do the trick when SUBSTITUTE is added to the original formula.
In a new blank cell, type the following: =”+“&SUBSTITUTE(TRIM(B7),” “,” +“) and your broad match keyword is created. Note that the quote symbol “” has been used to insert a space between each word.
A “+” symbol has now been inserted in front of each word:
Drag your new SUBSITUTED cell down, and all your keyword variations will have an added “+” symbol in front of each word.
Even the most accomplished PPC practitioners would attest that some PPC tasks are fastidious and time consuming if you don’t use these excel formulas. Mastering them can make the set-up and optimisation of PPC campaigns far less convoluted.
