About frans

Website:
frans has written 4625 articles so far, you can find them below.

How to Swaggerjack the Power of Visual Memes – Whiteboard Friday

Posted by lenawest

Visual assets like memes and truly informative infographics have always been (and will continue to be) effective ways of driving traffic and generating conversations. In today’s Whiteboard Friday, Lena West walks us through some of the more effective examples, proving that it isn’t difficult to create visual assets that get people talking.

Whiteboard Friday – Lena West – How To Swaggerjack the Power of Visual Memes

For reference, here’s a still of this week’s whiteboard:

Video Transcription

Hey there, everyone. Welcome to Whiteboard Friday. My name is Lena West from Influence Expansion, and I am here today to talk about how you can swaggerjack the power of visual memes to really boost your traffic and your SEO results.

So first, a couple of things I want to talk to you about is a couple tactics. So we’re going to kind of get into the nitty-gritty a little bit here, so tactics that I have used with our clients that I know work. So first let me also say that I am not an SEO expert, and I don’t play one on TV.

I’m a social person. But one of the things that I’ve noticed in our work with clients is when we are really heavy handed using a lot of images, you would think that it wouldn’t work, it wouldn’t be effective, but it is. I notice that not only do they get better search engine rankings, but they also get a lot of traffic and a lot of social signals. So all that’s good stuff. So I’m going to show you a couple of things that we do to get that done for our clients.

First thing I’m going to talk to you about is Wordless Wednesday. If you don’t know what that is, I’m going to go into it. Infographics, and do not laugh at me when I say infographics because I know everybody might be tired of infographics, but there’s still some life there and there’s still an opportunity for us to get it right and get some visibility going with infographics and get some juice out of it still.

Then I’m going to talk about quote graphics. So you’ve seen these probably on Facebook, a lot on Pinterest. It’s a really nice background, and then on top it’s got some text that kind of makes you feel good about yourself and good about your soul. So I’ll talk about that in a minute.

So first, let’s talk about Wordless Wednesdays. So what is Wordless Wednesday? I was first introduced to Wordless Wednesday when I started doing some work with BlogPaws, and complete disclosure, I’m on the board, but they are a great organization. BlogPaws is a pet organization, and they help pets with blogs, people who blog about pets, and that sort of thing.

What they do is they’ll post pictures of dogs and cats and ferrets, and it’s really interesting because they just post the picture, no words, hence Wordless Wednesday. They post it on a Wednesday. Because they don’t force the content on the reader, what will happen is people will start to comment like crazy about what they think that particular animal is saying or what they think the scenario is about, etc. It really boosts engagement, and it gets people talking.

The thing that I learned the most about Wordless Wednesdays is, if you Google it, like right now if you Google it, you will see that there is about 7.7 million, and it will probably be more as you’re watching this video, depending upon when you watch this, 7.7 million search results. If you look at the top five results there, you’re going to find that not a lot of them are big brands. They’re small companies. So there’s really room to grow and participate in this particular meme. I’m going to talk to you about how to do that in a second.

So that’s what Wordless Wednesdays is about. It’s about slapping up probably an innocuous looking picture and getting people to comment and share. It works. It’s super effective. Something creative happens when you don’t force content on people.

So how do you swaggerjack the Wordless Wednesday? The easiest thing to do, number one, is just chime in on Wordless Wednesdays. Just start tagging your blog posts as Wordless Wednesdays. Now that you can use hashtags on Facebook, you can do it on Facebook. Start really getting in on Wordless Wednesday.

The other thing that you can do is make your own meme. So one of my clients has, I believe she calls it, Scarlett Says Saturdays. So that’s the alliteration thing going on.

I’ve also seen Throwback Thursdays. You guys have probably seen that. It doesn’t matter. What matters is that you’re consistent. If it’s Throwback Thursdays, it’s not Throwback Thursdays once a month. It’s Throwback Thursday every Thursday. That’s the thing that makes the difference. That’s where you get the juice from this Wordless Wednesday type meme is being consistent and doing it every single week without fail.

The power of the visual meme is amazing. Some brands that get this right, and you guys probably have seen some of these ads, it’s the folks at Old Navy. Oh, they are amazing with the retro thing that they’ve done. They’ve brought back some ’70s stars, Mr. T and The Brady Bunch people. It’s just amazing what they’ve done with graphics. They’ve got that whole retro meme, that visual meme going, and it is working for them. Old Navy does more sales than Gap even does. So it’s been really effective for them.

Another brand that gets it right, our friends at Dos Equis. So what’s the saying this guy? He’s the most unforgettable man in the world or the most interesting man in the world. “When I’m busy I drink Dos Equis.” We know this guy. He’s like the Old Spice guy. He’s always around. He’s this distinguished looking gentleman with white hair and a white beard. It’s not Santa, but it’s the Dos Equis guy. But they’ve used the power of the visual meme so that every single time you see this guy’s face, you don’t even have to see the Dos Equis logo. You don’t have to see anything. All you know is something cool is going to come out of this guy’s mouth, and they’re going to make him look like James Bond meets MacGyver in this commercial.

So a couple tools that you can use to figure out which memes are hot so that you can jump on the bandwagon, KnowYourMeme.com, QuickMeme.com, and MemeCenter.com. So check those out. Those are really good places for you guys to get a start.

Second thing I want to talk about, infographics. Now I know that infographics have been overdone and overused, but I’m going to talk to you about something a little bit new in a minute. The first thing that we have seen work for our clients, in terms of getting more traffic and definitely more social shares, is going to Google images, doing a search and also going on Pinterest and doing a search for whatever industry they’re in plus the word infographics.

