mediaczar

Since writing this post, three simpler, better ways of solving the problem have been submitted in the comments section. Feel free to read this post, but look to the comments for the solution!

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.)

Continue reading ‘Referring to “this cell” using Excel conditional formatting’

On Friday 19 December 2008, we finally pressed the “Go” button on the new Porter Novelli site. It has taken us three months of planning and nearly six months of design, development and bug-fixing to get to this stage, so you’ll understand that we’re justifiably relieved and proud.

This is, I think, a great improvement on our old site. The old site was characterized by low information content, Flash front page, and the occasional shark. I’ve circled the shark in the following screengrab from our old site.

In order to keep all our various stakeholders involved at every stage of the project, we set up an “Under Construction” blog on TypePad, a Flickr set or two, and shared many of our planning docs on Scribd (you can see all of these on the blog.)

Because Porter Novelli is a global public relations network, we sent regular updates to the senior leadership around the world asking for feedback, case studies, and fact-checking. We couldn’t have done it without their help.

Our design and development partners were interactive design consultants Wilson Fletcher. I’d worked with Mark Wilson in the past life on a project for the Discovery Channel, and was impressed by his teams creative thinking and rigourous approach. One of the key objectives for the new site was to make something that would validate and be accessible to multiple audiences, and they’ve achieved that with grace and elegance!
Continue reading ‘The new Porter Novelli site goes live’

(NB: If you have both a blog and a short attention span, please skip the article, and go straight to this short survey. Many thanks!)

What is a blogger? Everyone seems to think they know, and yet the longer I work in this area, the more I realize I know nothing. And the less I know, the more suspicious I become of marketers who use vague terms like “conversation” (which has – after all – become little more than a Latinization of the ghastly “dialogue”.) I can just about understand what Technorati means when they talk about

The ecosystem of interconnected communities of bloggers and readers at the convergence of journalism and conversation.

(State of the Blogosphere 2008)

…but there are an awful lot of long words that could turn out to hide an awful lot. And that’s the carefully thought-out distillation of a bunch of experts. Most of us, most of the time fall back on lazy or confusing language. We talk about “social media” and never stop to think that — depending on who’s doing the talking (and what they have to sell) — what is meant by that apparently innocuous phrase shifts wildly from speaker to speaker.
Continue reading ‘Your help needed to develop “blogger typology”’

On November 10th, Stephen Davies collected together a list of “UK PR people on Twitter” According to PostRank, this (and his earlier post, “UK Journalists on Twitter“) are the most popular posts on his blog.

Then a couple of days later, Stephen Waddington pushed that list through TwitterGrader to come up with his list of “Top 50 UK PR people by Twitter influence“

