[Link Analytics Through APIs] [Richard Baxter] [CEO & Senior Search Marketing Strategist] 0:00 [SEOgadget] [SEOgadget.co.uk/mozscape] [@richardbaxter] How are we all? 0:02 Wasn't yesterday awesome? 0:04 Can we have a round of applause for the speakers and the SEOmoz team from yesterday? 0:06 Thank you very much. [applause] 0:09 Those of you who have never heard me speak at a conference before 0:14 you must have worked out by now that I'm here from England. 0:18 If you've never visited England before, 0:21 it's a pretty easy place to work out. 0:24 It's a country where policemen chase criminals with sticks. 0:26 Everyone likes a nice cup of tea, 0:30 and Casey loves it when I say, "Shine your shoes, guvnor?" 0:32 So anyway, we're here to talk today about data. 0:39 I love data. I love working with data. 0:46 It helps me—hey, AV team, we need the presentation up. 0:49 This is the thing I'm going to show them in a minute. Thank you. 0:54 That was the thing I was going to show you in a minute. 0:57 Anyway, so I love working with data. 0:59 It makes me smarter as a marketer, 1:03 and it helps me lead my clients to make the right decisions. 1:06 There is a problem, 1:12 and the problem is time. 1:14 Think about it. 1:16 How much time do you guys spend waiting for CSV files to download? 1:18 How much time are you putting in 1:21 collecting, fetching, collating data, 1:24 putting it in an Excel spreadsheet? 1:26 Imagine that you spend half an hour 1:29 a week just doing that one thing. 1:31 Half an hour a week is 2 hours a month. 1:34 Two hours a month is 24 hours a year, 1:36 and then imagine that you've got an SEO team 1:41 of 20 people. 1:44 That's like $300,000 worth of billable SEO consulting time 1:46 that we're wasting. 1:50 I'm going to help you guys solve this problem. 1:53 We're going to solve that problem with some tools I'm going to give you today, 2:04 and with those tools, we're going to build some awesome shit. 2:06 This, my friends, is a working version of Open Site Explorer in Excel. 2:12 This will tell you keywords with high volume, 2:20 low competition, and whether or not there is an exact match to name 2:26 in the top 3 ranking search results for that keyword 2:29 and the search volume. 2:33 I'll show you this stuff later. 2:36 And this will tell you who is linking to your site and what anchor text they're using. 2:39 I'm pretty excited to show you this stuff. 2:45 First we're going to start with some basics. 2:50 We're here to talk about link analytics using APIs. 2:52 Most of the people in the room aren't developers. 2:55 I'm not a developer. Will Reynolds was talking about this yesterday. 2:59 You don't have to be a developer to interact with APIs. 3:01 It's super exciting that you don't have to be one of these people. 3:05 Let me show you this. 3:09 This is an API called Shared Count. 3:12 I was at dinner with Dan Shore last night, and he said, 3:15 "What's a really good way to get into using APIs 3:18 if I've never interacted with an API before?" 3:21 This is it. 3:25 What we have here is JSON. 3:27 It's a form of text output. 3:29 When I go to this URL, api.sharedcount.com?url=/ 3:33 whatever URL you're interested in, the data that you get back 3:39 is actually social data. 3:42 You've got Stumbles, you've got Reddits, you've got Facebook activity. 3:45 It's all in there. 3:48 That's pretty exciting. 3:51 We have a tool called Link Cleanup and Contact. 3:56 You can paste in all of your URLs, and it will give you the contact details 3:59 for each one of those links, so if you're trying to outreach 4:04 to sites or if you're trying to clean up bad links 4:06 you can get the contact details for that. 4:08 This has an API too. 4:11 In fact, this is the first time anybody has seen this API. 4:13 This is free for you guys to use, and I'm going to show you how to use it. 4:16 Go to tools.seogadget.co.uk/use_api, 4:19 and look, actually, some of the stuff in that data 4:23 looks pretty familiar. 4:25 You've got Google page rank from the domain. 4:27 You've got Who Is details. 4:29 You've got the contact URL. 4:31 There is a lot of really interesting and useful data you can use 4:33 to be better at SEO, analyze your links faster and all of that stuff. 4:35 I'm going to switch to Excel, and I want to show you guys 4:40 how to start building these types of tools. 4:45 AV, if you can switch to Excel for me. 4:48 Thank you. 4:53 Like I said, APIs are really a source of information, 4:56 and there is no point having to go off to a website 4:59 and trying to download CSVs and integrating them 5:04 back into your spreadsheets if you don't have to do that. 5:07 I'm going to show you some toys. 5:09 Now, this is an API called Whip Mania, 5:11 and I like Whip Mania because when you give it an IP address, 5:14 it tells you where that IP address is. 5:17 If you're analyzing your back links and trying to work out 5:19 where all of your in links are coming from, 5:21 this is the tool for you, and I'll show you how it works. 5:23 You form a URL like this. 5:27 First of all, we need to get an IP address for it. 5:29 I'm using SEO Tools for Excel, 5:31 but there are lots of other ways to do this. 5:33 You can do this kind of thing with Google Docs too. 5:35 If I go = resolveip seomoz.org. 5:37 Whoops. I'm glad the Internet is working. 5:43 There we go, 5:48 so www.seomoz.org. 5:50 Okay, http. 5:57 Sorry. I'm going to start again. 6:04 Live demos. Aren't they awesome? 6:07 Okay, =resolveip. 6:15 You wouldn't believe how many times I did this when I was practicing 6:17 this presentation, and now it works. 6:20 [applause] 6:23 Right, so data into Excel. 6:25 It doesn't stop there. Don't clap yet, because this is the easy bit. 6:27 Right, so let's make a URL to form a valid API call in Excel. 6:31 We do that with our friend concatenate. 6:35 We're going to concatenate cell A1. 6:39 Look, sorry, A2. That's where our Whip Mania URL is. 6:43 And that IP address there. 6:46 Right, now watch this. We've made a URL. 6:52 We're going to copy that. 6:54 I'm going to show you how easy it is to interact with APIs. 6:56 Just paste an API URL into a browser and see what data you get. 7:00 We have US, which is pretty cool. 7:03 How do we get that into Excel? 7:10 This is the first and the easiest way for you to do that. 7:13 If we tell Excel to download that string there it is. 7:15 Think about that. 7:25 You could have a list of links and where those links are all hosted 7:27 just using that methodology, but it gets slightly more interesting 7:30 when you realize that JSON API output I showed you in the presentation— 7:34 let me open that—has loads of data points. 7:40 I love it when it's not my machine that I'm doing this stuff on. 7:48 Hang on. Here we go. 7:51 Check this out. 7:56 We've got Stumbles, Reddits, Facebooks, 7:58 Facebook shares. 8:01 Let's say that I wanted to get maybe the total number of shares 8:03 on LinkedIn that I've got for a link 8:06 and the number of tweets that I've got for a link 8:08 via the API rather than any other method. 8:11 Let me introduce you to a function called JSON Path on URL, 8:14 because we're working in JSON. 8:18 JSON Path in URL is called the JSON Parser. 8:20 It can look for what's called a response field, 8:23 like the words "stumble upon," and then fetch that data and put it into a cell. 8:25 The way we do that is like this. 8:29 JSON Path on URL. 8:33 I think that's cell A8 comma, and then we tell it what we're interested in. 8:38 We're interested in LinkedIn shares, 8:45 which is LinkedIn like that. 8:47 It's case sensitive. Let me just check. 8:56 Capital L, capital I. That will be it. 8:59 If I can do that, you can do it. 9:07 Now we can build a data set with our social share data 9:10 just from an API call, which is pretty exciting stuff. 9:14 [cheer] But don't cheer yet. 9:19 Thank you, but don't cheer yet, because it gets more exciting. 9:21 Let's imagine that you've got a list of links 9:25 in your spreadsheet, and you want to know 9:28 the email address in the Who Is record, 9:31 and you want to know the page rank of the domain maybe, 9:34 and you want the contact URL for that site as well 9:38 and maybe the Twitter URL if we've got time. 9:42 Let's get a bit more complex. 9:45 I'm going to introduce you to a feature called HTTP Downloader. 9:47 Very briefly, HTTP Downloader configures Excel 9:51 as a browser, so I can turn caching on and off, 9:54 and I can delay requests, that kind of stuff, 9:56 so the 1,000 is 1,000 milliseconds, 9:58 which equates to 1 call per second. 10:00 What we've got to do is form a valid request URL with our API. 10:07 That would look a little bit like this, 10:11 = concatenate. 10:13 Now we need to take tools.seogadget.co.uk/api. 10:17 We're going to anchor that with F4 comma, this URL here. 10:20 This is one of Gianluca's blog posts, 10:25 and our API key. 10:28 Try not to remember that API key, because that's my API key, 10:30 and you can't have my API key, 10:32 and we haven't built the functionality that changes API keys yet either. 10:34 Here we go. Let's test that in the browser, because that's how we do it. 10:39 Cool, there is our data. 10:46 What were we interested in first? 10:48 The email. 10:50 We'll check the name of the email data. 10:52 You'd think I'd know my own API better than—oh, email. 10:54 Okay, that's pretty self-explanatory. 10:58 So, =JSON path on URL. 11:00 That URL there, comma, and we need the email, 11:07 and we're also going to fetch HTTP Downloader. 11:13 Okay, I screwed that up again. 11:19 Oh, so US keyboards have the @ symbol in a different place. 11:22 That's all. 11:28 That's why I was screwing that up. 11:30 Now let me fix this. 11:34 I think I need to put $ and a dot dot. 11:36 There you go. 11:40 Emails addresses from Who Is into Excel from a list of links. 11:42 Okay, it gets more interesting than that. 11:47 Let's grab, I don't know, page rank or something like that as well. 11:49 Let's go for google_domain_pagerank. 11:54 Now we can copy our formula and paste that in like that, 11:58 and we'll change email to google_google domain page rank, wasn't it? 12:02 There you go. 12:17 Think about the power of that. 12:20 You didn't have to download a thing to make that work, 12:22 and I did it, and I'm really not that smart. 12:26 But it gets more interesting than this. 12:34 AV, could we switch back to the presentation quickly? 12:37 That's cool, right? 12:45 Thank you. [applause] 12:47 What was the thing that I showed you 12:55 at the beginning of the presentation? 12:57 The cool thing was the SEOGadget Links API Extension for Excel. 12:59 This is super exciting. 13:07 I'm going to switch back to Excel and show you this 13:11 rather than bang on about what it actually does. 13:13 Thank you. 13:17 Okay, so the problem that we've been having 13:19 with working with lots of link tools and not just open sites 13:24 or the Mozscape API is that we're always waiting 13:26 for absolutely ages to get our data, and then we have to 13:29 integrate it into Excel, consolidate it, merge it. 13:32 I want to spend my time analyzing. 13:35 How should we do this? 13:44 Everybody knows about the SEOmoz URL Metrics API, right? 13:46 Actually, guys, can you switch back to the presentation? 13:50 I want to show you one thing first before we do this. 13:52 This is important, actually, 13:57 because the Mozscape API is awesome. 14:00 There are 4 key APIs. I'll show you this first. 14:02 Then the Excel bit will make a lot more sense. 14:04 There are 4 key APIs in the Mozscape API. 14:06 We've all been using URL metrics, which is the thing that spits out 14:09 page authority, domain authority, 14:13 total linking root domains to the sub domain if you're a Pro member, 14:15 all of that stuff, so that's the one that we all know. 14:18 It's the one that all of the Google dot hacks work with. 14:21 But there are 3 more, and they are super exciting. 14:24 Top pages. 14:26 You know the report in Open Site Explorer that tells you your most linked to 14:28 pages on your site, top pages on domain? 14:30 It's the Top Pages API. 14:33 It returns a list of URLs in order of the number of linking root domains 14:35 to each URL. 14:37 Anchor Text API, you know what the Anchor Text API does. 14:39 It tells you how people are linking to you and with what anchor text. 14:42 And then the Links API is super powerful. 14:46 The Links API spits out links, 14:48 so you give it a URL, and it tells you who is linking to you. 14:51 That's the thing I'm going to show you next. 14:53 I'm super excited about this. 14:55 Our tool, links extension for Mozscape, 14:57 grabs all of that data with these functions. 15:01 If we can switch back to Excel now please. 15:05 Thank you. Let's do this. 15:07 This is how easy it is. 15:10 In an API response, do you remember I showed you 15:12 these little data points here? 15:16 They're called JSON response fields. 15:18 That's like what the data is called. 15:21 With SEOmoz, the response fields, 15:25 you have to learn them. 15:28 Let's do something like =SEOmoz_url metrics. 15:31 Let's find our function. 15:36 Let's say we want to learn about the link metrics for SEOmoz. 15:41 There you go. 15:46 That's kind of like the same as the SEOmoz toolbar output, 15:48 but it appears straightaway, and you can see what I've done here. 15:52 This UT and UU and UEID, 15:55 they're the JSON response fields 15:58 in the SEOmoz API output, 16:00 and what I've done is I've worked through each one 16:02 and worked out what they are 16:04 from a list of what we call response fields. 16:06 And if you go to Google and Google SEOmoz bit flags 16:09 there is a whole list of these so that bit is really— 16:15 oh, guys. 16:17 Who is spamming Google? It's not me. 16:21 That sucks. 16:27 Thank you. 16:31 Check this out. Go here. 16:33 Request response for format documentation. 16:36 And if you scroll down, each of the responses that you get 16:39 from the SEOmoz URL Metrics API 16:42 ties into a response field. 16:44 You know what data it is. 16:47 If we switch back to Excel, here we go. 16:49 The title is SEO Software. It's SEOmoz. 16:53 I've got that many external links, 16:58 this much Moz rank and so on and so forth. 17:00 But you can do this for a row as well, so it's not just one URL at a time. 17:03 It's super efficient, actually, because instead of making a single API call 17:08 for each URL it actually groups all of the responses, the requests, together. 17:14 It's really friendly on the SEOmoz API. 17:20 If I do this, there you go. 17:30 You can really build a quick data set with this, 17:35 and you can integrate this into a table of links. 17:38 It will factor all this data in and populate it into a huge table. 17:41 That's the first thing. 17:45 The next thing that you have to understand with these APIs 17:47 are what are called bit flags. 17:49 You see this Cols = x, y, and z? 17:52 It's a whole big number. I don't even know what that number is. 17:55 Well, what that's doing is we can describe what data 17:58 we actually want to fetch from the API. 18:03 Here's my list of response fields again. 18:05 If I asked SEOmoz just for the title and URL 18:08 in the request URI, then that's all the data I'd get. 18:13 That very briefly would look a little bit like this, 18:16 =SEOmoz, URL metrics to fit. 18:19 Let's get SEOmoz. 18:25 If we did this, look, cols = 5 18:27 then you see how I only get 2 data points? 18:36 That's being super polite to the API. 18:40 We're only asking for the information that we actually want, 18:42 and all we have to do is add up those bit flag values, 18:44 and you can tell it what information you want that way. 18:46 Yesterday Will was talking about how you should look at the distribution of links 18:50 across all the pages on your domain. 18:56 Well, we can do that with this. 18:58 There you go. 19:13 This is the 100 top linked-to pages on seogadget.co.uk. 19:15 That cols number there is the sum total of all the bit flags 19:23 that you can request in the free API. 19:27 I think the full number adds up to 13 something something something. 19:30 But there is something else really important happening here, 19:34 and this has been a big problem for us. 19:37 When we've been interacting with the SEOmoz API in the past 19:39 we've been asking for 1 URL at a time, which isn't polite at all. 19:42 What we're now doing is we're asking for batches of data, 19:45 which is why it's so much quicker, because we're getting it from one single API call. 19:49 That 100 there, fetch me 100 results in batches of 20 requests, 19:52 which is really cool. 19:59 It gets even cooler than that. 20:03 This is the Links API. 20:05 Now, the Links API is basically Open Site Explorer. 20:07 Let me explain this. 20:11 The Links API has a couple of different arguments. 20:14 You've got the bit flag thing that I've been talking about, 20:17 but you also have a thing called Scope. 20:19 Scope, which is this first argument, 20:22 page to page, just there, 20:26 Scope is a way of describing what type of links do you want to see? 20:28 Page to page is show me linking pages 20:32 that link to my page, but it could be page to domain, 20:35 which would be show me linking pages that link to my domain, 20:37 or domain to domain, which is really powerful. 20:41 Show me one link from each domain that's linking to my domain. 20:43 That means that you're not getting loads of duplicate data. 20:47 You're just seeing the best link from each domain linking to you. 20:49 Then we have sort. 20:53 Sort by page authority in this case. 20:55 Show me this linking data in order of the highest page authority. 20:59 You can sort by domain authority and a whole bunch of other stuff too. 21:04 Target cols. 21:07 Give me link data about the target, 21:09 the page that I'm actually linking to. 21:12 Source cols is the bit flag, URL metrics bit flag, 21:15 so this is the information I want about all of the pages 21:18 linking to my site, and then link cols is 21:21 give me information about the link itself. 21:24 Link cols = 8 is normalized anchor text. 21:26 That's just SEOmoz's opinion of the anchor text that people are using to link to me. 21:29 Now, if I grab this query, 21:34 I should be able to get 100 results. 21:37 Let's give this a go. 21:43 Yeah, that was quick, wasn't it? 21:45 [applause] 21:48 I'm really excited about this, because it's going to save me so much time. 21:56 It's cool. I've been really enjoying playing with this. 21:59 I'll show you why I've been really enjoying playing with this. 22:02 I'll give you 1 more query, and then we'll play with some tools. 22:05 This is the Anchor Text API. 22:09 It does exactly the same thing. There you go. 22:11 There is the top—so what this is actually doing 22:13 is it's showing me the most popular terms or phrases 22:17 linking to my domain in order of the number of sub domains 22:20 linking with that anchor text. 22:24 This is really interesting, actually, 22:26 when you look at this data, because if you look at 22:28 external pages linking here and compare that 22:31 to the number of external sub domains linking— 22:35 actually, I think I've got my formatting wrong. 22:39 That's actually supposed to be sub domains, not this one here. 22:42 So 8369 versus 7747. 22:45 You can spot really heavy site-wide links this way, 22:48 because if your external pages are really high, 22:51 but the actual number of domains linking to you 22:54 with the anchor text is really low, you should definitely go and work out 22:57 what's going on there, because that might be causing you some problems. 22:59 Let me show you this, 23:05 because this is a platform that you build stuff on. 23:07 You've got a whole bunch of different queries. 23:14 There is no set way that you actually have to perform your link analysis. 23:16 You can grab whatever data in whatever format you want, 23:19 and you can make really interesting things like this. 23:23 This is for you to download, by the way, 23:25 and you can also get this template from our site, 23:28 and it should be live on seogadget.co.uk/mozscape. 23:30 Let's have a look at the top pages linking to www.seomoz.org. 23:35 The formatting is a bit wacky, so you won't be able to see this 23:42 as well as I can. 23:44 It's done it already. 23:48 Let's get 100 results. 23:50 A little bit slower. 23:55 There you go. 23:57 And let's maybe sort by internal links. 23:59 I think we can do that. 24:04 Right, so the way this actually works is this. 24:08 This is the dev version of Open Site Explorer, 24:12 and all I'm doing is here is that great big long query string 24:14 that we've been playing with along the top. 24:19 All I'm doing is I'm making some of the values, the variable, 24:21 in the query. 24:26 The scope, page to page or page to domain, 24:28 is now using what we call data validation to change the values. 24:32 Here is G4, which is our scope, 24:36 and here is the G4 value, which is page to page, 24:39 but I've created a drop down, so I can change those things. 24:42 That should probably work. Yeah. 24:47 And the way I'm doing that is with what's called data validation. 24:49 If you look in here, it's actually looking for a list of different variables 24:53 in a different table, which I think it's hidden over here. 24:58 Yeah, here it is, so it's fetching the different values for the drop downs 25:02 from these 3 tables. 25:06 Yeah, pretty exciting. 25:11 [female speaker] Are you going to share that worksheet? 25:14 Yes, Dana, you can download this stuff. 25:16 I'm not going to make you build this from scratch. 25:21 But what I want you to do is I want you to show me what you can build, 25:23 because all of the instructions are on the site. 25:25 There is decent, interesting stuff. 25:30 Yesterday Matt was talking about 25:33 different kinds of links that might be kind of spammy, 25:40 and he said that MozTrust was a really good example 25:46 of how to weed out slightly sketchy looking links. 25:50 I haven't actually done this yet, so I'm going to work it out as I go along. 25:53 I think that what I need to do is find the bit flag 25:57 for MozTrust, which is going to be in here somewhere. 26:00 There it is. 26:04 Can somebody remember 132096 for me? 26:06 I want to find out how spammy my back link profile is. 26:10 According to Matt's research, he was saying that MozTrust 26:13 correlates very well with bad quality links. 26:16 The way I could do this now is go = SEOmoz url metrics, 26:20 which is down here. 26:25 I'm going to grab this whole list of URLs like that. 26:30 There does look to be some spammy links in there. 26:34 This is SEOgadget back link profile. 26:37 I've been doing some weird experiments with it. 26:39 Let's find out. 26:44 Cols = 196—what? It was what? 26:46 [audience] 132096. 26:53 Could one person say that? You. 26:56 [male speaker] 132096. 26:58 Thank you so much. My memory is terrible, isn't it? 27:00 132096. 27:03 Okay. 27:06 Cool, so that's how to do that. 27:11 MozTrust is in there somewhere. 27:15 I think if you go back and check the response fields. 27:17 UTRP and UTRR, yeah. 27:23 This is the only bit about working with APIs. 27:28 The response fields take a little while to get used to. 27:30 Yeah, there you go. That's our MozTrust. 27:32 And then you could do things like sort by lowest MozTrust 27:35 to weed out your worst links, 27:39 which is pretty exciting. 27:42 And then I was just playing with one thing, 27:45 and I'll probably switch back to the presentation after I've done this 27:49 to leave it running, but think about using APIs 27:54 for link outreach. 27:58 I built this with the SEOmoz API and our API. 28:02 What it does is it fetches all of the contact details 28:07 for your competitors' best links. 28:10 Now, Distilled. 28:16 [laughter] 28:21 This takes a little while, because there is a lot of heavy lifting going on in there. 28:25 But it's doing a couple of things. 28:30 It's making a call to the Links API 28:32 at Open Site Explorer, and it's saying, "Send me a list 28:35 of the 10 best links pointing to Distilled.net." 28:38 And then for each one of those URLs, 28:43 Excel has been going after our API that you can see 28:46 I've put enter your API key in there, 28:50 and it's making our API go off and fetch all of the contact details, the Who Is— 28:52 what else is in there? 28:56 LinkedIn, Twitter, blah, blah, blah. 28:58 I need to work on this a little bit. 29:00 It's really alpha. It's a bit slow. 29:02 But we'll come back to this as it's running. 29:05 It will jump up. 29:09 It's a little bit quicker than that. 29:12 Guys, can you switch me back to the presentation quickly? 29:14 We looked at, very briefly, how we can build things 29:19 like Open Site Explorer in Excel. 29:23 We're just using basic data validation now, 29:26 lumped queries and some text entry, 29:30 which I'm going to be hanging around at the conference for the next 2 days 29:32 to show you, so just tap me, and I'll probably grab a table 29:35 and show you guys some stuff if you want to see that. 29:38 We can find high volume, low competition keywords. 29:42 I'm going to show you this working in a bit, but check this out. 29:48 Basically if you put—oh, I've got to get all the way over there. 29:51 You see that list of keywords on the left? 29:56 If you put in a keyword, then it will fetch the first 3 ranking results, 29:58 ranking URLs by position. 30:02 It uses our Adwords API Extension to get the search volume for that keyword, 30:04 and then what it's doing is it's talking to the Links API 30:08 to fetch the number of linking root domains 30:10 that each one of those ranking URLs has, 30:13 and then it averages the number of linking root domains 30:16 that each one of those URLs has 30:19 to get average root domain links to sub domain 30:21 and average root domain links to page, 30:24 and then we've got an exact match domain 30:26 detector as well, so it's actually taken the keyword, 30:28 replacing all the spaces, and looking for that keyword 30:30 in the bit between the sub domain and the top level domain 30:33 in the link, so you know 30:36 if you're going for a keyword whether or not you're competing 30:39 with an exact match domain and roughly 30:42 how many root domain links you're going to have to build 30:44 to the page in the domain to have a bit of a chance at getting it to rank. 30:46 I'll show you that in a second. 30:53 This is how it works. 30:56 I know you can't see that epic query along the top, 30:58 but that's the exact match domain detector. 31:00 It's really epic, so if you want a copy of that 31:03 rather than having to work it out yourself, give me a shout. 31:05 And then finally, steal the contact details of your competitors' best links, 31:11 and if you can switch back to Excel for me, guys. 31:16 I broke it. 31:19 It sometimes works. I'll release this when I'm happy. 31:23 It's the ideas that count. 31:27 That's pretty exciting stuff. 31:32 What I have been doing, I'll show you something I'm developing at the moment. 31:34 I'm not sure if we've got enough time. 31:37 But this query here 31:39 is a Google search query to show me all Google+ profiles 31:42 that contain writer or blogger 31:50 and food, so food bloggers. 31:52 Now, in Excel, what you can do—have we got time, Laura? 31:58 I think we've got a little bit of time. 32:01 The thing that I've been working on, and I haven't quite finished it yet, 32:03 is what if we could get all of the contact URLs that are contained 32:05 on a Google+ page straight into our spreadsheet? 32:12 I'm going to give this a try. 32:16 Basically there is a function in Excel that's called XPath on URL. 32:19 In Google Docs it's called Import XML, 32:23 which uses XPath to find data from web pages. 32:26 In this case, the XPath that we're looking for 32:29 is this class here, which will give us the ranking URL 32:31 in each one of Google's search results for that query. 32:35 Now, if we just—I don't know. 32:38 Let's go for 20 Google+ profiles. 32:42 I'll press F2 to make that an array. 32:45 Let's see if I can—I'm building this as I go, 32:53 so I don't know if I'm going to be able to make it work in time, 32:58 but I'd like to give it a try, because I think it's fun. 33:00 We'll maybe come back to that later. 33:08 Forget about it. 33:12 Let me show you 1 more thing, and I'll get out of your hair. 33:14 The competitiveness tool. 33:16 Let's do full screen. 33:18 Let's see, we're interested in SEO tools. 33:25 What it should be doing is fetching those first 3 ranking URLs. 33:31 Please do that now. There you go. 33:37 What's it done is it's fetched those first 3 rankings. 33:39 It's already gone and got the average root domain. 33:42 It's the sub domain. 33:44 There is an exact match domain in there in the top 3, so I probably 33:46 want to avoid that like the plague 33:48 unless you're feeling really brave or you've got a super powerful domain. 33:50 And here it is in the chart, so you can see that even though the average— 33:52 well, the average search volume is pretty low. 33:55 The number of linking root domains 33:59 you need to rank well for SEO tools is pretty high. 34:02 I'd avoid that keyword and start looking for stuff 34:05 that's the opposite of that. 34:07 Anyway, if I can switch back to the presentation, 34:09 I'm going to leave you guys alone. 34:11 Marketers, now you can rapid prototype 34:22 applications on your own terms. 34:26 We don't have to be developers to work with APIs. 34:29 It's really easy to do. It's easy to do in Excel. 34:31 It's easy to do in Google Docs. 34:33 You can build stuff that you want to describe to your development teams 34:37 to make a reality, which I'm really excited about, 34:40 and before I close, just one thing. 34:43 We're making this tool free and for public use. 34:47 You can do whatever you'd like with it. 34:50 All I ask is that if you do actually use it, 34:52 we support a UK charity called Starlight, which is a children's foundation. 34:55 You can find out more about them on our site, 34:58 and if you're willing to make a donation to help those kids out, 35:00 then I would be very, very grateful for that. 35:04 Thank you so much for listening. 35:06 [applause] 35:08