A bunch of simple considerations when texturing your maps. Take notes if your latest map looks like something from 1995.
Selecting the correct texture for a particular surface has always been tricky, but at the same time the most important aspect. At the simplest level, it's about choosing the texture that looks best, or selecting one from a number of textures that are as good as each other.
The material is important - with a game like Counter-Strike players expect to be able to shoot straight through wood, and you must try to meet these expectations. Similarly, wood should look like wood and metal look like metal.
One of the more common mistakes is over-using a texture, simply because it's such an easy thing to do. Generally, if a texture is easily-identifiable, it's use should be limited. Textures that merge well with the environment should be used often. Don't be afraid to re-use textures for different purposes, provided it makes sense.
This is where the aligning and straightening and tiling of a texture comes into play - something that is learned best through practise and looking at how others do it. This is also where you have to consider the geometry, it's shape and how two sets of geometry collide or combine, and how to best manipualte their textures to make any seam look natural.
As an example, consider the perimeter buildings on the CS:S Dust maps - the buildings all use a variety of textures, generally consisting of a top, middle and bottom. They also jut out whenever the building (and thus texture) changes, which helps to hide the seam.
This is also where you consider lighting - if geometry needs to be changed to get the look right, it's worth imagining how the light will behave after the change and whether it will actually negate the effort. A strong shadow, for example, can make a seam look very ugly.
Remember, the real-world isn't always pretty. It has its eye-sores. It's all very well trying to add similar flaws to a map if they help with the atmosphere, but it doesn't work the other way. A surface that is blatantly a tiled texture sticks out like a sore thumb, so mask repeats either by choosing better textures, or through cunning use of shadows and stain decals. Anything to break up patterns without drawing attention is good.
Also consider the 'dirtiness' of the environment. If a wall is covered in dirt, rust or mud, the floor below it is likely to be pretty dirty as well. If a wall is rotten and broken, then make sure that adjacent surfaces match. There's nothing worse than seeing a really nice, shiny metal floor being supported by dull, dirty and broken wooden supports. Keep it all consistent.
This seems to be the more difficult bit these days. Getting the texture and geometry looking 'real' requires consideration about the architecture, lighting and even some structural engineering. For example, a corrugated-iron wall is not going to have a platform bolted onto it without some form of extra support. A thin brick wall is not going to be 50 metres high. A thin 10m plank of wood will not hold the weight of a human. All very simple things, but easy to forget when desperately looking for that elusive "perfect" texture. The thinking should be about real-world building materials rather than textures in a fantasy 3D world.
Special consideration should be given to the thickness of certain elements - a thick wall is unlikely to be made of solid wood, and a paper-thin wall won't be made of concrete or metal. What was acceptable in the Quake days is barely suitable for placeholders these days!
We also need to consider pairs of textures more than ever before - using the same texture on all sides of some structures can often look out of place (consider bricks, which really need two textures, one for the side and one for the top.) Again, times are a'changing... but that should have always been the case.
Performance often comes last, although it really shouldn't. That wonderfully shiny, reflective texture may look great, but it'll halve your FPS if you put it on everything. Similarly, getting tiling wrong (excessive use of scaling) can sometimes kill the framerate as bad as the reflection map...
...but unless your map is called cs_blingbling, that shouldn't bother you.