Mods can add many hours of gameplay and create much more immersion.
Though, most of the major mods, i.e. New factions, quests, characters, weapons, creatures, etc. have many bugs, script issues, etc. and can totally bring framerate down or cause other countless issues that negatively impact your game. After several major mods, I found myself uninstalling them as they were more shallow than described and frequently caused some sort of gameplay bug or issues. Not worth it, for the most part.
I found the graphic enhancing and gameplay tweaking mods are the most valuable and most used when I played MW and Oblivion, and even other games such as FO and CS.
Let me say just one thing :
FCOM for Oblivion.
3+ major overhaul mods combined into one. Modders are also [censored]in awesome. There are mods for Skyrim that are updated DAILY, sometimes skipping a day as a break. You want an example? Tytanis (Skyrim Mod). Sure, there will be lots of bugs but they ought to be fixed soon, those modders are quick and make Bethesda look like a flimsy turtle. Modders often make their mods compatible with other mods, so that the user can get much more content without sacrificing anything. Those "many bugs" can only come by one thing : carelessness. Be cautious not to download incompatible mods, read the comments before you download a mod and also don't install like 200+ mods at once. Do it like this :
i) Install a mod.
ii) Play the game for an hour so to be sure it's working correctly, doesn't crash the game, it's compatible with other mods, etc.
iii) If you have a positive result, keep it. If not, you'll just have to disable it with the push of a button

hope that's not much to ask

iv) Enjoy the masterpieces.