So if your client is in wellness, let’s say, so they’re in healthcare. Maybe it’s a vitamin company or a supplement company or something like that. Go and search for vitamins plus infographic, wellness plus infographic, healthcare plus infographic. You’d be surprised at the infographics that come up. Some of them are going to be crappy, and some of them are going to be really well done. All you have to do is copy-paste. Grab that infographic.

The beauty of infographics is, at the very bottom, mostly what you see is the attribution. So it’s whoever created it has their logo at the bottom. So you usually don’t have to worry about that when sharing infographics. Always make sure to give attribution though, because you don’t want to be steeling anybody’s stuff and trying to pass it off as your own. You don’t want any problems like that. But copy-paste. Share stuff. It doesn’t have to be your content or your client’s content for you to share it. Fill that editorial calendar with some infographics.

So for those of you who are tired of infographics that are already existing, I’ve got something for you too. Design your own. You can make your own. Your clients are sitting on tons of data. All you’ve got to do is ask them: “Hey, have you ever done a survey? What were the results? How many results did you get?” You’d be surprised at what clients reveal when you start asking the right questions.

Great way for you to design your own info-graphics, here are a couple tools, visually, Piktochart.com and Infogram. I am partial to these folks because they have a really nice pictogram. They have a really nice user interface. It’s very easy to kind of figure out what’s going on and it’s highly customizable, and what — free. So I like that.

Brands that get the infographics game right think outside of the box. So there’s this company called Warby Parker Eyewear. I’ve got to really slow it down with that — Warby Parker Eyewear. What they’ve done is they have done an annual report for I think the past two or three years. There’s one for 2010, 2011, and I think 2013 maybe, or 2012. They’ve done an annual report for their company using an infographic format. You’ve got to publish the annual report any doggone way. You may as well make it sexy. I think it’s great. Google it. You’re going to love it. You’re going to love how they’re used the infographic format to get that content out there and to share that content with their audience.

Another company who blew it out of the water, folks called LunaMetrics. You may not know who they are, but I guarantee if you work in the social space at all, you have seen their infographic. Google it. I promise you. It’s an infographic. It’s a white background, and what it does is it gives you all of the standard sizes for every single social channel layout. So it tells you the ideal size for your profile image on Facebook, your cover image for your Facebook page, your cover image for Twitter. It gives you all of those graphics all in one really long infographic. I know I have used this for us in my company. I’ve used this for our clients. I know other pros in the social space use this all the time. Who gets the credit at the very bottom?
The folks at LunaMetrics. It’s been passed around thousands and thousands and thousands of times. So really check that out.

The last and third thing I want to talk to you about in terms of swagger-jacking the power of visual memes is quote graphics. Say that three times fast. So quote graphics, you’ve seen them on Facebook and Pinterest. They’ve got that nice background, and usually it’s like a lake or some flowers or something, or maybe even a watercolor background. Some of them are bright, kind of in your face. They have some sort of saying or quote on top in very stylized text.

What I love about these images is it doesn’t matter the size of your company or your client’s company. You can use these. Here’s how. If you’re representing a bigger brand or if you work at a big brand, you can use these quotes because you get to choose what the quote image says. You get to pick which quotes you use. You can use these quotes to really humanize a big brand to bring it down, to make it connect with people in a very real way. So using words and images, you can use it connect with people.

If you’re representing a smaller brand or a smaller company or if you work at a smaller company, you can use quote graphics to develop that know, like, and trust factor with your clients and the people who are visiting your Pinterest boards or visiting your Facebook page.

Again, based on the quotes you select and the backgrounds you select, we’ve had custom backgrounds made for our clients. So we’ll create, I don’t know, a suite of like five or six custom backgrounds and just throw different quotes on the top of those various backgrounds and swap them in and out and get them up on Facebook and get them up on Pinterest. It’s been really amazing in terms of the sharability and the traffic.

Always, always, quick tip from Lena, at the very bottom put your URL or your client’s URL so that people know exactly where to go to if they want to find more information about this company that shared this great quote with them.

As always, just like with infographics, you can search, copy, paste. You can find them on your own. I think there’s a really good one on Facebook. If you go and search for quote graphics on Facebook, you’ll see it. There’s a whole Facebook page devoted to these.

The other thing you can do is create your own. I like these tools to do that. You’ve got to have your own background with most of these tools. But Pinwords is great. We use Pinwords a lot, especially if you have your own background. If your designer has done a custom background for you, Pinwords is awesome. So I’m going to circle the one that I like. Pinwords. Oops, I don’t think you guys can see that. Pinwords. I like Pinstamatic as well and Quozio. So those are three options for you to create your own.

Brands that get this right, I’m going to save Peugeot Panama for last because I love what they’ve done, and it’s like OMG. But LL Bean and HGTV. It’s very tempting when you’re on Pinterest or when you’re creating these graphics to smack products in there and use it as a sales channel. Could you do that? Yeah. But that’s like complete cheese-ball.

You want to be creative. So what the folds at LL Bean have done, so okay what’s the energy of LL Bean? What are they about? They’re about camping and outdoors and being in the wilderness and that sort of thing. It’s got that outdoorsy vibe to it. So every single board on their Pinterest board, their main Pinterest board, every board is about outdoors or animals in the wild. People are pinning this stuff like crazy. You would think, “Well, why don’t they just put pictures of their jackets?” Because nobody cares. People want to share pictures of animals and really cool tent set-ups and outdoor, what do they call it, glamping. So there’s loads of pictures of glamping on LL Bean Pinterest boards. They really get it right. Check them out.

HGTV does something very similar as well. So HGTV is all about DIY and renovating and painting your house and that whole bit. So they’ve got some boards.

