Featured Post

Update: SEO Issues - is it Penguin? Is it Panda? or is it me?

It was a little over a year ago that I posted the " SEO Issues - is it Penguin? Is it Panda? or is it me? " in which I detailed o...

Monday, February 19, 2018

Keyword Categorization

What is Keyword Categorization?

Simply put keyword categorization is the process in which you organize search phrases by common words. Organizing keywords helps structure your website capturing all phases of the buying process.

As mentioned in my post in 2013 "Categorizing Keywords" I cover the importance of categorizing keywords, but also a quick way to do it in Excel. This is a great strategy when working with a smaller set of terms, but what happens if you need to focus on larger sets including millions of terms to categorize that will most likely break you excel document?

There are tools like Conductor's Searchlight and Keylime that are great for dynamically tagging your terms with common words so that you can quickly categorize them. As more terms are added to your searches the dynamic tagging will add them without any additional effort from you. So easy!

Not every business has the thousands per month budget to shell out for these awesome tools so manually or setting up your own means of tagging is going to have to be done. Using Excel you can export your terms from Google Search Console and a simple sumif formula will pull in the totals for your terms.

For this lesson I am going to use my keyword list around auto make, models and year make model searches to categorize with the three distinct categories, and additional tagging to understand how many terms are reviews or the long tail and "for sale" that would be a high intent to convert.

We'll start with our exported CSV file that you'll pull from Google Search Console, Keylime, or or Keyword Planner. It should look something like this when you first open it up:
Downloaded CSV File from Google Search Console
Downloaded CSV File from Keyword Planner
 As you can see you will have three or 5 important columns. The Keyword column holds the list of terms you're going to set your lookup against. The data following falls under your Impressions, Clicks and additionally Click Through Rate and Average Position. We're going to focus on Impressions and Clicks for this exercise and use a formula to calculate the CTR from there.

Start by saving your document as an Excel Workbook. Formulas won't work in CSV.
Saving CSV as Excel WOrkbook
 Next up you'll want to create a new tab. I usually name the tab "Lookup" so that I know that this is where all my criteria I want to lookup is located.
Adding a new tab in Excel called "Lookup"
To make it easier to type in formulas and manage then I like to name the criteria that I will be using. In this case we are naming A2 through A55 "Make", doing the same with "MakeModel" and "YearMakeModel". I additionally will name the "All" tab columns as "Term" for the Keyword list, "Impressions", and "Clicks".
Excel naming feature

How to Sum Cells by List of Criteria

Using this formula and method only looks for exact match phrases to the keyword phrases in the cell and adds up all into a total. 

The formula will look like this:

=SUMPRODUCT(SUMIF(Term, Make, Impressions))

How the formula works is by using the first array which contains the TRUE / FALSE values that result from the expression (SUMPRODUCT) then using the SUMIF criteria to lookup the range (Term), Criteria (Make) and finally Sum Range (Impressions). Only the Terms that include the list of the criteria will be added up, or "summed".

Excel Sum cells by list of criteria

Excel Sum Cells by List of Criteria and is Included (not Exact)

In this case we want to add up all the keywords that include a few specific words within them. So we need to use the SUM formula to add up each of the words we have in our Lookup. In this case we have set three terms that we believe have a high user intent to purchase that are "for sale", "used" and "priced under".

A normal SUMIF sets the Range we want to lookup, the criteria of what to look for, and then what we want totaled. In this case the criteria involves a word that is located somewhere in the phrase so we will be using a wild card before and after. Ex: SUMIF(Term,"*for sale*",Impressions).

Because we are using criteria in a cell we need to concatenate the star to complete the formula. Ex:"*"&Lookups!E2&"*"

Since the Intent list just includes three words we'll SUM all three as a total.

Excel Sumif list within terms in cell
In order to calculate the Click Through Rate we'll just divide the Clicks total by the Impressions and format at a percentage.

I have saved the excel document I used for this example in my Google Drive so you can download and play with. The numbers for the terms are completely falsified, so don't be using them to optimize your auto site. 

