Start by selecting Custom Filters under Configuration.

Determine what you want to search for in the filters. I usually have one filter showing pages that do contain information I’m looking for then pages that don’t contain it.For example, filters 1 and 2 will usually be pages that do or don’t contain the UA number.

For the example below, I used SEER’s UA: UA-11852503-1.

If a site uses cross domain tracking because it spans multiple domains or subdomains, grab the line of code for cross domain tracking and drop it into filters 3 and 4. Make one filter Contains and the other filter Does Not Contain.

For the example below, I used setDomainName’, ‘seerinteractive.com.

For filter 5, try adding a snippet from the second portion of the GA code. I’ve been surprised by the number of sites that drop off the second portion and keep only the top part of the code.

For the example below, I used google-analytics.com/ga.js.

Once the settings are all in place, enter the URL in Screaming Frog and hit Start.

From there, go to the Custom tab. The data that shows up will be for any of your filters. You can quickly scan the filter data for issues, or can export data from each of the filters to a CSV for further analysis or to send to the client.

If you’re running Screaming Frog on a client’s site, give them a heads up first. Without that warning, someone may see the tool hitting their site quickly and think there is an issue.

Let me know if you use Screaming Frog to check for GA code too, or if you have other methods you currently use.

Quick update: Adding a well deserved shout out to Will Critchlow. Even though I wasn’t at Will’s Search Love presentation last October, I checked out the slides. They were so full of win. Slide 32 had the image of how Will’s team uses Distilled to confirm the presence of GA code, which got me to start looking at Screaming Frog and how to take that concept further. Will, Distilled team, etc, thank you!!

5 Scrapers for the price of 1 blog post! Here’s a new collection of 64 new ImportXML formulas and 5 spreadsheets to jump start your scrape-fest.

I wanted to update my original ImportXML Cookbook post so I recently asked twitter for requests (DJ Chris Le.. get it?! HAhaha ah h … ok, nevermind.)  The majority of requests were for social.  No surprise, right?  Well, here they all are! Let’s get cooking!

Preparations

1. You’ll need a new Google Docs Spreadsheet

2. In cell A1, type in the URL you want to scrape.

3. In any other cell copy-and-paste the recipe.  Press enter.

Noob tip: Use the example sheets as your starting point.  Pro tip: Use these recipes on any existing Google Doc! No code. Made with 100% pure formulas.

Quora: Followers of a Topic

Try the Quora starter spreadsheet

First up, by request (@dan_shure): Quora! The best way to get this URL is simply to find the topic on Quora, clicking on to followers and then cutting and pasting that URL into A1.

Example URL for cell A1:
http://www.quora.com/Search-Engine-Optimization-SEO/followers

User’s name
=ImportXML(A1, "//a[@class='user']")

URL to user’s profile
=ImportXML(A1, "//a[@class='user']/@href")

Number of answers
=ImportXML(A1, "//a[contains(@href, 'answers')]")

(Pro tip): To get around Google Doc’s 50 ImportXML limit convert cells into values.  First, select a bunch of cells, then hit copy, and then “paste as values.”

This will permanently record the result into the cell and free up some ImportXMLs for you.  I usually leave one row untouched so that I can use it later to get more data before converting those into data.

Quora: User Profiles

Example URL for cell A1
http://www.quora.com/Ian-Lurie

Photo
=ImportXML(A1, "//meta[@property='og:image']/@content")

Name
=ImportXML(A1, "//meta[@property='og:title']/@content")

Twitter profile
=ImportXML(A1, "//a[@class='sn_icon' and contains(@href, 'twitter.com')]/@href")

Facebook profile
=ImportXML(A1, "//a[@class='sn_icon' and contains(@href, 'facebook.com')]/@href")

Self description
=ImportXML(A1, "//meta[@property='og:description']/@content")

Canonical URL on Quora
=ImportXML(A1, "//link[@rel='canonical']/@href")

RSS feed on Quora
=ImportXML(A1, "//link[@rel='alternate']/@href")

