[Link Analytics Through APIs] [Richard Baxter] [CEO & Senior Search Marketing Strategist]

[SEOgadget] [SEOgadget.co.uk/mozscape] [@richardbaxter] How are we all?

Wasn't yesterday awesome?

Can we have a round of applause for the speakers and the SEOmoz team from yesterday?

Thank you very much. [applause]

Those of you who have never heard me speak at a conference before

you must have worked out by now that I'm here from England.

If you've never visited England before,

it's a pretty easy place to work out.

It's a country where policemen chase criminals with sticks.

Everyone likes a nice cup of tea,

and Casey loves it when I say, "Shine your shoes, guvnor?"

So anyway, we're here to talk today about data.

I love data. I love working with data.

It helps me—hey, AV team, we need the presentation up.

This is the thing I'm going to show them in a minute. Thank you.

That was the thing I was going to show you in a minute.

Anyway, so I love working with data.

It makes me smarter as a marketer,

and it helps me lead my clients to make the right decisions.

There is a problem,

and the problem is time.

Think about it.

How much time do you guys spend waiting for CSV files to download?

How much time are you putting in

collecting, fetching, collating data,

putting it in an Excel spreadsheet?

Imagine that you spend half an hour

a week just doing that one thing.

Half an hour a week is 2 hours a month.

Two hours a month is 24 hours a year,

and then imagine that you've got an SEO team

of 20 people.

That's like $300,000 worth of billable SEO consulting time

that we're wasting.

I'm going to help you guys solve this problem.

We're going to solve that problem with some tools I'm going to give you today,

and with those tools, we're going to build some awesome shit.

This, my friends, is a working version of Open Site Explorer in Excel.

This will tell you keywords with high volume,

low competition, and whether or not there is an exact match to name

in the top 3 ranking search results for that keyword

and the search volume.

I'll show you this stuff later.

And this will tell you who is linking to your site and what anchor text they're using.

I'm pretty excited to show you this stuff.

First we're going to start with some basics.

We're here to talk about link analytics using APIs.

Most of the people in the room aren't developers.

I'm not a developer. Will Reynolds was talking about this yesterday.

You don't have to be a developer to interact with APIs.

It's super exciting that you don't have to be one of these people.

Let me show you this.

This is an API called Shared Count.

I was at dinner with Dan Shore last night, and he said,

"What's a really good way to get into using APIs

if I've never interacted with an API before?"

This is it.

What we have here is JSON.

It's a form of text output.

When I go to this URL, api.sharedcount.com?url=/

whatever URL you're interested in, the data that you get back

is actually social data.

You've got Stumbles, you've got Reddits, you've got Facebook activity.

It's all in there.

That's pretty exciting.

We have a tool called Link Cleanup and Contact.

You can paste in all of your URLs, and it will give you the contact details

for each one of those links, so if you're trying to outreach

to sites or if you're trying to clean up bad links

you can get the contact details for that.

This has an API too.

In fact, this is the first time anybody has seen this API.

This is free for you guys to use, and I'm going to show you how to use it.

Go to tools.seogadget.co.uk/use_api,

and look, actually, some of the stuff in that data

looks pretty familiar.

You've got Google page rank from the domain.

You've got Who Is details.

You've got the contact URL.

There is a lot of really interesting and useful data you can use

to be better at SEO, analyze your links faster and all of that stuff.

I'm going to switch to Excel, and I want to show you guys

how to start building these types of tools.

AV, if you can switch to Excel for me.

Thank you.

Like I said, APIs are really a source of information,

and there is no point having to go off to a website

and trying to download CSVs and integrating them

back into your spreadsheets if you don't have to do that.

I'm going to show you some toys.

Now, this is an API called Whip Mania,

and I like Whip Mania because when you give it an IP address,

it tells you where that IP address is.

If you're analyzing your back links and trying to work out

where all of your in links are coming from,

this is the tool for you, and I'll show you how it works.

You form a URL like this.

First of all, we need to get an IP address for it.

I'm using SEO Tools for Excel,

but there are lots of other ways to do this.

You can do this kind of thing with Google Docs too.

If I go = resolveip seomoz.org.

Whoops. I'm glad the Internet is working.

There we go,

so www.seomoz.org.

Okay, http.

Sorry. I'm going to start again.

Live demos. Aren't they awesome?

Okay, =resolveip.

You wouldn't believe how many times I did this when I was practicing

this presentation, and now it works.

[applause]

Right, so data into Excel.

It doesn't stop there. Don't clap yet, because this is the easy bit.

Right, so let's make a URL to form a valid API call in Excel.