A couple of weeks ago, I was looking for a seed list with which I could test our “whitelist” and “canonify exception” rules on Rufus (the network analysis tool that Porter Novelli has been working on for the past six months.) This isn’t the right place to go into it, but to put it simply, the whitelist restricts the search to domains that are on the list (like a guest list), and the canonify exception list stops Rufus from chopping the subdomains or directories off the list (without this, a site like sethgodin.typepad.com would just show up as typepad.com or en.wikipedia.org/wiki/Social_network_analysis would show up as wikipedia.org. Rufus, by the way, is named after the George Carlin character in Bill & Ted’s Excellent Adventure.

My colleague, Tim Hoang used to work with Stephen W., so he sent him the image. Wadds then posted “the map on his blog“. My flickr page has never had so much activity.

Here’s the original graph:

Lots of people started drawing conclusions about the nature of PR, or the nature of Twitter from the graphs. There was lots of interesting speculation. Some people thought that this demonstrated how introverted the twitter crowd is. Others thought that it showed how introverted the PR/Social media crowd is. Others seemed to think that it didn’t matter.
Continue reading ‘Some Twitter Social Network Analysis’

This is a 300dpi map of the top 50 PR twitterers (as per Stephen Waddington’s analysis) and the interrelationships between them.

To generate this:

We first crawled all the accounts for “friends” (accounts that they follow) and “followers” (accounts that follow them). This is a profligate use of resources because we were always going to throw away a massive load of that data. But it’s always more interesting to start with a large data set. You don’t know what you’re going to find.

Then I wrote a quick-and-dirty perl script to process the data looking only for those instances where one of the top 50 followed another.

Then we dropped everything into NetDraw (if you are at all interested in this stuff, you really should get hold of a copy and start reading around the subject.) We laid out the chart so that the people who have the most peer-group followers are in the centre of the chart – and to make it even more obvious, we sized their nodes according to the number of peer-group followers that they have.

So people on the peripheries (like me – mediaczar) are peripheral to the community, and those in the middle are central. Obvious, huh?

This chart already shows a massive difference between our analysis (as it progresses) and the raw data from Wadds’s list. There are some really good reasons for this, which I’ll go into on the blog.

Map of top 50 UK PR twitter people and their followers

Originally uploaded by matmorrison

This is not a hedgehog in a cranberry field. It is a network map, but a particularly tightly-knit one.

Spurred on by some of the comments we’ve received about the Rufus map we made of the top 50 UK PR twitter people (as measured by Stephen Waddington) I thought it’d be a good idea to look at this in a bit more depth. Rufus isn’t really the right tool for looking at this kind of thing, so we’ve built something else to do it better. Looking at one site or service is a lot easier than looking at lots of sites — so this took hours, not months to create.

After a little debugging we were ready to test on a seedlist of 50. The crawl took about an hour to run.

This is a visualization of the data set we got (correct as at December 12, 2008) after very little processing.

The size of the blobs relates roughly to “how many people in the group follow you.”

We’ve removed anyone who is only followed by one person in the group. So everyone here is followed by at least two others (obviously.)

There are just too many people in this graph to show labels. And a lot of the top 50 people are hidden by other top 50 people. Maybe I should do a graph rotating in 3D. (Later, having tried this: if I had a SGI workstation, maybe I would.)

What I’ll do over the weekend is process the data files I’ve got (one’s got around 30K records, and the other 40K records) to see if we can tease a little more information out of them.

Then we’ll run this on other twitter communities, and random twitter seedlists to see how (if at all) the networks differ. Are PR people more introspective than the rest of the twitterverse?

This is a very high def image, so it will blow up nicely.

Here are the searches that led traffic to my site between midnight and 5am

This does not make me more likely to go back to sleep.

High network density in twitter UK PR community

Originally uploaded by matmorrison

For this graph we took a list of the top 50 PR twitterers as measured by Stephen Waddington (Nov 2008). We added “twitter*” to the canonify exception list to identify individual twitterers (this isn’t perfect — the regex may need some tweaking) and further limited the crawl to domains that contained the word “twitter” using the Whitelist function.

Again – look at how dense the network is here.

Reading RUFUS data with yEd

Originally uploaded by matmorrison

Most of the time we use UCINET and NetDraw to analyze the data from Rufus. Rufus exports crawl data to a Pajek .net file by default. But we can also export GraphML and read the data into other tools that handle that format. This is a test we ran of this feature using yEd

It’s not working beautifully yet, but it is working.

Porter Novelli has been working on its own “online influencer mapping” tool for about six months now. Recently, I’ve started posting screen grabs on our Flickr page to see what people think about it. I thought it was probably time to share some of the images here.

Version 3.5.4 (Always in Beta)

The project is named Rufus after the character George Carlin played in “Bill & Ted’s Excellent Adventure”.

For those of you who know how network analysis works and what it’s used for, this is revolutionary only in that it’s fast and accurate enough to use as an exploratory tool.

For those of you who have no idea what network analysis is or how it’s used in many, many situations, 2009 would be a really good year to start finding out.

For this graph (which took around 5 mins to generate), we took as a seed list the first 50 back links as generated by Yahoo Site Explorer (http://siteexplorer.search.yahoo.com/.) We’ve tested this up to 100 seeds, but there’s plenty of room to go further.

Continue reading ‘A sneak peek at our online influence mapping tool’