Now, these days i haven't seen much in terms of MODDING communities blowing the original game clear out of the water. BUT I did see large MOD projects that aim at balancing a game that was originally strategically impaired which made the original game completely unplayable but since it was a fantastic inspiring game to begin with, it was almost a must to have the mod. ---TALKING ABOUT HOMEWORLD 2 HERE.
But in terms of action shooters,3rd persons or first..there really is no real need for it unless there is something TERRIBLY WRONG or unforgiving about the original release.
Or perhaps, its things that didn't quite get developed on time and made it out as a Patch.