Archives for 

seo

Customer Journey Maps – Whiteboard Friday

Posted by kerrybodine

At every stage in the marketing funnel, it’s crucially important to empathize with your customers’ interactions with your business, feeling great about the high points and frustrated by the lows. In today’s Whiteboard Friday, MozCon 2014 speaker Kerry Bodine shows us all about customer journey mapping—a tool that allows us to visualize and learn from those experiences.

Video transcription

Hi, I’m Kerry Bodine. I am a customer experience consultant, and I am the co-author of a book called “Outside In.” The subtitle of the book is “The Power of Putting Customers at the Center of Your Business.” That’s really what I am all about. I try and help companies to take customer knowledge, customer insights and really bring it into their organization, so that they can become more customer-centric.

What I’d like to share with you today is a tool from the customer experience world that I think is really critical for every marketer out there to understand. It is called a “customer journey map.” Really simply, all a journey map is, is it’s an illustration that shows all the different steps that your customers go through as they do business with you over time.

In addition to showing just what they do, it also shows customers’ thoughts, their feeling, and their emotions. The goal of the customer journey map is really to get a holistic view of what the customer is going through from their point of view and really what it’s like for them on a personal level, that human level. I’ll share a little bit about how customer journey maps work, and we’ll wrap up with how you can do this yourselves within your own organizations.

What I’ve got behind me here is the start of a customer journey map, what this typically looks like. As you can see, as customers interact with you, it’s not just a straight line. Some of those experiences are going to be better, and some of those experiences are going to be worse. What you want to do is you want to track what those actually look like over time. Now ideally, you are going to be understanding where those bright spots are. Those are the things that your company is really doing well to help meet your customers’ goals.

You’ve also got to understand where things aren’t going so well for your customers, where you’re not delivering the value that they’re looking for, where you’re making it really difficult just to do business with you, or where you’re just not treating them as a human being. You’re treating them as just kind of a line in a spreadsheet or maybe a record in your CRM system. We’ve got to really understand our customers at a human level.

Why is a journey map like this so important for marketers? Well, part of the reason is that, at some point as we go along this journey, we’re going through that typical marketing funnel. The customer first learns about your products and services. Then there’s consideration, and they move into actually purchasing whatever it is that you’re providing. We’re not talking with those words when we’re doing a journey map, because no customer is out there saying, “Oh, I’m in the awareness phase right now of buying shoes.”

No, they’re just saying, “Hey, I’m out there researching shoes.”

Those are the types of steps that you put on here. As you go along, your customers are learning about your products and services, and then they’re buying them hopefully. At some point, the traditional role of the marketer ends. The rest of the customer journey, maybe receiving those shoes in the mail if they’ve ordered them online and then trying them on, and if they don’t fit, maybe the process of returning them, that all happens after that purchase point. We’ve got half of this customer journey that’s really all about making promises to the customer.

This is what marketing is traditionally set up to do. They are set up to help customers to understand why it’s going to be so amazing to spend money with their particular company. All of these different touch points here are in the service of making a promise to the customer about what they’re going to get after they’ve purchased from you. All of the touch points that follow are really about delivering on that promise. As you can see in this journey, the organization really didn’t deliver well on whatever it was that was promised during this phase over here.

The interesting thing is that not only do marketers need to care about these journey maps, but everyone else in the organization does as well. While marketers might be primarily responsible for this process of making promises, there are many, many other parts of the organization that are primarily responsible for delivering on those promises. You’ve got people who are working in customer service, in retail, in finance, in operations, behind the scenes, in parts of the organization like legal and IT, parts of the organization that never even talked to a customer typically during their employment at that company or maybe in their entire careers.

These journey maps can help to unite all of the different parts of the organization. It can help someone in marketing understand really what they need to be promising in order to have expectations set correctly for the end of this process. It can also help people who are responsible for delivering the rest of the customer experience. It can help them understand really what that pre-purchase experience is like and really what is being promised to customers.

This is really an effective tool at helping to break people out of their organizational silos, getting them to understand that holistic customer viewpoint across all the different touch points, and getting people within the organization to have empathy for each other, their fellow colleagues, or perhaps external partners, who are all playing a role in delivering this journey behind me.

