Playing Around with ImportXML in Google Spreadsheets Put me in front of a Mac and it’s almost as if I never learned to use a computer. Put me in front of Google Spreadsheets and all of the time I’ve spent working with Excel feels a little like time wasted, and not in a good way. I’m just not very used to a spreadsheet that isn’t Excel. Unafraid of a challenge, I recently decided to give Google’s (exceptional) importXML, importFEED and importHTML functions a try – the ability to fetch information from the web to retrieve the data you need. Mostly to make an interesting blog post, but partly out of envy that Excel doesn’t have this function. It’s frustrating trying to get XML data into Microsoft Excel – unless you’ve got the time and patience to build some basic Macros or VBscript for your requirements. A few resources If you want to use Google Docs to extract data from the web, it would be a good idea for you to learn a little xPath. If you’re interested, I made a Google Docs Spreadsheet with all of the examples below:
Excel and Google Docs: Tools for the Ultimate SEO Dashboard I love SEO tools. I’m sort of a pack-rat when it comes to Google Docs tools and Excel add-ins. I went absolutely nuts when Tom Critchlow posted his Google Docs SEO tutorial on distilled. I’m a firm believer in bootstrapping. Before we get started, be sure you have an SEOmoz/Linkscape API key. First off, I want to show you a tool I concocted after reading Tom’s tutorial. Long Tail Keyword Prospecting Long Tail Keyword Prospecting - Make a Copy Long Tail Keyword Prospecting - Instructions (right here!) The basic idea behind this tool is to quickly explode your keyword list with long-tail goodness. You’ve got four columns here. The really fun thing about this tool is how easy it is to drill down to the uber long-tail phrases; I got a suggestion for “car speakers reviews” from the root “car speakers”, so let’s drill a bit deeper and run the suggest for “car speakers reviews” by adding “reviews” to our 3rd word column. Ta-da! Content Strategy Generator SERP Competitive Analysis Oh, holy crap.
regex - Google Apps + HTML + Regexp Competitive Analysis in Under 60 Seconds Using Google Docs Faced with a new client, and having established a list of keywords they need to target, you want to evaluate the competition to find out what sites are dominating the SERPs for these keywords. However... being an SEO you're a busy guy (or gal), and you need it done right now. I've built a Google Docs tool to automagically do exactly that and this post will walk you through it. The basis for this tool comes from a report in this linkbuilding post on YOUmoz which contained a neat little 'SERP Saturation' report. Cool, eh? 1. So, what's the report all about? Ours will be sorted by the cumulative number of times a subdomain has appeared in the top 10 of the search results over all the keywords we specify, and will display the mozRank, Domain Authority and Linking Root Domains for each. You can just duplicate the Google Docs spreadsheet I provide below, and change almost any of this to add, modify or take away as per your needs. 2. You must configure it the first time you use it: 3. 4. 5. 6.
Excel for SEO – Powerful Cheat Sheet to Boost Productivity Learn the most important Excel tricks and tips to effectively manage and speed up your SEO and Analytics campaigns. These are the tricks which every internet marketer should know. No one has time to spend ages mining for Excel answers from mountains of text to quickly find and solve SEO and Analytics issues. These issues can be something like: Finding and removing duplicate URLs Extracting domain names from the URLs Converting upper case URLs into lower case URLs Scraping on-page elements like title tags, meta description tags, keywords etc. Finding and removing duplicate words for keyword research Creating the right charts for reporting and analysis. What I am going to present to you is actually a worksheet (we use internally for training purpose and quick reference) in the form of a blog post. Menu Editing in Excel Text Manipulation in Excel Data Validation in Excel Working with Formulas in Excel Selection in Excel Navigation in Excel Charts and Tables in Excel Macros and VB Editor in Excel 7.
How to Access Region-Locked Online Content From Anywhere - TechSpot Guides Online services like Netflix, Hulu, Pandora, Spotify, Steam and many others have changed the way millions of people access media. They've brought upon an era of instant, on-demand digital media consumption in a world where linear programming, bundled content, and physical formats no longer fit many people's lives. Unfortunately this is a revolution not everyone can partake in (not yet or not as easily, at least) as such services employ region locks to limit access from specific countries. In this article we’ll offer you three alternatives to get around these restrictions. Most likely you'll only need one of these options. Alternative #1: Proxy Using a proxy is a quick and simple way to bypass websites’ geographic restrictions. To start using a proxy as an intermediary for your web requests simply enter the information in your browser: As with almost anything that comes for free, though, there are a few caveats. After installing ProxMate you'll see a link to unblock videos on Hulu
Keyword Research - Using Categories to Make Your Process More Actionable The author's posts are entirely his or her own (excluding the unlikely event of hypnosis) and may not always reflect the views of Moz. One of the earliest and arguably most important parts of the SEO process is keyword research. Keyword research helps you answer that all important question, "In what quantities do people use search engines to find the products and services on my website?". So, it's a bit of a shame that some SEO's don’t like doing keyword research. This is a "give it up" post For several years now, I have used a methodology for our client keyword research that I believe adds deeper, actionable insight in to the decision making process. Before we get started, I’ll make the disclaimer now – I believe in this process so much that I built a keyword tool that does all of this work for you. What you need to know first To follow along, you need to understand a key principle in my methodology. That’s it! Here’s a basic example of what I mean: Gather your keywords Yay! All of it.
Using Excel for SEO – the Grand Collection of Tips You probably know that I am a big Excel fanatic (though not an expert). To me, Excel has always been the ultimate SEO, guest blogging and productivity tool. I’ve been collecting Excel tutorials for years and this post lists the most useful (yet, the least geeky) of them: no matter which SEO task you have come across, chances are you’ll find one of the following tutorials handy: 1. Export Any Data to Excel Any well-known keyword research or traffic analytics tool has the “Export-to-CSV” feature and a CSV file is easy to open in Excel – so converting your data into Excel shouldn’t be a problem. If you still you need some examples, I did a post quite some time ago listing many ways to export your backlink data to Excel; for instance: With Yahoo! 2. 1. This post by Richard Baxter on creating a pivot table and a beautiful chart using Excel offers a step-by-step tutorial on how to re-organize your data to run various types of analysis. 2. 3. 3. 1. Now use the macro: 2. Before: After:
HTML data-*-attributes | morgenstille.at ☼⟂ Since I’m working with a server-side programming language (PHP), JavaScript and HTML I’ve been dealing with a way to separate logic, structure and presentation layer. Especially when you work on projects that have a lot of frontend logic. There’s always a need for change late in the project in either one of them. When you have to change the structure or the representation layer you will mess up your logic when there’s no clean separation and the other way around. With the html data-*-attribute things get much easier. It solves the problem of how to pass data from your server to the frontend, helps avoiding mixing up style definitions and frontend logic (class-attributes) and gives your frontend data a home.There have been a lot of discussions since 2008 how and when to use the data-*-attributes, as you can read in the blog posts below. In the HTML5 spec the custom data attributes are clearly defined for use for in your own application. Passing data to JavaScript Bad: Good: Better: Upshot
Visualize Your Backlinks with Google Fusion Tables Data can be boring. I’m sorry, but it’s true. Don’t get me wrong, I love a good spreadsheet as much as I love a good novel, but whenever I can get away with it, I’d much rather just see the movie. Visualization and presentation have never been more important then they are today. In typical Google fashion, Google quietly rolled out a brand new “Experimental” version of their Fusion Table application a few weeks ago. This is what 15,000 links looks like Who said that competitor backlink research couldn’t be fun? You can view the completed Network Graph and Fusion Table here. Want to SEE who is linking to you? 1. For this experiment, I gathered the backlink profiles for four companies in the SEO industry, SEER Interactive, Distilled, SEOMoz, and Zamolution (who rank #1 for “Philadelphia SEO Company”). 2. If you’re pulling a lot data from a lot different places, you might want to clean it up. 3. (If there’s no option for Table, try logging in with a non-Google Apps account) 4. BOOM.