Technologically, the model of subsidizing consoles to put great hardware in your living room for not much money is a good one. You get ultra-low latency, games can work offline if need be, the experience is predictable and robust once installed and you can actually own the games. In practice it requires a willingness to commit to big expensive CDNs, download optimization, and smooth updates that Microsoft just don't seem willing to make. Streaming would appear to fix that regardless of other merits.
That said, I never looked at Stadia. We (me+fiancé) just don't play enough games to warrant changing anything right now, and we tend to pick the platform based on the game not the other way around.
You can also purchase games on your phone and send a request to your console to download and install it so that it's ready when you want to play.
You can buy a pretty great console for $500, and it'll last 5+ years. Well under $10/month, even if you buy a little late in the cycle.
I just don't see where any cloud gaming service has room to compete with that and actually turn a profit.
Precisely - as Steve Jobs observed, sometimes people don't know what they want until you show it to them.
Stadia does seem like a solution in search of a problem. For Microsoft a relevant problem is "How can we enable smartphone (or other non-console, non-PC computing device) gamers to play (and rent/pay for) Xbox games without porting them to/shrinking them onto Android or iOS and without cannibalizing the sales of Xbox consoles or Windows PCs?" But Google doesn't exactly have that problem.
Stadia does solve the problem of "how can I play (some) PC games on my phone/smart TV/PC without a great graphics card." So it does provide some clear user benefit, but it doesn't have compelling advantages over other cloud gaming services, and it doesn't seem to align directly with Google's business interests.