Topics
=ImportXML(A1, "//a[@class='topic_name']//span[@class='name_text']/span")

Number of followers
=ImportXML(A1, "//a[@class='link_label' and contains(@href, 'followers')]/span")

Number of topics
=ImportXML(A1, "//a[@class='link_label' and contains(@href, 'topics')]/span")

Number of questions
=ImportXML(A1, "//a[@class='link_label' and contains(@href, 'questions')]/span")

Number of answers
=ImportXML(A1, "//a[@class='link_label' and contains(@href, 'answers')]/span")

Number of edits
=ImportXML(A1, "//a[@class='link_label' and contains(@href, 'log')]/span")

Quora: User’s Followers

Example URL for cell A1
http://www.quora.com/Ian-Lurie/followers

User’s name
=ImportXML(A1, "//a[@class='user']")

URL to user’s profile
=ImportXML(A1, "//a[@class='user']/@href")

Number of answers
=ImportXML(A1, "//a[contains(@href, 'answers')]")

Reddit

Reddit scraper starter spreadsheet

The URL for Reddit is best typed in manually instead of copying what you see in your browser.  Replace “importxml” in the URL with your own search term.

Example URL for cell A1
http://www.reddit.com/search?q=importxml&sort=new

Title
=ImportXML(A1, "//p[@class='title']/a")

URL
=ImportXML(A1, "//p[@class='title']/a/@href")

Submitted on (as UTC)
=ImportXML(A1, "//p[@class='title']/../p[@class='tagline']/time/@title")

Author URL
=ImportXML(A1, "//p[@class='title']/../p[@class='tagline']/a[@href][1]/@href")

Posted to
=ImportXML(A1, "//p[@class='title']/../p[@class='tagline']/a[@href][2]/@href")

Topsy

Topsy scraper starter spreadsheet

Example URL for cell A1
http://topsy.com/s?q=importxml

Title
=ImportXML(A1, "//h3[@class='title']")

URL
=ImportXML(A1, "//h3[@class='title']/a/@href")

Tweeted by name
=ImportXML(A1, "//div[@class='author-bar']/a")

Tweet
=ImportXML(A1, "//span[contains(@class, 'twitter-post-text')]")

Original tweet URL
=ImportXML(A1, "//div[@class='actions']/a[contains(@class, 'date-link')]/@href[1]")

Trackback count
=ImportXML(A1, "//div[@class='actions']/a[contains(@class, 'trackback-link')]")

Topsy: Twitter Trackback

Stats on Influential people who RT’ed you spreadsheet

This is where the fun really is.  Combine lots of these ImportXML’s to make some really great spreadsheets.  This was fun. First, find the page you want to get trackbacks on, copy that URL into A1 and get going!

Example URL for cell A1
http://topsy.com/www.seerinteractive.com/blog/importxml-cookbook

Posts count
=ImportXML(A1, "//div[@class='retweet-button-box']/span[@class='count']")

Tags list
=ImportXML(A1, "//ul[@class='tags-list']/li[@class='tag']")

Related (influential): twitter name
=ImportXML(A1, "//span[@class='author-bar']/*[contains(text(), 'Influential')]/../a")

Related (influential): tweet
=ImportXML(A1, "//span[@class='author-bar']/*[contains(text(), 'Influential')]/../../span[@class='twitter-post-text']")

Related (influential): tweet date
=ImportXML(A1, "//span[@class='author-bar']/*[contains(text(), 'Influential')]/../../../div[@class='actions']/a[contains(@class, 'date-link')]")

Related (influential): tweet URL
=ImportXML(A1, "//span[@class='author-bar']/*[contains(text(), 'Influential')]/../../../div[@class='actions']/a[contains(@class, 'date-link')]/@href")

Related posts: twitter name
=ImportXML(A1, "//div[@class='twitter-post-big']//a[contains(@class, 'author-link')]")

Related posts: tweet
=ImportXML(A1, "//div[@class='twitter-post-big']//span[contains(@class, 'twitter-post-text')]")

