duminică, 5 februarie 2012

How to Build an Advanced Keyword Analysis Report in Excel

How to Build an Advanced Keyword Analysis Report in Excel


How to Build an Advanced Keyword Analysis Report in Excel

Posted: 04 Feb 2012 12:04 AM PST

Posted by Dan Peskin

Analyzing keyword performance, discovering new keyword opportunities, and determining which keywords to focus efforts on can be painstaking when you have thousands of keywords to review. With keyword metrics coming from all over the place (Analytics, Adwords, Webmaster Tools, etc.), it’s challenging to analyze all the data in one place regularly without having to do a decent amount of manual data manipulation. In addition, dependent on your site’s business model, tying revenue metrics to keyword data is a whole other battle.

This post will walk you through a solution to these keyword analysis issues and provide some tips on how you can slice and dice your data in wonderful ways.

With Microsoft Excel, we can create a report with all the keyword data you will need, all in one place, and fairly easy to update on a weekly or monthly basis. Then with all this data we can easily categorize segments of it to more quickly determine the better performing sets of keywords.

What we will need to do is push Google Analytics, Webmaster Tools, Adwords, Ranking data, and Revenue data all into one excel spreadsheet. Then we will put it all together into one master report and one categorized pivot table report.

To start, you should be especially familiar with pivot tables, the Google Adwords API, the Google Analytics API, and keyword research of course. Utilizing these APIs and being consistent in the formatting of the data you put into your spreadsheet will make it easy to update. If you aren’t familiar with these tools, I have provided resources below and some steps to organizing this data.

Here are some resources for learning to use pivot tables in Excel:

Excel for SEO
Microsoft Pivot Table Overview

Now let’s go fetch that data.

I Got 99 Problems, But A Keyword Visit Ain't One

First off we need to get our keyword traffic metrics through the Google Analytics API. I suggest using Mikael Thuneberg’s GA Data Fetch spreadsheet. You can follow the instructions, read the how to guide, and download the file here.

Make sure to build off the GA data fetch file or a copy of it, as it has the proper VBA functions (the Visual Basic code that allows for the API to work) installed for API calls. Once you have your API token and the spreadsheet setup you can perform your first API call.

We will be using the more complex query to extract organic keyword visits for a specific date field and filter by the number of visits. The query I use for example, will output visits, average time on site, page views, and bounces for any keyword with 5 or more visits in the last 30 days. However, you can modify the parameters to your liking. To see what other metrics can be used, check out the Analytics API documentation.

Your Analytics data should look something like this:

Analytics API Data

Google Analytics data called through the API in Excel.

Now select the whole keyword column and create a pivot table of the keyword list in another sheet. In the adjacent column create a table where the cells equal the values in the pivot table column. Label this table “KeywordList” or whatever you like. We now have the keyword table to reference for extracting Adwords data.

Keyword Lists and Tables

Pivot tables don’t have the same referencing abilities as regular tables, so the table in column B is what you will reference in future steps.

To Be, Or Not To Be Searched, That Is The Question

Next up is pulling in search volumes for our keyword table. Thanks to the wonderful Richard Baxter, there are a couple articles on using and installing the Adwords API Plugin. One on SEOmoz and one on Seogadget.

I know the Adwords API access is a bit of an issue for some, so if you cannot use the API, utilize the Google Adwords Keyword Tool (gathering data from this tool will unfortunately require a lot more work).

In a new sheet, use the Adwords API array formula called “arrayGetAdWordsStats” to pull in the average and seasonal monthly search volumes for your keyword table. Your formula should look something like this:

=arrayGetAdWordsStats(KeywordList,”EXACT”,”US”,”WEB”)

You should now have 12 months of historical search volumes and averages for all your keywords.

Adwords API Data

Results from an Adwords API call usually look like this.

Note: If your keyword list is greater than 800 keywords, you will have to break out the list into a few separate tables just to perform API calls for those keywords. If this is the case, make sure to keep each array of search volumes aligned in the same columns.

The Impression That I Get

No API required here, Google’s Webmaster Tools provides a pretty easy way to download its search query data. If you open up the Search Queries report in Webmaster Tools there is an option to “download the table” at the bottom. Download the table for the same date range you used earlier and drop it into a new sheet.

