The Integrated Marketing KPIs of Using Video to Build Your Brand

By | Building Community, Data and Analytics, SEO, Social Media, Web Marketing | No Comments


Using video to build your brand is powerful. It’s the closest thing to real, human interaction and it’s what helps to connect people to the authenticity and personality behind your company.

But if you’re simply using video to build your brand, you’re leaving opportunity on the table. There is so much in your video efforts that can be leveraged in other channels like social, search, email, and offline. Integrating these channels will accelerate the growth of your community and your brand. It’s the sum of all the parts that leads to your true value and return on investment (ROI).

Measuring The Success of Your Efforts

There’s two things to consider when you’re measuring the success of your efforts with something like video (and other types of content): you’ve got The Parts, and The Sum of The Parts. With The Parts, you want to consider how the individual pieces of your efforts, like video, are performing.

What value are they providing? Are those videos furthering the reach of your brand? Are they helping people to feel more connected? Are they garnering more leads? The same thing applies to other “parts” like social media, email marketing, other types of content, search, and offline efforts. Analyze how those parts are individually contributing to your success.

And then, more importantly, be sure you’re taking a look at The Sum of The Parts. This is your 30,000 foot view. When you add up all of the integrated pieces of your efforts like video, social, search, email, and offline, what effect have they had on your business as a whole?  It is the sum of all of these parts that provides the true ROI and that work together to build your brand and your community.

KPIs for The Parts and The Sum of The Parts

Depending on your project, and goals, there are many key performance indicators (KPIs) that will help you determine the success of your efforts and the impact they have on your business. This slide deck includes some suggested KPIs for both The Parts (video) and The Sum of The Parts (video, social, search, email, offline) communicating success indicators for integrated marketing efforts.

Remember that it isn’t just video, or social, or search, or email, or offline efforts alone that make a difference in your business. It’s the integration and sum of the parts that add up to a greater whole. And that’s what it takes to build a community.



The Super Awesome (and a little stalkerish) Email and Social Media Account Matcher

By | Building Community, Data and Analytics, SEO, Social Media, Web Marketing | 10 Comments

Mack Web is  now Genuinely. Learn more.

– –

Back in August, there was quite a rush of excitement around the office when our friends over at SEO Gadget wrote a blog post about using the FullContact API to mine user data.

We’re nerds. We admit it.

The one thing that really caught our eye was the ability to find social networks based upon a user’s email address. Say for example you have an email list that you collected at a conference. Now you’d like to follow up with those users via email, but you’d also like to find them on Twitter to see if any of them are professionally active or influential.

Traditionally, you’d have to enter all the email addresses (or names) manually into Google search to see what you can find. If you’re really clever, you may try to use a gmail plug-in like Rapportive to try and match the email addresses. But that’s all a lot of work because you have to look at email addresses one-by-one. If you picked up 200 business cards at a conference, you can easily burn a day or two trying to match all the profiles.

Enter FullContact

They have a great system that allows you to run queries against their massive database of users. Even cooler, they allow you to find up to 250 matches a month for free. Although you couldn’t run an entire marketing email list for a large company with these few queries, it’s great for those conferences or small batch lists you need to try and match up. Searching for social accounts tied to 250 emails manually would take quite a while.

Since this service is set-up as an API, it’s not incredibly user friendly for us non-programmer types. Thus the Microsoft Excel macro that FullContact gives away was great because it suddenly gave non-programmers access to the FullContact database.

But we ran into a couple problems.

1. It’s really slow. It can take 5-10 minutes to run less than a hundred names. That’s lame.

2. It only works on Windows and Excel. I use a Mac. We had one of our interns run it this summer, but he is back at school. And I’m not buying a computer, a copy of Windows, and a copy of Excel just to run this.

3. Macros are a pain. You have to install them to make sure they are operating correctly. Sometimes they don’t work. Sometimes they don’t run with your version of Excel. There are literally hundreds of reasons why one might not work. They are just a major inconvenience.

From Microsoft to Google

So one day, I decided I’d try to build a Google Spreadsheet that would query the API. At first I thought about trying to create a script. But I’m not that great of a programmer plus scripts require you to authorize and install them within your spreadsheet. I really wanted a plug-and-play solution. I wanted something I could give to someone who had zero background in Excel that would allow them to run the queries without any kind of training or help.

