My own instinct is to look at every spell as though it was a technology in our world.
If it would be something that the army/police would have access to for their use of protecting the populace, then it'd be regulated (and thus uncommon / rare). So most direct-damage spells would not be accessible to most people because you'd need to be licences to control such magic. Also, anything that would restrict the functionality of another person would also be locked up. So Charm Person? No way... that would be restricted and made Very Rare because society couldn't function if mind-control was in the hands of the entire society.
Any spell you'd look at and say "Any functioning society that had this capability for the betterment of the people would make this magic as accessible and used as much as possible." Looking at it from the perspective of the Eberron dragonmarked houses would be a good first thought. So spells that helped a person or kept them safe would made as Common as possible. Cantrips like Mending would be found all over because of how useful it is. Comprehend Languages would be made accessible to everyone. Create or Destroy Water, Feather Fall, Cure Wounds, Purify Food and Drink, Speak With Animals, Unseen Servant... these are all magics that once society learned how to do them... they'd proliferate its instruction and distribution until everybody had it.
Once the Romans built the first aqueduct to distribute fresh water to their people, they didn't cordon off this capability and only keep it in the hands of the few... they made the water accessible to everyone. Plus... they then continued their scientific work to improve the technology to make it better, faster, safer, cleaner, and even more distributable.
Which makes the idea in D&D that magic is only in the hands of a select few wizards and is not readably available to the whole of society really a dumb trope in my opinion. Because magic is like technology... once its application is learned and people realize just how much better it is to have... society will start teaching everyone how to use it and how to advance it... at the very minimum to make money off of if nothing else. This is one of the things that Eberron really got right.