Webmaster Tools Keyword Data

The report downloaded from Webmaster Tools. Note the “-“ is used for zero values, in the yellow columns I simply cleaned that up with an IF statement.

Impressions, CTR, and Average Rank can now been added to our metrics.

If You Ain't First Page, You're Last

Since we all know how accurate average rank is from Webmaster Tools, let’s get some current rankings into this report .Grab your main keyword list from the spreadsheet and run rankings for them with your application of choice. I usually use Rank Tracker, but I am sure everyone has their own preference. Once you have your rankings drop it into a new sheet.

The More You Know

The number of metrics we can add to the report are limitless, but there comes a point where adding too many can create more work for updating the report or create analysis paralysis. The only other metric I suggest adding in is the SEOmoz Keyword Difficulty if you have a PRO account. Again this may be very time consuming to add for large numbers of keywords, hopefully you have an intern for that.

Mo Money Mo Metrics

Revenue data may come from different places dependent on how your business works, so I unfortunately don’t have a one stop solution to importing that data. However, most applications usually allow you to download that data to CSV or Excel. If you have Ecommerce enabled in Google Analytics, you can use the API to pull in this data. As long as you have some metrics to relate to your keyword such as Average Order Value or Conversion Rate, drop it in a new sheet and you will be good to go.

Some of you may be asking yourself what to do if your revenue data does not tie back to the keyword visit. This is where the categorization of keywords plays an extremely important part in this report. In this case, we want to create a bridge between the revenue data and keyword data. This can be done through categorizing your keywords into a category that relates back to a field in your revenue data. For example, you might be able to associate keywords with product names or landing pages. These products or landing pages would then become categories. Once you have determined what your categories will be, you can assign them to keywords in a new sheet that simply contains keywords in one column and the category tag in the other. You can learn more about keyword categorization here.

Keyword Categorization

Categorizing the keywords above not only lets me group them to aggregate metrics for analysis, but it allows me to bridge the gap somewhat between the keywords and conversions in this example.

One Report To Rule Them All

Finally we have all the data; we just have to put it all together. Create a new sheet and pull in your master keyword list by using =NameOfTheTable, drag this down until you reach the last keyword on the list (paste values after if you want sorting capabilities). Now select your keywords and create a new table. In the columns next to the keywords all you have to do is a VLOOKUP of each metric you would like to add to your report. Once you fill in the first cell of each column, the column should automatically be added to the table and populate the other cells with the equation. Repeat this process until all your metrics are in this table.

There will also be a need to calculate some metrics such as the Bounce Rate or Conversion Rate if you pulled in revenue data. Those should be added in adjacent columns as well. Additionally, if you didn’t need to categorize your keywords earlier, I suggest categorizing them now in an adjacent column. When completed your master report should look something like this:

Master Report

The master report.

Amazing. We have all the data in one place in a simple to sort and use table! Just wait…it gets better.

Pivotal Success

Now you may be wondering how this report can get any better. Two words my friends: Pivot Tables.

Creating a pivot table of your master report will allow you to segment your data in a number of ways that weren’t possible before. In the Pivot Table Field List, the Row Labels, Column Labels, and Values will define the layout of your report. What we first need to do is drag and drop the Category and Keyword fields into the Row Labels respectively. This will set your top level metrics to summarize at the Category level and allow you to drill down into each Category to see the associated keywords and their individual metrics.

Next you will want to start dragging your metrics into the Values section, which will automatically populate the Column Labels section with the Values field. As you add your metrics in, you can edit their names and the way they are aggregated. You will want to think carefully about how you will aggregate certain metrics so that viewing those summarized numbers at a Category level makes sense.

Pivot Table Fields

This shows you how best to setup your pivot table fields and their value settings.

For instance, I might summarize Impressions and Visits, but average CTR and Bounce Rate. Seeing the average CTR and Bounce Rate for a Category will allow me to narrow down which sets of keywords are performing better than others. Then looking at the total Impressions and Visits for those well performing categories will allow me to see where there might be a higher potential to increase traffic to my site. While this may not be an absolute rule to determine keyword focus, it is a good rule of thumb and can be a way to prioritize which ones to focus on.

