The introduction, hmm. I didn't think it was too bad, but let's see how they could have improved it.
I think one issue with it is it doesn't really give you a good overview of what Skyrim as a whole is like. It would've been nice if they had set it in Whiterun or... the city on the cliff. The cart in the beginning goes around some corners to see... more trees and hills, no real spectacular view.
Even just raising the road somewhat or removing a few trees at one point to give a view down one of the valleys Helgen is at the intersection of would have given a good view.
Also the town doesn't have any real relevance once the introduction is done with. As far as I'm aware (I haven't gone over the game finely) there are no quests which send you back there to look for survivors or salvage goods.
As far as I recall there is one quest that takes you through the ruins when you follow Barnabus, but that is more a matter of the town being on the route that it being of any real importance.
Other than that, you can find the child you see during the attack having gone to live with his grandfather in a hut up in the mountains, and the ruins get taken over by a bandit gang, but there is no further interaction. Oh, and you can go back and find the mead with juniper berries in the destroyed inn, drink is important!
It certainly could have stood to have had more done with it later in the game, and there are a few plothooks left in, but I suppose that, like the originally much more expanded civil war system, it was cut/never given the time to develop.
There are a few Helgen rebuilding mods around, although I haven't found one that I like enough to keep so far.