If you like Bioshock at all, you should go download Minerva's Den. Seriously go, you can finish reading this post while it's downloading. If you haven't heard about it, it's the story-based DLC for Bioshock 2 and it is fantastic. The erudite Steve Gaynor helmed it and he really succeeded on the goals intended. This really is the gold standard for single-player DLC and any other games working on something similar just had the bar raised.
Minerva's Den is also exemplary because it does single-player DLC right. And that's no small feat.
DLC for SP offerings like this is tricky. Multiplayer is a whole different beast and honestly, a simpler one. Valve's been doing it with Team Fortress 2 literally for years (although I'm still on the fence about the new in-game item sales), Modern Warfare made fifty kerbillion dollars on its map packs, etc. Ideally you'll have a community that you can feed new content to, and if you're doing it really well, you'll get some new folks in it too. Again, as Valve demonstrates, a free weekend and a temporarily discount price can turn into a truly staggering number of sales.
But single-player story DLC? It's challenging. It's generally been positioned as an epilogue, which seems fundamentally pretty challenging. An epilogue as DLC either means the original game had an incomplete ending (or worse, a cliffhanger) or it ended fine and by definition the epilogue is excessive. Minerva's Den works so well because Steve et al. opted to tell their own story. It's intimate and well scoped to the size of the experience. Of course, it still uses all the same systems of Bioshock (and expands them a little). It's basically all the bits one loves about Bioshock through a new lens.
Fallout 3's best DLC (Point Lookout) basically did the same thing. Rather than trying to continue the story, it provided a similar setup to the original game, with a wide, wanderable space with lots of small interesting things to find. Scoped to the length of the DLC, it worked really well. The other Fallout 3 DLC ranged from alright to quite terrible, but Point Lookout was fantastic for all the same reasons Minerva's Den was.
Now the hard part is how do you actually sell this DLC? Multiplayer (at least good multiplayer) is sticky. People will be playing it for months after it comes out. But at the break-neck pace the conversation about games moves at, many players will likely have move past a single-player title by the time single-player DLC comes out. Maybe someone with an office expects SP DLC like this to move more new copies of the game, but really, that's god damn absurd. It might move a few used copies at GameStop, but almost nobody is going to drop $60 on a new game just to play some DLC.
But what if you didn't need the main game? What if the DLC could be fully played by itself? As Dead Rising: Case 0 demonstrates with its god damn gang-buster numbers, there are people interested in this. Not only was Case 0 making Capcom money, but it was basically serving as a demo for the game too. This is a slightly different circumstance since Case 0 came out before the main game, but I'd be very curious to see how stand-alone DLC for a successful game would do.
Fallout 3 did something different but also smart where they could combine the original game with all the DLC and sell it as a "Game of the Year edition." I think Oblivion (and maybe Fable or Fable 2?) did this as well. It provides that important "discounted" perception for people who might not have bought it before to feel like they're getting a deal now.
Making good single-player DLC is hard and selling it is even harder. Minerva's Den absolutely succeeds on the first (and I hope it does on the second! I just have no idea) and we're starting to see all kinds of interesting experiments with how to actually get people interested in SP DLC. It's one of those avenues afforded by digital distribution that we're only starting to really dig into. I'm definitely looking forward to the fruit of those experiments. Let's just hope the level of quality can match Minerva's Den.