IMHO there are basically three types of custom ROMS. "Lean" type ROMS, bare-vanilla ROMS, and highly-modified ROMS. Yes, my descriptors of the three types can overlap quite a bit, but I think it's a decent breakdown. I personally stick with the "lean" type ROM's anymore. For me, Vanilla is too plain, and highly modified is too much work.
"Lean" type ROMS are basically stock ROMS with a some or most of the added OEM manufacturer bloat (Samsung, HTC, etc) or vender bloat (AT&T, Verizon, etc) removed. These keep the flavor and best software of the manufacturer's original ROM while removing the worst offending software. I like these the best as since they are based around the original manufacturer's stuff, they tend to be very stable, while still allowing for cool manufacturer-specific hardware/software to work without issue (i.e. custom camera features).
Bare-vanilla type ROMS basically strip everything that was not originally written by Google away. Basically mimicking a Nexus type experience. Like the "lean" type ROM's the bare vanilla tends to also be stable, but you end up missing cool features that manufacturer's put in. So while the camera may work perfectly fine, the software to run the camera is not going to have interesting/useful manufacturer-specific features like particular manual controls over camera ISO settings and such. The missing manufacturer/vender software features can usually, but not always, replaced with an app that provides similar functionality. This type of ROM often appeals to people who prefer specific apps to do what they want and they don't want other stuff they never use wasting storage space. It also tends to feel faster and more responsive than lean ROM's on the same hardware as there is nothing fancy slowing user interaction down.
Highly-modified ROM's tend to have many custom features like custom kernels, launchers, color layouts, etc, etc. This type of ROM is very personally subjective in appeal to the person/group who uses it. Think Cyanogen type mod. As these are often very "tuned" to particular hardware, they can make your hardware scream and/or be incredibly beautiful, but vice-versa, if they are badly "tuned" stuff just might not work right and one man's beauty is another man's ugly. This type of ROM is more polarizing in that you either love it or hate it. These also tend to be more technically advanced and allow for more technical things like undervolting, overclocking, etc. While the other types of ROM's may allow for stuff like this, these highly modified ROM's tend to allow or already be tuned for particular customizations better. These ROM's are more geared towards people who are very experienced and like to experiment.
So for the average user, I think "lean" type ROM's are very worthwhile. This keeps the interface and software familiar while retaining the stability of the original without so much "bad junk". The other two types of ROM's, not so much for the normal person as one is too bare, while the other is too much work.