Thus after hours of trial and error, endless frustrations, and dozens of Google searches (like 6 hours of work straight…seriously), I was able to create a spreadsheet that pulls in social profiles from FullContact using only the built-in ImportXML function within Google Spreadsheets. I then managed to use a mess of CONCATENATE, Xpath, SEARCH, and other random functions(in other words, I did fancy spreadsheet things) to display each of the main social media profiles: Twitter, Facebook, LinkedIn, and Google Plus.

The Spreadsheet

So enough talking about it, let’s see how it works. First off, let me say it’s really simple. It’s literally copy and paste. I’ll include the link at the end, but here’s quick walk-through:

1. Open the spreadsheet by clicking the link. Make a copy of the spreadsheet for your own use. This document not editable, and I will not share it with you. So don’t ask. Just make your own copy. 🙂 It’s as easy as going to File>Make a Copy

Make a Copy in Google Docs

2. Next, set up a developer account with FullContact. It’s simple and easy. Just give them the info they want, and they’ll get you setup with the account.

3. Get your API key. Once you have your account created, go to the FullContact dashboard. Your API key is big and red and right in the middle of the page. You can see the screen here, but we blacked out our API code so you don’t steal it. Thief.

4. Enter the API key into your new copy of the spreadsheet. It goes in the red box labeled: API Key (clever, right?). As a rule of thumb, in this document you should edit the red cells. You should not edit the blue cells.

FullContact API Google Docs

5. Finally, open the document or spreadsheet where you have your email list saved (you do have the email addresses in some sort of list, right?). Copy only 50 emails on that list and paste them into the spreadsheet in the red cells in Column A, Rows 3-52. Within seconds, you should see social media links begin to appear as they are found by the FullContact API. All 50 ImportXML functions run at once, so you shouldn’t have to wait more than a few seconds for data to appear. Cool, huh?

6. In order to make this data accessible later, you need to copy the results into a new document (or a new sheet within this document). Once this sheet returns the data, select all the cells that you want to save, then go to a new spreadsheet, right click, and select Paste Values. If you just try to do a normal Paste, it will only copy the formulas, and that isn’t going to help you very much. Paste Values will actually paste the results (the links to social profiles) into your new sheet.

You should also know…

There are a few limits and things you should know about this document before you dig in.

FullContact API only counts successful matches. The successful match rate according to FullContact is around 60% – meaning you can probably run close to 400 emails before you run out of queries since many will be returned empty. If you don’t get anything back on a query, it won’t count as an API call. That said, if the person has other information within the FullContact database (such as another social network login, an address, or job title) that data won’t get returned to this spreadsheet, but it will still count as a positive match. You get 250 matches with the free plan and paid plans start at $99/month.

Currently, Google Spreadsheets limits the total number of ImportXML calls to 50 per document. So you can’t run more than 50 emails at a time. That said, you can paste the emails into the document, then copy the social data out into a new spreadsheet, then replace the old emails with 50 new ones. It’s still WAY faster than doing it by hand. If you upgrade to a paid plan and need to run hundreds or thousands of emails, try the Excel macro or have a developer build you a custom solution as running 50 at a time will take a while.

There is no “run” button! The ImportXML function runs automatically, which is usually a good thing. However, if you exit the spreadsheet and then reopen it later with the emails saved inside it, the sheet will immediately make those same 50 API calls. Any successful matches will count against your total. So when you’re finished with the document, you should DELETE all the emails (or your API key) so as to not waste calls every time you open the sheet.

OK, here is the link. Knock yourself out: Spreadsheet

Also, once you make a copy, you’ll be able to see the crazy long formulas I used on the back end. (For what it’s worth, this is probably the most intense spreadsheet I’ve ever made…and I have a graduate business degree). Feel free to customize the formulas to meet your data needs. I only ask that if you find other useful data to call, please share an updated copy of this sheet with me. I’m sure there are hundreds of pieces of data and requests you could pull into it. I’d love to see what y’all come up with. You can find me on Twitter: @tyler_brooks.

Hope this helps!

Onwards and Upwards – 2013 in Review

