Design is everything
Without a good design, your map is essentially wasted - unless that was the whole idea. You might have the prettiest map in the world, but if it turns out to be less fun than eating wooden spoons, then you’re better off investing in a wooden spoon factory.
Appearance is everything
You could have the best CS defusion design ever known to man, but if it looks like an explosion in a pit of vomit, it won’t be very popular.
Similarly, if your map constantly flashes red, blue and goatse, few people will play it. A few will suffer epileptic seizures.
Atmosphere is everything
The best way I can describe atmosphere when it comes to maps is to say “atmosphere is the feeling of something real that you wouldn’t expect in an artificial world.” The map de_Velvet_East oozes atmosphere - even just looking at that main screenshot is enough for me to get chills down my spine. The CS:S version of Nuke does this too. Hooking into even a little of this is like reaching out through the monitor and pulling the player through. It’s a foot in the door, and after that it makes the player feel more in-touch with the world.
Similarly, atmosphere is about holding onto the player. If they’re engaged in the ambience of a wonderfully spooky warehouse, and then encounter a large Teletubby, they’ll instantly become detached. This means don’t add any surprises that don’t fit with the game or environment.
Present choices
The worst possible multiplayer map consists of a long, thin corridor in which the player can go one of two ways. Even a big, open box is better, because at least then the player has a chance to react and move. A map should allow the player to use their skill, not restrict it.
Of course, there’s always the exception: in some cases, you want to restrict the player. The most-used, obvious example is the ‘sniper window’ - a sniper has a good view through some window of some area, but because this effectively locks their position, it’s hard to escape someone shooting at them through said window. This is used to overcome the fact that without this restriction, they might have too much of an advantage.
Think like an eejit
Players don’t like getting lost or being confused. They expect to get slightly lost initially, or slightly confused, but even when that happens they should be given a gentle helping hand to redirect them into the game. Try putting your idiot specs on to help predict where this might happen, and do something about it.
Know basic modelling
Basic modelling skills in a tool like Maya or 3DSMax or XSI or Blender are a must for working with modern game engines. You might not be a great modeller, but you can create placeholders that a real modeller can prettify later.