Pivot table reports also allow you to add report filters, letting you filter out data by any metric or even multiple metrics. With this you could analyze keywords that only rank on the first page of SERPs using the current ranking as a filter. Hell, you could add a field to the master report calculating the number of words in each keyword phrase, then filter by that and bounce rate, giving you your well performing long tail keywords. Get creative, let loose, play with the metrics, you will be surprised at what kind of conclusions you can make about your site’s keyword traffic.

Final Keyword Analysis Report

The final product.

Conclusion

Updating the report is simple. Rerun the API calls with the new date range, rerun your rankings for the new keyword list, and export the other reports you need with new date range. As long as you kept your formatting and equations the same, the rankings and other reports should be dropped into their respective sheets without having to change anything. The master report should automatically be updated once you update the keyword column and the pivot report should update once you hit refresh under the pivot table menu. That’s it!

Well I should probably stop talking now and let you get to your hours upon hours of keyword analysis fun. Hopefully this was informative enough to make building a report such as this fairly easy. I would love to hear your feedback and will gladly answer any questions or comments about the post below. If you have issues later on, you can always contact me via Twitter.


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!

Seth's Blog : In search of a timid trapeze artist

In search of a timid trapeze artist

Good luck with that, there aren't any.

If you hesitate when leaping from rope to another, you're not going to last very long.

And this is at the heart of what makes innovation work in organizations, why industries die, and how painful it is to try to maintain the status quo while also participating in a revolution.

Gather up as much speed as you can, find a path and let go. You can't get to the next rope if you're still holding on to this one.

 

More Recent Articles

[You're getting this note because you subscribed to Seth Godin's blog.]

Don't want to get this email anymore? Click the link below to unsubscribe.




Your requested content delivery powered by FeedBlitz, LLC, 9 Thoreau Way, Sudbury, MA 01776, USA. +1.978.776.9498

 

sâmbătă, 4 februarie 2012

Mish's Global Economic Trend Analysis

Mish's Global Economic Trend Analysis


Groundhog Day: Allegedly "Only One Day Left" to Save Greece; LAOS Party Leader Rejects Ultimatum, Hands Out Drachma Coins to Fondly Recall Greece's Pre-Euro Days

Posted: 04 Feb 2012 01:54 PM PST

Greek default drama is much like the movie Groundhog Day. If you prefer a quote from Yogi Berra instead, then please consider It's déjà vu all over again.

Every day for weeks we have heard a "deal is close". Moreover, on multiple occasions at the end of the week we were informed Greece "had" to reach agreement over the weekend or Greece would default. Let's hope this time someone really means it.

Once again, Greece says faces 24-hour deadline to clinch rescue.
Greece has just one day left to strike a deal with impatient lenders and reluctant political party leaders on a 130 billion rescue plan before the country is pushed towards a chaotic default, its finance minister warned on Saturday.

In an apparent warning to Greek political leaders opposing key reforms, Finance Minister Evangelos Venizelos said the patience of European partners and the International Monetary Fund footing the bill for Greece's bailout was wearing thin.

Technocrat Prime Minister Lucas Papademos was due to continue talks with lenders on Saturday in a bid to clinch agreement before calling in the socialist, conservative and far-right party leaders in his coalition to seek their blessing.

That meeting of party chiefs, initially scheduled for Saturday, has now been put off until early Sunday afternoon, a government source said.

NOT BACKING DOWN

Athens' talks with its international lenders have stumbled over their demands, which include cutting labor costs by axing holiday bonuses and lowering the minimum wage - proposals vehemently opposed by Greek political party chiefs.

Greek officials have described the negotiations as tough, with the troika of European Central Bank, European Union and IMF lenders unwilling to yield an inch from their demands. Marathon negotiations ended without a deal on Friday.

"The troika is not backing down on wages, holiday bonuses and supplementary pensions," a Greek government official said.

"None of these issues have been resolved. They are all open and the onus is on political leaders."

The bond swap talks were now the easier part of the overall process to save Greece, Venizelos said earlier. Representatives for the banks and insurers were expected to continue talks in Athens over the weekend.