How can you do this yourself within your organization? What I want to do is share with you a very simple method for doing journey mapping with any group. All you really need is to have a whiteboard like this, or maybe you’re going to have a big sheet of butcher paper that you can get at any office supply store. You want to have some markers. I typically like using Sharpie markers, because you can read them from a distance. My very, very favorite tool for doing this, packs of sticky notes.

All you’re going to do is you’re going to write down each step in the customer journey on a different sticky note. Then all you need to do is put them up on your whiteboard or your piece of white butcher paper in the order that the customer would go through their particular journey.

I mentioned buying shoes before, and what I’m putting up here are all the different steps that a customer might go through if they were buying shoes from your company. They’re going to search for the shoes. They’re going to follow a link to a website. They’re going to learn about the product. They’re going to buy the shoes. They’re going to wait to receive them. Then they’ll finally receive them. They’re going to try on the shoes, and they’re not going to fit here. They’re going to go to the website, but they can’t find the returns information. They’re going to call customer service. They’re going to get the return information. I’m running out of room here. They’re going to print a return label. They’re going to box up their shoes, and then they’re going to drop the box off at the shipper, UPS or USPS, whatever it is that they’re using.

That’s really the basic building blocks of creating a journey map. It’s just going through and mapping out step by step what the customer is going through. I like using stickers for this. You can get red and green stickers at your office supply store. You can use markers. The idea is that you’re going to note where the different steps in that process are going well and then maybe where those steps start to go south. This will give you a really good depiction of where the problem points are in your customer journey and where you need to focus on improving interactions to better meet your customer’s needs.

You can go a lot further with this. You can start detailing what your customers are thinking and what they’re feeling. You can add those in on different colors of Post-it notes. You can also denote all of the different touch points that they’re interacting with. Are they talking to the call center? Are they on the website? Are they on Google? Whatever those touch points happen to be. You can even dig down deeper into the organization to start to identify who is responsible for all of those different interactions, so that again you really know where you need to be focusing on fixing the systemic problems within your organization.

What I would recommend that you do is conduct this type of exercise with people from across your organization. I mentioned that this is a really great tool for breaking down organizational silos. Really, that’s only going to happen if you get the people from all of those different organizational silos involved in creating this diagram. Hold a half-day workshop. Bring in people from all the different parts of your organization, maybe some of your key partners, and map out what you think this journey is based on your best assumptions about customers.

But don’t stop there, because, often, what we find are that our assumptions are either wrong or they’ve got big gaps in them. The second step to this process is to bring customers into the workshop and have them validate this. The beauty of this is that when you’ve created this out of sticky notes, your customers are going to have no problem going up and removing sticky notes, adding new sticky notes, moving them around so that the journey more accurately reflects what it is that they go through when they do business with you.

That is Journey Mapping 101. I hope that I’ve introduced you to a tool that you can use within your organization. If you would like more information about customer journey maps, please visit my website. It’s KerryBodine.com/CustomerJourneyMaps. Thanks very much.

Video transcription by Speechpad.com


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

Continue reading →

Google Webmaster Tools Just Got a Lot More Important for Link Discovery and Cleanup

Posted by RobertFisher

This post was originally in YouMoz, and was promoted to the main blog because it provides great value and interest to our community. The author’s views are entirely his or her own and may not reflect the views of Moz, Inc.

What if you owned a paid directory site and every day you received emails upon emails stating that someone wants links removed. As they stacked up in your inbox, whether they were pleasant or they were sternly demanding you cease and desist, would you just want to give up? What would you do to stop the barrage of emails if you thought the requests were just too overwhelming? How could you make it all go away, or at least the majority of it?

First, a bit of background

We had a new, important client come aboard on April 1, 2013 with a lot of work needed going forward. They had been losing rankings for some time and wanted help. With new clients, we want as much baseline data as possible so that we can measure progress going forward, so we do a lot of monitoring. On April 17th, one of our team members noticed something quite interesting. Using Ahrefs for link tracking, we saw there was a big spike in the number of external links coming to our new client’s site. 

