Archive for the 'how to' Category

A simple perl script to interrogate the Technorati API

Technorati API perl query in action

Sometimes (for instance when I’m doing the research for the blogger typology) you need to get a whole load of Technorati data for a whole load of blogs.

This research can (of course) be done by hand. And (of course) for a long list of blogs this would take a great deal of time. Handily, Technorati provides developers with an API that lets you automate those queries. An API (for those of you who don’t know) is an Application Programming Interface - a toolkit provided by a service or application (in this case by Technorati) that lets other computer applications ask it questions and use the answers for their own purposes. It may be helpful to think of APIs as being like the knobs on top of a Lego brick that let you stick other Lego on to it without in any way changing the nature of the brick itself. On the other hand it may not be so helpful after all.
Continue reading ‘A simple perl script to interrogate the Technorati API’

Referring to “this cell” using Excel conditional formatting

If you already know about conditional formatting and navigated here via Google, please jump straight to the hack. If not, I hope the following introduction is useful. You might also like to check out the WikiHow introduction to conditional formatting in Excel. This post is actually concerned with an interesting hack that lets you reference the value of a cell itself when setting up formula-based conditional formatting rule.

Conditional Formatting

Excel’s conditional formatting feature is a boon to heavy spreadsheet users like me. It is a flexible and powerful tool that (among other things) lets me highlight data according to a set of rules so that I can easily spot the interesting bits in what would otherwise be an almost impossibly dense and meaningless cloud of numbers. Here’s an example; a table of the correlations between 32 different statements (taken from some ongoing work looking at a simple blogger typology.)

Table of pairwise correlations between 32 statements
Continue reading ‘Referring to “this cell” using Excel conditional formatting’

Use RSS to Track Thousands of News Sources Easily

OK - I’m not sure about the “thousands”, but Marshall Kirkpatrick’s article shows how you can use a combination of tools and tricks to get the most out of your RSS feeds - without getting swamped. Useful stuff!

read more | digg story

Influence Mapping: The Maverick Cop Way (Part 2)

the_maverick_cop_way.jpg

The story so far: In the last episode of Influence Mapping: The Maverick Cop Way, we discussed a simple process for organizing what you know about influencers. We discussed briefly the decision making unit model we were using, our (very broad) definition of “influencer”, and showed how we can score them quickly for the three key variables reach, authority, and “ease-of-use”. At the end of the process, we found ourselves with something like this:

influence score

Now we’re going to go a little further, and show how we can map the relationships between the various stakeholders. This is the second and final post, and it may introduce a lot more that’s new to you. But stick with it - it might be worth it.

What you’ll need before you start

You’re going to need UCINET, a programme that analyses matrices and networks. It comes along with another programme from the same publishers (Analytictech) called NetDraw that draws networks. You’re going to want both. UCINET costs $250 for a corporate license, but the first 30 days are free. NetDraw is a free download.

They only work on Windows, but I’ve not experienced any real problems running them on a Mac using Parallels.

1. Create a matrix

Take your list of influencers (as per the table above), and add in the four key players from the decision making unit; the initiator, the decision maker, the purchaser and the end user.

Paste them down the left hand side of your table, and along the top edge, as in the illustration below. Excel’s Edit > Paste Special > Transpose command is useful, not to say essential, when you’re doing this. At the end of the process, you should have something that looks like this.

matrix (empty)

The rows show the influencers, the columns show those being influenced, the targets.

Because we don’t think that an influencer can exert influence themselves we drop a line of zeroes down the diagonal. This is more to help us navigate than anything else. Don’t feel you have to do this.

2. Fill in the matrix

Go through the rows one by one, deciding if a given influencer has any effect on the targets (the column headings). So for example, we know that the initiator influences the decision maker, and so on. But knowledge of the end user may well influence the decision maker, too. So - for example - when I’m choosing my dad a computer, I take into account the fact that he’s not so au fait with technology, and that if he can’t use it, he’s going to call me to ask for tech support. So I’d better choose something that will limit these calls.

At the end of the process, you’ll end up with something like this (although probably much bigger). It can take quite a lot of time to go through this process - this may be one of those times when you want to work with a partner to bounce the ideas around.

matrix (filled)

Continue reading ‘Influence Mapping: The Maverick Cop Way (Part 2)’

Influence Mapping: The Maverick Cop Way (Part 1)

the_maverick_cop_way.jpg

You know the maverick cop? The one who breaks all the rules, has to surrender his gun and badge somewhere in Act II, but nevertheless somehow gets the job done? His approach to solving problems is crude but effective (usually it involves shaking people down, which isn’t something we get to do a lot in the communications business.)

I don’t know that maverick cops are into influence mapping. I don’t know how many people really are, but it’s a big part of my work life. These days it seems I’m always being asked, “Mat, who is the most influential x?”, or, “Mat, who influences the online discussion on y?” or, “Mat, what influences people’s purchase decision-making when it comes to z?” These are all interesting questions, and bear a lot of thought and research and planning.

However, time isn’t something we all have a lot of these days, so right now I’m going to share a very quick-and-dirty method I’ve been working on; the research equivalent of holding a pimp upside down over a balcony.

