Yet undoubtedly I have met many at least otherwise smart people who are big fans of unit testing even if they cannot quite define what a unit is.
What am I missing, if anything?
IMO both are to be used together...unit testing for increasing development speed by catching errors earlier, end-to-end testing for making sure the full workflow works
the failure mode is writing so many unit tests that you actually slow yourself down maintaining a bunch of tests that barely ever increase your total velocity
By automatically mutating application code, the quality of the test suite itself can be evaluated: for example, a method is mutated to return null--and tests still pass! That could indicate a real bug or nothing.
End-to-end (e2e) is ideal to really exercise everything, but they can take a long time to finish. The feedback cycle is an eternity compared to unit tests.
One way to improve e2e is to have an agent on the app server and an agent on the test server, building a probabilistic model of what e2e tests to actually run; then it's a real time-saver to only execute those tests. At least one company offers this.
In the end, it's preferable to have both unit tests for microservices and e2e tests that run against one or more browsers.
Ideally, you'd test everything via the public interface (be it an API, a UI, or an SDK.) That would make it so you only change your tests when the user-facing behavior changes.
But your public interfaces are typically doing slow, non-deterministic things (e.g., calling external services.) Therefore, they aren't easy to test. Some people fix this by testing individual classes. It's better (but harder) to isolate slow/non-deterministic dependencies to small services that can be faked/stubbed out. Then you can have your cake and eat it too: Your tests are fast/deterministic, and they can still be end-to-end (and therefore not need to be updated whenever internals change.)
Your intuition is leading you in the right direction. There are ways to write "end to end" tests that have all the benefits of "unit" tests. The (free) book "Software Engineering at Google" has chapters on testing that describe the techniques better than I can. (They reach the same conclusion about how testing internals ends up being less useful than testing the public interface, and then they explain techniques/tradeoffs.)