We do that with our friend concatenate.

We're going to concatenate cell A1.

Look, sorry, A2. That's where our Whip Mania URL is.

And that IP address there.

Right, now watch this. We've made a URL.

We're going to copy that.

I'm going to show you how easy it is to interact with APIs.

Just paste an API URL into a browser and see what data you get.

We have US, which is pretty cool.

How do we get that into Excel?

This is the first and the easiest way for you to do that.

If we tell Excel to download that string there it is.

Think about that.

You could have a list of links and where those links are all hosted

just using that methodology, but it gets slightly more interesting

when you realize that JSON API output I showed you in the presentation—

let me open that—has loads of data points.

I love it when it's not my machine that I'm doing this stuff on.

Hang on. Here we go.

Check this out.

We've got Stumbles, Reddits, Facebooks,

Facebook shares.

Let's say that I wanted to get maybe the total number of shares

on LinkedIn that I've got for a link

and the number of tweets that I've got for a link

via the API rather than any other method.

Let me introduce you to a function called JSON Path on URL,

because we're working in JSON.

JSON Path in URL is called the JSON Parser.

It can look for what's called a response field,

like the words "stumble upon," and then fetch that data and put it into a cell.

The way we do that is like this.

JSON Path on URL.

I think that's cell A8 comma, and then we tell it what we're interested in.

We're interested in LinkedIn shares,

which is LinkedIn like that.

It's case sensitive. Let me just check.

Capital L, capital I. That will be it.

If I can do that, you can do it.

Now we can build a data set with our social share data

just from an API call, which is pretty exciting stuff.

[cheer] But don't cheer yet.

Thank you, but don't cheer yet, because it gets more exciting.

Let's imagine that you've got a list of links

in your spreadsheet, and you want to know

the email address in the Who Is record,

and you want to know the page rank of the domain maybe,

and you want the contact URL for that site as well

and maybe the Twitter URL if we've got time.

Let's get a bit more complex.

I'm going to introduce you to a feature called HTTP Downloader.

Very briefly, HTTP Downloader configures Excel

as a browser, so I can turn caching on and off,

and I can delay requests, that kind of stuff,

so the 1,000 is 1,000 milliseconds,

which equates to 1 call per second.

What we've got to do is form a valid request URL with our API.

That would look a little bit like this,

= concatenate.

Now we need to take tools.seogadget.co.uk/api.

We're going to anchor that with F4 comma, this URL here.

This is one of Gianluca's blog posts,

and our API key.

Try not to remember that API key, because that's my API key,

and you can't have my API key,

and we haven't built the functionality that changes API keys yet either.

Here we go. Let's test that in the browser, because that's how we do it.

Cool, there is our data.

What were we interested in first?

The email.

We'll check the name of the email data.

You'd think I'd know my own API better than—oh, email.

Okay, that's pretty self-explanatory.

So, =JSON path on URL.

That URL there, comma, and we need the email,

and we're also going to fetch HTTP Downloader.

Okay, I screwed that up again.

Oh, so US keyboards have the @ symbol in a different place.

That's all.

That's why I was screwing that up.

Now let me fix this.

I think I need to put $ and a dot dot.

There you go.

Emails addresses from Who Is into Excel from a list of links.

Okay, it gets more interesting than that.

Let's grab, I don't know, page rank or something like that as well.

Let's go for google_domain_pagerank.

Now we can copy our formula and paste that in like that,

and we'll change email to google_google domain page rank, wasn't it?

There you go.

Think about the power of that.

You didn't have to download a thing to make that work,

and I did it, and I'm really not that smart.

But it gets more interesting than this.

AV, could we switch back to the presentation quickly?

That's cool, right?

Thank you. [applause]

What was the thing that I showed you

at the beginning of the presentation?

The cool thing was the SEOGadget Links API Extension for Excel.

This is super exciting.

I'm going to switch back to Excel and show you this

rather than bang on about what it actually does.

Thank you.

Okay, so the problem that we've been having

with working with lots of link tools and not just open sites

or the Mozscape API is that we're always waiting

for absolutely ages to get our data, and then we have to

integrate it into Excel, consolidate it, merge it.

I want to spend my time analyzing.

How should we do this?

Everybody knows about the SEOmoz URL Metrics API, right?

Actually, guys, can you switch back to the presentation?

I want to show you one thing first before we do this.

This is important, actually,

because the Mozscape API is awesome.

There are 4 key APIs. I'll show you this first.

Then the Excel bit will make a lot more sense.

There are 4 key APIs in the Mozscape API.

