To answer those questions, we need to understand where the core fun of RPGs comes from. Now, a lot of the fun of an RPG comes from getting together with friends (community, fellowship), looking at the cool pictures in the books (art, visual appeal), fiddling with miniatures and terrain (tactile pleasure, physical activity), eating junk food (sense pleasure, sugar rush), and gossiping and telling jokes around the table during breaks in the action (comedy, sociability).
But a lot of the fun - dare I say most of the fun - comes from the game itself. That's why we play! So where does that fun come from?
Excitement -> Fun
Fun comes from excitement. Exciting games are fun. Obviously! What players find exciting varies pretty substantially. What's exciting for one player might be boring for another. Some players love to roleplay, but get distracted during combat. Others love tactical battles, but fade into the background whenever people start a conversation. That's just how players are. What's exciting depends on the player's motivation for playing.
Tension -> Excitement
Excitement is a kind of nervous system arousal. Excitement of the nervous system comes from conflict and narrative tension, whether it be an action scene ("Will that ogre kill me if I don't run away?") or a social scene ("Can I trust the Duke enough to tell him our plan?") or whatever. Tension is when something is going to happen that you don't have control of, and it might or might not go the way you want. That's narrative tension.
Problems -> Tension
Narrative tension comes from problems. If there weren't problems, there wouldn't be any tension. Without an orc to guard it, there is no tension about whether or not you can have the treasure.
Story -> Problems
Problems are generated by the story. The orc is guarding the treasure chest. That's a story fact. Story facts generate problems: The orc might be a slave, and therefore the problem is "should we kill a slave to get some treasure?" The orc might be a jealous warlord, and therefore the problem is "can we defeat the warlord and steal his treasure?"
You -> Story
The story comes from everyone at the table. Why is there an orc and some treasure there? That's up to the GM. Why do you want that treasure? Why would you kill a slave for it? Why would you risk battle with a jealous warlord for it? How did you get into the room? What are you going to do to the orc? That's up to the players. The story doesn't have to be complicated or innovative or particularly artistic for it to generate fun. RPG players can be thrilled by the barest premise.
Story -> Problems -> Tension -> Excitement -> Fun
You've seen this before, if you've been reading Run a Game. This is the formula for fun in tabletop RPGs.
Here's an expanded graphic detailing the Fun Formula. View the thumbnail below or click the link for full size. (The color progression isn't the same as here in this post, but I thought it looked better this way.)
I've included some common RPG story creation concepts (Big Issues, Fronts) and some topics you've seen on this blog: