We have that handy “Search Queries” link in Google Webmaster Tools that is supposed to show for which keywords our website does well on Google. Besides, the tools shows “Average position” for each keyword. The option has been widely discussed – and more often than not it arouses some kind of a doubt: people seem to see few words that actually generate search traffic.
Today’s (somewhat geeky) Excel tutorial will help you estimate how Google Webmaster Tools data is close to the reality: today we will learn to merge that data with your Google Analytics “Google search traffic” column.
In this article, I will show you how to merge two Excel documents the way that your “real” traffic numbers (from Google Analytics) are added to keyword list generated from Google Webmaster Tools (for you to compare the numbers from two tools)
Attention: you can use this tutorial to merge any type of statistics data: Keyword Rankings and Keyword Volume, Google Rankings data and Traffic data, Backlinks and Traffic Sources, etc, etc – so I strongly recommend to give me a few minutes to try to explain everything in an easy-to-understand language.
This step is the easiest one: go to Google Webmaster Tools, select your site, click “Your site on the web” to expand it and select “Search queries”. Now scroll down to the bottom of the page and click “Download this table”.
In your Google Analytics, go to “Traffic Sources” and select “Google”. There click on “Export” and select CSV.
You are almost done. Depending on which Excel version you are using, you may also choose “Text to column” option in “Data” menus to break the data into columns. Now, delete those rows you don’t need.
Now you need both tables in one Excel document (to be able to apply the formula). So: Create two sheets and name them accordingly to be able to identify each one in the formula
I ended up with two sheets / lists: GWT (for Google Webmaster Tools data) and GA (for Google Analytics Data).
Now just copy paste the whole tables in the corresponding sheets / lists (you may call them tabs as well).
This is the complex step: start following!
First, let’s make it clear again what we are going to do: we want to add visit numbers from GA (Google Analytics) sheet to the corresponding lines in the GWT (Google Webmaster Tools) sheet.
This means that the formula we are creating will:
So what you should do next is to create a new column (where the visit number is going to be pasted) in the GWT sheet:
The formula we are going to use: VLOOKUP
So in your new column (D2 cell on the screenshot) start typing
and you will be promptly suggested the syntax:
Note: depending on the Excel versions you may see commas between the arguments.
So let’s quickly describe each argument and build the formula:
Important: If you use an absolute reference ($A$2:$B$101), instead of a relative reference (A2:B101), it will be easier to copy the formula to other cells. To use the absolute reference, just click F4 – very easy!
So we ended up with the following formula:
Click ENTER and you should see either the numeric value (that stands for the number of visits from the GA tab) or #NA (if there’s no such a phrase in the GA tab). Now just copy the cell with the formula and paste it to the whole column “Visits”.
Here’s my result (as you can see only 11 phases from the Google Webmaster Tools report generate some actual traffic to my website):
Local business listings began with a basic business directory more than a decade ago. Chances are, your business has a local listing wherever you have a physical address location. Check it out — go to Google, type in your company name. You’ll probably see a map locating your business and an address. Of course all you may find other information too. Local business listings are treated passively by many businesses. But that could be a mistake. You’ve probably read that prospects have something specific in mind when they are shopping. So try this test. Go back to Google, type in the name of a product or service your business provides plus your hometown. Did your business show up? If it did: congratulations. If it did not: you’re missing a major marketing opportunity.
Times have changed.
From a marketing standpoint, the use of local business listings has exploded with the increase of social media and mobile devices. Consumers not only use these interactive yellow pages to locate a business, product or service in their area; they are also posting reviews of those products and services. Today, there are over 60 local business listing websites on the Internet in five different categories. They include the search engines, social communities, 411 websites (aka yellow page type websites), GPS websites and that age-old business directory.
You can no longer be passive.
To make your interactive yellow pages listing a stronger marketing tool, you must first “claim” the listing with all the search engines, social communities, websites and organizations that lead people to it. Once you prove the listing is really yours you can update it with your business marketing material. Businesses are realizing the importance of this claiming process. Once you have claimed your local listing you can update information with text, keywords, business descriptions, products, services, photos, videos, coupons, and more. Some websites, such as Google, allow you to use all these options while others charge a fee for enhanced listings making this information present for local consumers through web or mobile searches
So what’s the “security challenge”?
The claiming process is crucial to security because if the wrong person gets access to your business local listing they can direct customers to a different location by phone or website address. Additional damage can include incorrect information on photos, videos, coupons, and more. Because consumers are using local business listings to locate a business, product or service in their immediate area, the security around local business listings must have a high priority for any local listing website.
Phishing was described in 1987 before the Internet was a commercial boom. The first recorded use of phishing was in1996. The question is whether the business industry is going to wait for something similar to occur using Local Business Listings. The security holes are quite evident with Local Business Listings and I don’t think it takes a genius see what could happen if businesses do not “claim” their listings – the first step in closing those “holes.”
When hackers capture a Local Listing it’s called “high-jacking.”
It is absolutely important that businesses not passively wait for local listing websites to put the appropriate security in place before you claim your listing. Installing security starts with the obvious claiming process, but many sites allow data to be inserted from other databases on the Internet and I am not sure there’s good security around this later process. If someone wanted to hijack a local business listing, they could easily insert the wrong information through a low level business directory that sells its data upstream or inserts its data directly into a higher level local listing website.
While the top search engines like Bing, Google and Yahoo have “some” front-end security; their API’s (Application Programming Interface) makes them vulnerable through the back door. Data is provided to their local listings from 3rd party sources including “get listed” services. Additionally, if someone cannot claim a listing easily, the process within these local listing websites allows for additional listings with the same address to be submitted by anyone.
I can tell you from experience that without fail, one of the most common reasons websites suffer from a low conversion rate is because they fail to make a statement. There’s no “push” – no energy and no interest in the offer.
So how do you grab your readers’ attention when they’re in that scrolling and scanning state of mind? You hit ‘em with bullets! When used correctly, copywriting bullets slice through your buyer’s defenses and get them off the fence and actively engaged in your content.
But so many people stop short of turning ordinary “here’s how it helps you” bullets into extraordinary “I’ve GOT to have this” bullets.
Let’s say we’re selling a product that helps treat heartburn. How many times have you seen text like this on a web page?
With “Heartburn B Gone”, you’ll:
There are some benefits sprinkled in there, sure. But nothing that makes me bolt up out of my chair and say “FINALLY! This is just what I’ve been looking for!”
To make your bullets more convincing, add in some livelier examples that your reader can truly relate to. One of the best ways to do this is to put that added piece in parenthesis – like this:
With “Heartburn B Gone”, you’ll:
By adding a real-life benefit to your bullet points, you get your prospect thinking “Wow, I’d LOVE for that to happen!”
A common mistake used when writing bullet points is to give away your best information up front, in the hopes that it will entice people to buy the product. For example:
Learn how just one capsule of Vitamin E a day can help clear up your skin almost overnight!
“Oh really? Is that all I need to do?” – and the prospect rushes out to buy her vitamins without giving your product a second look. Try this instead:
Learn how a common vitamin that you already have in your home can help clear up your skin almost overnight! (But you don’t take it by mouth – do THIS instead for radically softer, blemish-free skin!)
We’re not saying what vitamin it is – other than the fact that they probably already have it. And we’re telling them that there’s a special way to use it – both of which they’ll find out by buying your product.
The “silver bullet” of bullet copywriting is to fire off bullets that are filled with curiosity – so that when they hit the mark, your reader is always left imagining how much better off they’d be as a result of ordering your product or service. Test it for yourself and see how it works for you!
As SEO practitioners and content writers, we are taught to do two things with regularity when creating: link internally with great frequency, and also do our best to spread the link love by linking out to others.
The SEO and time-on-page fundamentals that these things revolve around haven’t changed, but much of our thinking about what this does has. Research has shown that the act of linking dramatically drops readability, and also severs the ability for retention.
In the most recent issue of WIRED, there is an article titled “Chaos Theory”, which is an adaptation of a piece of Nicoholas Carr’s book “The Shallows: What the Internet Is Doing to Our Brains”. Within, Carr details two studies that delve into the perils of linking out:
“In a 2001 study, two scholars in Canada asked 70 people to read “The Demon Lover,” a short story by Elizabeth Bowen. One group read it in a traditional linear-text format; they’d read a passage and click the word next to move ahead. A second group read a version in which they had to click on highlighted words in the text to move ahead. It took the hyper-text readers longer to read the document, and they were seven times more likely to say they found it confusing.”
Carr continues a paragraph down:
“A 2007 scholarly review of hypertext experiments concluded that jumping between digital documents impedes understanding. And if links are bad for concentration and comprehension, it shouldn’t be surprising that more recent research suggests that links surrounded by images, videos, and advertisements could be even worse.”
As SEOs, this has obvious dilemmas for our work. We are extremely more likely to link out due to our craft, and as such, extremely more likely to make the content we create more difficult to understand and retain. For example (and the only time I will do this during this post), look at Aaron Wall’s recent entry SEO is a Zero Sum Game. Wall links out 47 times, and that, mixed with the difficult subject matter, makes the post essentially incomprehensible.
SEO shouldn’t get in the way of user experience, and as these studies suggest, the way we create content definitely does. There are ways around it, thankfully, although it takes conscientiousness to implement them.
It is my suggestion that SEOs should make a concerted effort to not link out within the main body unless absolutely necessary, and instead, take the extra three or four minutes to summarize the link you would have used. We can still get links in the content, where studies show that links offer more juice, we just have to do so at the tail end, after we‘ve summarized our point.
Consider creating a “Additional Resources” addendum, where you can link out to multiple places without concerns about readers not understanding the content. You also have the ability to create blog posts like term papers or Wikipedia entries, with footnotes addressing anything that needs to be linked to.
Of course, this isn’t always necessary, and the more vanilla your topic, the less it matters. If you’re creating content a la “50 ways to..” that really has no lessons or long-term intentions attached, link away, as dragging your users all across the internet will have little to no implications on the retention of ideas. In cases like Wall’s, though, where he is trying to instruct a point and create understanding, his act of linking out so often actually greatly disrupts his original intentions.
Studies like these should mark a fundamental step forward in the way we learn online. As the article continues, much of the way we consume content is very “surface” level, and because of that, skimming has become our dominant mode of thought. If our goal is to progress and not simply consume, we have to take steps away from this mode of thinking.
Let’s start off funny. The following are NOT the 5 steps to viral video results:
You get the point.
I recently interviewed a couple of people for my Social Media Expert Interview series – Scott Stratten of UnMarketing fame and Carrie Wilkerson, The Barefoot Executive – who gave me a new perspective on viral video creation.
Why You Shouldn’t Try To Be Funny
The upshot is: funny videos are the hardest to get to go viral. Sense of humor is very personal. And it doesn’t matter if 100,000 people see your skateboarding dog catch fire and faceplant if you don’t get anything out of all those video views. Unless you’re just trying to have fun. But if you care about results, keep reading.
There’s a simpler way. And you can tie it to a conversion event you want to get.
A More Effective Viral Video Style
Just create what I call the “Emotional Slideshow” (because there was no name for it and that’s all I could think of on the spot) type of viral video.
These are nothing new, but they work like gangbusters.
The Time Movie has received 1.4MM views despite being very simply and cheezier than Fabio movie backed by a Yanni soundtrack. Scott admits to being sick of it. But his goal was to get motivational speaking gigs and launch his speaking career without years of painful free gigs- and it worked.
The Boss Movie helped Carrie Wilkerson build a list of 24,000 stay at home moms to market to in just 9 months.
The Crappy Day Movie just debuted and is my first attempt at one of these Emotional Slideshow movies. But it has its own Facebook page and I hear those are really hard to start and very, very expensive.
The Five Steps to Creating An “Emotional Slideshow” Viral Video
You can have more points, but you want each image and sentence to last about 7 seconds, and the full movie to be 3-4 minutes.
Then, of course, watch it on several occasions and have other people proof it, especially if they’re your target audience.
If you watched The Boss Movie, you’ll notice Carrie brings up an opt-in page after the movie for a pdf about the 7 Things Your Boss Doesn’t Want You To Know. This is a bribe that goes straight to the audience’s core problem- the limitations of employment. When conceiving your bribe, make sure you start with titles and think like a copywriter before you create the content you’re going to give away.
Google has just announced the completion of it’s Caffeine Project which according to Google offers the freshest search results than what its current search index can offer. Google Caffeine is also the largest collection of web content that Google can offer.
Google Caffeine is all about delivering fresh content, be it a blog or forum post or even news story. The idea is to highlight this real-time search results. Caffeine is different from Google’s current search index for the simple reason that these results are not really the live web content but rather Google’s index of the web.
According to Google , Caffeine was created to keep up with the evolution of the web as well as to meet rising user expectations. Google has analyzed the web in small portions and updated it’s search index continuously. Caffeine allows Google to index web pages on a huge scale and processes hundreds of pages in parallel.
Last month I went over the basics of how to future plan & setup a mobile version of your site correctly both for the user and the search engines.
This month I will go over coding that will play a critical role for mobile in the future as well – microformatting.
Microformatting – includes Microformat, RDFa, & Microdata – allows you to explicitly delineate the property type of the text you are displaying on your site and explained well HERE.
I personally prefer RDFa as Google, to the best of my knowledge, treats them all equally & I feel RDFa will dominate over the long term.
Microformatting is extremely useful to the search engines as this type of coding removes doubt to what you are referring to when you display, for example, an address or phone number.
This can even be used for people, as LinkedIn does, which directly ties a person to their images, nickname, title, organizations, as well friends.
This strikes similar to the recent privacy issues Facebook is facing but with microformatting on your own site you can control this information and mold it to your advantage in the standard & mobile search engines.
Google even provides a crystal ball tool to provide you insight to what your listing will look like with rich snippets based on your microformatting code HERE.
You don’t need a crystal ball to show you how this will also improve your mobile search placement going forward as mobile further separates its results from the standard search.
Mobile search is heavily reliant on location & quick accurate data as most of mobile search is done while being on the go.
That clarity of accurate & local data is gained with microformatting to provide mobile search those more accurate & local results.
One of the more prominent Geo-Listing (a.k.a. local business listing) hijackings occurred in New Zealand. A florist was found guilty for hijacking competitor local business listings at Google Maps. The business owner was ordered to complete 100 hours of community service after it was discovered she changed the business listings of rival companies in Google Maps to direct traffic to her own business.
This is a real situation that can and does have an impact upon a business that is not paying attention to their local business listings. If your business relies on the local consumer market for its primary revenue and you are not clear about local business listings, then you need to get caught up real quick.
Hijacking the geo-listing of another company does require the hijacker to be fraudulent and thus this activity falls into criminal court versus a civil court. This is far from being merely a domain name registration and why the florist in New Zealand was found guilty.
For security purposes we are not going to identify how a criminal or competitor can actually make the changes and redirect phone numbers and web addresses, however, please be certain that it can be done. Oh, and incidentally, don’t think that we’re talking just about a competitor in your local area. Consider criminals from other countries where we have seen activity from continents like Africa and Asia.
Furthermore, since geo-listings are free to the business from multiple geo-listing websites, you, as the business owner, cannot expect these websites to provide high level of security. These websites are in the business of providing information, not security. Since there is a way for you to insure your own security, you will need to become an early adopter and engage!
Local Business Listing Primer
Feel free to read our previous articles on Geo-Marketing that explains the big picture of Local Business Marketing through Geo-Listings, Mobile Marketing and Geo-Social Marketing. We will stay focused on Geo-Listings for our discussions here.
Local business listings are the demise of the traditional yellow pages. If the traditional yellow pages have not already seen their coffin and at minimum a door stop, then free local business listings are the nails in the coffin. Certainly you will find that you can save money in this space.
Local business listings give the local business the ability to not only have their general information available, but photos, videos, coupons, offers, discounts, promotions to name a few item. Additionally these can be updated 24×7×365 24 hours per day. Certainly none of these are features of the traditional yellow pages.
The process gets even better. Not only do these listings show up on the first page of Google for a search result in which a local listing is appropriate, but they show up on mobile devices while local consumers are driving around town. Studies have shown that there is an increase in local search and local search via mobile applications on mobile devices. We won’t get into all that detail here, but you can certainly search for the stats yourself.
Just in case you still are not convinced that you need to pay attention to your local business listing, think about the consumers walking through your store and writing reviews about their experience, products or services. If they don’t like something they can twitter or write a review in any local listing website before they even make it back to the car.
These consumer reviews are very important because in part they determine your ranking amongst other local listing websites for a web search (vs. mobile search). They also have a tremendous value to the search engines, which is one of the reasons Google wanted to acquire Yelp recently.
Local Business Listing Security
So, let’s get back to the issue of local business listing hijackings. To prevent this process the first action is to claim your business listing at multiple local listing websites. Certainly Google Local Business Listing will be your first stop, followed by Yahoo, Bing, Local.com, Ask.com and Yelp. You will find that there are many more locations, this is only a start. We have indentified 60 websites that provide geo-listing information to the general public in four categories (Search, Social Community, GPS, and 411). This does not count the countless business directories that have been on the web for years.
Claiming, updating and managing the ongoing updates and reviews to your local business listing can become a time consuming process. It may at some point require more time than your website. Certainly the local business listings can become your website too.
Because there are multiple locations that have to be updated you may need to consider a low cost service that will help you maintain and manage your local listing at multiple local listing websites. When it comes to the web, Google may be the 800lb gorilla, but in the mobile marketing space, the mobile application developer will ultimately decide what database they will use. Furthermore, the local consumer will be the one to decide which local listing they will use to write their reviews. Therefore, for both of these reasons, you will want to manage your local listing at multiple local listing websites.
I recently read an article by Sarah Carling entitled “The Best Job In The World.” and found it so compelling that I wanted to add to what she started. As Sarah states, there are so many aspects of this job that are exciting and it’s a profession that will require you to always be on the top of your game. This “game” doesn’t guarantee much but it will always keep you on the tips of your toes. Now on to why being an SEO is so awesome!
Getting Paid To “Play” On Social Media
More and more companies are blocking their employee’s access to social media these days. Even if access isn’t revoked getting caught by the boss or by monitoring software isn’t any fun. As an SEO I get to use social media on a daily basis. Yup, the boss is fully aware of it and he understands the value it brings to our company AND our clients. So while other companies punish their employees for participating on Twitter and Facebook I am getting paid! How cool is that!?
Techniques Vary But Results Rule The SERPS!
One of my favorite parts of Search Engine Optimization is that you could have five different SEO’s that all use unique techniques and all of them can still be successful! SEO isn’t cookie cutter, we all more or less follow the same rules but we have our own successful twists. These “twists” are what makes each SEO professional unique.
Discussions, Rants, and Debates.
As Sarah mentions, the discussions, rants and debates are top notch. I mentioned earlier that every SEO has their own “twists” to their craft and most of them swear by these techniques. Ask a group of SEO’s about the value of a forum link or a “No Follow” link (especially from a top authority domain such as Wikipedia). This and many more debates have been circulating for quite a while. They often get pretty heated as well.
Many fantastic discussions are brought up on a daily basis too. Due to SEO being applicable to almost every niche and website ever made we don’t always have to worry about hiding our techniques from each other. In fact the SEO community is very generous and is always willing to help if you are willing to listen. With that said if your going to walk in as the “new guy” and tell everyone how it’s done (incorrectly that is) you better watch out the SEO crowd sure can tear you a new one.
Only Google Knows Best!
Ok, Bing and Yahoo too… There isn’t an SEO alive that knows the exact algorithm that the search engines use to rank sites in the search results. It’s been estimated that over 200 factors contribute to this algorithm. With this said, nobody can master the craft of SEO. Hard work, testing and experience play a large role in the success someone achieves.
No Barrier To Entry Here.
To become a Doctor or Lawyer you need countless years of schooling and in addition you need to pass brutal state examinations. SEO on the other hand requires no formal training. Some SEO professionals have four year college degrees; some have high school diplomas and many with much more or less. SEO is a battle of will, do you want to succeed? Do you have what it takes to keep up in this game? SEO is constantly changing and if you’re willing to work hard you too can succeed in this niche. A basic knowledge of html will do you wonders too but isn’t necessary to start learning.
The Never Ending Competition and Results!
Every SEO needs to be competitive. I have yet to talk to an SEO that is ok with a second place ranking let alone a second page listing. Very frequently SEO’s can see the results of their hard work. Write some new content, build a couple links and see the results in your traffic and or conversions. The more creative you are the quicker you can see the results. If you get lazy or do something “illegal” you will see the consequences in your results. Everything you do has an effect. This is why it’s exciting to wake up every morning; you just don’t know what that day of work will net you.
Don’t let me fool you; SEO isn’t all fun and games. It’s hard work! Take a wrong step and your website can get banned from the search engines. Not too many people get second chances either. SEO’s always need to be on their toes; we’re always researching the newest techniques, talking to one another and testing our new theories. Search Engine Optimization is a job that gets a bad wrap, there is no formal education and at any moment the search engines can flip your site upside down. Even with all that said being an SEO is awesome and I continue to welcome all future challenges.
Because Facebook privacy is such a hot topic lately, I thought I might write something so you know where you can check your privacy settings, or at least get a laugh out of what other people aren’t making private. With the amazing (that’s right, I said A-M-A-Z-I-N-G) capabilities of open graph and all the changes that came with it, we all want to make sure we are keeping our personal lives under wraps. After all, no one REALLY wants to know your bathroom habits via status message unless they’re purposely friends with you. And even at that… really?
Open Book- remember the aforementioned website where you can laugh at other people’s expenses due to what they are displaying publicly? Yep. This is it. This uses the Facebook API available to developers. All you have to do is type in something to search, and it will bring you random updates with that word. I won’t even get into what you COULD look up but…go try it out. It might make you double check your settings.
Facebook on Zesty.ca- This is pretty simple. You need to know your user ID…and if you don’t know it, there’s a little search box on the right where you can find it. Once you find your ID you can see what is visible from your events, posts, groups, pages etc. It’s simple and straight to the point.
ReclaimPrivacy- This is a pretty awesome service. Go to the homepage and follow the instructions. Drag the little button into your toolbar and click it when you get into Facebook. It will show you what’s connected, what’s not connected, and give you the option to fix it. I had instant personalization turned on and it gave me the ability to turn it off right from the results, but I think there must be an error because it didn’t fully deactivate. I bet Facebook is blocking it. ReclaimPrivacy is quick to fix errors so full functionality for that one option will most likely return ASAP. There is also a small disclaimers saying that ReclaimPrivacy is not held accountable for any troubles we might run into with Facebook due to use of this. Hmmmmm.
I hope these few sites will give you some confidence (and laughs) about privacy settings. After all, Open Graph is our future; we should embrace it. What you choose to share, and not share, can be controlled by you. Even though Facebook is shady-like and automatically opts you in to their changes (ask me how I feel about that. I dare you), you do have the option to opt-out.
Move over Nike. New public saying of 2010- “Just opt-out!”