But Peugeot Panama, they take it for me. What they did, you have to see it. Please Google it. What they’ve done is they’ve taken pictures of their cars, and Peugeots are kind of small cars. So what they’ll do is they’ll take pictures of the car, and they’ll chunk them up into nine or six different images and they’ll put them back together on the Pinterest board so that when you go to the Pinterest Board, it’s almost like a puzzle. It’s the coolest effect. It’s a very cool way that they’ve deconstructed the images to reconstruct the bigger picture. It’s absolutely amazing.

So I hope that you see that images are not our enemy, and images are actually our friend. We can do so much with images. It’s not just about alt text and trying to cram text in there and only use text. You can get a lot of social signals. You can get a lot of traffic and really great search engine rankings, because if you’re doing well on social, you all know that you are going to come up high on those search engine rankings.

So feel free to chat me up in the comments below. Let me know what you’re thinking. Ask me any questions. If I speak too fast and I left something out, let me know. So thanks so much for listening. See you online.

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 →

Checkout Page Optimization: Just Follow the F.A.C.T.S.

Posted by Yoast

Editor’s note: This post was co-authored by Joost de Valk and his brother Thijs de Valk.

At Yoast, we’ve recently written about how we’ve drastically changed our checkout page. This process and our findings gave us the idea to do a best practice of sorts on checkout pages. As it is, a lot of checkout pages are far from optimal, and this short film from Google Analytics is rather harsh but lays it out quite well:

There are some elements to a checkout page that, in general, really help your conversion rate. Ignore these findings at your own peril. I’ll be looking at the following factors, all of which have a serious impact on your conversion rate: Focus, Assurance, Clarity, Time, and Social proof.


Focus

A page needs focus for people to understand what it is you want. On the checkout page this is doubly true; if your focus isn’t on the process of checking out, people will get confused. And confused people don’t convert.

An easy way to add focus to your checkout page is to implement calls to action. Calls to action are somewhat of a science, and people have performed a lot of tests to find the call to action that will work on any website. However, we believe it all depends on context.

Call to action

You should always use a color for your call to action (CTA) that stands out in your design. Usually we recommend people use a color they haven’t already used in their design (or at least that page). Next, bigger is better. The bigger you make your CTA, the more attention it will draw to itself, thus the more focus it will render.

Obviously the size of your button needs to stay workable. Also think about the shape of your CTA. We like to use calls to action that are shaped like an arrow, because they give people a sense of direction. The shape of your CTA alone will give people a sense of forward movement, which is associated with all things positive:

Button2

Lastly, the copy on your CTA is of importance as well. In general, be short and concise. People don’t like reading buttons, they just want to know where it leads.

Clutter

Especially on your checkout page, you need to remove as much clutter as possible. By clutter we mean “mess” on your website that distracts from the main goal. Any element on a page that’s not aimed toward that page’s main goal is clutter, and too much clutter makes your page lose focus altogether.

Clutter that is “outbound” (takes your visitor to another page) is especially bad. Make sure that the most important thing on the page is your checkout process. This can mean removing breadcrumbs, products you’re cross-selling and sometimes even a “continue shopping” button.


Assurance

Assurance is something people crave. People have to feel safe on your website and its pages, because if they don’t, they simply won’t spend their money there. So be sure to give them the safest feeling possible.

Safety signs

One of the most important things is to assure people your website is ok using safety signs—signs that mean the page/site you’re currently viewing is free from malware and is “hacker proof.” People want a visible affirmation that the page they’re viewing is safe, especially if that page is the checkout page. They want assurance that their money will be safe. So don’t just expect people to understand your website is safe; show it.

Payment methods

People want to know as soon as possible if you’re offering the payment method they want to use. So it’s best to show the credit card logos of the companies you support. If you support PayPal, include that as well, obviously. To avoid clutter, we’ve done this on our checkout page:

Logos

This way, it actually adds clarity to the payment options, assures people we have their kind of payment method, and reduces clutter by not showing it somewhere else.

Unexpected costs

As in the video, people do not like to be surprised at the counter. That’s why unexpected costs are the #1 reason people abandon their shopping carts. To prevent this, you need to assure people that there won’t be any unexpected costs. This can be as simple as adding a line like “there will be no additional costs” next to the total of the visitor’s shopping cart.


Clarity

Clarity and conversion are often mentioned in the same sentence. If your copy and your pages aren’t clear, they just won’t convert as much as they could. So be sure to make your checkout page process as clear as possible.

Progress bar

A progress bar gives people insight in how far along the process of checking out they are, and also adds the positive effect of gamification. This gamification effect means people will want to get to the next step as fast as possible. On our own checkout page we’ve decided to have people always step in on the second step. The first step is choosing a product:

The reason we do this, is because the visitor has already taken the hardest and biggest step in your process: he chose a product he wants to buy! So validate that step and make them feel good about it. This way the visitor has already done part of the process without any effort.

Product images

It has to be very clear that the product people have added to their shopping cart is actually the product they want. Showing pictures of your product is by far the easiest way to do this. An added bonus is that (decent) pictures actually increase your conversion rate.

Inline validation

Another form of clarity is inline validation. This makes it very clear for users whether they’ve correctly filled in the field or not:

validation1Error

This kind of immediate feedback is very clear and actually makes people more likely to complete the entire form. And in fact, creating inline validation for your form fields isn’t hard at all, as we explained in a post on the technical aspects of implementing inline validation recently.


Time

Time plays an important role in your conversion rate. You literally just have seconds for your page to load and convince your visitor that they should stay on that page.

Site speed

In order to convince your visitors within that few-second window, your site needs to be fast enough. Having a website that’s too slow, can literally cost you money. This is especially important if you have a (large) user base on mobile devices.