Depending on what data you need to look at for your terms, you can use these formulas to quickly add up totals to help you understand how your SEO is working, and determine what you will need to build out to capture new opportunities.

Monday, February 12, 2018

Enterprise SEO - Four Pillars to Making In-House SEO a Success

I began my career as an in-house SEO in 2006 and while I have worked in a few roles as Director of all of Digital Marketing for startups and for an agency. However, I prefer working as an Enterprise SEO in-house. While there are many pros and cons to working for agencies (which I covered in 2009 SEO Career - Agency vs. In-House SEO) I have really found my place and have had great successes in-house for enterprise organizations. One major factor in this decision is that I have just one client and (usually) one website to focus all of my energy and efforts on. In the case at Nordstrom, in which I was the SEO Manager, I had an entire team helping me manage many aspects of the site. As the site gets larger, more complex, more people and other teams to work with and more revenue generating it becomes imperative that there are more people involved with SEO that specialize in key aspects that make the channel successful for the business. The main reason is that there are four major pillars to making SEO a success in an enterprise organization. Those four pillars include:

  1. SEO Mitigation - error management and/or technical SEO
  2. SEO Analysis/Reporting - calculating assumptions and reporting on successes
  3. SEO Project Management - determining growth and managing projects for SEO
  4. Relationship Building for SEO - Championing SEO to stakeholders and other teams

Some larger enterprise organizations will have robust teams that support the big four, with addition of SEOs that have expertise in various fields (for example an SEO that is focused on local in the U.S. or perhaps outside the U.S. for a specific country, LATAM, EMEA, etc). Some medium sized corporations or startups will often just have one SEO Manager that acts as an individual contributor that will cover all four of the key aspects on their own until the organization supports bringing on more people to take on one or a few parts (that was me in my early years). Whatever the structure for the SEO or the SEO Team in a startup, medium sized company or enterprise organization the success of SEO within that company relies on managing all four aspects successfully.

I'll dive into each one a bit more to help explain how each plays a role in making an enterprise SEO successful.

SEO Mitigation - error management and/or technical SEO

Engineers will overlook what's best for SEO.
It's up to the SEO to mitigate any issues that might arise.
No matter how supportive companies are of SEO within the organization, there are always going to be issues that come up that will negatively affect SEO. Whether it be a video project that one team launches in which the videos are in an iframe with no JSON or Schema to support them or a set of pages that generate filter URLs causing duplicate content.. The issues not only need to be identified quickly before causing any possible damage or inadvertent spamming, but they need a solution that is fully acceptable (white hat) and prioritized with assumptions associated in order to mitigate the situation.

In my experience, Since SEO equals revenue for the business, I have found it best, in an enterprise situation, to get the teams responsible for key components of the website that effect SEO to become the bestest of friends to the SEO involved. In most cases the individuals responsible for these parts of the site value SEO and understand that if they play by the rules set by Google that they will be successful in their roles. The struggle they face, though, is that they just don't know what all of the rules are; and they shouldn't be expected to. While an SEO might not know what color a button should be and where in order to drive conversions; or what merchandise should appear on what pages linked to from top navigation that an expert hired does, those experts shouldn't be expected to understand SEO at the level that a highly technical SEO does. Therefore, those teams may have a difficult time understanding the impact some of their decisions can make both negatively and positively for SEO, and look to the SEO to help them understand.

The SEO tasked with technical SEO should insert themselves into meetings, gaining trust, and being viewed as an authority in SEO to help mitigate any potential issues within the enterprise SEO landscape. In addition, staying on top of the landscape of the site and how it pertains to SEO is key. Tools like Moz (for medium sized companies), Botify, Deepcrawl, Conductor and Brightedge  allows the SEO to really dig into the site to identify any issues. For smaller sites that don't have the large budgets for enterprise SEO simply using Google's Search Console is a great way to see into how Google is viewing the site. SEOs can manage parameters, identify server code errors (,404, soft 404, and 500), understand page indexing, incoming and internal links, schema markup and many important factors that Google takes into consideration.

