http://skyrim.nexusmods.com/
Arrow mods, armor mods (I suggest "Immersive Armor" and "Immersive Weapons"
, Magic mods (Apocalypse or Midas Spells), Realism mods (Frostfall [for environmental survivalism] or Immersive Potions [potions heal over time]), et cetera. "Dance of Death" is good (adds killmove options), "Cities Enhanced" as mentioned above,"Enhanced Flora", "Sounds of Skyrim" for more diverse sounds...
Oh, and if you were unsatisfied with the "War" storyline and how it doesn't really feel like there's a war, try "Hardcore Economy" to simulate a wartime economy, and "WARZONES: Civil Unrest". WARZONES adds a couple thousand new NPCs, plots, skirmishes, battles, and warzones to the game. Nothing worse than travelling the roads as a Stormcloak and stumbling upon a massive battlefield between the Imperials and a Bandit army; arrows and spells flying, the screams of the dying, the clashing of steel, knowing that once they're all finished killing each other, you'll be next.
The great thing about the Nexus is that on the page for most of the popular mods, you'll find a group of other similar or like-minded mods. You follow the links to those other mods, and they lead you deeper and deeper into the modding community.
I hope that's a mite helpful for anyone interested.