Almost every first party franchise was bigger on the NES than on the SNES. Mario, Zelda, Metroid, Punch Out, etc... were all more popular on the NES (i.e. sold better). Mother only made it out of Japan on the SNES, but it had disappointing sales. The notable first party games on the SNES were either bigger on the NES or brand new series (Mario Kart, DKC).
Third party games are a different story though. Third party games often were more successful on the SNES. Final Fantasy is a good example. (Although PS1 Final Fantasy was far more popular than SNES Final Fantasy.)
I know what you're saying and you are right in terms of sales and commercial success and a lot of this is because the SNES, in general, was a significantly less successful console compared to the NES due to the competition from Sega.
That being said, if you forget about sales for a moment and ask people about the greatest old-school RPG's of all-time (at least in North America) lots of people are going to give you Earthbound, Final Fantasy 6/3, and Chrono Trigger in their top 5. Games like Final Fantasy and Mother 1 (which obviously wasn't localized) will be nowhere on that list. Even games like Metroid which were for sure very popular on the NES was significantly eclipsed by Super Metroid in terms of adoration from fans, at least today. I can't think of many people who would tell you in 2021 that they like Metroid more than Super Metroid. In fact, in terms of nostalgia, the Super Nintendo seems to eclipse the NES in general. Maybe this is because of the generation gap where most Millennials are too young to remember the NES days and that is most of who still play games today while most Gen X'ers and Boomers moved on from gaming when they got older and never came back. For some reason, when you talk about nostalgia it is the 16 bit era that comes to most gamers mind, the 8-bit era seems to be shrouded in obscurity except for some stand out classics like Super Mario Bros, Duck Hunt and Tetris.
I am actually going back to play 8-bit RPG classics like Might & Magic, Ultima, and Uncharted Waters. Most people today do not even know what these franchises are and you literally never hear them spoken about (which is what makes me so interested in discovering them). It just seems like so much has been forgotten from the NES era. In the minds of a lot of gamers today, the SNES was really the beginning for a lot of Nintendo's most beloved franchises. That was what I was trying to get at with that statement, although I should have been more clear that I wasn't thinking strictly in terms of sales.
When it comes to RPG's, I agree with you 100%. Back in 1990, Final Fantasy 1 was in my top 3 NES games. Today, I still like the game, but it might be my #10 NES game (at best). One reason a game "ages" is when later games obviously surpass it. The SNES had much better RPG's in general (and so did the PS1).
However, when it comes to Metroid I can't agree at all. I definitely think Metroid is a much better game that Super Metroid. Super Metroid is too easy. I can also find other people like myself, who prefer Metroid, but they all played Metroid back in the 80's. Super Metroid is preferred by people who never played the first Metroid when it was a current game. Not only is it easier with better graphics, but both games are so similar. Super Metroid really feels like a remake of original Metroid with better graphics and easier gameplay. It doesn't really feel like a sequel to me. Whichever game you play first, that is the one you will tend to prefer, because they are both pretty similar.
If a person is too young to remember the NES, then they will probably prefer the SNES, because that was the last real 2D generation. 16-bit graphics are the best 2D graphics get with a few exceptions like Castlevania: SotN. People judge a book by it's cover. The graphics on NES games look dated, even though the gameplay is often phenomenal (at least on the best games).
Aside: since you mentioned Ultima, I just want to say that I want to replay Ultima 4 again soon. It was also in my top 3 NES games back in 1990, but I haven't played it since. Hopefully it still holds up. I think it probably will, I don't think there is still another RPG quite like it. (Maybe Octopath Traveler comes the closest?)