Let's say an SEO identified a set of pages that are extremely valuable to the business in which they generate traffic from SEO but the average position is low as a result of issues with duplicate or very similar content, and even thin content (falling under Google's Penguin updates). Ecommerce websites are the biggest culprit of developing hundreds of thousands (or even millions) of URLs that are all similar to one another. Because of the way users browse of search for products in different ways, most product teams want to develop multiple touch points in which the user can access products. Let's use a simple heel shoe for example. A user that is searching for a specific shoe like a dress shoe with a thick block heel might search "block heel pumps" but they also might search "chunky heel pumps" or "block platform heels". Unfortunately you end up with search results that look like this:
All of these pages are from the same website that all have the same (or very similar) content on them because block heels, chunky heels and block heels for women are all essentially the same.

Most enterprise companies will have a dedicated SEO focusing on the technical side of the SEO, but smaller companies that don't have the budget for large teams will most often hire just one SEO in which that person should be spending a good amount of time monitoring and mitigating any issues that might come up.

SEO Analysis/Reporting - calculating assumptions and reporting on successes

Identifying growth for SEO and reporting on successes
 are key to gaining buy in from stakeholders.
Proper SEO should never be focused just on identifying issues and fixing them. A really good SEO and SEO Team will be more proactive than just reactive. In order to grow traffic and revenue from SEO a company needs to constantly innovate and add to it to grab new opportunities.

Keyword analysis plays a major role in identifying opportunity for SEO. Using a tool like Conductor or Brightedge allows you to plug in a few terms that are driving traffic to the site and suggest additional terms that the site may not show up for that are similar. By bucketing those terms you can develop a strategy around a set of high volume and additional long tail terms with a strong user intent to purchase that will grab searches you might not necessarily show up for. With these reports come average search volume that will give you an estimated number of how many times those terms are used in a search. By using these numbers, an SEO can calculate an estimated percentage of ranking that they believe the site could obtain the first month (and subsequent months as Google indexes and places the pages of site for these terms). This number with a break down from an estimated click through rate and conversion rate with average purchase number will allow the SEO to show what the value would be if a project for those terms were to be completed. A great example of this is a keyword analysis I had completed around search for car makes, models and the long tail high intent to purchase year make model (ex: 2012 honda accord). During my research I had identified additional terms around reviews, for sale, used, and more that all would provide value to the user, but the website wasn't generating much traffic from SEO for. Using this analysis in conjunction with a report on what traffic and revenue the site was generating for these terms I put together a presentation for the company's VPs and CEO. The numbers generated from this report were also used to report against after the launch of the project.

In addition to a Keyword analysis a good enterprise SEO will know how to draft up assumptions of revenue increase when any work around SEO is done. Even the fixes that are talked about above that mitigate SEO for issues. Using the duplicate and similar content mentioned before, knowing how to fix this isn't enough. SEOs know just by looking at the situation that the issue is a problem and by making the corrections there will be an increase in traffic. However, expressing what the increase in traffic will be when the issue is resolved is what is required by an enterprise SEO. By understanding how much the click through rate will improve with an average position increase the SEO can then calculate the current click through rate adding the percentage increase if the average position of those pages saw an improvement. From there the SEO would take the average conversion rate with average order value to show the estimated revenue increase if the correction were to be made.

The SEO isn't finished with estimations and assumptions. Most enterprise SEOs are asked "what happened with that project we did?" or "How did that fix we did impact the business?". Using tools like Google Analytics in conjunction with Conductor the SEO can then report against the assumptions originally set. Conductor even has a great tool in Searchlight for Business Cases in which you plug in your estimates and it will track performance for you. For smaller sites setting up custom reports in Google Analytics works, and monitoring average position for a set of keywords that include a common word (or a few) as well as pages in Google Search Console will work.

SEO Project Management - determining growth and managing projects for SEO

SEO isn't just about being reactive.
It's about being proactive with projects that capture new opportunities.
Using the keyword analysis an SEO will often complete a full evaluation of a set of terms that could potentially constitute a project. In my role at Classmates in 2006 I had developed two major projects around developing pages for schools (we called "affiliations") and a set of pages around people's names. The idea behind the two was to have a page show up in the search results when a user would search for their school or an old friend. We even set the pages up so that they would also show for users that would be trying to find an old friend from school in a specific city.

