Mobile telcos charge us for the texts, minutes and megabytes we use. They buy our loyalty by heavily subsidising our increasingly expensive phones. And they’re terrified of becoming like the people who supply our electricity or gas. They’re terrified that one day they’ll be nothing but interchangeable providers of a commodity, irrelevant logos printed on tedious, humdrum bills.

This is why their marketing focuses so much on music, culture and lifestyle. It’s why O2 customers get priority tickets to concerts at the arenas bearing their name. It’s why Orange customers get half-price cinema tickets on Wednesdays. And it’s why T-Mobile runs that insufferable campaign about Josh and his ever-growing band.

Join the b(r)and: T-Mobile want to be associated with music and lifestyle

Customers are being encouraged to associate the brands of mobile operators with a certain type of lifestyle experience instead of just voice and data. This experience extends from the marketing to exclusive content services and even the interfaces and feature sets of the handsets themselves.

In this sense, mobile telcos are offering their customers a walled garden, in which the mobile internet is presented as part of a convenient package branded Orange, AT&T, T-Mobile or O2. If your internet memory goes back as far as the mid-1990s this might sound slightly familiar. But in the next ten years this walled garden is due to come under direct assault.

Charlie Stross has posted an excellent, thought-provoking piece looking at how the next ten years might pan out for the mobile industry – and making it sound in some ways like a technology rehash of the Great Game, with Apple and Google as the chief protagonists.

As Stross sees it, Apple and Google both want to destroy the walled garden built by telcos but for different reasons and in different ways. As a premium marque, Apple wants to work with telcos while preventing their brands from adulterating the Apple experience:

Apple don’t want to destroy the telcos; they just want to use them as a conduit to sell their user experience… [they] want to maintain the high quality Apple-centric user experience and sell stuff to their users through the walled garden of the App Store and the iTunes music/video store

Google, on the other hand, wants people to view more of its ads. To make this happen, Google wants to fundamentally reshape the mobile industry:

I think Google are pursuing a grand strategic vision of destroying the cellco’s entire business model… turning 3G data service into a commodity… getting consumers to buy unlocked SIM-free handsets [like the Nexus One]… and ultimately do the Google thing to all your voice messages [through Google Voice] as well as your email and web access.

These distinct strategies both threaten the mobile telcos, who stand to lose any emotional connection they have with their customers either way. But this doesn’t mean that Apple and Google are going to be bedfellows:

Apple’s iPhone has been good for Google: iPhone users do far more web surfing — and Google ad-eyeballing — than regular phone users. But Apple want to maintain…  the walled garden of the App Store and iTunes… [and] Google can’t slap their ads all over those media. So it’s going to end in handbags at dawn … eventually.

The piece (here’s the link again by the way) has me thinking that the coming decade in mobile networks will be much like the previous decade was in land-line internet service provision.

If Charlie Stross is right, the idea of the telco as provider of an experience will not last the decade, meaning that flash mobs, Orange Rock Corps and Josh Ward will become nothing but a dim and distant memory. And customers will hopefully have greater choice over how they use mobile networks, which would be nothing but a good thing in my opinion.

Update (5th December 2017): Several years ago, Twitter changed its API in a way that completely broke the process I describe below. I don’t know how you’d do the same thing today. It would probably help if you were some kind of white supremacist, going by where Twitter’s moral compass seems to be pointing.

Last weekend I was looking for ways to extract Twitter search data in a structured, easily manageable format. The two APIs I was using (Twitter Search and Backtweets) were giving good results – but as a non-developer I couldn’t do much with the raw data they returned. Instead, I needed to get the data into a format like CSV or XLS.

Some extensive googling led me to this extremely useful post on Labnol, where I learnt about how to use the ImportXML function in Google Spreadsheets. Before too long I’d cracked my problem. In this post I’m going to explain how you can do it too.

Data you can extract from Twitter

This walkthrough will teach you how to extract two types of Twitter data using Google Spreadsheets – tweets and links.

Tweets are extracted using the Twitter Search API in conjunction with ImportFeed. This allows Twitter search results to be extracted into a spreadsheet format.

Links are extracted using the Backtweets API in conjunction with ImportXML. The Backtweets API allows you to find any links posted on Twitter even if they’ve been shortened using services like bit.ly or tinyurl.

