Basic Guide to Predicting Adwords Conversions

The purpose of this blog is to provide an easy-to-understand and easy-to-use method of predicting conversions off of a chosen variable in an Adwords account.

Keep in mind – this is the simplest way, and therefore one of the less accurate. You’ll get a good estimate, but some more heavy lifting is needed to factor in the huge number of variables the average account has.

So, first thing first. Go to your Adwords account and export a report of weekly data for whatever ad group or campaign you’d like to predict. The more data you have the better (make sure to highlight the whole spreadsheet once it’s in excel, including headers, and select to format it as a table). Once you do this, you will have a spreadsheet that looks something like this:

(image has been blurred because contains client info)

(data has been blurred because of confidential client info)

Now, from this spreadsheet go to the headers – they’ll be the top row, and select the arrow next to the filter that says “Clicks”.













Once you reach this menu, click “Sort Smallest to Largest” at the top. This will sort all of the data according to number of clicks.

You will end up with a selection of data that looks something like this:














Once you have your variable and conversions data sorted properly, go ahead and make a line chart out of the conversion data. Just highlight the conversions data column, and click to insert Line Graph on the top right. Now you have a graph of conversions sorted by the number of clicks.












There are still a few more formatting things to be taken care of before we get to the predictive model.

Right click your graph and hit select data:












Once you get to the Select Data Source window, click Edit below the “Horizontal (Category) Axis Labels” title. From here, select your click data, which should already be sorted in ascending order like this:


Congratulations! Your graph is ready.

The next step is to insert the linear or exponential equation. To do this, right click on the line in your new graph and select “Add Trendline…”. You’ll see a window like this:


Select either Linear or Logarithmic, and select the “Display Equation” and “Display R-squared” boxes. Your window should look like this when you are finished:


Okay! You now have your equation. Your graph will look like this now:










The Y= equation in the top right is your new predictive model. Simply plug how many conversions you want into the Y position, and solve for X. This will get you however many of X you need (within a range, of course) and help you plan your budgets alongside your goals.

The R2 value below the equation is a measure of the accuracy of the equation, see the Wiki. The higher this is the better. You see a lower R2 here because we’re only taking one variable into account.

Next, I’ll show you how to make it a simple tool in your spreadsheet so that you don’t have to solve the equation by hand every time you want to predict something.

There may be an easier way to get excel to solve your equation, but here’s how I do it. To restructure your equation, basically use your algebra skills to switch it around so X is on the left side. Mine looks like this:

Original: y=0.09x=0.75

Modified: x= (y-0.75)/0.09

Once you have that, you can make a table in excel where you can easily change your desired conversions and get an estimate on your variable.

Make a 2×2 table, one column is Conversions, the other is your variable (for me it’s Clicks). In the variable column, put in your modified equation, with Y being the cell under conversions. For the spreadsheet I’m using in this example, the cell under “Required Clicks” is “=(F645-0.75)/0.09”.

The results look like this:




- Joel (MOS Online Marketing Specialist)


Rule 40: Not in Favor of Olympic Athletes

Summer Olympics 2012 As the summer 2012 Olympics quickly approaches, an estimated 4 billion people will tune in to watch their country’s top athletes compete (NPR). This is an enormous opportunity for any company to drive marketing efforts and reach a massive audience, however, due to Rule 40 it is an opportunity that only official sponsors of the London games can truly take advantage of. Continue reading

What it takes to be Steve Jobs

“Why join the navy when you can be a pirate?” Wiser words never uttered by a man more influential and responsible for single-handedly transforming the computing and mobile landscape.

Steve Jobs told us to be crazy. He told us he wanted to put a ding in the universe. He didn’t put a ding, but he sure left an Apple.

A single glance at the non-stop media coverage, and you’d think America’s greatest superhero finally befell to his kryptonite. #SteveJobs, #iSad and #ThankYouSteve rotate Twitter’s trending topics, and pictures of our generation’s Thomas Edison continue to flood Facebook’s news feeds.

But in no jest, he was a hero to millions, probably billions, of people he never met. He changed the way we think about and interact with the world. Every time you have to ask your kid a computer question, remember, he’s the reason.

Continue reading

Back to Basics: What is your Marketing Objective?

