I think that this is a tough question because "compelling" and "story" aren't necessarily in the same boat. That is, you can have plenty of compelling goings-on in the campaign without a spectacular story actually forming. Sci-fi and Fantasy stories are particularly vulnerable to this because they can have a very enticing setting or even premise that the reader is excited to just explore, characters and their development can sometimes take a backseat. Consider the differences between the original Battlestar Galactica and the recent "Darker and Edgier" version. You watch(ed) the old one for the spaceships and the aliens and the laser and the pew-pew, you watch the new one to see how Adama and Roslyn are progressing in their relationship. Of course, some of this is a matter of opinion and context. I'm sure
somebody out there thought the whole "starchild" business in the old show was masterful storytelling, and the new one is almost better without the finale. Contextually, the sci-fi audience nowadays may not be as starved for material to consume as they were in the late seventies.
So, we all look at media like Firefly and think "Yeah, I want that in my game." Unfortunately, I don't think there's much you can do to
ensure that you have the in your game. That's simply because shows are written by writers and they can take days to spice up the dialogue and keep these compelling conflicts going as long as necessary.
Players are not often a) as clever about impromptu dialogue* b) as engaged by the same premises as the viewing public or c) as willing to play another PCs love interest/sidekick/life companion. That doesn't mean that you
can't have that kinda thing in your game, but it may be less "compelling" than on screen.
I've had the most success with games/subsystems that drive/reward the players for addressing such conflicts and relationships during play. Fate does it with aspects and the fate-point economy (although the potential there for "compelling story" depends greatly on the aspects chosen.) For d20 games there are things like the
Sweet 20 Experience System, and even the (rather weak, IMO) Inspiration system in 5e. I've seen several games (particularly supers games) that have some kind of "Drives" system. I'm sure there are plenty of other systems out there as well. One of the best little nuggets I gleaned was from a game called "Capes", wherein your drives are all framed as "I really want or need to [x], but I can't because [Y]." or something similar. So: "I want to uphold the law and our traditions, but I can't let Baltar win the election." or "I really want to retire and be with my family, but the king would have us all killed." A Fate character with aspects like that will find a good deal of story whirling about them at my table.
Barring the use of such systems (and some people dislike them for various reasons like so-called "metagame" currencies....although if HP aren't a metagame currency I don't know what is...but I digress) you are left with doing it freestyle. Its not impossible, but most games (especially D&D) don't give you any real help. Notice that compelling stories often involve two conflicting interests (Interests tend to fall into three categories: A Value that the character wishes to uphold, or Goal the character wishes to see occur, or a Fear that the character wishes to prevent.) The classic D&D example would be to put a LG character in the position of having to obey an evil lord. You are then putting two of the character's values in conflict. Unfortunately most D&D characters don't come with obvious hooks, and alignment is a fairly slippery beast so even plenty of LG characters would weasel away from that conflict above without noticing it.
Finding or creating those interests is (IME) the hardest part of the deal. I figure your choices are:
a) Before the adventure starts, have a "session 0" wherein you get player input on the world, particularly NPCs and politics. "Okay guys, the Rustbelt Dwarves are scattered about the country as an angry minority, why?"
b) Be patient and set up play so that you can provide them plenty of opportunities to show you their interests.
Once you have those interests, just put them into conflict somehow. Villains that can create these conflicts are particularly memorable. I would even advise against having a "resolution" to the conflict in mind, just have ways to crank up the pressure as you go. Eventually, something snaps and you have your climactic moment. This kind of thing is easier to work in a "sandboxey" type of game. Which (I suspect) is why some people like that style of play so much.
*Probably a good reason why every gaming group I have ever been a part of recycles quotes from the relevant media all. the. time.
Anyway, that's my take on it, I hope it helps.
D'OH BSG ninja'd while writing this.
