Here's my very simple, "how I think about it" non-technical view, FWIW
With film cameras, we used to have only two usable variables: aperture and shutter speed, with the third, film speed, locked in for whatever film you had in the camera. Once cameras had some reasonable electronics you could let the camera decide both (Program), fix one or the other (Av, Tv), or manage both (Manual). Of course, on Manual you had to take care of the exposure.
Now all three variables are available to us, so now we can let the camera do all three (Program/Auto mode and Auto ISO), or any combination of fixing one, two or all three of the variables.
Each variable has it's own characteristics (like the ones for ISO that have been well described in this thread), so it's really just a case of deciding which ones matter most for the image that I am trying to make (and which compromises I'm prepared to make), and letting the camera take care of the rest - or telling me if it can't!. Or controlling all three, of course.
I'm sure the details vary from camera to camera, but that's my idiot's guide to me.
