It makes it possible to play modern AAA games on devices where it previously was impossible.
That's a dramatic difference. There are plenty of incredible gaming experiences you can have on low-powered hardware, without needing a clunky streaming-as-a-service infrastructure to back them up. They just won't be the latest FIFA/Elder Scrolls/Call of Battlefield: Modern Duty.
Honestly, I tend to think that having environments that don't have full fat AAA games available is good for the gaming market as a whole. It opens up markets for indie developers and new formats. I'm thinking of the Wii and Switch for comparison-- if they had been built with the same spec hardware as their competitors, they would have been flooded with ports of the big multi-platform franchises, rather than the unique experiences they're known for.
Sonme of my favourite gaming experiences include playing Zangband on an already-obsolete Pentium II in 2003, and Cave Story on a pre-Atom netbook starring a 900MHz Celeron-M.
The real opportunity is for games where latency isn't really important. Beautiful turn based games, cinematic choose-your-own adventures.
The second path is for games to be developed with appropriate tolerances & latency-forgiving mechanics. Take a modern platformer like Super Meat Boy or Celeste, where you are given a window of frames in which you can jump/react before failure. Even modern games like Mario Odyssey accommodate input latency of wireless controllers/HDMI displays. Similar accomodations can and will be made for games targeting streaming services.
I disagree, in this age of phones/tablets, I'm pretty sure total amount of time played on these devices is greater than consoles/PCs.
That said, I don't need 4K HDR 60fps <10ms ping (nor should any reasonable person expect this from a streaming service).
The fact is that Stadia makes it possible to play games on devices where it previously was impossible is pretty compelling. (even if it's at 720p).