Boy did I get sick of having to set this every time I ran Panopticon:

    look_back = 3.days

Some days it was “2.days,” some days it was “1.day,” some days it was 3.days. So:

    tsf  = "/Users/mike/.panopticon_stamp"
    timestamp = File.stat(tsf).mtime
    look_back = Time.now - timestamp

then when all is done:

    \`touch #{tsf}\`

It wouldn’t have even been a problem if I’d just checked some items as “done,” but when I accidentally create an item, or decide an item doesn’t need to be read or processed or whatever, I just want it gone and I don’t want it sitting in a log claiming to be a thing I did or read. Because I didn’t. But when you delete an item, it’s gone and Panopticon can’t find it anywhere and will recreate it, which meant I was being haunted by zombie tasks that I most pointedly did not want to deal with anymore. Now I won’t be.

But all that put the next thing in mind: It’d be pretty nice to have things I mark as “done” in Panopticon get unmarked/unflagged/unstarred or whatever in their native app. So a starred item in Google Reader stops being starred, a flagged message gets unflagged, a bookmark in delicious gets tagged as “read,” etc.

Fixing Sampled Reporting

Probably no time for that this week. I spent a lot of time fiddling around with some problems introduced by sampling in Google Analytics.

The brief version: If you make queries against the Analytics API that involve more than 500k events, you start getting sampled data. The article specifically mentions pulling reports for long periods of time.I’ve been pulling reports for a number of sites that do well north of 500k visits per month, so when I started pulling queries for periods of 60 or 90 days long I was most certainly getting back sampled data.

When I started trying to do really simple reporting about how page views changed month-over-month, I started seeing articles that somehow had fewer total page views when they were 60 days old than when they were 30 days old. Changing my approach to gathering page views from “single long period” to “consecutive shorter periods” cleared the problem up. Rather than pulling queries for a period like “from the date of publication to 60 days after the date of publication,” you’re a lot better off pulling a pair of queries: “from the date of publication to 30 days after the date of publication,” and “from 60 days after the date of publication to 29 days before that date” then adding them up. Unless the site is doing more than 500k visits a month, sampling is less likely to get you.

Have I mentioned Sequel Pro? If I haven’t, I should have.

It’s a Mac GUI for MySQL that’s good if you’re like me and don’t really like dealing with MySQL. It doesn’t cost anything, either.

A few things I like:

• Easy record editing. You can double-click and make a quick change on the spot.
• Easy export and import of selected or all tables in a database. When you’re in the “noodling around” phase of writing something that touches the db, it’s really easy to back in and out of changes quickly.
• Simple view filters. If you’re not fond of a lot of typing just to find a few records, you can filter records from the data view. It’s not complex (you can only filter on a single column), but there are plenty of filters: contains, greater/less than, earlier/later than, etc.
• Real queries. If the view filters aren’t complex enough, you can write real queries. It saves any queries you write in a history drop-down, and you can save them to a “favorites” list with a human-readable label. They’re syntax-highlighted, too.

It makes setting up a database pretty easy, too: auto-completion of column types and a little bit of guidance in setting up indexes.

There are some features I haven’t used yet (or as much), that also look pretty neat:

• Export of the database structure to a GraphViz document.
• Syntax export for table creation.

In some ways, it’s the best kind of training wheel software: It smooths out a lot of console-jockeying stuff, but doesn’t completely separate the user from what’s going on underneath. You still need to have some idea about what you’re trying to accomplish, but you spend less time worrying about the peculiarities of a particular interface style and can dip into the deeper waters as you learn.

It also makes it a lot easier to add database functionality to scripts, just because it makes managing a database so much easier. If you’re like me and tend to think in terms of ActiveRecord first, whatever the db backend you’re using second, Sequel Pro makes it easier to get to the good stuff faster.

So, I’ve got this Excel spreadsheet that represents a dump of over 2,000 articles from an old CMS. Using the same general workflow I used before, I’m importing the spreadsheet into a local MySQL database using the Ruby spreadsheet gem. The importer I wrote just reads line by line, creates a new ActiveRecord object then saves it.

Two thousand lines of spreadsheet — two thousand lines that include news-length content in each of their lines — is enough to make Excel (on the Mac) fall to its knees whining. Worse, after a few changes and saves, the spreadsheet library couldn’t even read the content. I tried saving it between .xls and .xlsx a few times and the problem persisted. I thought about pasting just the data into a blank spreadsheet to see if that would shake whatever rot had settled into the spreadsheet’s soul, but getting Excel to copy and paste from a spreadsheet that big just made it beachball and crash.

So it was in a state of agitation that I decided to try one last thing: I opened the spreadsheet in Numbers, re-saved it as an Excel file and tried reading that in to my importer script. It not only worked, but the execution time of the script itself went from somewhere around 10 seconds to about 1.5. It was so much faster I rechecked my script, thinking I’d commented out the line that actually saved the record.

I have no idea what goes on inside an Excel file, but if I had to guess, “a lot of crap” would probably be a big part of it. Some of that crap is, no doubt, useful to someone somewhere who’d just die if they couldn’t have this or that feature that’s enabled by this or that bit of crap. I’d also bet that there are people who are bitterly disappointed that Excel spreadsheets they’ve opened or saved in Numbers have quietly stripped out some essential bit of crap they need to do their work. As near as I can tell, Numbers stripped out a bit of crap that kept me from doing mine. It’s almost like discovering that high-compression JPEGS make bad pictures look better.

I like reading MetaFilter. I also like playing the “Guess the Deletion” game on MetaFilter.

Materials Required

• An RSS reader that updates frequently enough to remember items that appear on MetaFilter but are eventually deleted.
• MetaFilter members who post lame stuff.

Game Play

1. Read the MetaFilter RSS feed now and then. Do not click through to read comments until you complete step 2:
2. When you think you’ve spotted a post that will surely be deleted, signify that by saying to yourself “oh boy,” or “that’s not gonna fly” or “dead.” (Feel free to amend the list of signifying phrases.)
3. Click through. Look for the little gray box with text that begins: “This post was deleted for the following reason:”
   • Is the little gray box there? Score a point.
   • Is it not?
     • Signify that you’ve doubled down by saying “This isn’t gonna last” or something similar. Two points if it’s eventually deleted. Lose two points if it stays put.
     • Signify that you choose to lose only one point and move on to the next round by saying “Huh.”

Victory Conditions

• We can’t tell you how to live your life.

Game Variations*

• Gain 5 points for getting in before actual deletion and taking a big crap all over the thread provided it’s eventually deleted.
• Lose 5 points for thinking you made it in before actual deletion and taking a big crap all over the thread, only to realize a day later that it hasn’t been deleted.
• Gain a point for any “favorites” earned in either type of comment.

* Here is where my flat game manual affect goes out the window: It’s pretty rare for me to comment on MetaFilter, let alone engage in daredevilry like I describe here.

O would some power the giftie gie us to see ourselves as others see us.
— Robert Burns

Re: the Great Tech Purge:

Maybe it’s not so much “tech writing” as it is “things writing.”

I like tech-related things, so a lot of the things I read about will be written about by tech writers. But in the end, whether they’re battery powered, Wi-Fi connected, assessed in terms of their storage capacity or resolution, they’re things.

One thing a lot of those things have in common is that they’re made by really big companies that invest millions and millions of dollars to build affinity with their brands.

One thing brands do is exploit a back door left open by the gap between our images of ourselves, ourselves as we imagine others perceive them, and ourselves as we aspire to be. It’s hard to balance all three sets of perceptions, let alone actually reconcile them. Brands offer an opportunity for relief from that emotional and cognitive stress by suggesting that they can speak for us now and then, relieving us of the burden of being what we wish we were — or what we wish other people would think we are.

Al & I have been watching Lost via the Roku (and the iPad while we were on vacation). One way you can gauge your emotional involvement in a series is the eagerness with which you wish death on some characters. At six episodes into season 4, a list:

Wished Dead at Some Point

• Jack: Stopped wishing him dead once his flashbacks and flashforwards started dirtying him up a little.
• Kate: Stopped wishing her dead once we decided keeping her alive to suffer might be better. Perhaps the most willfully stupid character on the show, and the one most likely to provide fuel for the many, many plotlines that rely on someone doing something ridiculous on the basis of partial information. Our magma-hot loathing of Claire keeps us from actively rooting for Kate’s demise. We can only hold so much hate in our hearts at once.

Worth noting that Kate and Jack in combination were irritating from Episode One.

Actively Wishing/Wished Dead

• Michael: Wished dead. Settled for him leaving. Not happy to see him back.
• Charlie: Wished dead repeatedly and violently. Got our wish and resented that it wasn’t swift and unsentimental. The whole “arrow in the throat” vision was a cruel tease. The thought of him hanging around as “White Gandalf” Charlie makes me twitchy.
• Claire: Hoped she’d be taken out with Charlie.

Never Wished Dead

• Sawyer: Liked him from day one. Never wished him dead. Sometimes wish Kate dead for his sake.
• Locke: Never wished him dead. We like Locke, even during his current (for us) Colonel Kurtz phase.
• Sayid: Never wished him dead. Like the effect quantum particles, cosmic rays and the pollen index seem to have on his moral disposition.
• Hurley: Never wished him dead.
• Sun: Never wished her dead.
• Jin: Never wished him dead.
• Ben: Never wished dead. Great admirers of the “15-degree head-turn” thing he does to denote that his attention has shifted for some inscrutable reason. Kind of tired of seeing him get punched all the time.
• Desmond: Never wished dead.
• Juliet: Never wished dead. But do wish she’d quit looking like she’s in a perpetual state of thoughtfully sniffing International Coffees and thinking back to that little café in Vienna.
• Juliet’s Sister: No opinion. Did inspire us to say “I’m gonna have a baby … cocksucker!”
• Ana Lucia : Never wished dead. Did wish she would have stuck around and taken up with Sawyer or Jack, whichever would have messed with Kate the worst.
• Mr. Eko: Never wished dead.
• Walt and His Dog: Never wished dead. “Wet Walt” manifestations were creepy, though.

There are a few we don’t want dead (Bernard & Rose) less because we’re so attached to the characters and more because their deaths will waste valuable screen time with maudlin piano music and tragic stuff.

While I’m here, a few more notes:

• With the flashbacks, I decided by the end of the second season that it felt like we were watching the eighth season of a show that had jumped the shark with a ridiculous “crashed on island” plot conceit some time in its sixth season.
• Sometimes I laugh with delight. Sometimes I laugh because I’m delighted with the way the island is just this big vacuum in which anything can happen for no good reason except as an excuse to cause Kate to fly into a sobbing panic or start making out with someone, because those seem to be her two reactions to everything. Well, those and “tell somewhat static character only part of the story to activate the character for a minor plotline driven on the strength of the misunderstanding.”
• The show could have been The Best Daytime Drama Evar.
• Need to go see if anyone has done a comprehensive census of the the many survivor extras who appear now and then.

I was wondering who among my friends was the biggest tweeter, then I got to wondering how I could learn that prettily.

Prereqs:

• John Nunemaker’s Twitter gem (sudo gem install twitter)
• Matt Aimonetti’s Google Charts gem (sudo gem install googlecharts)
• Deepak Jois’ gchartrb gem (sudo gem install gchartrb)

Also, the most tweets Twitter will cough up at a time is 200 without, er, doing something I don’t know how to do.

Sample chart above is using randomized names because it seemed polite and non-accusatory to do it that way, considering one of those people needs to lay off the caffeine. (I kid, “coscet,” I kid. Much love.)

Update: Thanks to Ed in the comments, who noted I was citing the wrong gem for the Google Charts action.

Apropos yesterday’s “Four Things You Think When You Read Headlines Like This“:

My feelings about tech columnists gain some clarity whenever I’m away from tech commentary for a spell, like I was while I was on vacation last week. There’s a certain amount of tech writing I have to read because it’s my job, and a certain amount I do because I like to keep up with what’s happening with stuff I like to use. It’s kind of hard to disentangle the straight news/reporting from the commentary, though.

When I’m on vacation and away from the tech stuff I’d probably be fiddling with, I tend to “mark all as read” most of the tech writing then never go back to see what I missed. Once I get back and begin to read new stuff coming in, I get frustrated because so much of it isn’t very good and I have to readapt the mental filters I apply to ignore the likely chaff.

So today I’m going to experiment with eliminating all of it from my RSS reader and Google News page except the professionally mandated stuff (which is a pretty narrow field) and maybe a few security update sites. Consumer tech, though? Gone. If it’s not something I use, I don’t want to read about it. If it is something I use, I don’t need a columnist telling me what to think about it. If I need to know something, I’ll just go looking. Passively setting up trawls and then sifting through whatever gets caught up in a few keyword searches or catches the fancy of tech bloggers is for the birds.

Update:

“Isn’t very good” is pretty subjective, so here are a few of the things that set me off:

• the aforementioned “{thing}-killer” mode of writing
• meta-commentary (“I am using this here thing I just got to tell you about how awesome this here thing I just got is so awesome”)
• “I have decided to …” (“return this device” is a big one, but also assorted switcher narratives)
• almost any writing about social networking. Social networking itself is cool. Writing about social networking is boring. Crabbing about social networking is sad and boring.
• advocate-baiting (“I wrote 600 words about why Ubuntu sucks and now all the Ubuntu fanatics are being mean to me because they suck just like Ubuntu, which sucks.”)
• “I was wrong about {thing},” mostly because I seldom have read the person being wrong in the first place (how many tech columnists do you make it a point to follow specifically and regularly?), which tells me I didn’t know to care whether they were right or wrong to begin with.

Like Marco, I think the “pads/tablets will kill e-readers” sentiment is a little foolish:

Naturally, the tech press is already declaring [the new Kindle] “dead”, because the tech press loves product “killers” and other perceptions to completely rule out entire classes of products because they lack the empathy or worldview to recognize these products’ markets.

Well … I don’t know about all that.

The tech press usually wraps the whole “killer” thing in a question mark in the headline, e.g.:

Is the iPad a Kindle Killer?

Then follows the headline up with some overthinking and dithering in the actual content. A definitive conclusion is more of a nicety than a requirement^*, so it’s wrong to think the headline is harboring some motivation beyond engaging the part of the reader’s brain that reads:

Is the iPad a Kindle Killer?

and thinks a number of things in no order I’ve paused to consider, like:

• “I know the answer to that question …”
• “I bet the way they answered that question is wrong …”
• “I hope they answered that question the same way I did …”
• “Grar!”

Then the brain makes the finger click the button before any executive function can blurt out “flamebait! flamebait! we’re being trolled!”

The limply engaging “Is the {noun} a {noun}-killer?” article can be safely classified as a close cousin to the “{number} {superlative} {nouns} you {must/haven’t/will/should/shouldn’t} {verb}” article. The point isn’t that anyone on the other end has any particular stake in the answer or has even considered whether it’s a good question. The point is that we’ll foolishly click through because someone might be wrong on the Internet.

Reading a lack of “empathy or worldview” into these provocations is like calling a Mynah bird “hard-hearted” because it has been taught to repeat choice quotes from Milton Friedman.

* And may even be a liability, unless the author is the sort to plan in advance for columns built around topics like “I was right …”, “I was wrong …”, and “People who like {thing} are unreasonable, unlike me.” The problem is, tech writers often don’t have enough job security to think that far ahead.

“Just don’t get hit that way.”

So, yesterday I wondered if Consumer Reports not recommending iPhone 4 was somehow like Johnson losing Cronkite. I mean, Consumer Reports has to carry a lot of weight — as much for casual buyers as a Pogue or Mossberg has with slightly less casual buyers.

But no … There Is Another:

“Seems nutty to me to give it a ‘don’t buy’ for this single annoyance alone.“

Natch.