Related posts: tweet date
=ImportXML(A1, "//div[@class='twitter-post-big']//div[contains(@class, 'actions')]/a[contains(@class, 'date-link')]")

Related posts: tweet URL
=ImportXML(A1, "//div[@class='twitter-post-big']//div[contains(@class, 'actions')]/a[contains(@class, 'date-link')]/@href")

(Pro tip): Interested in stalking people for links? (Links to slide deck by @wilreynolds)

Combine these ones with ImportXMLs from Twitter to devastating effect. Use Google Docs automate your hit list. You can be creepy and efficient!

LinkedIn: User’s profile

Here are some updates to the original LinkedIn ImportXML formulas from the first ImportXML Cookbook.

Example URL for cell A1
http://www.linkedin.com/in/wilreynolds

Current
=ImportXML(A1, "//dd[@class='summary-current']//ul[@class='current']/*")

Number of connections
=ImportXML(A1, "//dd[@class='overview-connections']//strong")

Profile summary
=ImportXML(A1, "//p[@class=' description summary']")

Twitter

Again are some more updates and additions to the original formulas from the first ImportXML Cookbook.

Example URL for cell A1
http://www.twitter.com/djchrisle

Bio
=ImportXML(A1, "//span[@class='bio']")

Location
=ImportXML(A1, "//span[@class='adr']")

Website
=ImportXML(A1, "//a[@class='url' and @rel='me nofollow']/@href")

Following
=ImportXML(A1, "//span[@id='following_count']")

Following
=ImportXML(A1, "//span[@id='follower_count']")

Lists count
=ImportXML(A1, "//span[@id='lists_count']")

Number of Tweets
=ImportXML(A1, "//span[@id='update_count']")

Randomly selected followed names
=ImportXML(A1, "//div[@id='following_list']/span/a/@title")

