Several times we've had Reincarnate come into play. One story is below after a list of ways it's impacted our games:
1. It's cheaper than Raise spells and available earlier.
2. It's a great wilderness option if a character dies away from civilization.
3. It doesn't always make the player happy, but the randomness often makes for memorable stories.
It also was originally designed as a spell that challenged your creativity. In AD&D, animals were on the table. Per the original: DM's Advice: Regardless of the form, you should allow the reincarnated character to continue to advance. For instance, a badger might grow to giant size, gain maximum hit points, gain bonus hp for high Constitution, and regain the Intelligence of its former form. A centaur might gain additional hit dice, and could use armor and magic items.
On the other hand, one need not play a new race for a memorable story. We had a Ranger (older edition) with "kobolds" as her favored enemy who was reincarnated as one. She declared she was committing suicide rather than live a life as a vile kobold. When the party tried again, she rolled kobold again. After that, she declined any more attempts.
"1. It's cheaper than Raise spells and available earlier."
Both are 5th level spells and was there ever a time when druids got reincarnate before clerics got raise dead - level-wise? As for 500gp by 9th level that is not a significant cost in most campaign expectations i have encountered.*
"2. It's a great wilderness option if a character dies away from civilization."
Raise dead works just fine in the wilderness. there is no requirement for getting to a city.*
"3. It doesn't always make the player happy, but the randomness often makes for memorable stories."
things that make players happy can have the makings of great stories too.*
*Obviously campaign specific setting elements and house rules can change any of these.
making these points because, when analyzing a given spell, the actual facts about what it does as opposed to others also matter. If reincarnate was a 4th level spell druid option, i would say that was a major part of a balance trade-off between the things. As it stands, you are trading 500g for odds are a lowering of "what i had chosen to play."
As for story, GMs and players can make good or great stories out of anything that they come across - there is nothing particularly "good story provoking" about randomly re-assigning character elements. if there were existing stories in play, they may be even more hindered than helped.
As for the GM advice, any basic game element that heads directly into "you are on your own as to how to resolve this" is not promoting creativity any more than say "elves use longswords" and then not giving stats for longswords would or leaving out what damage a fireball would do.
personally, i would like to think that the memorable parts of "my character's story" was not that "the gods kept making him a kobold so he gave up."
But, that said, we each have our preferences. thats why there are more than 51 flavors.