I think about unit tests being useful for getting more confidence that some deterministic, pure (mathematically speaking) and stateless piece of code that's data in data out actually works, particularly when you change it.

If any of those conditions doesn't hold the cost/benefit certainly and even sometimes the absolute utility goes way down.

If I have to mock anything, in particular, or more generally care at all about any implementation details (ie side effects) then I just think might as well make this a full on automated functional test then.

As soon as fake code is introduced into the test its utility rapidly decays in time as the things it fakes themselves change.