By | Building Community, Data and Analytics, Miscellany, SEO, Social Media, Web Marketing | No Comments


One of the best things about having a blog is that it becomes a natural archive of everything you’ve ever written. Better yet, it holds the story of the transformation and evolution of your company.

I am tremendously grateful for the prosperity and lessons that 2013 brought. We saw tremendous growth not just in our team, but in our reach. We continued to hone our focus, seek our passion, and experience first hand the rewards that come when you invest in your own brand and community.

What follows are some of our most visited posts of 2013 and the story that they tell of our growth over the past 12 months.

Measuring Community: KPIs and Social Media Metrics for Community Building

We’ve had many pivotal moments in this company over the last couple of years and this post was one of them. Tyler wrote  Measuring Community: KPIs and Social Media Metrics for Community Building as a start on our quest to answer the recurring question of return on investment (ROI) earned from social media. Tyler continued this conversation with Our Experience With See, Think, Do – A Reporting Framework and you can look forward to more in the days to come. This topic will be a priority on our blog as we continue to add value to the discussion throughout 2014.

This Job (as an Entrepreneur)

I am inherently an extremely positive person. I constantly exude energy and passion. I dwell in possibility. And normally that’s what I write about when I share my experiences of growing this company.

2013 was a test of my optimism. It challenged my endurance, my commitment, and my spirit. This Job (as an Entrepreneur) divulges the personal and mental struggle I sometimes face as a mom and CEO of a growing company. This was a risky one for me to write, and I’m glad I did.

More than you Ever Wanted to Know About Building Online Communities

2013 began with my very first post on building community. Over the course of the year I continued with 5 more posts that provided all kinds of details and goodness to help businesses understand the benefits of and grow their very own online communities. In order to make those posts easily accessible, I put together More Than You Ever Wanted to Know About Building Online Communities, a curated post of every community building resource I’ve written on the Moz blog.

Our Guide to Building Online Communities is Finally Here

The biggest milestone we’ve ever had in the history of our company’s existence was when we launched our community building guide. I’m really proud that we were able to mark our 10th year with this achievement. We knew people needed it and we knew it would be awesome but we had no idea how well received it would be (more than 3,100 downloads to date).

Our Guide to Building Online Communities was the final post in a video promotion series we had put together to promote the guide (or ‘Arthur’ as we so fondly refer to it). Certainly the guide was a huge accomplishment and so was the fact that we were finally experimenting with video. Sort of a buy one, get one free of achievement.

4 Flat Design Takeaways and How You Should Use Them

What I love most about the fact that 4 Flat Design Takeaways and How You Should Use Them made the list of our most visited posts from 2013 is that it was written by Nat. By the middle of the year we had all agreed to contribute to the blog, but of all of us, Nat was the most reluctant. As a designer, she didn’t recognize the strength she had in writing.

It has been incredibly refreshing to have Nat’s design knowledge on our blog. She too has had a remarkable journey this past year with starting grad school, embracing flexible work hours, and stepping up her illustrating skills. Everything that Nat writes is definitely worth a read.

Our Experience with See, Think, Do – A Reporting Framework

In 2013 we did a whole lot of testing. Throughout the year we had iterated many different versions of reporting with our clients in an attempt to effectively communicate the value of what we do. Our Experience With See, Think, Do – A Reporting Framework was a big victory for us. Adapting Avinash’s framework for use at Mack Web was a breakthrough in our approach and certainly in effectively presenting how our efforts (heavily weighted in content and social media) affect the entire brand, revenue and all.

How We Accomplished (big) Goals with Content & Social Media Marketing (in just 10 months)

I wrote How We Accomplished (big) Goals with Content & Social Media Marketing (in just 10 months) in 2012 when we first began seeing the positive results of making ourselves a client. With the use of content, social, and email marketing along with SEO and concerted offline efforts, Mack Web was quickly gaining traction and momentum in the industry. It’s neat to see that this post was one of our most visited in 2013 as we are still making Mack Web a priority and it continues to pay off.

Make Friends not Followers: Targeting the Right People on Social Media

