The S.T.A.L.K.E.R. games have always been some of the major go-to examples for showing how much of a positive impact modding can have. You definitely can't go wrong with the Complete series of mods, which streamline the overhaul process.
The latest Complete package, this time for Call of Pripyat, is now being offered as a free download. The focus has always been on improving the visuals, audio, and atmosphere (arguably the reason to play these titles) without revamping elements just for the sake of it.
Now that the time is right, I need to go get me some CoP.