I mean new areas to explore, new enemies, quests, areas, dungeons, loot, weapons, etc.?

This is what I'm wondering...

I haven't seen anything along the line of ooo or mmm yet. Wars in skyrim is maybe the closest and there're tons of weapons mods. Still hoping even though I have yet to finish the actual game.

Just go to the Nexus and look. That's what I always do. It also helps to subscribe to youtube channels that do mod reviews. Those can be really helpful. Gamespot is doing a weekly top 5. One of those this week is a mod of a Khajiit homeland area that looks pretty big. It has some new creatures, a really big treehouse type area with vendors, and an area to explore that has enemies in it. Not sure how big it is but it looked pretty cool.

There is some guy working a black marsh like area if i recall correctly.

Not found anything huge but there are a few WIP one's that have potential and quite a few smaller ones that are really good. All on the workshop ofc.

Jaysus Swords, Dovahkiin Hideout, Tytanis' gobbledigook of miscellaneous c-list mods, amd various things like dynamic depth of field, more animations, ingame shader tweaking.

they may not seem like much separate but they add up, (Although I wouldn't use tytanis)

And also go to the mods forum and take a look around there. There is a mods forum here where these things are discussed.

Such large mods take time to make, and the CK's navmesh bug and the maximum gridsize limitation (unless they've been fixed?) add additional diffculty for new landscape and landscape altering mods.