One of the biggest contributors to our growth in the latter part of 2013 was when we pushed toward that T-shaped specialization and invited an experienced Social & Community Strategist to join our team. Ayelet spearheaded a bunch of valuable efforts for our community and industry last year.  Make Friends not Followers: Targeting the Right People on Social Media was a delightful collaboration of interviews with several community managers from some of the best companies in the industry to determine their best approach for making proactive  friends instead of just passive followers.

On Processes and Predictability

I’ve always had a strength in systems and process development. I think it comes from the many years of teaching I endured. Because of this, I continually work with the team to develop systems and processes that will make our work more enjoyable, efficient, and certainly pave the way for the team to come.

In 2012, we threw out a whole lot of systems and processes. By the end of the year, I felt like giving up as nothing seemed to stick. Then finally, toward the end of  2013, something changed and our systems and processes started working.

Tyler is our minimalist. He’s always asking why and looking for ways to reduce effort and maximize efficiency. When he  wrote On Processes and Predictability to provide his take on the need and importance of process, I knew we were making progress.

Not Remotely as Expected

It’s quite fitting that we end our year in review with Not Remotely as Expected.  In 2013, Courtney, our beloved Content Strategist and voice of the Mack Web brand, decided to move to Chicago. At first we discussed keeping her on as a contractor, thinking that her relocation meant she couldn’t contribute as a full-time member of our team. As her move got closer, I realized I had overlooked the possibility of her remaining on our team, fully in-tact, remotely.

The way we work is changing. Everything from productivity to balance and how work is ingrained in our lives. We’ve been learning to adapt and evolve in a lot of ways here at Mack Web, and allowing for a flexible work environment this year has been such a benefit. We are passionate about the work we do and diligent about the people we work with. There’s no denying that our work lives are part of our lives as a whole and so learning to blend and balance the different parts is a natural part of caring for our incredible team as we grow.

What a journey running this company continues to be. Always so much to do and even more to learn. Maybe it’s the fresh taste of the New Year, but I’ve got a feeling that 2014 is going to be a great one. We wish you all of the abundance, joy, and satisfaction you can possibly handle.

Perceived Risk in Marketing

By | Creativity, SEO, Social Media, Web Marketing | No Comments

If you’ve ever done any investing, you’ve undoubtedly been asked about risk. Do you want more guaranteed investments (like bonds or other government backed securities)? Or do you prefer an investment style that is more risky. Say, startups or technology companies?

Most portfolio managers begin any interview by asking those questions. The idea is that, ultimately, more risk brings more reward (hopefully).  But, of course, with more risk also comes more risk. There is always the chance that you could lose some or all of what you’ve invested.

The exact same is true in the marketing space. Now more than ever, there are more ways to engage with customers. In the old days you had billboards, print, radio, and TV and the choice was a fairly simple numbers game. TV was the most expensive and often gave you the greatest ROI, but if you were a small business, billboards, print, and radio were great secondary options.

Today, we’ve kept all these “old” mediums and added dozens of new ways to engage. Each one comes with its own sliding scale of risk and reward. How is a small business marketer to know where to start?

Even “the internet” doesn’t refer to a single medium, but dozens of channels and broadcasts intertwined. Social media alone can be broken down into a dozen different networks that each require their own unique blend of personality and content.

Deciding which channel to use and when is, among other things, a game of balancing the risks, whether the effort expended will return satisfactory results.

Assessing Risk

So let’s start with a couple assumptions about marketing and perceived risk. Now understand that in this article we’re talking almost exclusively about perceived risk. There are way too many variables to talk about actual risk.

After all, one company might spend $4,500 to make a video that gets millions of views while another company spends ten times that amount and gets 5,000 views. Marketing is inherently unpredictable, so analyzing real risk accurately is far beyond the scope of this post. That said, perceived risk is much easier to understand. To do this, you just need to understand the point of view of your client, manager, or customer. So perceived risk is what we’ll be working with for the rest of this post.

How Risky Do You Want to Be?

So let’s boil it down a little bit. We are going to make two assumptions with regards to perceived risk:

The harder it is to track the exact ROI of a marketing effort in the short run, the riskier it is perceived to be.

The more expensive a marketing effort is, the riskier it is perceived to be.