Before we start, what’s wrong with this approach?

It builds on what we know, or what we think we know. Using it successfully will require all those assets that the maverick cop has in spades: a sharp brain honed by experience, a deep knowledge of the streets (well, your market), and an underworld intelligence network of pimps and hookers (in our case these are more likely to be clients and colleagues, of course.)

Axel Foley aside, maverick cops don’t always do so well out of their jurisdiction. This approach isn’t going to expose surprises or new information so well. It’s all about organizing what you know.

If you are a rookie cop, you’d better stick by the book. That’s all I’m saying. Or someone will bust you down to traffic duty before you know what’s hit you.

And what’s so good about it?

Well, it’s fast, for one thing. And it’s a process - which is another. Now if you add into that the fact that - when you have more accurate data, you can go back and plug it into the model without breaking it, but only making it better - then you’re onto a winner.

Continue reading ‘Influence Mapping: The Maverick Cop Way (Part 1)’

how to: create alternate row-shading in Excel

Why would I want to do this?

I think it’s easier to scan along lines if you colour alternate rows, particularly when you print out your sheets. Gridlines are somehow a little disorienting. You may not find this, and you may prefer the big black borders approach. But if you don’t, read on.

What’s wrong with the way I do it?

Well, if you’re doing it by hand, you’re going to run into two problems.

  • Depending on how many rows you want to shade, it can take a long time.
  • When you delete or add cells, you can throw off the whole thing, and have to start again

These are sufficiently irritating that it’s worth sharing this approach.

More to the point, I wanted to test out some new screen capture software (

Select all the cells you want to change. I usually select all. On the Format menu, click Conditional Formatting. Under Condition 1, select Formula Is. In the data entry box, type =MOD(ROW(),2)=1. Click the Format button. In the Format Cells dialog box, click the Patterns tab. Select a light-blue color, and then click OK. In the Conditional Formatting dialog box, click OK.


Alternate row shading in Excel

how to: perform free Facebook audience research

Facebook provides a free, useful way to interrogate its database of users. This can be invaluable whether you’re actually thinking about planning a campaign on Facebook, you’re looking for some quick-and-dirty audience research, or you just need some corroborative detail.

So where’s this tool? It’s all part of Facebook’s Social Ads platform. Here’s how you do it.

Step 1

Facebook audience research step 1.0

Go to http://www.facebook.com/ads/create/. You don’t need a Facebook account to do this.

The page may look a little different if you’re already logged in, or if you’ve created something using the platform in the past.

acebook audience research step 1.1

Type in any URL. It doesn’t matter.

Step 2

Facebook audience research step 2.0

Now this is where it really gets interesting. Play around with the various drop-downs and check boxes. How many Australians aged 25-30 have registered accounts with Facebook? How many of these work for the Macquarie Group?

Or ask Facebook how many of its UK users enjoy peanut butter?

Facebook people who like peanut butter enough to tell their friends

If you have a Facebook account yourself, you’ll know that there are lots of places in your profile where you can answer questions.

So for example, it’s perfectly possible to dig around and find out what portion of the Facebook audience say, likes peanut butter enough to feel that it’s important that they tell their friends.

Or which age group most (publicly) enjoys Curb Your Enthusiasm.

UK Facebook users who enjoy \

Caveat 1

Facebook users who live in London

We’ve seen that (at May 6, 2008) there were around 9.7 m registered UK accounts aged 18+ (or approximately 16% of the total 2006 UK population). But - of course, we can be more granular than that, and look at specific cities. For example, we can see that there are around 2.5 m users based in London.

Facebook Londoners who are male

Of these users, 788 K are male. This should raise a few suspicions, because at first glance one might infer that the remainder (2.5 m - 788 K = 1.7 m) of the users are female. This would mean that (in London, at least) female users of Facebook outnumber male users two to one.

But when we add women into the mix, we see that women and men combined account for only 1.7 m Facebook accounts in London.

What gender are the remaining 900 k (2.6 m - 1.7 m) users?

Facebook can only tell you what its users have shared. If they have chosen not to share their age, gender, location, etc. you can’t see it. Double check your numbers, and bear in mind that the more granular you get, the more room there is for error.

Caveat 2

18 and 19 year olds who like peanut butter enough to tell their Facebook friends

It appears that there are around 140 people aged between 18 and 19 on Facebook who like peanut butter enough to tell their Facebook friends.

18 year olds who like peanut butter enough to tell their Facebook friends

Breaking it down a little further, we see that 40 of these are aged 18.

19 year olds who like peanut butter enough to tell their Facebook friends

And 140 of them are aged 19.

Adding those numbers together, that’s 160 peanut butter lovers aged 18-19 , not the 140 peanut butter lovers we saw in the first run.

Running the exercise again confirms that twenty new peanut butter lovers haven’t suddenly signed on in the past five minutes.

There’s clearly some kind of rounding error. It seems that Facebook rounds to the nearest twenty. Bear this in mind that these estimates are intended for the purposes of estimating advertising audiences.