Warning: my comment only addresses my use case for automation. I don't use cloud services either. I also kinda just kept writing, so this is a bit of a text wall.

I live in an old house. 80% of the lights in my house are operated by walking up to them and twisting the stem. The remaining 20% are switched.

To properly turn the lights on in my living room, I have to visit four separate lamps and turn each one on. The dining room has three lamps, bedroom has three, office has three, etc. When it's time for bed, I have to walk around the house turning each lamp off. If I want them dim, no luck. To do that would require either all new lamp fixtures, or rewiring the house with new dimmer switches.

Or, that was how it was before I did the Zigbee/HomeAssistant thing. Now I just hit that master switch on my nightstand and all the lights turn off. My whole house changes into "Night Mode". The thermostat will widen the setpoints. The doors lock if they weren't already. If I happen to get up at 3AM to take a piss or get a glass of water, the lights all know to come on at minimum brightness, and to turn off shortly thereafter.

My front door lock used to be a pain in the ass when I had my hands full of groceries. Or my coffee and the mail. Now my door unlocks automatically when I walk up to it. It's a small joy, but it reliably makes me smile each time. (And I don't have an ugly keypad, and still have a standard key slot if I need it).

I have an ancient stove and oven. No electronics at all. So I wrote a simple automation to alert me if the kitchen motion sensor's temperature rises 10°F more than the rest of the house, for longer than 30 minutes. This has saved me a couple of times now when I forgot to turn the oven off. (It takes a good hour for that temp sensor to reach the threshold as well. I wrote that automation after discovering that my oven had been on for hours. When I looked through the temp logs, I saw a clear signal I could use in the future.)

I also put a remote temp sensor in one of my HVAC registers. Comparing its reading to the ambient reading gives me a ΔT on my air conditioner, and a couple years ago the steadily-declining value of that delta alerted me to a refrigerant leak weeks before it would have been large enough to notice otherwise. I was able to get that repaired in the spring rather than in the heat of summer. This isn't something I would have done with a regular thermometer; having to remember to check it every so often and do the math taking into account the humidity and the elapsed time since the start of the cycle. But seeing all that temp data logged over many weeks makes the pattern easy to spot.

In the den I sometimes want it to be bright enough to read or do detailed work, and other times I want it dim so there's no glare while watching TV. Before, that meant I would have to buy lamps with a dimmer on them, then dim each one and go flip the ceiling fan light off. Now when I click the switch[1] to turn the TV/stereo combo on, it automatically dims the lights at either end of the couch, and turns the overhead light off.

Color temperature! That's another thing that isn't possible without some smartness in the bulbs. At night my whole house is as close to 2200K as possible. I really like that kind of light. But in the middle of the day, my kitchen lights are closer to 3300K.

My porch light turns on 30 minutes after sunset and off before sunrise. It's under a roof so I would have needed to either replace the switch with one of those fancy ones, or installed a photocell somewhere else. But it was just a couple automations added to the config file to get that functionality.

[1] I originally put a Tasmota wall relay in to save the 20W (!) of idle power my old stereo receiver was constantly drawing. When I realized I always fiddled with the lights whenever I turn the TV/stereo on, I just automated that away.