Cookie expire time

You can actually have your website “remember” what people added to their shopping cart. This information is stored in cookies, and you can determine how long this information will be stored. Only 50% of your shopping cart visitors will buy within an hour. After that, it takes people a lot longer to make a decision.

If you want 80% of your shopping cart visitors to buy what they added to the cart, you’d have to save those items for 7 days. Doing this will make sure people can leave your website, come back later and simply pick up where they left off. This makes it a lot easier for people and removes the risk of having people go through the choice and thought process of picking one of your products twice.


Social proof

Social proof is a powerful way to persuade people. There’s nothing more useful for influencing people than other people telling them your product is awesome. Social proof works because people will get more confidence in a product if they, for instance, read good reviews from other people. The manufacturer of a product is always going to say their product is fantastic. Having other people talk about it, simply makes it more objective, and thus more trustworthy.

However, you have to take care, because social proof can also seriously backfire. You have to make sure you’re using positive social proof. Positive social proof means providing people with things they can gain from using your product, instead of what they’re risking not to gain (negative social proof). Telling people what they’re risking, or worse, how many other people are risking it, can have the exact opposite effect of what you’re trying to achieve. People will only find more reason not to use your product.

Testimonials

Testimonials are stories or pieces of text from actual users about your product. If positive, these can have great impact on people. Always be sure to use as much information of the person giving the testimonial as possible. Adding pictures has been found to increase trust, even if the picture didn’t make any sense at all. However, a picture of a face always draws more attention:

This actually has a drawback, because people looking right at you will keep your attention. If at all possible, it’s best to have the eyes of the person in the picture pointed to an important element on your page, such as your call-to-action.

Lately we’ve been reading a lot about ‘mini stories’. These are basically testimonials, but written like short stories. When written right, mini stories are supposed to have a kind of seductive effect on people.

Numbers

Telling people how many went before them in buying your products persuades people to also buy your products, especially if it’s a high number. This is due to multiple things, among which peer pressure is probably the most important. It’s basically trust based on numbers. Peer pressure means people are afraid to “stay behind” if a lot of other people have already done something. When using this tactic, be sure to use the exact number of people who’ve bought something or signed up, and to not round anything up. It turns out that for your visitors exact numbers are more believable.

Authority

Telling people what other important people or businesses have used your product or service is a great way of persuading people. Especially if you use the kind of people and businesses you know your visitors will like. This is all because of the Halo Effect, which means people’s impression of you can be influenced by their overall impression of you. So if you make that overall impression more positive by naming those great names working with you, they’ll think more highly of you.

Up to you

Your checkout page needs every much bit of thought as the rest of your website. In fact, if you manage to convert more people here, this will be the closest thing to direct money you’ll ever find in Conversion Rate Optimization. So what do you think? Let us know!


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 →

Automate Your SEO Reporting by Exporting Your Leads into Excel

Posted by Brian_Harnish

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.

For any SEO who collects email leads from web forms, the dreaded part of their existence tends to be the end of the month, when it comes to reporting conversion results to clients—verifying, re-verifying, downloading, and exporting them to generate the all-important month-end reports. It can take hours and can be very tedious, but the information gleaned from this process is well worth it. There are, however, ways to optimize your workflow to the point that it almost feels like cheating your way through the process.

By using standalone programs or macros (mini scripts within a program), a project that would normally take hours turns into minutes, and I want to take this opportunity to teach you how to do this on your own. I will use a standalone program and a macro that I found through my research to demonstrate the process so you can get a better idea of what is involved.

How to scrape leads from your Gmail (or almost any other email client)

There are a wide variety of ways to scrape leads from Gmail. You can spend the money to get a program like UBot that will help you automate the task without much effort. You can get a program like iMacros, and spend the time learning how to build proper macros that will scrape from your email box. You can spend the time to learn how to program scripts using Grease Monkey, or you can program your own stand-alone scripts. Whatever you do, you will want a solution that is as quick and easy as possible and helps to automate the task without adding much effort. I found a program on Black Hat World that is made to work on Windows, so you Mac users will need to install Windows to use it. You can download the program here.

While I am aware of the hesitation involved in downloading anything from black-hat websites, my own tests of this tool have worked out well. There are comments and reviews about this tool around the web, and it seems to work well for many users. My own research has not found an instance of this tool doing anything nefarious behind the scenes, and I would not hesitate to use it in my own email scraping.

How it works

This program works by accessing the Gmail account that is added to it and exporting the To:, From:, Body:, and Date: fields from each email. Here is how to use it:

  1. Select the email settings you wish to use to download your emails. You can select To:, From:, Subject, and Date. The “Body” export is disabled; according to the tool’s creator it would end up scraping all of the HTML.

  2. Enter your username. This is your full email address (username@domainname.com).
  3. Enter your password.
  4. Enter the server and port number you wish to use. By default, it’s set to pop.gmail.com and port # 995.
  5. Select whether or not you wish to use a secure connection. This will allow the program to access Gmail whether or not a secure connection is available. If your email does not actually require a secure connection, be sure to uncheck the box.
  6. Once these settings are selected, it will save a file in the email extractor folder with a name that looks like this: 10-1-2013-1-00 AM_Username@gmail.com.
This program is quite useful for those who either do not have or just don’t use Microsoft Outlook. If you have Outlook but are not comfortable with downloading and using this program, you can set Gmail to send your messages to Outlook, and then set up Outlook macros to to export all messages to Excel (covered later in this article).

Be sure you don’t violate your host’s terms of service