Increasingly frustrated with Athens' inability to enact the reforms needed to reshape the recession-hit Greek economy, foreign lenders have demanded proof of the country's commitment to spending cuts before doling out any more funds.

They have demanded extra spending cuts worth about 1 percent of GDP - or just above 2 billion euros - this year, including big cuts in defense and health spending.

They want all Greek political leaders - who are keen not to be linked directly with the painful reforms as they gear up for elections expected in April - to endorse the measures, irrespective of the outcome at the polls.

"Greek political leaders must offer their commitment to the program," said a source close to the lenders. "No more loans will be approved if they don't."

In the latest sign that coaxing political leaders into backing the reforms will be anything but easy, the leader of the far-right LAOS party, George Karatzaferis, rejected Venizelos' "ultimatum" to strike a deal by Sunday.

"We must go through every letter, every comma of the lenders' proposals and see whether they help the country and boost growth," he said at a Greek ceremony to celebrate the new year. "If the bailout doesn't suit us, we will not accept it."

At the traditional "pie-cutting" ceremony, Karatzaferis also gave away drachma coins to fondly recall Greece's pre-euro days -- a return to which many Greeks fear.
Please stop the madness. Bailouts to Greece cannot possibly work. Moreover, the world will not end on Monday if Greece exits the Euro, nor will the world end if Portugal and Spain do the same later this year or next.

Mike "Mish" Shedlock
http://globaleconomicanalysis.blogspot.com
Click Here To Scroll Thru My Recent Post List


Cost for Doing God's Work Declines from $13.2 Million to $9 Million

Posted: 04 Feb 2012 02:45 AM PST

Inquiring minds note a stunning drop in the price for doing God's Work. Last year Goldman Sachs CEO Lloyd Blankfein received a salary of a $600,000 as well as a stock bonus worth $12.6 million.

This year the base salary for doing God's work rose to $2 million, however, bonuses fell to a shockingly-low $7 million. The net effect is a decline from $13.2 million to $9 million.

That is a 30% reduction in the overall cost of doing God's work. Said Blankfein "Now I know how those in Greece feel".

For additional details, please see Goldman's Blankfein Awarded $7 Million in Stock for '11. Here is one key fact from the article.
The filings don't include how much any of the executives have been awarded in cash bonuses. Blankfein received a $5.4 million cash bonus for 2010, his first since getting about $27 million in cash bonuses for each of 2007 and 2006.
Mercy!

How can the man survive a cut like that?

Inquiring minds just may be interested in the performance that merited $9 million (plus undisclosed cash compensation, if any).

GS 2011 Stock Market Performance



Said Blankfein when questioned about plunge in share price "This just goes to show you how difficult it is to do God's work. I earned every penny."

Mish note: the quotes of course are fictional, the rest of the story unfortunately is not.

Mike "Mish" Shedlock
http://globaleconomicanalysis.blogspot.com
Click Here To Scroll Thru My Recent Post List


Seth's Blog : Will energy consumption stay private?

Will energy consumption stay private?

It's clear that the consumption of energy has external effects that impact more than just the person who is paying for it. Geopolitical, health and economic issues come to the neighbors and nearby citizens of entities that are using a lot of power.

It was always straightforward to see who was burning a lot of wood or drove a huge car. It's easy to see when a company has a huge smokestake belching carbon. What happens when sensors make it easy to see how efficient a machine is, how much of a resource is being consumed and how much exhaust is being spewed? What happens when Google maps shows you the block or the building that consumes the most electricity, or makes it easy to compare across industries?

When we have the opportunity to rank consumption by industry or by neighborhood, will we? We already watch our neighbors litter or have loud parties or paint (or fail to paint) their house...

A significant byproduct of the connection revolution is that things that were private because they were difficult to measure will no longer be private. When devices can talk to each other, the information rarely remains private. It's not going to stop with energy, of course. Just about all our buying decisions are going to be shared, and that changes the marketers job.

In a world of horizontal marketing, where tribes are aware of what their members are up to, I think it's going to happen quicker than most people expect.

 

More Recent Articles

[You're getting this note because you subscribed to Seth Godin's blog.]

Don't want to get this email anymore? Click the link below to unsubscribe.