I’m in a hurry, can I just do this right now?

If you just want to do it – instead of learn how to do it – just open this Google spreadsheet I’ve created.  You’ll need to make your own local copy so you can edit it. Instructions can be found in the spreadsheet itself.

How to extract tweets containing links

The instructions below will help you create a Google Spreadsheet that pulls in and displays the time, username and text of all tweets containing links to a specified page. Because it uses Backtweets, these tweets will be retrieved even if they used shortened URLs from services like bit.ly or tinyurl.

1. Create a new spreadsheet in Google Documents.
2. Enter column labels in this order: “Search criteria”, “Timestamp”, “Username” and “Tweet text” in cells A1 to D1.
3. In cell B2, underneath Timestamp, insert the following formula:

=ImportXML("http://backtweets.com/search.xml?itemsperpage=100&since_id=1255588696&key=key&q="&A2,"//tweet_created_at")

4. In cell C2, underneath Username, insert the following formula:

   =ImportXML("http://backtweets.com/search.xml?itemsperpage=100&since_id=1255588696&key=key&q="&A2,"//tweet_from_user")

5. In cell D2, underneath Tweet Text, insert the following formula:

   =ImportXML("http://backtweets.com/search.xml?itemsperpage=100&since_id=1255588696&key=key&q="&A2,"//tweet_text")

6. Now paste a search query into cell A2 – say, http://www.google.com. After a few seconds, you should see columns B, C and D fill up with tweets, looking something like the image below:



7. The formulas pasted into cells B2, C2 and D2 all reference the URL in cell A2. This means that whenever you paste anything new into A2, the search results should refresh.
8. Also, you can paste parts of URLs into A2 – not just entire ones. This is useful for seeing all links to a specific directory on your site, for example.

Finally, this tool can only extract 100 results at a time – but it is possible to set it up to retrieve more than that. Look at my sample Google Spreadsheet if you want to do this.

Extracting tweets from Twitter search results

The method for doing this is identical to the above, but uses the ImportFeed function instead of ImportXML.

1. Create a new spreadsheet in Google Documents.
2. Enter column labels in this order: “Search criteria”, “Timestamp”, “Username” and “Tweet text”. For the rest of this walkthrough, I’m going to assume that these labels are in cells A1 to D1, but in reality you can put them wherever you like
3. In cell B2, underneath Timestamp, insert the following formula:

   =ImportFeed("http://search.twitter.com/search.atom?rpp=20&page=1&q="&A2, "items created")

4. In cell C2, underneath Username, insert the following formula:

   =ImportFeed("http://search.twitter.com/search.atom?rpp=20&page=1&q="&A2, "items author")

5. In cell D2, underneath Tweet Text, insert the following formula:

   =ImportFeed("http://search.twitter.com/search.atom?rpp=20&page=1&q="&A2, "items title")

6. Type a search query into cell A2 - say, "Hoth." Hit enter and the results will load. It should look something like this:
7. Things will go wrong if you insert characters like # or @ into the search query. To get around this, type %23 instead of # and %40 instead of @. This will allow you to search for hash tags and usernames.

I haven't been successful in generating more than 20 search results per request, but you can get around this using the page number parameter in the ImportFeed query string. See my own Google spreadsheet to find out how to do this.

I hope these instructions are useful - if you have any comments, questions or feedback, please let me know in the comments.

You might not have heard of Google Knol, the service Google launched in an attempt to eclipse Wikipedia as a world-accessible font of knowledge. In a post last year I included it in a list of reasons why Google might be thought to have jumped the shark, and asked in vain if anyone reading this blog actually used it.

The resulting silence probably said more about my obscurity than Google Knol’s, but it turns out that Google Knol is indeed floundering. As TechCrunch’s Erick Schonfield wrote yesterday:

[Knol’s] model doesn’t work so well if nobody bothers to read the articles… Quantcast estimates that only 174,000 people visited the site in the past month.

And Knol is seeking to counter this evident public apathy by repositioning itself as a competitor to Craigslist. But Schonfield doesn’t see this working out:

