I'm the typical user that you mention at the beginning of your poster. I do some fader automation to compensate for inconsistent levels in a track before compressing. It's very helpful when comping vocals or guitars (or whatever) from multiple takes, if you weren't careful to track the different takes at the same level going in (I'm getting better about that). I also use it for volume fade ins and outs. For example, if you've got a clip in your mix that starts or ends abruptly, automation will smooth it out and help it fit better.
One example of the latter is when building up background vocals from multiple takes. One of those takes might start or end a little earlier than others. You could use something like Reaper's stretch markers to adjust the length of the take, but you are also changing its playback speed (usually not noticeable). An alternative is just to clip ends of the take so that it starts and ends at the same time as the other ones. Then use volume automation to smooth out the clipped ends.
I haven't gotten into the more creative uses of automation, personally. I would automate if I actually wanted to hear a parameter changing in real time, as in the EQ transitions and panning effects you mention. If different parts of a track need different basic EQ settings, an alternative would be to mult the parts out to separate tracks and apply the correct EQ to each track. For me that is simpler and more intuitive than automating. It's also easier to see at a glance what I've done if I return to the project some time later.
One example of the latter is when building up background vocals from multiple takes. One of those takes might start or end a little earlier than others. You could use something like Reaper's stretch markers to adjust the length of the take, but you are also changing its playback speed (usually not noticeable). An alternative is just to clip ends of the take so that it starts and ends at the same time as the other ones. Then use volume automation to smooth out the clipped ends.
I haven't gotten into the more creative uses of automation, personally. I would automate if I actually wanted to hear a parameter changing in real time, as in the EQ transitions and panning effects you mention. If different parts of a track need different basic EQ settings, an alternative would be to mult the parts out to separate tracks and apply the correct EQ to each track. For me that is simpler and more intuitive than automating. It's also easier to see at a glance what I've done if I return to the project some time later.