This program can also work for other email hosts. Try it! Be sure to put in your applicable login details, and you should be able to scrape your emails without any trouble. However, be sure that you are actually allowed to scrape email from your host. Not all hosts will allow you to do so. Before using egregious scraping on your email account, just double check your terms of service (ToS) so that you don’t accidentally get yourself banned from your email service. Why would an email service not allow scraping? Well, it can cause bandwidth issues if you have hundreds upon hundreds of thousands of emails to export. If this becomes an issue, you may raise an eyebrow or two at your email provider. So, be sure that you really want to do this if you want to place such a large load of use on the email services. The author of this article is not responsible for things that may happen if you do not follow specific terms of service regulations. For your reference, here are the terms of service from several common providers:

Gmail ToS: Gmail does not have any terms that specifically prohibit scraping emails. While Gmail does state you may not access it using a method other than the interface, this is a very gray area that does not provide examples. If someone is collecting lead information for a valid reason like monthly reporting for their own use, there shouldn’t be an issue. If, however, someone is using access via another method in order to take down the Gmail service, then I would imagine this is where the Terms of Service here comes into play. And this is why I mentioned the large bandwidth usage that downloading thousands of emails can cause to a server, for example. Be sure you really want to proceed before doing so and make sure you won’t be somehow banned from your email service as a result. We are not responsible for egregious misuse of a service with intentions to cause interference of the service through significant bandwidth use.

MSN ToS: Does not have any terms that ban exporting emails using any of these methods to export emails. (Be sure to read your own ToS).

Yahoo! ToS: Does not seem to have any terms that prohibit exporting emails. (Be sure to read your own TOS).

Hostgator email limits: While ToS doesn’t specifically seem to limit scraping or exporting of emails, there are policies and limits in place. According to Hostgator’s mail policy and limits page, “Each connecting IP is limited to 30 POP checks per hour.” Possible interference issues with Hostgator services and this software can occur if you are using the software 100s of times per hour, for example. However, because it uses at least one pop check in order to download your emails, you shouldn’t have too many issues unless you continue multiple downloads of emails from your account per hour. In which case, you will “likely get a password error indicating that the login is incorrect.” Such an issue corrects itself within an hour and the email checking will automatically unlock.

Also according to their mail policy and limits page, their VPS plan and Dedicated do not have the same restrictions as their shared accounts do, so you will probably have more success with high-volume scraping on your own private servers.

A fair warning, however: I haven’t specifically tested this with Hostgator, so be sure to use caution when exporting too many times.

Importing your scraped file into Excel

Once you have scraped your email and it saves it as a text file, it shows up all garbled. What we want to do now is import it into Excel so it displays all of the tab-delimited items as columns, so that we don’t have to manually copy and paste every single one. To do this, let’s open up our file in Excel by clicking on File > Import.

It will ask you: What type of file do you want to import? By default it has selected the CSV format but let’s select the text file format since our program saved this to a text file.

Now, click the file that you want to open and click on “Get Data.” The text import wizard will pop up showing you settings to choose from. Select the “Delimited” option unless it is already checked by default. Then click on Next.

In this step you can set the delimiters that your data contains. Remember when we selected the semicolon back while importing our file? Select the semicolon option here. Then, let’s click on next.

Here, we can set up our columns and set the data format. For our purposes, however, let’s just go with the default options.

Now, it will ask you where you want to put the data. You have a choice of Existing Sheet (which starts at =$A$1), new sheet, and pivot table. For the purposes of this article, let’s just go with the default and click on OK.

Here, you see we have perfectly aligned columns and data without much work. Now you can move forward with formatting these columns and data in whatever orientations or pivot tables you like.


How to download leads from Outlook to Excel

For those who use Outlook, depending on your version, it can be cumbersome to get the data out of the program and can take longer than in just about every other program. Thankfully, Outlook features macros which can be used to export all of your data in the span of just a few seconds!

Step 1: Find or create the macro script you want to use

There are a ton of options and configurations available for this task. For our purposes, we will use modified versions of the scripts located here.

Before we get started, we will need to get the basic code from the very first code snippet, shown below. This code only exports the Subject, Received Time, and Sender of the email message. Our goal is to modify this script so that our new code will extract the entire body of the message and output it to the spreadsheet as well. Don’t worry! I am going over each line of code that we modify in this tutorial! This way, you will understand exactly what we are doing and why.


Sub ExportMessagesToExcel()
  Dim olkMsg As Object, _
     excApp As Object, _
     excWkb As Object, _
     excWks As Object, _
     intRow As Integer, _
     intVersion As Integer, _
     strFilename As String
  strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
  If strFilename <> "" Then
     intVersion = GetOutlookVersion()
     Set excApp = CreateObject("Excel.Application")
     Set excWkb = excApp.Workbooks.Add()<br>  Set excWks = excWkb.ActiveSheet
     'Write Excel Column Headers
     With excWks
        .Cells(1, 1) = "Subject"
        .Cells(1, 2) = "Received"
        .Cells(1, 3) = "Sender"
  End With
  intRow = 2
  'Write messages to spreadsheet
  For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
     'Only export messages, not receipts or appointment requests, etc.
     If olkMsg.Class = olMail Then
        'Add a row for each field in the message you want to export
        excWks.Cells(intRow, 1) = olkMsg.Subject
        excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
        excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
        intRow = intRow + 1
     End If
  Next
     Set olkMsg = Nothing
     excWkb.SaveAs strFilename
     excWkb.Close
  End If
  Set excWks = Nothing
  Set excWkb = Nothing
  Set excApp = Nothing
  MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub
Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
  Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
  On Error Resume Next
  Select Case intOutlookVersion
     Case Is < 14
        If Item.SenderEmailType = "EX" Then
           GetSMTPAddress = SMTP2007(Item)
        Else
           GetSMTPAddress = Item.SenderEmailAddress
        End If
     Case Else
        Set olkSnd = Item.Sender
        If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
           Set olkEnt = olkSnd.GetExchangeUser
           GetSMTPAddress = olkEnt.PrimarySmtpAddress
        Else
           GetSMTPAddress = Item.SenderEmailAddress
        End If
  End Select
  On Error GoTo 0
  Set olkPrp = Nothing
  Set olkSnd = Nothing
  Set olkEnt = Nothing