We've all been using URL metrics, which is the thing that spits out

page authority, domain authority,

total linking root domains to the sub domain if you're a Pro member,

all of that stuff, so that's the one that we all know.

It's the one that all of the Google dot hacks work with.

But there are 3 more, and they are super exciting.

Top pages.

You know the report in Open Site Explorer that tells you your most linked to

pages on your site, top pages on domain?

It's the Top Pages API.

It returns a list of URLs in order of the number of linking root domains

to each URL.

Anchor Text API, you know what the Anchor Text API does.

It tells you how people are linking to you and with what anchor text.

And then the Links API is super powerful.

The Links API spits out links,

so you give it a URL, and it tells you who is linking to you.

That's the thing I'm going to show you next.

I'm super excited about this.

Our tool, links extension for Mozscape,

grabs all of that data with these functions.

If we can switch back to Excel now please.

Thank you. Let's do this.

This is how easy it is.

In an API response, do you remember I showed you

these little data points here?

They're called JSON response fields.

That's like what the data is called.

With SEOmoz, the response fields,

you have to learn them.

Let's do something like =SEOmoz_url metrics.

Let's find our function.

Let's say we want to learn about the link metrics for SEOmoz.

There you go.

That's kind of like the same as the SEOmoz toolbar output,

but it appears straightaway, and you can see what I've done here.

This UT and UU and UEID,

they're the JSON response fields

in the SEOmoz API output,

and what I've done is I've worked through each one

and worked out what they are

from a list of what we call response fields.

And if you go to Google and Google SEOmoz bit flags

there is a whole list of these so that bit is really—

oh, guys.

Who is spamming Google? It's not me.

That sucks.

Thank you.

Check this out. Go here.

Request response for format documentation.

And if you scroll down, each of the responses that you get

from the SEOmoz URL Metrics API

ties into a response field.

You know what data it is.

If we switch back to Excel, here we go.

The title is SEO Software. It's SEOmoz.

I've got that many external links,

this much Moz rank and so on and so forth.

But you can do this for a row as well, so it's not just one URL at a time.

It's super efficient, actually, because instead of making a single API call

for each URL it actually groups all of the responses, the requests, together.

It's really friendly on the SEOmoz API.

If I do this, there you go.

You can really build a quick data set with this,

and you can integrate this into a table of links.

It will factor all this data in and populate it into a huge table.

That's the first thing.

The next thing that you have to understand with these APIs

are what are called bit flags.

You see this Cols = x, y, and z?

It's a whole big number. I don't even know what that number is.

Well, what that's doing is we can describe what data

we actually want to fetch from the API.

Here's my list of response fields again.

If I asked SEOmoz just for the title and URL

in the request URI, then that's all the data I'd get.

That very briefly would look a little bit like this,

=SEOmoz, URL metrics to fit.

Let's get SEOmoz.

If we did this, look, cols = 5

then you see how I only get 2 data points?

That's being super polite to the API.

We're only asking for the information that we actually want,

and all we have to do is add up those bit flag values,

and you can tell it what information you want that way.

Yesterday Will was talking about how you should look at the distribution of links

across all the pages on your domain.

Well, we can do that with this.

There you go.

This is the 100 top linked-to pages on seogadget.co.uk.

That cols number there is the sum total of all the bit flags

that you can request in the free API.

I think the full number adds up to 13 something something something.

But there is something else really important happening here,

and this has been a big problem for us.

When we've been interacting with the SEOmoz API in the past

we've been asking for 1 URL at a time, which isn't polite at all.

What we're now doing is we're asking for batches of data,

which is why it's so much quicker, because we're getting it from one single API call.

That 100 there, fetch me 100 results in batches of 20 requests,

which is really cool.

It gets even cooler than that.

This is the Links API.

Now, the Links API is basically Open Site Explorer.

Let me explain this.

The Links API has a couple of different arguments.

You've got the bit flag thing that I've been talking about,

but you also have a thing called Scope.

Scope, which is this first argument,

page to page, just there,

Scope is a way of describing what type of links do you want to see?

Page to page is show me linking pages

that link to my page, but it could be page to domain,

which would be show me linking pages that link to my domain,

or domain to domain, which is really powerful.

Show me one link from each domain that's linking to my domain.

That means that you're not getting loads of duplicate data.

You're just seeing the best link from each domain linking to you.

Then we have sort.

Sort by page authority in this case.

Show me this linking data in order of the highest page authority.

You can sort by domain authority and a whole bunch of other stuff too.

Target cols.

Give me link data about the target,

the page that I'm actually linking to.

