A deep understanding of Excel is key to a PPC manager’s ability to view and analyze data in a way that results actionable outcomes. That’s why we’ve created a new web series, Deep in the Sheets, beginning with the first five #TASTY hacks below! For PPC newbies and advanced pros alike, Deep in the Sheets will cover Excel hacks and analyses to streamline your workflow and processes.
Winning Display Network with Object Based Scraping
Controlling the display network placements your image ads serve on out of the gate can be cumbersome. In many cases, the return would not be worth the time it would take to hand research eligible placements, in other words the time could be better spent elsewhere.
This episode of Deep In The Sheets shows you how to automate the research process in order to fine tune placement selection through search operators. There are a few caveats here:
– Not all results harvested are eligible for GDN
– AOL search is powered by Google. Google is harder to scrape 🙂
Formulas to Calculate Your Best Bids
Learn how to manage bids that result in predictable results when automation is not an option.
Take Action with Conditional Formatting
Make your data stand out with Excel’s conditional formatting. Apply this to day parting data and find more cash in your pocket each and every day.
Getting Visual with Keyword Analysis
Sometimes starting keyword analysis can be cumbersome or intimidating. Where do you start?
This episode of Deep In The Sheets will show you how you can quickly discern where you’re spending your money and how efficiently it’s being spent using conditional formatting to give you a quick visual look at where you need to dive in deeper. This will help you decide to keep, remove, or modify keywords quickly.
Check back here on Wednesday, November 5 for the latest videos available on Deep in the Sheets! Reach out to us and we’ll work to make it happen!
Read on for the full transcripts:
Episode 1:
Hey! This is Manny, from AIMCLEAR! And on this episode of Deep in the Sheets we’re gonna talk about how to do managed placement research for your GDN campaigns, and that includes YouTube, using an object based scraper. [MUSIC PLAYS]Â Alright, so let’s jump right in.I’m going to be using an object-based scraper known as Mozenda. There’s several out there that you could use, but this one works time and time again for us, so that’s what we’re using. Basically, the reason that we’re doing this is because we like to have as much control over the placements that we show for in the Google Display Network. So what we’ll do is create a macro. Essentially that’s what it’s doing. You’re going to point and click around the internet like you would by hand, but this automates that and does it in mass. So we’re gonna start with the keywords we think [are] contextually related to the pages that we wanna show for. So, I created a keyword list, and simply put, once you created that macro, you let that run and do exactly what you would do by hand. It then retrieves those listings and then puts that into your spreadsheet. So once you get your output, you take your spreadsheet, open it up, you grab the page URLs, and you drop those into the associated campaigns. That’s it! You’re done after that.
Episode 2:
Hi, this is Jeremy. On this episode of Deep in the Sheets, I wanted to review a simple bid formula you can use when automated bidding is not an option. [MUSIC PLAYS] Alright let’s get started. Today I just wanted to show an example of a formula that I’ve used over time that’s very-very helpful when, for whatever reason I’m not able to use any automated bidding methods. My first advice to everybody is that whenever you have a chance to use automated bidding methods you absolutely should, but there are some reasons and some circumstances where that is not an option that is available to you. So in those cases, rather than just kind of bidding blindly, or following the direct recommendation from the various search engines, I like to apply a formula to things to make sure I stay within the target, in this case, cost-per-lead that we’re trying to achieve. So what we have in this spreadsheet is a sample download of data that you can pull out of either the AdWords user interface or AdWords editor. We’ve done some formatting to it just so that it’s a little bit easier to read and pay attention to on the screen. But the important thing is here—we added a new column into our download called “New Bid.” What we’ve done here in this particular column: we have two parts to the formula put together. The first part simply takes the CPL goal in this case which is $110, multiplies that times the conversion rate. In theory, the number that is the output of that, if you were to set that as your max bid, and your conversion rate held, you should come in or below that $110 CPL goal every single time. We know there’s some variability there, so you wanna make sure you have enough data that that seems to be significant enough before you really bank on that. The second piece that we’ve put in here is what I call kind of a little bid adjustment that accounts for the lower positions on the page. So sometimes I’ve run this formula it will assign a new max bid based on my conversion rate. But that bid is so low that it causes us to fall in average positions of 6, 7, 8, 9, 10 where nobody’s ever gonna click on it. So, you’re not gonna get the opportunity to generate the conversions. So what I like to do here is apply a small multiple to the bid based on the average position, and that’s what the second part of this formula does. It essentially looks up position, sees if you’re lower on the page, adds an incremental amount to the bid, to get you better positioning on the page. It’s a pretty safe way to approach things because as we all know the— the— your maximum bid is not what you actually pay on a per-click basis in most cases. So you have a little bit of head room. And this tends to be pretty safe operating in that space. What we have when we’re all done, with applying the new bids copying the formula throughout the spreadsheet we just do a little bit of conditional formatting so we can see at a quick glance the significance of the change associated with the bid either positive or negative, so as an account manager you can give something the human check once-over before you decide to just start pasting things into the account. Assuming everything looks good on this particular spreadsheet, you would have something you can simply copy and paste back into AdWords Editor and apply all of your new bids, which should keep you within your target CPL as well. And that’s how we manually adjust bids when more automated methods are not available.
Episode 3:
Hi, this is Dion. Today we’ll be looking at conditional formatting for day parting on this episode of Deep in the Sheets. [MUSIC PLAYS] So when you first pull down your data for a time of day report, the numbers aren’t always obvious right away. you can start to see some patters emerge where, we’re looking at registrations here, some times of day start to stand out. if you apply conditional formatting to this, it will really make it easy to see, right away, where the best times of day are. So, just—even just doing it on registrations, you can see that around 12 o’clock seems to be really good, and the earlier hours of the day aren’t as good. So, if we apply this to all of our KPIs, wherever they might be, it really makes it obvious where the money should be spent. In this particular account, we can see that noon and just after noon are our best times of day, and quickly see that the off hours are not good. In this particular account, we’d probably choose to only run during business hours. Using this method, it makes it very easy to do your day parting, the data really stands out with conditional formatting, and it will make you save money real quickly with this easy tip.
Episode 4:
Welcome to another installment of Deep in the Sheets. I’m Jake, and today we’re going to be going over how to use conditional formatting to do a simple keyword analysis. [MUSIC PLAYS] Today we’re gonna go over using conditional formatting to look at your core metrics for your keywords and help you decide what keywords to keep, or maybe modify, or remove. So, we can get right into it. What I like to go on first is take a glance is CTR. So I’m gonna select my whole row here, and I’m gonna go to conditional formatting and apply a color scale. I like to use red to green, as that’s kind of reminiscent of a stoplight. I think it’s very appropriate. And let’s say I want to get a better idea as well of cost associated with CTR which gives me some insight into the efficiency of my keywords. I would apply another scale here. And maybe I care the most about the keywords are spending the most. So I will go here and sort it. And this gives me a really quick visual representation of where and how I’m spending my money. So that’s just where we start with keyword analysis. It’s a really simple way to look at a bucket of information and quickly decide where you should focus you effort. Hopefully it’s useful to you and thanks for watching.
