To me Ducimius' original "flavoured to taste" kind of describes it - it's all about customizing the game to what you want it to be like. Sometimes other people are kind enough to do it for you, and that's great. But sometimes you just run up against something that noone's really changed and it absolutely drives you nuts, so you have to do it yourself - and when you're done, why not share it with others?
So I guess it's a combination of wanting things your way and the spirit of sharing. The former is usually just a selfish drive stemming from the game not working the way you want it, but the later is really something that's facilitated by a great community. And often sharing with a responsive community starts becoming an end in itself, and that's where modders generally get crazy and start modding instead of actually playing

And that's usually where the mix of booze and caffeine comes in to sustain them, along with a horde of hungry fans demanding the next beta. Very unhealthy if you ask me