Let’s look at a couple examples. For instance, say that you work for a national company that sells car tires. You go and spend $1 million on a TV campaign and sales across the nation rise by about 3% over the previous month. A million dollars is a lot of money, and it’s difficult to tie that increase directly to that campaign. How do you know that sales increase did not come from the radio ads or print ads you’ve published? How do you know that people are not just getting their ties on their cars changed before winter? This marketing can very easily be perceived as risky. Say that $1 million was around ⅓ of your total marketing budget. That’s a lot of money! Because this campaign is both difficult to track and rather expensive, it is perceived as high-risk.

With this in mind, before you start proposing marketing strategies and designing campaigns, it is important to understand the willingness to take risks of the organization you’re working with. Are you working with a bunch of risk averse business folks? Do they much prefer the predictability of PPC to the less predictable content marketing? Are they all about maximizing trackable ROI and minimizing the costs? If so, you’ll want to find a methodology that caters to their wants.

On the other hand, do you have a company that is willing to take risks? Even if they don’t have huge, Budweiser-sized budgets, are they willing to think outside the box and create content that may win big (or totally flop)? If they are, then by all means, do that!

Ironically, many companies are highly averse to taking risks online, but are more than willing to burn through thousands (or hundreds of thousands) of dollars on traditional advertisements. They will pay for full page ads in industry publications (where they can’t track any direct ROI), but do not want to spend a dime online unless they know exactly how much they are getting back.

And part of this is our fault. We’ve promised things that we can’t always deliver. In the early days of the internet (before social media) when display ads were the go-to type of marketing online, we promised businesses lots of metrics. “Track everything!” is what we screamed from the rooftops. And yet, in an age of social media and content, we are now forced to begin thinking long-term again. These types of marketing defy easy tracking – despite our best efforts.

So it’s helpful to think of your marketing mix as you would a portfolio. If you have a young company that is able (or needs to) take some drastic risks, then do something brave. Be bold. Turn heads with your content. If the company is much more conservative, they may prefer to simply sit on their PPC ads to generate additional sales leads. But it’s helpful to understand their mindset upfront, before you come up with a grand strategy that is at the opposite risk spectrum that they should be operating in.

In order to build out your portfolio, it’s helpful to think of marketing in terms of four quadrants. Let’s illustrate:


In this quadrangle we are tracking two primary metrics. The first is cost. How much does it actually cost you to do your marketing? And don’t just look at literal and direct costs. For example, people who haven’t really had to do social media marketing generally think, “Social media is free! Why not just use that?” In a sense they are right, the tool is free to use. Nobody is going to charge you to use Twitter. But there are additional costs associated with using Twitter. Do you use any tracking tools? Do you pay for a subscription to Hootsuite or Buffer?

In addition to the tools, the real cost of social comes from the time it takes to do it well. You need to invest in strategy. You need to create graphics. You need to update (or pay someone to update) your feed and respond to users. These should all be included in your costs.

Ability to Track ROI

The challenge with many marketing efforts is the ability to track a solid return on investment. We discussed this pretty in depth a couple weeks ago. There are also a bunch of other great posts across the web so start searching!

Marketers (as well as CEOs, CMOs, and especially CFOs) really, really like to know if their investments are getting a good ROI. And really it’s only sensible for them ask. After all, there are many companies spending lots of money on marketing, and they want to make sure it’s worth it.

Communicating Perceived Risk

So that a neat little graph, but how does it help us?

It’s a communication tool. Clients (or your bosses) want to know what they are paying for. They want to know (dollar-for-dollar) what they are getting from the investment they are making. This is a very fair request, but if they need to know this exactly, you’ll generally only end up investing heavily in the efforts on the right side of the graph.

SEO, PPC, and email marketing are by far the easiest metrics to track. Using Google Analytics (or some similar platform) you can build an entire campaign and report the return of virtually every single dollar that you invest. CFOs and marketers love this.

The problem with these forms of marketing (generally) is that you get a diminishing return on your investment. Eventually you get to the point where more investment does not equal more sales.

Furthermore, most of these efforts (with perhaps the notable exception of email marketing) do not help you develop a long-term relationship with your customer. Your relationship (if you can even call it that) is purely transactional. As soon as something new or shiny pops up, they’ve left you for someone else.

The most uncomfortable quadrant for most marketers is the top left. This category is dominated by things that cost a lot of money, but don’t have a very easily measurable ROI.