When the client came on board on two weeks prior, the site had about 5,500 links coming in and many of those were less than quality. Likely half or more were comment links from sites with no relevance to the client and they used the domain as the anchor text. Now, overnight they were at 6,100 links and the next day even more. Each day the links kept increasing. We saw they were coming from a paid directory called Netwerker.com. Within a month to six weeks, they were at over 30,000 new links from that site.

We sent a couple of emails asking that they please stop the linking, and we watched Google Webmaster Tools (GWT) every day like hawks waiting for the first link from Netwerker to show. The emails got no response, but in late May we saw the first links from there show up in GWT and we submitted a domain disavow immediately.

We launched their new site in late June and watched as they climbed in the rankings; that is a great feeling. Because the site was rising in the rankings rather well, we assumed the disavow tool had worked on Netwerker. Unfortunately, there was a cloud on the horizon concerning all of the link building that had been done for the client prior to our engagement. October arrived with a Penguin attack (Penguin 2.1, Oct. 4, 2013) and they fell considerably in the SERPs. I mean, they disappeared for many of the best terms they had again began to rank for. They had fallen to page five or deeper for key terms. (NOTE: This was all algorithmic and they had no manual penalty.)

While telling the client that their new drop was a Penguin issue related to the October Penguin update (and the large ratio of really bad links), we also looked for anything else that would cause the issue or might be affecting the results. We are constantly monitoring and changing things with our clients. As a result, there are times we do not make a good change and we have to move things back. (We always tell the client if we have caused a negative impact on their rankings, etc. This is one of the most important things we ever do in building trust over time and we have never lost a client because we made a mistake.) We went through everything thoroughly and eliminated any other potential causative factors. At every turn there was a Penguin staring back at us!

When we had launched the new site in late June 2013, we had seen them rise back to page one for key terms in a competitive vertical. Now, they were missing for the majority of their most important terms. In mid-March of 2014, nearly a year after engagement, they agreed to do a severe link clean up and we began immediately. There would be roughly 45,000 – 50,000 links to clean up, but with 30,000 from the one domain already appropriately disavowed, it was a bit less daunting. I have to say here that I believe their reticence to do the link cleanup was due to really bad SEO in the past. They had, over time, had several SEO people/firms and at every turn, they were given poor advice. I believe they were misinformed into believing that high rankings were easy to get and there were “tricks” that would fool Google so you could pull it off. So, it really isn’t a client’s fault when they believe things are easy in the world of SEO.

Finally, it begins to be fun

About two weeks in, we saw them start to pop up randomly in the rankings. We were regularly getting responses back from linking sites. Some responses were positive and some were requests for money to remove the links; the majority gave us the famous “no reply.” But, we were making progress and beginning to see a result. Around the first or second week of April their most precious term, geo location + product/service, was ranked number one and their rich snippets were beautiful. It came and went over the next week or two, staying longer each time.

To track links we use MajesticSEO, Ahrefs, Open Site Explorer, and Google Webmaster Tools. As the project progressed, our Director of Content and Media who was overseeing the project could not understand why so many links were falling off so quickly. Frankly, we were not getting that many agreeing to remove them.

Here is a screenshot of the lost links from Ahrefs.

ahrefs New and Lost Links March 7 to May 7

Here are the lost links in MajesticSEO.

MajesticSEO Lost Links March to May

We were seeing links fall off as if the wording we had used in our emails to the sites was magical. This caused a bit of skepticism on our team’s part so they began to dig deeper. It took little time to realize the majority of the links that were falling off were from Netwerker! (Remember, a disavow does not keep the links from showing in the link research tools.) Were they suddenly good guys and willing to clear it all up? Had our changed wording caused a change of heart? No, the links from Netwerker still showed in GWT; Webmaster Tools had never shown all from Netwerker, only about 13,000, and it was still showing 13,000. But, was that just because Google was slower at showing the change? To check we did a couple of things. First, we just tried out the links that were “lost” and we saw they still resolved to the site, so we dug some more.

