This was inspired by a mod for Left 4 Dead 2, but most of the points apply to tabletop games as well.
The reason I started writing is: the mod wasn't fun. It had elements which suggested it ought to be fun; puzzle-solving, zombie destruction, and so on, but it finished up not being fun because the critical elements weren't there. I'm going to compare and contrast it with the official campaigns, to figure out exactly why it missed the mark.
The big miss was in a sense of purpose. The official campaigns give you a starting situation and a prospect of a solution to the problem. Dead Center starts with you having just missed the last chopper out of the city from the hotel roof; but there's another evacuation centre at the mall. Therefore, you have to get to the mall; this gives you the dynamic for the entire campaign. The DLC campaign The Passing is even more direct; to continue your escape, you have to lower the bridge. Everything is focused around getting into a situation to achieve that goal. Dark Carnival: the road is blocked, but there are searchlights still on at the nearby amusement park, so there's a prospect of rescue. Swamp Fever: the helicopter's down, but you could try to push through and see if there's something. Swamp Fever has the least strong purpose of all the official campaigns. Hard Rain: your friendly Cajun boat captain needs diesel. Get him some diesel, and he'll pick you up. The Parish: New Orleans is the last bastion of civilised society, and the Army are evacuating people. Get to the Army's location.
The mod I was playing, meanwhile, didn't provide any context. It didn't give any kind of story, so that itch wasn't scratched, and that led to easy boredom with it. The puzzles rapidly became repetitive, and without story, killing the zombies became a chore. Add in the longer-than-stock levels, and it just wasn't going to work out.
Then there was the unfairness of the map. Several of the puzzles were "gotcha" tricks; things that work once, as shock value, but with repetition become frustrating. The first time the floor drops out from under you, it's a surprise; the second time, you start looking for signs it's going to do that. When no such signs appear, you rapidly begin contemplating the designer's trousers and how much better they'd look if they were on fire. No need to remove them, I can set fire to them right now. It's one thing to give me a trap with clues that I should have spotted; it's quite another to simply declare, without warning, that this particular location is a trap.
It's also distinctly not on to punish the player for adhering to the established conventions. A common element in Left 4 Dead 2 is the "gauntlet event"; the players are required to trigger an alarm, which triggers a neverending horde of zombies until they can turn the alarm off. The accepted method for dealing with these is to run upstream to the off switch; this is how players are trained to act, and to put unmarked "gotcha" traps in their path is unfair. Admittedly, the game doesn't ever play fair, but in the official campaigns it at least provides the appearance of doing so.
Another thing to consider is the pacing. Pacing is always tricky, but it's worth thinking about it. I can best compare it to rollercoaster design; your pacing should allow brief "breath-catching" moments in between the onslaughts. The more intense the onslaught, the longer the required pause. This is something the AI director does very well in the stock game; in the mod, it was almost entirely lacking. The expected tension curve for a map, for a campaign, and for the game as a whole is self-similar, and does look like the profile of a rollercoaster; buildup, and then a fast release as high-intensity events happen, with smaller hills and loops following. Long periods of nothing are boring; avoid them. There's a reason there aren't any campaigns centred around driving the car, or riding the boat; there's no tension, and no sense of purpose, without obstacles to overcome. Long periods of nothing but action are worse; they induce the deadly combination of tiredness and boredom, and that's the combination which will get your trousers set on fire.
So to sum up: provide a purpose. Keep it as short as it needs to be, but no shorter. At least pretend to play fair. Vary the amount of action constantly; provide breathers after the intense moments. And above all, never let your players get bored. That's the recipe for fun.