Your requested content delivery powered by FeedBlitz, LLC, 9 Thoreau Way, Sudbury, MA 01776, USA. +1.978.776.9498

 

Weekly Address: It’s Time for Congress to Act to Help Responsible Homeowners

The White House Your Daily Snapshot for
Saturday, February 4, 2012
 

Weekly Address: It’s Time for Congress to Act to Help Responsible Homeowners

President Obama continues his call for a return to American values, including fairness and equality, as part of his blueprint for an economy built to last.

Watch the video:

The Weekly Address

President Barack Obama delivers his weekly taping in the Library of the White House, Feb. 3, 2012.  (Official White House Photo by Chuck Kennedy)

In Case You Missed It

Talking Tuition with the Wolverines: Shortly after delivering his State of the Union address, the President took his Blueprint for making college more affordable straight to the people at the frontlines of the issue—students. Speaking from the University of Michigan in Ann Arbor, the President noted that a college degree “will be the best tool you have to achieve that basic American promise.”

A Georgian Visitor: Georgian President Mikheil Saakashvili met with the President in the Oval Office on Monday, discussing relations between our two countries—including Georgia’s contributions to the military operation in Afghanistan and the potential for a free trade agreement between the United States and Geogia—and marked the 20th anniversary of diplomatic relations between both countries.

Hanging Out on Google+: The President participated in the first-ever virtual interview from the White House on Monday from the Roosevelt Room. After more than 227,000 people submitted questions or voted for their favorites, the President sat down for a discussion with a group of Americans from across the country in a Google+ Hangout. In case you missed it, you can watch the full video here.

The Cabinet Convenes: On Tuesday, the President held a Cabinet meeting in the Cabinet Room of the White House to discuss the ideas he laid out in the State of the Union. A top priority mentioned by the President during the State of the Union was the need to promote small business throughout the country, which was echoed in the meeting, where a new Cabinet member and the head of the Small Business Administration, Karen Mills, joined the discussion and ensured that entrepreneurs were represented.

In the Showroom: With Detroit’s newest vehicles on display at the Washington Auto Show, the President took a trip across town to get a glimpse of the outstanding work produced within the U.S. auto industry. He was impressed by what he saw, saying, “Because of folks coming together, we are now back in a place where we can compete with any car company in the world.”

Fairness, Responsibility and Housing: One way the President plans to achieve his Blueprint for an America Built to Last is by taking action to help responsible borrowers and support a housing market recovery. On Wednesday, he expanded on the ideas he presented in the State of the Union—including a proposal for a Homeowners Bill of Rights—at the James Lee Community Center in Falls Church, Virginia, where home values have fallen by about a quarter from their peak.

The National Prayer Breakfast: At the annual National Prayer Breakfast in Washington, D.C., the President spoke about how his faith and values guide the difficult decisions he makes as he leads the country.

West Wing Week: Your guide to everything at 1600 Pennsylvania Avenue. Check out the video.

Get Updates

Sign up for the Daily Snapshot

Stay Connected

 

This email was sent to e0nstar1.blog@gmail.com
Manage Subscriptions for e0nstar1.blog@gmail.com
Sign Up for Updates from the White House

Unsubscribe this email address | Privacy Policy

Please do not reply to this email. Contact the White House

The White House • 1600 Pennsylvania Ave NW • Washington, DC 20500 • 202-456-1111

 

Seth's Blog : Rightsizing your passion

Rightsizing your passion

Excitement about goals is often diminished by our fear of failure or the drudgery of work.

If you're short on passion, it might be because your goals are too small or the fear is too big.

Do a job for a long time and achieve what you set out to achieve, and suddenly, the dream job becomes a trudge instead. The job hasn't changed--your dreams have.

Mostly, though, it's about our fear. Fear is the dream killer, the silent voice that pushes us to lose our passion in a vain attempt to seek safety.

While you can work hard to dream smaller dreams, I think it's better to embrace the fear and find bigger goals instead.

 

More Recent Articles

[You're getting this note because you subscribed to Seth Godin's blog.]

Don't want to get this email anymore? Click the link below to unsubscribe.




Your requested content delivery powered by FeedBlitz, LLC, 9 Thoreau Way, Sudbury, MA 01776, USA. +1.978.776.9498