Funnily enough, this is also primarily where traditional marketing resides. TV ads are the pinnacle of this quadrant. Think of all the money Budweiser and Pepsi spend on Superbowl (or am I supposed to say “The Big Game”?) ads. They pay millions of dollars just to air the ad. That’s not even considering the costs of pre and post production. It’s a lot of money!

Understand Your Client’s (or Customer’s) Willingness to Take Risks With Marketing

We created the above illustration to serve as a guide to help marketers engage with their clients in meaningful discussion before strategies are developed. Each unique channel brings it’s own risks and costs, so finding the right marketing mix will be important for your brand.

Sit down with your client and talk to them about the risks that they are willing to take. What does their budget look like? Just as importantly, are they willing to live with some vagueness in their marketing efforts – at least in the short term? If you can’t give them a 100% accurate dollar-for-dollar report, are they going to be frustrated?

If they want exact figures, it might be helpful to point out how they are (likely) not getting such reports on their other marketing efforts either. Do they know exactly the ROI of the latest full page ad they put in the big industry publication? Probably not. At best they are just making educated guesses. The new era of internet marketing relies heavily on this educated guessing.

You may also want to point out the longevity of great content. A full page ad in an industry publication is done in a month. A great piece of online content about a relevant topic may last for months or even years.

So, next time you bring on a new client, talk to them about their marketing risks. How much budget do they want in each category – understanding that with great risk comes the opportunity for even greater reward. Get on the same page, then build out their strategy based upon their risks. If your primary focus is on content marketing, but they only want low risk and exact reports on ROI, maybe you’d better pass them off to your display ads team. It’s probably going to be a better fit.

Don’t Get Lazy

What I’m certainly not advocating is that we give up trying to track ROI on our social media and online efforts. We should always be doing what we can to gauge our ROI, but sometimes this is difficult at best. And clients need to understand this. So we recommend that you discuss risk with them. Some companies will be perfectly fine living in their little, happy PPC world. But brand building involves perceived risk. Are they willing to take those risks?

Because We’re Guys: The Men of Mack Web’s Quest to Build a Keyword Research Tool

By | Keyword Research, SEO | 2 Comments

Now don’t be too surprised at seeing men in the workplace; it’s the twenty first century after all, and as many of you know Mack has finally decided to add some testosterone to her team.

For those of you who don’t know me (so, all of you, really), I’m Reid, the intern. In between the coffee runs and manual labor that I assume are the standard fare for any Intern, I’ve been helping Tyler build a tool to streamline keyword research.

In the, hopefully not-too-distant future, this tool will scan the bulky, data strewn spreadsheets that I’ve come to know and love (and by love, of course, I mean fear), and spit out a sleek, new spreadsheet complete with a sorted list of keywords to target in our campaigns.

Actually, it kind of does that already. (Emphasis on the “kind of”). As of last week, we’ve got a working alpha up and running and now we’re looking to test it, turn that “kind of” into an “absolutely and with grace, ease, and aplomb.”

This is where you, our beloved lab-rats-to-be, come in.

“Help me, Obi Wan Kenobi; you’re my only hope…”

Help Me Obi Wan

To improve our code we need to know how much it sucks, where it sucks, and why it sucks. We figure that the best way to determine this is by shamelessly handing it off to other people so that we can get data from outside the office.

What exactly do we want from you?

We are looking for criticism, but we need it to be constructive. (And not just because we’re 21st century men, complete with emotions and stuff). That’s the only way we’re going to be able to make the tool better.

Ideally, we’d like you to run old keywords through our code, keywords that you’ve tried out and determined the success (or failure) of as a source of traffic.

Then check the output of our tool against which keywords you finally ended up using (or not using), see how they match up, and, if you’re feeling generous, report back to us.

Are our top keywords the same as yours? What about the low ranked keywords? Do your actual experiences with these keywords reinforce the value estimates that our tool placed on them? This is the kind of stuff we want to know.

Once we have a good picture of how the tool works in its current form we’ll tweak our algorithms, add new ones if we need to, and try to get our results more on track with what actually works.

“Beneath this mask there is an idea, Mr. Creedy.”

Beneath this mask...

