If there's one Skyrim mod that best stands as an example of what is possible to achieve with a total overhaul, it would easily be Enderal.The mod presents an entirely new world with its own characters, lore, races, and history completely separate from The Elder Scrolls universe, even featuring all-new voice acting.
If you're willing to sink a lot of time into a follower mod, Follower Live Package is by far the best, but it's complex enough that it's not going to be worth your time for most users.