Using a bit of magic in the form of a User-Agent Switcher extension and eSolutions, What’s my info? (to verify the correct user-agent was being presented), our head of development ran the user-agent string for Ahrefs and MajesticSEO. What he found was that Netwerker was now starting to block MajesticSEO and Ahrefs via a 406 response. We were unable to check Removeem, but the site was not yet blocking OSE. Here are some screenshots to show the results we are seeing. Notice in the first screenshot, all is well with Googlebot.


But A Different Story for Ahrefs


And a Different Story for MajesticSEO

We alerted both Ahrefs and MajesticSEO and neither responded beyond we will look into it canned response. We thought it important to let those dealing with link removal know to look even more carefully. Now August and three months in, both maintain the original response.

User-agents and how to run these tests

The user-agent or user-agent string is sent to the server along with any request. This allows the server to determine the best response to deliver based on conditions set up by its developers. It appears in the case of Netwerker’s servers that the response is to deny access to certain user-agents.

  1. We used the User-Agent Switcher extension for Chrome
  2. Next determine the user-agent string you would like to check (these can be found on various sites, one set of examples can be found at: http://www.useragentstring.com/. In most cases, the owner of the crawler or browser will have a webpage associated with them, for example the Ahrefs bot.)
  3. Within the User-Agent Switcher extension, open the options panel and add the new user-agent string.
  4. Browse to the site you would like to check.
  5. Using the User-Agent Switcher select the Agent you would like to view the site as, it will reload the page and you will be viewing it as the new user-agent string.
  6. We used eSolutions, What’s my info? to verify that the User-Agent Switcher was presenting the correct data to us.

A final summary

If you talk with anyone who is known for link removal (think people like Ryan Kent of Vitopian, an expert in Link cleanup), they will tell you to use every link report you can get your hands on to ensure you miss nothing. They always include Google Webmaster Tools as an important tool. Personally, while we always use GWT, early on I did not think GWT was important for other than checking to see if we missed anything due to them consistently showing less links than others and all of the links showing in GWT are usually showing in the other tools. My opinion has changed with this revelation.

Given we gather data on clients early on, we had something to refer back to with the link clean-up; today if someone comes in and we have no history of their links, we must assume they will have links from sites blocking major link discovery tools and we have a heightened sense of caution. We will not believe we have cleaned everything ever again; we can believe we cleaned everything in GWT.

If various directories and other sites with a lot of outbound links start blocking link discovery tools because they, “just don’t want to hear any more removal requests,” GWT just became your most important tool for catching the ones that block the tools. They would not want to block Google or Bing for the obvious reasons.

So, as you go forward and you look at links with your own site and/or with clients, I suggest that you go to GWT to make sure there is not something showing there which fails to show in the well-known link discovery tools.


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

Continue reading →

Announcing the All-New Beginner’s Guide to Link Building

Posted by Trevor-Klein

It is my great pleasure to announce the release of Moz’s third guide for marketers, written by the inimitable  Paddy Moogan of Distilled:

The Beginner's Guide to Link Building

We could tell you all about how high-quality, authoritative links pointing to your site benefit your standing in the SERPs, but instead we’ll just copy the words straight from the proverbial horse’s mouth:

“Backlinks, even though there’s some noise and certainly a lot of spam, for the most part are still a really, really big win in terms of quality for search results.”
— Matt Cutts, head of the webspam team at Google,  2/19/14

Link building is one area of SEO that has changed significantly over the last several years;  some tactics that were once effective are now easily identifiable and penalized by Google. At the same time, earning links remains vital to success in search marketing: Link authority features showed the strongest correlation with higher rankings in our 2013 ranking factors survey. For that reason, it has never been more important for marketers to truly earn their links, and this guide will have you building effective campaigns in no time.

What you’ll learn


1. What is Link Building, and Why Is It Important?

This is where it all begins. If you’re brand new to link building and aren’t sure whether or not it’s a good tactic to include in your marketing repertoire, give this chapter a look. Even the more seasoned link earners among us could use a refresher from time to time, and here we cover everything from what links mean to search engines to the various ways they can help your business’s bottom line.

2. Types of Links (Both Good and Bad)

Before you dive into building links of your own, it’s important to understand the three main types of links and why you should really only be thinking about two of them. That’s what this short and sweet chapter is all about.

3. How to Start a Link Building Campaign

Okay, enough with the theory; it’s time for the nitty-gritty. This chapter takes a deep dive into every step of a link building campaign, offering examples and templates you can use to build your own foundation. 

4. Link Building Tactics

Whether through ego bait or guest blogging (yes, that’s  still a viable tactic!), there are several approaches you can take to building a strong link profile. This chapter takes a detailed run through the tactics you’re most likely to employ.

5. Link Building Metrics

Now that the links are rolling in, how do you prove to ourselves and our clients that our work is paying off? The metrics outlined in this chapter, along with the tools recommended to measure them, offer a number of options for your reports.

6. The Good, the Bad, and the Ugly of Link Building

If we’re preaching to the choir with this chapter, then we’re thrilled, because spammy links can lead to severe penalties. Google has gotten incredibly good at picking out and penalizing spammy link building techniques, and if this chapter isn’t enough to make you put your white hat on, nothing is.

7. Advanced Link Building Tips and Tricks

Mastered the rest of what the guide has to offer? Earning links faster than  John Paulson earns cash? Here are a few tips to take your link building to the next level. Caution: You may or may not find yourself throwing fireballs after mastering these techniques.

The PDF

When we released the Beginner’s Guide to Social Media, there was an instant demand for a downloadable PDF version. This time, it’s ready from the get-go (big thanks to David O’Hara!).

Click here to download the PDF.

Thanks

We simply can’t thank Paddy Moogan enough for writing this guide. His expertise and wisdom made the project possible. Thanks as well to Ashley Tate for wrangling the early stages of the project, Cyrus Shepard for his expert review and a few key additions, Derric Wise and David O’Hara for bringing it to life with their art, and Andrew Palmer for seamlessly translating everything onto the web.

Now, go forth and earn those links!


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

Continue reading →

How to Build Your Own Free Amazon Organic Search Rank Tracker

Posted by n8ngrimm

Do you want a free tool that tracks your organic search rankings in Amazon? Yes? You’re in luck.

I am going to show you how to build your own organic search rank tracking tool using Kimono Labs and Excel.

This is a follow-up to  my last post about how to rank well in Amazon, which covered the basic inputs to Amazon’s ranking algorithm. It received a lot of comments about my rank-tracking prototype in Google Docs; the Moz community is overflowing with smart people who immediately saw the need for a tool to track their progress. As luck would have it, something in Google Sheets broke the day after I published, so I had to replicate the rank tracking tool in Excel using the SEOTools for Excel plugin. The Excel tool is a low-setup way to record your progress, but if you want to track more than a few terms, it is very laborious. I’ve since built a more (but not completely) automated, scalable way to track rankings using Kimono Labs to scrape the data and Excel to run the reports.

(Shout out to Benjamin Spiegel for turning me on to Kimono Labs through an excellent Moz post.)

Pros and cons of rank tracking

The death of Google rank tracking has been widely reported, so I feel compelled to review why Amazon rank tracking is both useful and a terrible KPI.

Amazon rank tracking is great because…

  • You get feedback on your content optimization. How else are you going to determine if your content changes actually produce a positive effect?
  • It can provide a possible explanation for increases in listing traffic and sales. Amazon doesn’t provide traffic source data so you’re often left guessing about the source of changes.

Amazon rank tracking is a terrible KPI because…

  • You have no way of assigning a monetary value to a rank. Amazon does not report on search query volume, you don’t know how well your users convert for each keyword, you don’t know the click-through-rate at each position, and you don’t know what percentage of users use organic search vs. other methods of finding your product.
  • Many factors besides rankings will drive your success on Amazon. Inventory outages, winning the Buy Box, and a good seller rating will impact sales drastically and directly. You can even assign revenue and profit numbers to some of those attributes.

So use rankings as a leading indicator of traffic and sales improvements and to see if your changes are making a difference.

Overview

To build our rank tracking tool, we’re going to

Build the scraper

Extract structured data from an Amazon search

Kimono Labs has some great documentation on using their tools. If, at any point, you get lost or want to do something slightly different from my scraper, you can find their documentation here. I’m going to show you the fastest way to copy my existing scraper so you can get up and running as quickly as possible.

After you create an account with Kimono Labs and install their bookmarklet or Chrome extension, the first thing you need to build a scraper is a URL to start scraping. I’m using this search in Amazon as my start URL: http://www.amazon.com/s?field-keywords=juicer. It’s a basic keyword search for the word “juicer.”

Click on the Kimonify bookmarklet, then click on the data model view.

Then click on Advanced

We’re going to make two properties.

To make things faster, you can copy the Xpath I use to identify the listing title and the ASIN (Amazon’s unique product identifier) from here:

Listing: div > div > div > h3 > a

ASIN: div > div.prod.celwidget

Next we’ll select which attributes to scrape from the elements we identified with the XPath. For the Listing property’s attributes, we’ll select the Text Content and href then click Apply.

For the ASIN attribute, we’ll select id and name. Deselect the other attributes that are selected by default, then click Apply.

So long as Amazon hasn’t changed the number of results they display by the time you are reading this, the two yellow circles at the top of the toolbar will say 15. That means that for each property defined, Kimono Labs has identified 15 different instances on the page. Does your screen look like this? If so, click Save.

Give your scraper a fancy name, tag it if you want, and decide how often you want it to run. I set mine to run daily. Kimono Labs will store a new version of the data every time it runs so if you don’t record it one day, the older data will still be there. I could have it scrape hourly but then it’s more laborious to go back through the data and find the version I want to save.

Click on the link to view your scraper. To verify that the data is gathering correctly, click on the Preview Results tab and select the CSV endpoint. You should see the title in the Listing.text field, a link to the listing in Listing.href, the ASIN in ASIN.name, and the rank in ASIN.id.

Finally, to make sure that Kimono Labs is gathering and saving data correctly, go to the API Detail tab and switch Always Save to On.

Then go to Pagination/Crawling and make sure crawling is turned on.

Congratulations! You just made a scraper that will record the ranking of every product for the keyword “juicer” every single day!

Which types of searches do you want to monitor?

There are many types of searches in Amazon. You can search for a keyword, brand, category, and any combinations of those. I’ll explain the URL parameters used to generate the searches so users can track whichever ranking is most important to your business. You will use these parameters to construct your list of URLs to crawl in Kimono Labs.

To start with, this URL can be used as a base for all Amazon searches:  http://www.amazon.com/s. We will add the parameter name-value pairs to the end to construct our search.

Name Example Value Description
field-keywords Juicer Add any keyword that you want to track
field-brandtextbin Breville Add any brand name. It must exactly match the brand name listed for the product in Amazon.
node 284507 Amazon’s ID number for a category. You can look through this list of Amazon’s top-level category nodes, download the most relevant Browse Tree Guide for every node, or simply navigate to the category and find it in the URL.
page 2 If you want to scrape beyond the first page, you’ll need to list a new URL for every page you want to scrape.

As an example, here’s the search for the keyword Juicer, with a brand name of Breville, in the Food & Kitchen category, page 2.

http://www.amazon.com/s?field-keywords=juicer&field-brandtextbin=breville&node=284507&page=2.

Here are a few notes that will be helpful (even critical) as you construct your searches.

  • Place a question mark (?) before your first parameter
  • Separate subsequent parameters with an ampersand (&)
  • You cannot search for a brand by itself; it can only be used in conjunction with a keyword or a node. I don’t know why.

Once you create every search URL, add them to the “List URLs to Crawl” field in Kimono Labs on the Pagination/Crawling tab.

Transform and store the data in Excel

Now that we’re scraping and storing rankings data for your searches every day, we want to display the data in a useful format. You could talk to a developer to hook into your Kimono Labs API, or you can download the data as a CSV and store it in Excel.

I’ll use this Excel template to transform my data into a more readable format, store the data, and create reports.

Transform

First, download the data from your Kimono Labs endpoint or results preview.

Paste the data into cell A2 of the Excel file. If the data ends up filling only the first column, go to Data >> Text to Columns. Select Delimited, click Next, select Comma, and click Finish. Your data should end up looking like this.

I use the table on the right to transform the data in a few key ways. I’ll explain each.

ASIN: I don’t transform this data; I just copy it as is. If it shows a number instead of an alphanumeric string, that’s an ISBN. It’s probably a book, movie, or cd that’s ranking

Title: Again, I’m not transforming the title, just copying it over.

Keyword: The keyword is included in the Listing.href on the left as part of the URL. I made a really long formula to extract just the keyword and replace plus symbols with spaces.

Date: This uses Excel’s TODAY() function which simply returns the current days date. If you’re adding data that is from a previous day, replace this date with whichever date is correct.

Rank: I remove the “result_” from the beginning of the ASIN.id field on the left and add one since the rankings start at zero.

Store historical data

If you continue adding data day after day, you can begin to see a change in rankings; copy the data from the table on the right (not the headers).

Then go to the Historical sheet and paste values at the bottom of the table. You just want to paste values, not formulas:

The table should automatically expand to include the new data. If not, click on the corner of the table and drag it down to include the new data. Next, click on the Data tab in the ribbon, then click Refresh All; the pivot tables in the Table and Graph sheets will now include the new data.

Build some useful reports with pivot tables and charts

In the Excel Template, I added a Pivot Table and Pivot Chart that you can use to report on the Data. The Historical data sheet has six days of rankings data. You may want to skip this section and just watch Annie Cushing’s videos on creating pivot charts and pivot tables. Once you are comfortable with pivot charts and tables, you can look at the data however you want.

Here are a few useful rankings charts and tables I use to look at rankings data. I’ve included the visualization as well as my settings in the screenshot.

All ranked keywords for a product over time

This chart displays all the keyword rankings for one product over time. I use the ASIN to filter the chart instead of the title, because the title for a listing can change over time but the ASIN won’t. This product ranks for both of our keywords and has moved around slightly throughout the six days we’ve tracked (there are no rankings on 7/31 and 8/1 for “masticating juicer” because I was not scraping data for this keyword on those days).

Two competing products for one keyword

This chart compares two products for one keyword. If you are monitoring a key competitor or have multiple products for your brand, this is a useful view. I used the filters to select the keyword “juicer” and the two products.

Rank by day

To quickly pick out which products improved or lost ranking over a time period, I use a table. In the row labels I group each rank by keyword then ASIN. I add Title below ASIN so I can recognize which product is moving up or down.

To the right of the table, I added a formula to subtract the rank on 8/2 from the rank on 8/5 (=G7-D7). To make it more obvious which products improved and which did worse, I added conditional formatting to highlight negative numbers with red and positive numbers with green.

Is there another view you’d like me to demonstrate? Ask me in the comments.

Limitations

This system for tracking and reporting rankings is not perfect.

You must manually download the data from Kimono Labs to Excel to run a report. That’s a bit clunky. This process could be automated with some code.

Kimono Labs is still in free Beta so stability is an issue. Scraping, as a general rule, fails fairly often and I’ve experienced spotty page loading. They allow you to scrape and store an impressive amount of data for free though. If you know of a better, free tool be sure to let everyone know in the comments.

Excel itself is a limitation. If you get beyond 500,000 rows of data it will start to crawl. That may sound like a lot, but if you want to track 5 pages of results for 100 keywords every day, you will generate 8,000 rows of data per day. Excel is not a long-term solution.

My company is working on a rankings tool that will address all of these limitations, but it is a couple months away. If you want an email when it’s ready, fill out the form here. For now, I’m living with the limitations of this system and getting some great insight.

Questions?

This post has a really long list of steps so if you have an issue, let me know via email (my first name @dnaresponse.com) or in the comments.


Sign up for The Moz Top 10, a semimonthly mailer updating you on the top ten hottest pieces of SEO news, tips, and rad links uncovered by the Moz team. Think of it as your exclusive digest of stuff you don’t have time to hunt down but want to read!

Continue reading →