Sadly, tweaks and minor fixes will only get us so far. If we want to move our tool out of alpha, into beta, and eventually into a finished product to share with the SEO community (which we intend to do, in case you hadn’t realized) we need to continue developing it.

Right now, our tool analyzes two major metrics as well as a user input weight for each keyword (I’ll go more into this later).

We can get a rough idea of which keywords are going to be valuable to us from just these three factors. In fact, so far, our outputs have matched up fairly well with the keyword research we have on hand. But, honestly, these three factors are just a few trees in the middle of a forest.

Anybody who has performed their own keyword research has their own techniques and set of metrics that they use to judge a keyword. To really hone this tool, we want to learn from your techniques and your metrics. By understanding the relationships between pieces of keyword data we can enhance our algorithms and rank keywords more accurately.

So please, please, pretty please, with sugar on top and a cherry or two: tell us what you do. What math do you use? What weight do you give particular variables? How do they interact with each other? If you’re willing to share, we promise it’ll be worth your while.

“Wait a minute, Doc. Ah… Are you telling me that you built a time machine… out of a DeLorean?”

Because Science!

There’s just one thing left. If you’re going to be giving us feedback on our tool, well, you should probably know how it works.  The tool is designed to take several factors and calculate an output number. The output then ranks keywords from most valuable to least valuable. We’re currently evaluating two major metrics and a user input value in our algorithm.

The first metric is the Google Adwords monthly searches. Now each of you (probably) has your own special way of doing keyword research. Whether you pull broad match, phrase match, exact match, or some combination of these three is entirely up to you. Regardless of  what actual values you use, just make sure you are consistent throughout the spreadsheet. If you use broad match for one keyword, make sure you use broad match for the other. Apples to apples – ya know?

As a fun experiment, you could even try two different sheets, one with broad match and one with exact match to see what happens.

“Go ahead, make my day.”


Our second metric comes straight from the big boys and girls over at Moz. Much like Google Adwords, the Moz Analytics tools will give you a difficulty ranking for keywords based on the who is trying to rank for them and just how hard they’re trying.

Unlike the Google Adwords competition ranking, though, we find the Moz Keyword Difficulty Score to be pretty accurate. So, when we want to know what kind of fight we’ll be getting into when we target a keyword, Moz are our go-to guys (and gals).

Finally, we’ve factored in the need for human input in this whole process. (Because until they’re smart enough to pull a Battlestar Galactica or a HAL, computers can’t entirely replace us).

The third variable our alpha code processes is a “transactional value”. The idea behind this is that, at a glance, most every keyword is going to indicate intent on the part of the searcher and that intent will have a certain value to your specific client. Our job, and the job of the fancy meat organs in our head buckets (which, again, barring Cylon evolution is an entirely human trait), is to determine what this value is.

For example, your client sells discount dress shoes. So a searcher using a keyword like “cheap brown leather shoes” is fairly likely to convert. This keyword probably ranks about an 8 or 9 (on a scale of 0-10).

On the other hand, a user searching “Does Tom Cruise wear brown leather shoes?” is a lot less likely to be of use to you. (Unless your blog runs a monthly feature on the footwear of eccentric celebrities). So you’d rank it around 0 or 1.

Be warned, though, that there’s a lot of room for subjective interpretation in the transactional value. The first reaction of two people to the same keyword could be wildly different, and when humans are asked to rate something on a scale we have a tendency to artificially inflate or deflate the values we assign.

To avoid submitting garbage inputs (and therefore getting garbage outputs), you need to do extensive research, consult with your clients, and be as sure as you can be of the values you attach to your keywords.

“Fasten your seatbelts. It’s going to be a bumpy ride.” (TL;DR)

So, how does this work? It’s really quite simple.

1. Create a CSV without any headers. The first column should be the keyword, the second column should be the Moz difficulty score, the third column should be the total search volume (local or global), and the fourth column should be likelihood of conversion (on a rounded scale between 0-10). (Here is an example of what a final CSV should look like)

2. Upload the CSV here.

3. Download the results.

4. Review your results. At this point the numbers are not on a scale, but the higher the number, the better the score.

5. Give us your feedback! Email us at or comment below with your thoughts.

We’d be eternally grateful for any feedback you can share. Although we can’t pay you, we do offer hugs and Skittles.