If it's overdone, or not blended well, it doesn't look great at all. That said, there are varying degrees of it, depending on how much one wants to get into. And it can help put cheekbones where there weren't much of anything before. Yes, it DOES make a difference defining the planes of ones face, and makes one look better in photos too. Otherwise, the application of just foundation tends to flatten out the face while it's evening out that skin tone. Since I already have higher cheekbones, I just do an informal version of it with just a little bit of light bronzer swept on in the "3"-shape(from above outer eyebrows, under cheekbones and a bit on jawline. Then for the lighter part, I just use my lighter concealer in the "triangle of light" application with the outer part swept up past the outer part of eye. I know this sounds like a lot while written, but it actually goes very fast and looks very natural as the tones I have for both products are in keeping with my own skin tone. The key to any kind of contouring, like any other makeup application, is blending everything well, and not going overboard with the contrast. There are a bajillion "contouring kits" out there right now but I've found just my light bronzer and lighter concealer combo to do the job just fine without buying more stuff. Again, everyone's face is different and what one wants to get into. Another way is just to highlight and not even worry about the deeper shading color at all - this is where the newer "strobing sticks" come in(lighter, usually luminous product). For everyday, just a light touch with this goes a long way, if one gets into it at all. Evening, or for performers, is a different matter.
Hope this helps!:)