Randomly selected followed URLs (add http://twitter.com)
=ImportXML(A1, "//div[@id='following_list']/span/a/@href")

RSS Feed of tweets
=ImportXML(A1, "//div[@id='rssfeed']/*[contains(@href, 'statuses')]/@href")

RSS Feed of favorites
=ImportXML(A1, "//div[@id='rssfeed']/*[contains(@href, 'favorites')]/@href")

Tweets (recent)
=ImportXML(A1, "//span[@class='status-body']//span[@class='status-content']")

Tweets (timestamp)
=ImportXML(A1, "//span[@class='status-body']//span[@class='published timestamp']/@data")

Users recently mentioned in tweets
=ImportXML(A1, "//span[@class='entry-content']/a[@class='tweet-url username']")

Google Insights

Google Insights scraper starter spreadsheet (say that 3 times fast)

And the last song for tonight, this one goes out to @dsottimano and @dohertyjf!  Google Insights!

Example URL for cell A1
http://www.google.com/insights/search/overviewReport?q=seo%20company&content=1

Headline Trends
=ImportXML(A1, "//td[@class='trends-headline-headline']/a")

Headline Trends URLs
=ImportXML(A1, "//td[@class='trends-headline-headline']/a/@href")

Rising search terms
=ImportXML(A1, "//div[@id='overviewRisingSearchesDiv']/table/tr[2]//table[@class='trends-table-data']/tr/td[2]/a[1]")

Rising search percent
=ImportXML(A1, "//div[@id='overviewRisingSearchesDiv']/table/tr[2]//table[@class='trends-table-data']/tr/td[3]")

Top search terms
=ImportXML(A1, "//div[@id='overviewTopSearchesDiv']/table/tr[2]//table[@class='trends-table-data']/tr/td[2]/a[1]")

Trivia: The trick to finding this one is that the content is loaded when you specify the query string “content=1.”  By turning on “Log XMLHttpRequests” in Chrome’s Inspect Elements window, you can watch JavaScript make AJAX calls and then start figuring out the right URLs to make calls to.

OMG! Epic! Here’s more Google Docs insanity from SEER:

Visualize Your Backlinks with Google Fusion Tables (May, 2012)
Google Search Scraper (February, 2012)
SEOmoz Data for Google Docs (February, 2012)
(Not Provided) Report in Google Docs (November, 2011)
ImportXML Cookbook 1 (September, 2011)

Want more?

(Shameless plug) Talk to me on Twitter @djchrisle. I take requests and birthday shout-outs :-D

Here are some of the quick details for you!
When – June 22, 2012
Where – The Search Church (1028 N. 3rd Street, Philadelphia, PA)
Price – FREE! (but donations are accepted)

The event will feature speakers including Distilled’s Tom Critchlow (@tomcritchlow), iAcquire’s Mike King (@iPullRank) and SEER’s own Wil Reynolds (@wilreynolds) and Ethan Lyon (@ethanlyon). In typical SEER fashion, this isn’t just an SEO conference. This is also an opportunity to give back to the community and help out an awesome cause!

All of the speakers listed above will demonstrate how to rank for a specific charity or non-profit’s top keyword, focusing on different methods and linking strategies that they’ve acquired from over the years. SEER’s own Google Doc wizard Ethan will also be presenting a super new tool that he’s created specifically for keyword ranking! This is definitely an event you do not want to miss.

So, who’s the lucky non-profit organization we’ll be teaming with?

You tell us! Fill out the form below (or click here) to submit your charity or non-profit for consideration. We’ll host a contest on SEER’s Facebook Page afterwards with all of the recipients, and whichever non-profit receives the most Likes on their logo in our Facebook contest will win the following:

• The focus of each individual presentation at the Did You Google It? Search Church event!
• A FREE SEO consultation by our team of experts to analyze how to increase traffic and conversions for your website, along with different top SEO industry applications and programs worth over $20,000! 
• All cash donations from the event! The Search Church will be going back to its roots of helping the community and donating to charities by accepting donations throughout the event. All proceeds we fundraise will go directly to the winning non-profit organization.

We’re looking forward to hosting another awesome SEO meetup here at our Search Church and learning about keyword ranking strategies from some of the most influential SEOs in the country, and we hope you are too! Please spread the word about our search to find great a non-profit organization to focus our event around and get ready for June 22nd’s “Did You Google It Event!” For any questions, please email me at melissaa@seerinteractive.com.

Visualization and presentation have never been more important then they are today. The amount of data that we collect, process, and analyze is overwhelming. Have you ever peeked into the backlink profile for a domain that has over a million links? Can you even imagine what a million links looks like? I can’t. Luckily, we’ve come a long way in terms of visualization since the invention of the pie chart.

Introducing . . . Fusion Tables.

In typical Google fashion, Google quietly rolled out a brand new “Experimental” version of their Fusion Table application a few weeks ago. If you haven’t had the chance (or reason) to play with Fusion Tables yet, the time has come. Consider it Google Spreadsheets on steroids, or Google’s answer to “big data.” I’ll be writing a lot more on how to harness the power of Fusion Tables to create massive databases, but for now, I just want to focus on one incredibly awesome feature–the Network Graph.

This is what 15,000 links looks like

Who said that competitor backlink research couldn’t be fun? Aside from hosting enormous amounts of data, Fusion Tables can also visualize the connections between your data in a simple, interactive map. This new Network Graph feature takes seconds to set up and can have many different applications.

You can view the completed Network Graph and Fusion Table here. 

Want to SEE who is linking to you? Here’s how:

1. Gather your data

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”). What I wanted to see is how are these four sites connected? I pulled all of this data using SEOMoz’s ever-so-helpful Open Site Explorer. You can gather your data any way you choose.

2. Clean up your data.

If you’re pulling a lot data from a lot different places, you might want to clean it up. Fusion Tables will actually do this for you and you can even merge tables and aggregate data via the web interface, however, it is still “experimental” and there are still some bugs so I prefer to have everything in one pretty .csv.

3. Create a new Fusion Table