When it comes to this whole marketing thing, do you sometimes feel like you’re in over your head? Maybe you’re new to the “branding” concept? You hear so much talk about “engaging your audience” via Facebook and social media, but are you completely lost in where to even start?

I’m not surprised. Go ahead, just try Googling “marketing ideas.” You’ll get 40,200,000 search results.

Talk about information overload. No wonder most business owners don’t even want to think about their marketing, so they put it off for as long as possible. I’m thinking you need a little direction. Am I right?

I’m gonna level with you – doing it right is not as easy as it looks. That’s why there are millions of professional marketing firms out there. And for the most part, the professionals will do a good job for you. But whether you’re coming up with the creative ideas, or you’re paying someone else to do it for you, you should NEVER go into your own marketing strategy blindly. It’s EXTREMELY important that you get involved. After all, who knows your clients, your product and your company’s needs and goals better than you do?

So before you delve into the details of how often to Tweet and which keywords to base your SEO around, let’s start with the basics. I just want to get your feet wet and get you thinking. Always start by figuring out what your marketing OBJECTIVE is. Everything else will come from there.

What’s your Objective?

1. Awareness – Potential customers must know of, or be reminded of your existence, your location, your product, your price range, and what makes you stand out from the competition

2. Build Contact List – Your mailing list is like gold. It’s what you rely on for promoting any campaign, and it takes a lot of nurturing to build and keep.

3. Get Involved – Your business is not soul-less. Companies are made up of people, and people like to patronize companies that they know go out of their way to help other people. Your company’s positive image reinforces the perception that you are genuinely caring and sharing.

4. Staff Incentives – A cared for and happy team makes for a harmonious, smooth operation. Customers can tell when your employees don’t want to be there. Think about the grouchy, frowning cashier at the supermarket. Make sure your employees feel appreciated!

5. Excitement – This is the kind of energy and excitement that makes you stand out from the competition. This is when you need to turn on your thinking cap and come up with some really creative promotions.

6. Frequency – When you’re trying to be “the place to go.” This is a brand loyalty thing, and keeping them coming as frequently as possible.

7. Publicity – For this one you’ve got to be interesting and newsworthy. Maybe you’re doing BIG things for the community, or you’re doing something so completely out of the box that it’s a story too good to pass up.

8. Generate Traffic - Simply getting people in the door or on the website is your goal here. Contests are great for this! You want to get potential customers to complete some sort of action, whether it’s entering the contest or downloading a form.

9. Branding – Image is everything. Period. If you don’t define your company and your brand, the market will define it for you. And that’s never what you want!

10. Increase Sales – a.k.a. Up-selling – This is every CEO’s favorite! From the sneaky but ingenious psychological tactics (a la putting candy, gum and magazines in the grocery checkout line) to having an in-depth understanding of how your industry works, there are really unlimited opportunities to do this one.

11. Creating Activity - Most industries have their “slow” periods during the year, but that’s not set in stone. This is where you will get creative to make the slow season profitable.

12. Trial Offer – Who’s gonna turn down a free sample? Come on, you’ll love it. This is when you really want to get those potential customers who are living on the edge to finally make the plunge – but without any of the risk.

(Great list compiled by Tom Feltenstein – And if you’re looking for a good book on the basics, try Tom’s “Encyclopedia of 401 Proven Killer Promotional Tactics”)

Grand Prix is a Boost for Business, but a Pain in the Butt for Baltimoreans

Is the economic boost really worth the HEADACHE for people living in the city?

Crazed drivers high on adrenaline will be speeding through the Baltimore city streets at ridiculous speeds, drafting around sharp turns and probably resulting in several head-on collisions with the wall and other drivers. So what makes this scene different from any other day in Baltimore City? This time it’s sanctioned. It’s Baltimore’s Grand Prix, baby!

In case you haven’t noticed the precariously placed sets of bleachers that have magically erected in the harbor over the last few weeks, and inconvenient road closures that you’ve been swearing at for weeks now, something exciting is coming to town. Exciting, that is, unless you have to navigate anywhere in a hurry this weekend. Even getting to work this morning was a frustrating experience.

The Baltimore Business Journal has already reported about the traffic nightmares today, including Lombard St. traffic halting to a stop during this morning’s rush hour, and creating some cranky commuters. One woman complained about sitting in traffic one block from her office for an hour. That’s enough to drive any rational person way over the edge. Continue reading