End Function


In order to get started, fire up your version of Outlook. I’m using a relatively old dinosaur version (Outlook 2003), but the steps can easily be found online for all versions. Most Windows versions should allow you to use Alt+11 to open the Visual Basic code editor, which we are going to fire up next. To do this, follow these steps:

Step 1: Click on Tools.
Step 2: Click on Macro.
Step 3: Click on Visual Basic Editor.

Next, we are going to copy and paste our code here into the editor window. Now, I used the revision 1 script and modified the original version to extract text from the body by coding the following lines. One after line 19, and one after line 29:

.Cells(1, 4) = “Message” <– This line tells the macro program to add another column to the first row that is labeled “Message”. This will add a new column that displays the text extracted from the email. This one was added after line 19.

.excWks.Cells(intRow, 4) = olkMsg.Body <– This line tells the macro program to extract the message text from the Body of the email. This way, we have an extremely easy and fast method of verifying all of our important conversion emails that we are going to be using in our reporting.

Now that we have our script ready, let’s go to the Visual Basic macro editor.

In the project window underneath the project, right-click within the window, click on insert, and then click on module. This will bring up a VbaProject.OTM file that you can add your code into, as shown in the following screenshot:

Once you have made your desired modifications (or if you desire to use the original script and copied and pasted it, just click on the floppy disk in the upper left hand corner and save the file. Or you can use Ctrl+S to save it. Then, close the Visual Basic editor.

Next, we’re going to run our newly modified macro! First, make sure the folder that you want is selected and all the leads you want to export to an excel spreadsheet are in that folder. Then, let’s click on Tools > Macro > Macros.

Next, you will see a Macros window pop up. We need to click on the macro we want to run, and then click on run.

True to the nature of the script, you will be prompted with a dialog box that asks you what you want to name your file. Let’s call it “ExcelExportTest”. It will save it into your My Documents folder. Fire up Excel, and open your brand new spreadsheet. Here is the final version of our example, complete with all extracted elements of that folder:

Wrap-up

By using these methods, it is possible to greatly reduce the time that you spend on manually verifying and copying/pasting leads from your email box. It will be completely automated! Once you get the hang of using these methods, most of your time will be spent in the formatting phase that comes next. So, it will be necessary to spend this time adding some proper formatting that will help make your reports beautiful and impactful.


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 →

Was There a November 14th Google Update?

Posted by Dr-Pete

On the morning of Friday, November 15th, we woke up to a substantial one-day temperature spike on MozCast. Digging in, there were no signs of a glitch, and it seemed to hit across multiple IPs. The 30-day history looks like this:

Webmaster chatter seemed normal and Google has not confirmed an update, but soon other major flux-tracking tools showed one-day spikes. Here’s the data from SERPmetrics:

Both SERPmetrics and SERPs.com (that graph is a bit less clear, due to an unusually low-flux day earlier in the month) show the spike on November 15th, but one-day shifts are common due to measurement differences in the three tools.

Did big sites win big?

The first thing I dig into when we see a temperature spike is a set of secondary metrics that look at large-scale trends across the data set. That morning showed a solid jump in the “Big 10,” which simply represents the percentage of total search results in the set occupied by the top 10 domains (for that day):

The one day jump from 15.39% to 15.89% represents a 3.2% relative increase – it may not seem like a huge amount, but it’s historically unusual. Wikipedia, Amazon, and eBay all had one-day gains in the 3-5% range.

Unfortunately, it’s easy to jump to conclusions but much harder to interpret this kind of change. Some algorithm updates might benefit large brands, but it’s just as often the case that an update penalizes low-quality sites, and the big brands simply end up filling the gaps. For example, if the #10 result on a SERP falls out, and the #11 pops up one position to fill that spot, the new #10 is more likely to be a big site with a large Google footprint than a small site.

Our larger data set (not currently public) set shows a similar trend. All I can say with certainty is: (1) this was a historically unusually one-day change, and (2) the “Big 10” metric is at now at a historical high (going back to April 2012). I have no reliable clues about the causality and what specifically changed to cause this increase.

What did Wikipedia win?

Since digging into high-temperature keywords didn’t reveal any clear patterns, I thought it might be interesting to see where a big winner (like Wikipedia) picked up top 10 listings. In most of the cases I saw, the big domains didn’t gain prime real estate, but simply picked up a top 10 result because another site fell out. For example, here are the top 10 on November 14th for “famous footwear store hours” (domains only):

  1. FamousFootwear.com
  2. FamousFootwear.com
  3. FamousFootwear.com
  4. FamousFootwear.com
  5. FamousFootwear.com
  6. FamousFootwear.com
  7. FamousFootwear.com
  8. MyStore411.com
  9. Wiki.Answers.com
Clear, this SERP was dominated that day by the main brand’s site (in this case, individual store locations). On November 15th, though, it appears there was a shuffle in domain crowding:
  1. FamousFootwear.com
  2. FamousFootwear.com
  3. FamousFootwear.com
  4. FamousFootwear.com
  5. MyStore411.com
  6. Wiki.Answers.com
  7. OutletLocation.com
  8. Indeed.com
  9. Wikipedia.org
  10. Yelp.com
The main brand’s site dropped from eight results to four, and Wikipedia simply picked up one of the newly opened spots. This domain crowding/diversity pattern didn’t seem to hold up across the data set, but it does appear that the gains by big domains were primarily due to losses higher in the SERPs. In other words, big domains like Wikipedia and Amazon only picked up top 10 rankings because someone else fell out.

Was there a glitch?

Something else happened on November 14th that was a bit odd. I informally polled my Twitter followers about that day and got the following bit of information from Galen Ward:

Coincidentally, I had just been in the Moz Google Webmaster Tools account that morning and happened upon this (I didn’t put two and two together until Galen’s tweet):

I didn’t think much of it at the time (temporary glitches happen), but it seems that multiple webmasters and SEOs got the same error on the same day. Is it possible that a bug on Google’s end could cause large-scale ranking fluctuations? It depends a lot on the scope and nature of the bug. Last April, a Google bug caused a number of domains to be misclassified as parked, and the impact was large enough to cause noticeable ranking changes.

If this was simply an unexpected side effect of a bug, though, we’d expect a reversal. The temperature the next day or soon after would spike again, and the secondary metrics, like the Big 10 increase, would settle back to their former values. In this case, we’ve seen no such reversal.

Is Andy Kaufman alive?

When it comes to daily ranking changes, separating the signal from the noise is incredibly difficult. The morning of November 15th, we captured a change that illustrates just how dynamic Google has become (and is something I’ve wanted to capture in the wild for a while).

Around November 13th, TMZ broke a story that a woman claiming to be Andy Kaufman’s daughter said that her father was still alive. Multiple news sources picked up on this story on November 14th. Early that morning, we captured the first page of results for “kaufman”, which were as follows:

  • IMDB (Charlie Kaufman)
  • Wikipedia (Kaufman, TX)
  • Wikipedia (Andy Kaufman)
  • RobertKaufman.com
  • KaufmanCo.com
  • KaufmanCounty.net
  • KaufmanTX.org
  • Kauffman.org
  • Fandango.com (Kaufman Astoria Cinemas)

Google was viewing a search for “kaufman” as informational and generic, returning results for Andy Kaufman, Charlie Kaufman, cities named Kaufman, etc. A disambiguation box on the SERP even makes it clear that Google has trouble interpreting the query.

After the story about Andy Kaufman broke, the SERP changed dramatically:

  • CNN
  • IMDB (Charlie Kaufman)
  • CNN
  • Wikipedia (Kaufman, TX)
  • Fox News
  • KaufmanCounty.net
  • RobertKaufman.com
  • KaufmanCo.com
  • US Today
  • NY Daily News

Where there were no news-related organic results before, news articles now accounted for half of the top ten, including the #1 and #3 spots. You may have heard the term “QDF” (Query Deserve Freshness) in the SEO world. What’s interesting here is that QDF is not something that’s just on or off for any particular query. A query that was relatively static transformed overnight because of new information. In other words, Google decided in real-time that this informational query was now a news query, simply based on new data and content.

Is this the cause of the overall flux? No – it’s very unlikely that a single event could move the needle. Even an event like 9/11, that had a huge impact on many people, is only going to be relevant to a small percentage of queries. Events like these simply go to show how dynamic any given query can be on any given day. In a case like this, the query isn’t even historically high flux – it transformed overnight, and that transformation had nothing to do with algorithm updates.

So, what happened?

If it seems like I’m stalling, then, well – hey, is that Elvis?! One of the difficulties of retroactively explaining rankings fluctuations is that we typically can only look at the results themselves. This essentially means that we’re measuring positions, position changes, and characteristics of the domains and URLs. This makes it easy to measure something like domain diversity but very difficult to profile something like a Penguin update, where the changes are due to characteristics of the individual sites and their link profiles.

We’re also creeping into the holiday season – we’ve already seen a pattern of above average flux in the weekend before Thanksgiving. As we get into Black Friday, commercial SERPs naturally fluctuate, and it’s hard to separate what Google is doing from changes due to competition and seasonality.

Whatever happened on November 14-15, it doesn’t appear to have rolled back. The one-day spike is similar to a more traditional algorithm update, but that’s about the best we have for now. If anyone has seen additional clues or has any follow-up on the DNS errors in Google Webmaster Tools, please leave a comment.


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 →

Was There a November 14th Google Update?

Posted by Dr-Pete

On the morning of Friday, November 15th, we woke up to a substantial one-day temperature spike on MozCast. Digging in, there were no signs of a glitch, and it seemed to hit across multiple IPs. The 30-day history looks like this:

Webmaster chatter seemed normal and Google has not confirmed an update, but soon other major flux-tracking tools showed one-day spikes. Here’s the data from SERPmetrics:

Both SERPmetrics and SERPs.com (that graph is a bit less clear, due to an unusually low-flux day earlier in the month) show the spike on November 15th, but one-day shifts are common due to measurement differences in the three tools.

Did big sites win big?

The first thing I dig into when we see a temperature spike is a set of secondary metrics that look at large-scale trends across the data set. That morning showed a solid jump in the “Big 10,” which simply represents the percentage of total search results in the set occupied by the top 10 domains (for that day):

The one day jump from 15.39% to 15.89% represents a 3.2% relative increase – it may not seem like a huge amount, but it’s historically unusual. Wikipedia, Amazon, and eBay all had one-day gains in the 3-5% range.

Unfortunately, it’s easy to jump to conclusions but much harder to interpret this kind of change. Some algorithm updates might benefit large brands, but it’s just as often the case that an update penalizes low-quality sites, and the big brands simply end up filling the gaps. For example, if the #10 result on a SERP falls out, and the #11 pops up one position to fill that spot, the new #10 is more likely to be a big site with a large Google footprint than a small site.

Our larger data set (not currently public) set shows a similar trend. All I can say with certainty is: (1) this was a historically unusually one-day change, and (2) the “Big 10” metric is at now at a historical high (going back to April 2012). I have no reliable clues about the causality and what specifically changed to cause this increase.

What did Wikipedia win?

Since digging into high-temperature keywords didn’t reveal any clear patterns, I thought it might be interesting to see where a big winner (like Wikipedia) picked up top 10 listings. In most of the cases I saw, the big domains didn’t gain prime real estate, but simply picked up a top 10 result because another site fell out. For example, here are the top 10 on November 14th for “famous footwear store hours” (domains only):

  1. FamousFootwear.com
  2. FamousFootwear.com
  3. FamousFootwear.com
  4. FamousFootwear.com
  5. FamousFootwear.com
  6. FamousFootwear.com
  7. FamousFootwear.com
  8. MyStore411.com
  9. Wiki.Answers.com
Clear, this SERP was dominated that day by the main brand’s site (in this case, individual store locations). On November 15th, though, it appears there was a shuffle in domain crowding:
  1. FamousFootwear.com
  2. FamousFootwear.com
  3. FamousFootwear.com
  4. FamousFootwear.com
  5. MyStore411.com
  6. Wiki.Answers.com
  7. OutletLocation.com
  8. Indeed.com
  9. Wikipedia.org
  10. Yelp.com
The main brand’s site dropped from eight results to four, and Wikipedia simply picked up one of the newly opened spots. This domain crowding/diversity pattern didn’t seem to hold up across the data set, but it does appear that the gains by big domains were primarily due to losses higher in the SERPs. In other words, big domains like Wikipedia and Amazon only picked up top 10 rankings because someone else fell out.

Was there a glitch?

Something else happened on November 14th that was a bit odd. I informally polled my Twitter followers about that day and got the following bit of information from Galen Ward:

Coincidentally, I had just been in the Moz Google Webmaster Tools account that morning and happened upon this (I didn’t put two and two together until Galen’s tweet):

I didn’t think much of it at the time (temporary glitches happen), but it seems that multiple webmasters and SEOs got the same error on the same day. Is it possible that a bug on Google’s end could cause large-scale ranking fluctuations? It depends a lot on the scope and nature of the bug. Last April, a Google bug caused a number of domains to be misclassified as parked, and the impact was large enough to cause noticeable ranking changes.

If this was simply an unexpected side effect of a bug, though, we’d expect a reversal. The temperature the next day or soon after would spike again, and the secondary metrics, like the Big 10 increase, would settle back to their former values. In this case, we’ve seen no such reversal.

Is Andy Kaufman alive?

When it comes to daily ranking changes, separating the signal from the noise is incredibly difficult. The morning of November 15th, we captured a change that illustrates just how dynamic Google has become (and is something I’ve wanted to capture in the wild for a while).

Around November 13th, TMZ broke a story that a woman claiming to be Andy Kaufman’s daughter said that her father was still alive. Multiple news sources picked up on this story on November 14th. Early that morning, we captured the first page of results for “kaufman”, which were as follows:

  • IMDB (Charlie Kaufman)
  • Wikipedia (Kaufman, TX)
  • Wikipedia (Andy Kaufman)
  • RobertKaufman.com
  • KaufmanCo.com
  • KaufmanCounty.net
  • KaufmanTX.org
  • Kauffman.org
  • Fandango.com (Kaufman Astoria Cinemas)

Google was viewing a search for “kaufman” as informational and generic, returning results for Andy Kaufman, Charlie Kaufman, cities named Kaufman, etc. A disambiguation box on the SERP even makes it clear that Google has trouble interpreting the query.

After the story about Andy Kaufman broke, the SERP changed dramatically:

  • CNN
  • IMDB (Charlie Kaufman)
  • CNN
  • Wikipedia (Kaufman, TX)
  • Fox News
  • KaufmanCounty.net
  • RobertKaufman.com
  • KaufmanCo.com
  • US Today
  • NY Daily News

Where there were no news-related organic results before, news articles now accounted for half of the top ten, including the #1 and #3 spots. You may have heard the term “QDF” (Query Deserve Freshness) in the SEO world. What’s interesting here is that QDF is not something that’s just on or off for any particular query. A query that was relatively static transformed overnight because of new information. In other words, Google decided in real-time that this informational query was now a news query, simply based on new data and content.

Is this the cause of the overall flux? No – it’s very unlikely that a single event could move the needle. Even an event like 9/11, that had a huge impact on many people, is only going to be relevant to a small percentage of queries. Events like these simply go to show how dynamic any given query can be on any given day. In a case like this, the query isn’t even historically high flux – it transformed overnight, and that transformation had nothing to do with algorithm updates.

So, what happened?

If it seems like I’m stalling, then, well – hey, is that Elvis?! One of the difficulties of retroactively explaining rankings fluctuations is that we typically can only look at the results themselves. This essentially means that we’re measuring positions, position changes, and characteristics of the domains and URLs. This makes it easy to measure something like domain diversity but very difficult to profile something like a Penguin update, where the changes are due to characteristics of the individual sites and their link profiles.

We’re also creeping into the holiday season – we’ve already seen a pattern of above average flux in the weekend before Thanksgiving. As we get into Black Friday, commercial SERPs naturally fluctuate, and it’s hard to separate what Google is doing from changes due to competition and seasonality.

Whatever happened on November 14-15, it doesn’t appear to have rolled back. The one-day spike is similar to a more traditional algorithm update, but that’s about the best we have for now. If anyone has seen additional clues or has any follow-up on the DNS errors in Google Webmaster Tools, please leave a comment.


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 →