I've been thinking about this for a while, and I think I've discovered a few explanations as to why single-player add-on content is rare.
I almost never get DLC, primarily because I almost never play online. Well, I play online plenty when reviewing games but I don't do it during my leisure time; I still prefer to think of gaming as a stress-reliever and I relax by being by myself . Anyway, I know the downside to this: Multiplayer entertainment lasts a lot longer than single-player campaigns; even if you played a campaign ten times through, it probably won't amount to the number of hours people dump into the multiplayer.
And if that's the case, it means people are still playing the same game many months down the road. Logically speaking, DLC makes more sense because the fans are still playing, right? If it's just a campaign and it's over in the first few days of playing, are you really going to be that interested in extra story-based content a few months – or even a few weeks – later? Typically, there are more than enough games out there; rarely are we looking around going, "gee, there's nothing to play." So with the sheer number of entertaining titles that arrive in any given year, single-player DLC releasing for a campaign-oriented title a month later is…well…
That's one point. The other point is bound to annoy people; I know the multiplayer fans are going to hate me for saying this, but it just seems that multiplayer is far more addictive. The only games that I believe truly cause people to become clinically addicted are all multiplayer driven; it began with MMOs and in truth, I don't think the online portions of some games like CoD are any different. It all generates a certain…mania…in gamers that I find borderline disturbing. Maybe it's just because they're playing with humans, or maybe because they're lost in a virtual world for such extended periods of time. It's merely a personal observation, guys.
To that end, I think multiplayer DLC will invariably be more successful, because multiplayer lovers play way more in terms of hours. At least, it appears to me that they do. Granted, they may be playing the same damn game over and over for months on end, but the number of hours is often through the roof. Lastly, I imagine it would take developers a great deal longer to produce story-driven DLC; putting out a new map or two isn't a huge deal, but expanding on a complex plot requires more writing, probably new locations and characters, etc. It just might not be feasible and it certainly isn't practical all the time; they'd probably have to charge more for story-based DLC.
I still wish we had gotten those extra Heavy Rain episodes, though.