I was also wondering. There are even people who modify the beginning of the game, so that it doesn't begin in Helgen but somewhere else: no Alduin, no main quest...
Yep, entirely the point. So far I've seen several posts that entirely do not understand modding, so I'm going to throw a few ideas out there.
First, your example. I really enjoyed the start of the game. I've done it 3 times. And now I'm just about done with that part. 2 or 3 years from now, when I've picked up the game again (after taking a break for a year or so), I might want to redo the introduction. But the next few times I play Skyrim, I don't need it, I don't want it, it's turning into an annoying scripted, no-control half hour of my life that I want back. Mods can fix that. How is that not a good thing? (BTW, mods being what they are, eventually there will be options to skip the intro and still do the MQ, or to skip the intro and MQ entirely).
Second, random other examples. Mods can bring the graphics up to 2012 standards or beyond. Mods (will) fix thousands of bugs that BGS will never address. Mods will be built by people with actual experience in medieval combat to enhance the combat experience. Mods may be built by people so that it's possible to really play a pacifist character if that's what you want.
Mod will add hundreds of new armors and weapons to the game. Many will be atrocious, some will be better than anything in the vanilla game.
There will be literally tens of thousands of mods, covering everything that you can imagine and more. Many will be sloppy and poor quality, hundreds, maybe even a few thousand will be unbelievably professional.
I understand that occasionally a mod will come out that fixes something so fundamental that you wonder why BGS didn't do it in the first place, but a lot of it comes down to personal taste. And BGS has time and money constraints that aren't shared by tens of thousands of obsessed modders.