Building An Automated PPC Spend Tracker In Google Sheets
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
Are you interested in starting an AdWords PPC account but been a little hesitant to set it up on your own? Then PPC Hero’s Starting An AdWords Account From Scratch series is just what you’re looking for. In this month’s series, we will walk you through the basics of setting up an account, from keyword research all the way to activating your first campaigns. Before you get started though there are a few questions you should ask yourself to make sure you are ready to create your own AdWords account:
Do I have a website in place and is it appealing to users? AdWords ads do a great job of getting people to your site, but that’s as far as the ads can take them. In order to keep people on your site and get them to convert you have to have a website that is visually appealing and allows the searcher to easily find the information they are looking for. Make sure you have a good, quality website before setting up any PPC account.
Do I have a PPC Strategy in place?Before you even begin to set up an AdWords account you want to make sure you have a strategy and defined goals. Check out Amy’s article on how to develop your own PPC strategy.
Am I willing to commit to PPC advertising? We have seen clients have success on AdWords with budgets as little as $500 per month to over $100,000 per month. No matter what the amount of your budget, you have to be committed to making PPC an integral part of your marketing strategy in order for it to be a success. We suggest letting your account run for 3 months to begin truly seeing its potential.
If you’ve been able to answer yes to the three questions above, then you are ready to create your first AdWords account!
Keyword research is the foundation on which you build your PPC account. The stronger the keyword list, the stronger your account will be.
What are keywords? Keywords are the words or phrases your customers would use when searching for your product or service. When a customer types in a keyword you are bidding for they will be shown an ad.
I thought it would be helpful to walk you through an example using our company Hanapin Marketing and show you how we build an initial PPC keyword list.
Pull From The Company Website
The first place you should start looking for keywords is the company website. This is the best way to identify the core words and phrases that describe your business. Looking at the website layout will also help you see any keyword themes that may be present. Let’s take a look at website for Hanapin Marketing:
Right away you can see the services we offer.
To help keep myself organized, I like to group my keywords by theme, in this case types of services offered, instead of just creating one long keyword list. Next, I’m going to click through to each individual service page and write down every relevant keyword I find on the site for each category. Here’s an example of some of the keywords I identified for PPC:
As you are going through your site, don’t forget to include branded terms when developing your keyword list. Branded keywords are extremely beneficial to any PPC account, as they tend to have higher conversion rates and lower cost per conversions. Below are the list of keywords I put into my excel spreadsheet from the website:
As we move through the rest of this example, we’re only going to focus on building out the keyword list for the Pay Per Click terms.
After you’ve pulled keywords from your website, look at your list and see if you can think of any synonyms or variations of those keywords. For example, pay per click is also known as paid search, so I’m going to substitute “paid search” for “pay per click” in my keyword list. Pay per click also has other variations. It is commonly abbreviated as “ppc” or is written to include hyphens “pay-per-click.” I want to target these keywords as well so I am going to substitute the variations in for the original keyword.
Now that you’ve spent time looking at the company website and thinking of other keyword variations, take a step back and put yourself in the customer’s shoes. What would someone who’s looking for your product or service type into the search engines?
Hanapin is targeting people who need management for their PPC accounts. However, if the person searching is inexperienced in the industry they might not use the term “pay per click” or “paid search”. They may search on more generic terms like “online advertising” or “internet marketing”.
Also think about the intent of your customer. If a person is looking for help with managing their account it’s likely that their current account is not performing well and wants to improve their account performance. Because of that “improve ppc account performance” is a longer-tail keyword we could also target.
Now that you’ve spent a lot of time brainstorming keywords, implement these keywords into a keyword research tool to uncover more keywords that fit your business. There are many great keyword tools out there, both free and paid, that will help you expand your PPC keyword list. I’m going to use the AdWords keyword tool since it’s free and one I use often, though I do recommend using a variety keyword tools to get different perspectives.
All you have to do is enter in a keyword and hit search, and Google will generate a list of related keyword ideas.
Go through the list and check the ones you think would fit into your account. You can see here I’ve selected four of the first five words, but left pay per click management software unchecked since we don’t offer that service. Once you’ve selected your keywords you can download them into an excel file and add them to your original keyword list.
Below are some of the additional keywords I found using the keyword tool.
Notice that I’ve also expanded these new keywords to the other pay per click variations identified earlier. I’ve also broken down my original pay per click list into sub categories. I try to keep myself as organized as possible when creating keyword lists because it will make setting up your AdWords account a much easier process.
Here are 3 tips for creating your initial PPC keyword list:
Keyword research is an on-going process, but starting off with a strong list of initial keywords will start you on the path to PPC success.
The most important thing to remember when developing your AdWords structure is that organization is key to making the most of your PPC account. Account structure and organization will affect your quality score and dictate how your account performs as a whole on the search engines. Now let’s get to it!
There are many ways to create a useful account structure. Your campaigns should be segmented by higher-level ideas so that you can drill down into more tightly themed ad groups and keywords. Consider the following ways to segment your campaigns:
Make sure that your ad groups follow suit and are relevant to the campaigns you have chosen. Having a campaign about handbags and associating ad groups focused on shoes and lip-gloss is not in your best interest. But setting up something along the lines of clutches, fanny packs and slings for your ad groups might perform better. The point is to make your campaigns, ad groups, keywords and ads all relevant to each other within your structure. Of course, reviewing your keyword research and lists will help you determine what kind of ad groups you will need and how you can silo those keywords for maximum performance.
It is a good rule of thumb to have around 3 to 5 ad groups per campaign so that they remain easily manageable. You can always create more concentrated campaigns and ad groups. For example, you could potentially have campaigns for each kind of handbag such as clutches, slings and fanny packs and break specific ad groups out from there. Do remember that the more concentrated and organized your structure is, the better your account will perform overall.
Once your campaigns and ad groups are configured with keywords, your next step is to make sure settings are correct. This is a very important step. Having the wrong settings in your campaigns will not completely ruin your account, but it can cause a PPC Manager to have one too many “Ah ha” and “Oops” moments.
Here are some of the top settings to make sure are set properly:
Locations and Languages
In what geographical locations do you want your ad to show? Also, are you planning to target people who speak different languages? You can change the setting here to reflect these options. Though available for the Search Network only, there are advanced location options that can be utilized for both targeting and exclusion methods.
Networks and Devices
Pretty much just what it sounds like –this section will allow you to pick where you would like your ads to show on the Internet.
Bidding and Budget
Arguably, this is the most important setting. If you set your daily budgets too high, you can blow through your entire month’s budget like a hurricane. If you set the daily budget too low, your account runs the risk of not spending the entire monthly budget. Either way, you are not allowing your account to reach it’s full potential. Lurking quietly somewhere in the middle is the perfect daily budget.
The best way to determine this is to take your total budget, divide it by the number of days in the month and then split out that number amongst your campaigns. Once the account is running, you will have a better idea of how your campaigns are spending their budgets and can reallocate accordingly! It is always best to set your budgets at a level you can afford and are comfortable with.
As for bidding settings, the best way to start out is to Focus on Clicks and Manually bidding for clicks. This choice allows you to have more control over your money. Don’t be afraid to experiment with the other bidding options, eventually, but wait until your account gains the needed data to make these decisions.
The ad delivery option is what you should worry about in this section. You can show your ads in a rotating fashion, which will allow Google to show them more evenly. This is the preferred method since it makes a/b testing for your ad copy easier and it also offers better control over your metrics. Other options include Optimizing for clicks (this is the default – change it!) and Optimizing for conversions.
This setting is not to be confused with the Delivery Method setting in the Bidding and Budget area. You do want to change the delivery method to Standard, which will show the ads evenly over time and not spend your budget all in one swoop!
These are just the basics of getting started. There is a lot to learn, but don’t feel overwhelmed. Like any worthwhile shopping spree, running a PPC account is a never-ending process that takes patience, organization and a little luck when you first start out.
Thorough keyword research and a tightly knit structure will be useless without great ads. You need to entice searchers to click on your ads, and once they land on your page, perform the desired conversion. Relevant ads and landing pages help you achieve a good cost per lead and ROI, however, there’s something even larger at play with great ad text; your Quality Score depends on it.
The AdWords system issues a keyword level Quality Score to each of your keywords (calculated according to your exact matches) and click-through rate is one of the biggest players in the calculation. A high Quality Score ultimately helps you achieve a higher position on the page and lower overall cost per clicks, which means your budget can stretch a bit further for the same amount of clicks.
You have 130 visible characters (headline, description, and display URL) to encourage a user click on your ad – that’s less than a Tweet. The best way I’ve found to write ads correctly the first time is to open up Excel, and use the LEN function to count the characters in each of your lines.
Try to use as much of your allotted characters as possible and add in punctuation where necessary. In the past, it wasn’t always necessary to add a period for the end of description line 1, because the way AdWords displayed it still made sense. Today, you’ll want to make sure that your ads have some sort of punctuation at the end of the first line whether it’s a dash, period, or a comma.
In February of 2011, Google started promoting eligible ad’s first description line to the headline. To qualify for this style, an ad needs to be above the search results and end with appropriate punctuation. For the most part, these ads look normal, just with longer headlines, but you really need to check your ads after upload to make sure that they look nice. For instance, after this change was made, ads that were missing punctuation in line 1 could end up a bit awkward to read in the longer headline version.
The example below shows a traditional AdWords ad where the missing punctuation isn’t very difficult to read. The longer headline ad on the bottom, however, makes no sense. Ads that don’t make sense (even in a top position) are more likely to receive less user attention, and all your hard work will be spoiled.
You might also notice that your ads are displayed with the Display URL in the headline. Google started doing this in May to achieve even higher click-through rates for advertisers in position 1. The domain URL in the ad headline combined with promotion of description line 1 to the headline might mean you’ll receive impressions where your ad’s headline, description line 1, andDisplay URL are all in the headline spot with a count of up to 68 characters.
Before you dive head first into ad writing, take a step back and think about the brandand productsyou’re promoting and the audience you want to appeal to. There are a lot of aspects surrounding a company that should help you in writing your ad text.
Below are five elements to writing effective PPC ad text.
Last, but not least, are the ad settings. We’ve given you a rundown of the campaign setting options in AdWords, but we’ll recap the ones vital to how your ads will work.
Frequency capping: This setting is only available for the Display Network. Essentially, you can limit the number of times your ad is shown to the same user per day, week or month for an ad, ad group, or entire campaign
You are almost at the end of the Yellow Brick Road and will soon be able to see the Great Oz of Google… You’ve gathered your keywords list from the Scarecrow (the brains of this operation for sure!), your account structure is set up from the Tin Man (I really do have a heart!) and you have all your ads written with the help of the Cowardly Lion. Last steps are to activate your account and track your leads!
Google takes you through four steps to set up your account. Since you have your research done and all of your settings are ready, this will be fairly simple.
Once you have completed the first 3 steps and reviewed your entries, you will be able to enter your Google account email address and password. There are two options here – you can sign up with an existing Google account or you can create a new one. It just depends on your preference. Our suggestion is to set up another email account specifically for AdWords. This way, if there are additional people working in the account, they won’t have access to your personal email.
If you are creating a new account, a screen like this will be revealed:
You will be asked to go through a couple of checks and balances, like verifying your email address. After that, there are only a couple more steps and we promise you will be set!
There are two ways that you can set up billing with Google for your shiny new AdWords account.
The final step to setting up your AdWords is to place your conversion tracking codes. Conversion tracking is when a cookie is placed on the user’s computer and a correct match is made, then Google credits your account with a successful conversion. A conversion is designated as a purchase, sign-up, page view or lead. To view your successful conversions in AdWords, click your Campaigns tab or click on the Reporting and Tools tab and select “Conversions.”
Here are the steps to generating and placing your conversion code snippet:
You are in! Now, the journey does not end here… there is so much more you can do with your account. It’s time for optimizations, experiments, mobile campaigns, display network campaigns – the list goes on and PPC Hero is here for you. We will help you through all of it. So, just click your heels and gather that data. Remember, there’s no place like Google…
Browse By Category
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
Learn 3 easy ways to quickly test out variants on low-traffic landing pages to improve performance.
Get a client services view on 10 traits of successful PPC account managers and see which are on your list!
Hero Conf Los Angeles is one short month away. And now we get to share the good stuff. Join us at Hero Conf Los Angeles, April 18-20, for 10 of the most fun and unique networking opportunities in the industry.
In light of the March Madness season it was thought that we could have some fun and review some of the previous PPC Hero articles on the best ways to utilize Excel in order to more efficiently and effectively manage PPC accounts.
In this webinar recording, Hanapin Senior Account Analyst Rachael Law will cover how to get your data into Sheets, as well as some useful formulas to help you manipulate your data for key insights.
Learn why digital audio advertising should be included in your strategy and leverage another way to reach potential customers while they are on the go.