Source cols is the bit flag, URL metrics bit flag,

so this is the information I want about all of the pages

linking to my site, and then link cols is

give me information about the link itself.

Link cols = 8 is normalized anchor text.

That's just SEOmoz's opinion of the anchor text that people are using to link to me.

Now, if I grab this query,

I should be able to get 100 results.

Let's give this a go.

Yeah, that was quick, wasn't it?

[applause]

I'm really excited about this, because it's going to save me so much time.

It's cool. I've been really enjoying playing with this.

I'll show you why I've been really enjoying playing with this.

I'll give you 1 more query, and then we'll play with some tools.

This is the Anchor Text API.

It does exactly the same thing. There you go.

There is the top—so what this is actually doing

is it's showing me the most popular terms or phrases

linking to my domain in order of the number of sub domains

linking with that anchor text.

This is really interesting, actually,

when you look at this data, because if you look at

external pages linking here and compare that

to the number of external sub domains linking—

actually, I think I've got my formatting wrong.

That's actually supposed to be sub domains, not this one here.

So 8369 versus 7747.

You can spot really heavy site-wide links this way,

because if your external pages are really high,

but the actual number of domains linking to you

with the anchor text is really low, you should definitely go and work out

what's going on there, because that might be causing you some problems.

Let me show you this,

because this is a platform that you build stuff on.

You've got a whole bunch of different queries.

There is no set way that you actually have to perform your link analysis.

You can grab whatever data in whatever format you want,

and you can make really interesting things like this.

This is for you to download, by the way,

and you can also get this template from our site,

and it should be live on seogadget.co.uk/mozscape.

Let's have a look at the top pages linking to www.seomoz.org.

The formatting is a bit wacky, so you won't be able to see this

as well as I can.

It's done it already.

Let's get 100 results.

A little bit slower.

There you go.

And let's maybe sort by internal links.

I think we can do that.

Right, so the way this actually works is this.

This is the dev version of Open Site Explorer,

and all I'm doing is here is that great big long query string

that we've been playing with along the top.

All I'm doing is I'm making some of the values, the variable,

in the query.

The scope, page to page or page to domain,

is now using what we call data validation to change the values.

Here is G4, which is our scope,

and here is the G4 value, which is page to page,

but I've created a drop down, so I can change those things.

That should probably work. Yeah.

And the way I'm doing that is with what's called data validation.

If you look in here, it's actually looking for a list of different variables

in a different table, which I think it's hidden over here.

Yeah, here it is, so it's fetching the different values for the drop downs

from these 3 tables.

Yeah, pretty exciting.

[female speaker] Are you going to share that worksheet?

Yes, Dana, you can download this stuff.

I'm not going to make you build this from scratch.

But what I want you to do is I want you to show me what you can build,

because all of the instructions are on the site.

There is decent, interesting stuff.

Yesterday Matt was talking about

different kinds of links that might be kind of spammy,

and he said that MozTrust was a really good example

of how to weed out slightly sketchy looking links.

I haven't actually done this yet, so I'm going to work it out as I go along.

I think that what I need to do is find the bit flag

for MozTrust, which is going to be in here somewhere.

There it is.

Can somebody remember 132096 for me?

I want to find out how spammy my back link profile is.

According to Matt's research, he was saying that MozTrust

correlates very well with bad quality links.

The way I could do this now is go = SEOmoz url metrics,

which is down here.

I'm going to grab this whole list of URLs like that.

There does look to be some spammy links in there.

This is SEOgadget back link profile.

I've been doing some weird experiments with it.

Let's find out.

Cols = 196—what? It was what?

[audience] 132096.

Could one person say that? You.

[male speaker] 132096.

Thank you so much. My memory is terrible, isn't it?

132096.

Okay.

Cool, so that's how to do that.

MozTrust is in there somewhere.

I think if you go back and check the response fields.

UTRP and UTRR, yeah.

This is the only bit about working with APIs.

The response fields take a little while to get used to.

Yeah, there you go. That's our MozTrust.

And then you could do things like sort by lowest MozTrust

to weed out your worst links,

which is pretty exciting.

And then I was just playing with one thing,

and I'll probably switch back to the presentation after I've done this

to leave it running, but think about using APIs

for link outreach.

I built this with the SEOmoz API and our API.

What it does is it fetches all of the contact details

for your competitors' best links.

Now, Distilled.

[laughter]

This takes a little while, because there is a lot of heavy lifting going on in there.

But it's doing a couple of things.

It's making a call to the Links API

at Open Site Explorer, and it's saying, "Send me a list