Google should just end its misery, just like it did when it killed other under-performing projects such as Lively and Google Notebooks. Knol will never come close to Wikipedia. It can’t even cut it as a classifieds listing site.

Ouch!

Before Christmas I suggested that Google may have reached its apex during 2008, especially as it had, for the first time, allowed a dubious new feature – SearchWiki – to infiltrate the product that sits at its core – search.

And over the weekend, Google spent an hour saying that every site in its index was potentially harmful. This was the result of human error – namely, someone listing a harmful site with the URL “/” and this being treated as a wild card across the whole index.

I don’t really subscribe to the view that this was an apocalyptic error on Google’s part, but I do think that, like SearchWiki, it’s a small but significant example of the fallibility of Google search. And for a company with Google’s visibility, perceived fallibility can be every bit as harmful as actual fallibility.

As the year draws to an end and I retreat home to wrap presents and eat mince pies, I find myself wondering if 2008 will go down as the year in which Google’s fall from grace began.

Don’t get me wrong – there’s no way I’m forecasting doom for Google. It’s not Woolworths. But a large part of Google’s advantage in its decade of existence has stemmed from the unparalleled reputation it enjoys. Indeed, earlier this year it was named as the world’s most powerful brand for the second year running.

Why is its brand so strong? Google has always been a good example of a business that diversified without corrupting its core offering (in Google’s case, search). Yahoo! is a counter-example. As it acquired companies like eGroups and GeoCities, expanding its set of available services, it lost its central focus and gradually became bloated and flawed.

The increasing clutter of its homepage was a visual manifestation of this strategic drift. Google’s remained an appropriate distillation of its focus on search – even as it added mail, news, calendar, maps and other successful services.

Yahoo! and Google’s homepages from 1996 to 2005

But I think that this year might mark a turning point and that future historians might go as far as saying that Google jumped the shark in 2008, even though it saw off the laughable challenge from Cuil. Let’s look at some of the things that Google’s launched this year:

• Google SearchWiki – I’m listing this first because out of all Google’s product launches this has been the first to really impact its core offering, search. The idea is that users of the feature can manipulate and personalise their search results. Someone suggested to me that it heralded the end of natural search optimisation. My prediction? The feature will be gone within 12 months.
• Google Knol – Google’s “Wikipedia killer”. I don’t like basing conclusions on anecdotal evidence but, well… have you ever used it? The press hype around the Knol launch was driven more by negative attitudes to Wikipedia than positive ones towards this competitor. I don’t think Knol will be going away any time soon but I think it’s been something of a damp squib. I’d be interested to hear from anyone who uses it regularly.
• Google Chrome – Google’s “Firefox killer”. Like around 3% of the internet I installed and started using Chrome when it came out. However, I’m not among the 0.83% of the internet who are still using it. The only good thing about it is its start-up time. Apart from that I think Google should spend more time going after the likes of Apple and Microsoft rather than Wikipedia and Mozilla.
• Google Lively – Lively was full of fail. Launched in July as a competitor to Second Life, people who know about such things (e.g. not me) were immediately critical of Google Lively. Generous souls waited for subsequent releases to deliver improvements, but instead the service was officially killed in November 2008.

Oh yeah – there’s Jaiku as well, but I’m tired of writing bullet lists. It’s Christmas after all!

Google has a far from perfect track record when it comes to product launches and its policy has always been to develop experimental projects and see how they fare in the market. However I think 2008 has been different for two core reasons – one, that it has started to alter its core search offering (in the form of Search Wiki) and two, that many of these other launches do actually seem to be strategic as opposed to whimsical.

If it’s true that these releases have indeed been strategic, then the underlying strategy – whatever it is – is failing. Google is in danger of its brand being tarnished by failure. 2008 has been the year in which it’s become possible to at least envision a future Google that’s not a million miles from AOL or Yahoo!.

Ever sent an email you later regretted? Well you might like the new Gmail Labs feature, Mail Goggles. It’s activated whenever you try to send an email late at night, when you’re most likely to be under the influence of alcohol.

How does it stop you sending indiscreet, inarticulate or embarrassing emails? It forces you to solve a number of simple mathematical problems within a set time period. If you pass, it judges you to be sober, but if you fail it silences you… most likely for your own good.