I think you might be going a bit deep on the tier quantity. I'd prefer just having, like, five or so tiers, and each tier is just clearly better than the one above it, with little to no extra definition having. My metric would largely skip problem solving, and instead assess how often I want to prepare it. Like, primal instinct just offers a decent buff, to the extent that it might hit tier three or four by your classification, but I'd still toss it into tier two because it's so efficient for its level.

A spell based same game test makes little sense, because it assumes spells ought to be proactive. A commoner with heart of water is basically just a commoner, but that's still an amazing spell. That the spell is at-will also makes little sense, as that just disregards the fact that duration matters.

I dunno. I kinda just wanna start tiering near arbitrarily. Maybe even somewhat poorly. There are so many spells. I'll do, let's say, spells that start with an A in the SRD until I get bored. I'll toss them in five tiers, cause that matches up with a typical handbook ranking system for spells.

Acid arrow: Four. It's not entirely useless, but the damage output is pretty low, and damage output in general is pretty weak.

Acid fog: Three. The level cost renders this something of a downgrade to solid fog, which I'd put at tier two.

Acid Splash: Five. With combos, this can do some things, but in a vacuum it's not really want you want your cantrips to be doing. Why are you spending an action to deal this little damage, y'know?

Aid: Four. Seems decent but marginal. Bit below replacement rate, I think, though these cleric spells are where I'm most likely to be wrong.

Air walk: Three? I dunno, how critical is this? It's a pretty middling flight method, but if it's just the cleric flight method then it might get a two.

Alarm: Three. Decent defensive spell for the level, but access to rope trick right after this renders it a bit redundant.

Align weapon: Five. Super situational and kinda weak.

Alter self: One. It's a defining spell for the tier.

Analyze dweomer: Four. Seems like a relatively low ranking divination effect, with the focus cost being a bit prohibitive here.

Animal growth: Two. One of the stronger buff spells out there. Well, on a druid anyway. Lot weaker on a wizard and still quite a bit weaker on a ranger.

Animal messenger: Four. I feel like you can do a lot better for message oriented spells. This has so many limitations.

Animal shapes: Four. So high level, and with such limited utility. It does have a nice duration and target quantity though. Gotta be worth something.

Animal trance: Five. So situational, and it doesn't do that much in those situations.

Animate dead: Two. Very strong, and central to some strategies, but not quite broken.

That seems like a good place to stop. This project is prohibitively massive, I gotta say.