Got into an interesting side discussing with my wife this weekend. We both play WoW and enjoy questing and PvE (at least for the moment I haven't dragged us into a BG yet *evil smile*). As we started the War Banners quest in the Wetlands we got to talking about the history involved in the quest. I started talking about the fall of the Dragonlords and while my wife seemed curious, I could see that familiar "Do I really care?" glaze forming.
We started talking about how I really dive into quests that have a story or give background/history on the world around me. It helps me to get hooked into caring about the pixels in front of me. I recently had a lot of fun doing the Missing Diplomat, Legend of Stalvan, and Sven's Revenge. All of which told a story about a person or gave hints about the lore of the area.
Each, now that I look back at it, got me hooked with the "innocent event leading to a big finale" style story. For Stalvan, for example, you open a chest to find a letter and get jumped by a "Forlorn Spirit" who doesn't want you looking into the legend. Sven has you helping him recover things he lost when fleeing his farm, avenging his families death, and then taking on something much more sinister.
For me, these quests pulled me in and got me curious to see what would happen next. Similar to the history of the Dragonmaw and their loss of their Dragon mounts and the fall of their empire. The story catches my attention and draws me in. For my wife, they're quests. The story is nice, but she's there to get it done and move on.
As with any good game, doesn't matter what you want, both options exist - but I'd be curious to hear from more folks about what grabs them. Do you go for the FPS with the interesting story (Half Life, FEAR, etc.) or just prefer to frag at will (DOOM).