Head on over to Google Docs / Google Drive / Whatever We’re Supposed To Call It Now and Create > New Table.

(If there’s no option for Table, try logging in with a non-Google Apps account)

From there, it’s pretty straight forward. You can either upload a CSV, use an existing Google Spreadsheet or create an empty table.

After you’re done uploading, you can choose which columns you want to import.

4. Create a Network Graph

Once you have your massive table of data, you can create your Network graph under Experiment > Network Graph

Set Vertex 1 to URL and Vertex 2 to Target URL and weigh by Number of Links

BOOM. That’s it. Now the fun begins.

As you increase the number of nodes shown, more and more blue links begin to appear. At around 2,000 nodes, it’ll start to become unresponsive. You can also use the Filter feature and filter out all links that have a Page Authority lower than 30.

Analyze This

So what does this tell us (other than that SEOMoz = the Death Star)? Well, for starters, if I were an SEO company, I’d want to make sure I was getting a piece of this pie. This visualization gives us a very clear picture on where we’re getting our links from.

Need help identifying a link network?  Here’s your tool. Want to find out who the Death Star is in your industry? Here’s your tool.

Social Media

If you’re like me, you probably thought that this kind of Network Graph visualization would be great for visualizing social media data. It is. Here I pulled some Twitter data from some of the big names in the SEO industry to see who is following who.

A Few Things To Consider

This is barely scratching the surface of what Fusion Tables can do. Since this is “Experimental” also don’t expect things to run smoothly. This feature could also disappear over night (although I hope it doesn’t). Believe me, there is A LOT more that can be done with Fusion Tables and this is a great push by Google to help make data less intimidating.

Furthermore, I’m very interested to hear your ideas. How will you use Fusion Tables? What other tools do you use for quick visualizations?

It’s probably not a smart idea to run with these suggestions as they’re presented – as with many of Google’s suggestions in AdWords, they may not always be fully applicable to your exact needs. However, it is a great way to help sort the keyword suggestions you are given.  Whether you’re just getting your feet off the ground or looking for expansion ideas, this beta has a lot of potential to help!

You can use this tool to build new campaigns and ad groups within an existing account or create a new account build from scratch. I, personally, have found this beta valuable in weeding out themes unrelated to the keyword research I was completing, themes which I would want to exclude from my campaigns. Additionally, you can use this beta to better zoom in on keywords that are more related to your clients’ initiatives. For example, I was able to easily weed out all keywords relating to online and free sales, as these themes do not closely align with my clients’ goals.

A few quick notes about some cool features:

• Clicking on the plus sign to the left of each theme to expand the section and get more similar keywords.

  

• Selecting the Save all button will select all keywords in that ad group theme.
  

  

• Selecting the drop down arrow to the right of each specific keyword will give you additional options including performing a Google search and going to Google Insights for Search.
  

  

• To exclude a particular ad group theme that you have already selected, you can remove it by clicking the x next to the respective ad group theme idea in the bottom left Save Ideas box.
  

  

• You’ll see in the image above that there is an option to “View in Traffic Estimator.” If you click on that link, the Traffic Estimator will open in a new window with your selected keywords already plugged into the tool. You’ll just have to enter your Max CPC and/or Daily Budget. There is also the option to change the metric of the y axis to clicks, impressions or cost.

What do you think about this Ad Group Ideas beta? Have you already seen this beta? How have you been using it? What do you like and dislike about it? Share your comments and be sure to check out Chris’ latest blog for other keyword research ideas!

Don’t believe me? Check out #ppcchat on Twitter.

So, what is all the fuss about?

Today AdWords has decided that they know what is best when it comes to Advertisers & ad testing by making a change to Ad Settings …

Introducing “Near Rotation”

You can read the full announcement here

To highlight the important part of this change:
Starting next week, the “rotate” setting for ad rotation will change. Instead of rotating creatives for an indefinite period of time, this setting will only rotate for a period of 30 days. After that, the setting will then optimize to show the ads expected to generate the most clicks. Every time a creative is enabled or edited, the ads in that ad group will rotate more evenly for a new period of 30 days. This change will be rolling out next week. At that time, ad groups with creatives that haven’t been added or modified in the past thirty days will see this new ad rotation behavior. Otherwise, this change will begin 30 days after your last creative was enabled or edited.