Knowing that we wanted to target those search terms wasn't enough for SEO. I was tasked with writing up a project brief that would detail out exactly everything that was needed in order to get any sort of results. With SEO, content is key so I had added requirements around content that was valuable to the user. Working with other teams we brainstormed what users might find valuable that the business would allow us to expose outside of being logged in. I even had our legal team in the room to weigh in on what was legally allowed to expose to the public. Unfortunately, there wasn't much we could use that was provided by users so we identified data driven content (ex: people with similar names from different schools, nearby schools, rival schools, etc)  that would provide value and would be unique to each page. Requirements around friendly URLs with a hierarchy (directory to file structure), breadcrumbs, etc all were added to the project brief. I was assigned a Project Manager who I worked closely with to ensure the project was moving along and all things SEO were being addressed.

At every enterprise company I have worked with in SEO I have developed major projects. From the names and schools at Classmates to location pages at usedcars.com and Nordstrom, and even the make model and year make model project from the keyword analysis example I mentioned earlier. Each and every one of the initiatives was set up with a Project Brief and managed through a Project Manager for SEO and overseen by myself.

Relationship Building for SEO - Championing SEO to stakeholders and other teams

Nothing will ever get done for SEO
if there isn't buy-in from other teams.
One of my favorite lines to say is "90% of an in-house SEO's success stems from relationships." I covered this a bit in my post in 2007 "In-House SEO Isn't Just Optimizing" in which I state:
But what I am most thankful for is the ability to communicate and work with other individuals within the company. While my main responsibilities may be to not only increase traffic to the website through natural search marketing, my success stems from the success of others.
I wrote that post during my time at Concur in which I was on a team that was extremely collaborative. We all seemed to help each other out with our roles even though we had specific responsibilities. There were a few people on the team that knew SEO and that I could bounce ideas off of. We would work together to come up with a plan. The team included the engineering team, designers, copy writers, and paid media channels. I specifically remember a project in which the directive came from above to create a community website around travel stories for our users. Using Eloqua as an email marketing tool we all sat in a room and developed a plan around user sign up with email touch rules that encouraged engagement. I was representing SEO, but was asked to provide ideas in all aspects, even the emails (which had nearly no value to SEO, though I snuck in some ideas around link sharing encouragement).

While I wrote my post while at Concur, my realization of how much the success in my role was reliant on others was years before while working at Classmates. I found myself in heated discussions with the head of engineering and often pushing the analytics team out of their comfort zone that wasn't getting me very far. I soon took the approach of  inviting key stake holders to lunch and even set up weekly happy hours every Thursday that the CEO would attend to help build relationships I needed to gain buy-in for SEO. I soon became everyone's closest friend and was able to work through issues identified without arguing. As a result, over 10 years later I am still very close friends with quite a few people I worked with and had the head of engineering speak at one of my conferences a few years back.

During my time at Nordstrom I set up the goals of the team around strengthening relationships with other teams and being viewed as one voice of authority around SEO. I encouraged each team member to communicate often and stressed the importance of grabbing a cup of coffee or inviting people on other teams to lunch from time to time. I even found by going to lunch with a few of the stakeholders they worked in conjunction with me to prioritize projects where the team had gotten empty promises or push back in the past. Had I not have built those relationships and gotten to know the people I would not have the friends I have now, nor the support that SEO needed during my time there.

I'm not quite sure how most enterprise SEOs structure their work, if they focus on the four key aspects that I mentioned or if they have other structures they have established that work for them. I do know that after nearly 20 years working in SEO that the success that comes from SEO stems from these four key parts. For without one or another SEO would be simply just rolling along relying on the brand that a company has established as authority. But to show actual growth in enterprise SEO outside of the usual industry trends and relying on social media or PR to do it, a successful SEO will have a keen ability to mitigate SEO issues,  analyse and report on SEO, develop and manage projects for growth and have strong relationships with others within the organization.