of the 10 best links pointing to Distilled.net."

And then for each one of those URLs,

Excel has been going after our API that you can see

I've put enter your API key in there,

and it's making our API go off and fetch all of the contact details, the Who Is—

what else is in there?

LinkedIn, Twitter, blah, blah, blah.

I need to work on this a little bit.

It's really alpha. It's a bit slow.

But we'll come back to this as it's running.

It will jump up.

It's a little bit quicker than that.

Guys, can you switch me back to the presentation quickly?

We looked at, very briefly, how we can build things

like Open Site Explorer in Excel.

We're just using basic data validation now,

lumped queries and some text entry,

which I'm going to be hanging around at the conference for the next 2 days

to show you, so just tap me, and I'll probably grab a table

and show you guys some stuff if you want to see that.

We can find high volume, low competition keywords.

I'm going to show you this working in a bit, but check this out.

Basically if you put—oh, I've got to get all the way over there.

You see that list of keywords on the left?

If you put in a keyword, then it will fetch the first 3 ranking results,

ranking URLs by position.

It uses our Adwords API Extension to get the search volume for that keyword,

and then what it's doing is it's talking to the Links API

to fetch the number of linking root domains

that each one of those ranking URLs has,

and then it averages the number of linking root domains

that each one of those URLs has

to get average root domain links to sub domain

and average root domain links to page,

and then we've got an exact match domain

detector as well, so it's actually taken the keyword,

replacing all the spaces, and looking for that keyword

in the bit between the sub domain and the top level domain

in the link, so you know

if you're going for a keyword whether or not you're competing

with an exact match domain and roughly

how many root domain links you're going to have to build

to the page in the domain to have a bit of a chance at getting it to rank.

I'll show you that in a second.

This is how it works.

I know you can't see that epic query along the top,

but that's the exact match domain detector.

It's really epic, so if you want a copy of that

rather than having to work it out yourself, give me a shout.

And then finally, steal the contact details of your competitors' best links,

and if you can switch back to Excel for me, guys.

I broke it.

It sometimes works. I'll release this when I'm happy.

It's the ideas that count.

That's pretty exciting stuff.

What I have been doing, I'll show you something I'm developing at the moment.

I'm not sure if we've got enough time.

But this query here

is a Google search query to show me all Google+ profiles

that contain writer or blogger

and food, so food bloggers.

Now, in Excel, what you can do—have we got time, Laura?

I think we've got a little bit of time.

The thing that I've been working on, and I haven't quite finished it yet,

is what if we could get all of the contact URLs that are contained

on a Google+ page straight into our spreadsheet?

I'm going to give this a try.

Basically there is a function in Excel that's called XPath on URL.

In Google Docs it's called Import XML,

which uses XPath to find data from web pages.

In this case, the XPath that we're looking for

is this class here, which will give us the ranking URL

in each one of Google's search results for that query.

Now, if we just—I don't know.

Let's go for 20 Google+ profiles.

I'll press F2 to make that an array.

Let's see if I can—I'm building this as I go,

so I don't know if I'm going to be able to make it work in time,

but I'd like to give it a try, because I think it's fun.

We'll maybe come back to that later.

Forget about it.

Let me show you 1 more thing, and I'll get out of your hair.

The competitiveness tool.

Let's do full screen.

Let's see, we're interested in SEO tools.

What it should be doing is fetching those first 3 ranking URLs.

Please do that now. There you go.

What's it done is it's fetched those first 3 rankings.

It's already gone and got the average root domain.

It's the sub domain.

There is an exact match domain in there in the top 3, so I probably

want to avoid that like the plague

unless you're feeling really brave or you've got a super powerful domain.

And here it is in the chart, so you can see that even though the average—

well, the average search volume is pretty low.

The number of linking root domains

you need to rank well for SEO tools is pretty high.

I'd avoid that keyword and start looking for stuff

that's the opposite of that.

Anyway, if I can switch back to the presentation,

I'm going to leave you guys alone.

Marketers, now you can rapid prototype

applications on your own terms.

We don't have to be developers to work with APIs.

It's really easy to do. It's easy to do in Excel.

It's easy to do in Google Docs.

You can build stuff that you want to describe to your development teams

to make a reality, which I'm really excited about,

and before I close, just one thing.

We're making this tool free and for public use.

You can do whatever you'd like with it.

All I ask is that if you do actually use it,

we support a UK charity called Starlight, which is a children's foundation.

You can find out more about them on our site,

and if you're willing to make a donation to help those kids out,

then I would be very, very grateful for that.

Thank you so much for listening.

[applause]