For my money it was the 90s explosion of fighting games or else the rhythm games of the turn of the century. But hey I'm biased by my actual experiences just like everyone else is.
That plus consoles and PCs began to have the same graphics capabilities as arcade games at a reasonable price. No sense in pumping endless quarters into a machine you have to beg your parents to take you to when they can just as easily buy the console version to play at home.
Rhythm games like DDR, Para Para, or Bemani are something else entirely. There's still no great way to play those at home without a bunch of special hardware.
Although I'm not sure I agree 100% with your last comment. Many of my friends bought DDR home versions and Guitar Hero/Rock Band accessories aplenty when they were popular. A lot of folks were certainly willing to shell out for the extra hardware.
Check out Jersey Jack Pinball, Spooky Pinball and American Pinball.
We often got to play after hours for free, and Street fighter and mortal combat were favourites.
Most importantly, arcade games by their nature are finite. The limits of whatever storage medium used as well as the inability to rewrite or update them easily means that they exist in a vacuum. Once you've 'explored' the levels or found the 'secrets' they cease to be new. There are no more secrets. The strategy to beat single-player games never changes.
However the person you're playing against in a fighting game is always going to be different. Even if they play the same character as another person they will play them differently. They will have different skill levels and unique weak points to attack. No round of a fighting game is ever exactly the same. This is why fighting games are beautiful. They turn a static game into something timeless. There are still weekly Street Fighter 2 tournaments all over the world. When was the last time you competed with tens of other friends in say, Qbert 25+ years later?
I do enjoy some games of this type like Robotron that have enough variation in the random patterns of the enemies to still feel fresh. I also love games like Pole Position or Outrun that have well-defined endings and force you to play 'perfectly' like you describe. However most games of this earlier time period are unfortunately just not designed that way.
And it's not as simple as just 'memorizing' the combos if you never play well enough to get yourself in a position to land them. There's still a journey there both outside the game to learn and inside the game to break down the patterns in your opponent's play that will never be present in a single-player game. That's what keeps fighting games interesting and new 20+ years later for me.
The skill ceiling rising was mostly due to consoles, and I'd put it during the dreamcast/psone era when fighters started becoming really intricate.
Then the first color game I saw was Cosmic Alien. I dropped a lot of quarters down that hole.