Yowzah.

Anyone who is a savvy PPC professional is pulling their hair out now … if Google had ears, they would be ringing!

Before we drill into why everyone is up in arms, some background on the Rotate ad setting…

The Rotate setting allows for true A/B testing, meaning that each ad serves (fairly) equally in order to accurately compare results and find the optimal ad text. This is a setting AdWords has blatantly advised against:

The interesting part is that Rotate is by far the preferred ad rotation setting for savvy PPC professionals. Hence, the outrage from #ppcchat members on Twitter.

This setting is absolutely critical for accurate ad testing. So much so that it’s been a request for adCenter for years (See Wishlist here) and recently has been added to the roadmap.

Bravo adCenter, you are killing AdWords on this one!

Let’s dive into why this may be the worst change, potentially EVER, in AdWords history!

1. This change automatically takes place NEXT WEEK! Goodbye to-do list…..

2. This new setting is focused on one thing only – Clicks! Since when do clicks equate to conversions? Call me crazy, but if I wanted to optimize for clicks, I’d use the ‘Optimize for Clicks’ setting!

3. 30 days is not always enough time for ad testing! Especially for campaigns that are lower volume.

4. This is replacing equal rotation, not in addition to, with no way to avoid this.

5. Obviously since this is focused on finding the ad to generate the most clicks, it’s critical to note that this also doesn’t take into consideration any offline conversions, such as phone calls that may be WHY clicks are less but the ad converts better.

6. All of the Ad Rotation settings lead you to optimize for clicks to some degree:
Near Rotation – After 30 days optimizes for clicks
Optimize for clicks – Optimizes for clicks
Optimize for conversions – If there isn’t enough data for conversions, optimizes for clicks

While this is becoming known as the “Evil Setting” and “Worst Change Ever” in the PPC World, I always like to step back and think about the positives this could bring.

It was like pulling teeth to come up with this, but I think I found some.

First, to a small degree I can see where Google is coming from in regard to “This change will enable us to provide users with the most relevant ad experience and should help advertisers improve the performance of their AdWords accounts.”

In theory, if an ad is generating more clicks, it’s likely it has a higher CTR, which plays a significant role in Quality Score.

However, puting that aside, back to my point above – clicks do not equal conversions/sales for my clients. And neither does a perfect Quality Score. If they did, my job would be easy peasy!

For advertisers who currently don’t review ad tests, first, shame on you. Second, this may force you to review your ad tests!

And of course, for Google, the one everyone is thinking…. more clicks = more money!

Come’on…had to put that in there, especially as it falls so timely after Q4 reported CPC drops (Read more on CPC decreases:Forbes article ClickZ article)

The big question now is how do you prepare for this change?

1. Take a deep breath.

2. Put on some good tunes.

3. Inform your clients of the change, what you expect as a result, solutions for how you plan to address the change.

4. Review each individual account to determine what the best action steps are.

For example maybe you should:
• Review current ad tests & launch new tests. (Tip: set a milestone to review those tests at 25 days so you can launch a new one before Google optimizes for the one getting more clicks)

• If you Review current tests & haven’t found a winner, consider using ACE to split test your ads. (Note: remember that ACE has a maximum time frame for tests at 90 days. Set a reminder to check results!)

• Review optimal ad settings – Will some accounts now fair better on Optimize for conversions (again, remember if there isn’t enough conversion data it optimizes for clicks), Optimize for clicks?

5. Be prepared with a bank of new ads to test!

This is causing quite the upset in the PPC world and it was announced just a few hours ago. Leave your comments/thoughts/suggestions on the change and how you plan to handle ad testing moving forward.

AdWords – would love to hear from you as well! Help us understand how this will still allow for strategic ad testing!

]]>