ONERank Internet Marketing Blog

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.

Step 1: Export Your Data

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.

Step 2: Create 2 Lists of Data

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).

Step 3: Apply the Formula

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:

• Look up a keyword in the GWT sheet;
• Find the exactly same phrase in the GA sheet;
• Grab the number next to it in the GA sheet;
• Add it next to that phrase in the GWT sheet.

Still following?

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

=VLOOKUP(

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:

1. lookup_value: What value do you want to look up? In our case, we need to look up the word in A2 cell;
2. table_array: Where is the lookup table? What I did here was just going to GA sheet and select the whole table there. The formula would automatically add: GA!A2:B101

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!

3. col_index_num: Which column has the value you want returned? In my case, that’s 2:



4. [range_lookup]: Do you want an approximate match? In our case, you are likely to want the exact match, so just type FALSE here.

So we ended up with the following formula:

=VLOOKUP(A2;GA!$A$2:$B$101;2;FALSE)

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):

Check out the SEO Tools guide at Search Engine Journal.

How to Compare Google Webmaster Tools Data with Google Analytics Data (Excel)

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.

Remember phishing?

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.

Check out the SEO Tools guide at Search Engine Journal.

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.

Copywriting Bullet Examples

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:

• Prevent heartburn from coming back by turning off overactive acid pumps in the stomach.
• Be able to eat your favorite foods again
• Save money on expensive antacids and prescription medicines

Yawn.

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:

• Prevent heartburn from coming back by turning off overactive acid pumps in the stomach. (EVEN if your heartburn erupts like a geyser, you’ll be able to quiet belching and pain once and for all!)
• Be able to eat your favorite foods again (Go ahead and cook up a batch of your world famous 4-Alarm Chili – and go back for seconds!)
• Save money on expensive antacids and prescription medicines (Even if you know your pharmacist on a first-name basis and ask for your prescriptions as “the usual”!)

By adding a real-life benefit to your bullet points, you get your prospect thinking “Wow, I’d LOVE for that to happen!”

Don’t Give Away Everything!

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.

Using Bullets Effectively

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!

Check out the SEO Tools guide at Search Engine Journal.