The reason to use it is if you have a repetitive task that you need to preform a large number of times. Like resizing 1000 pics, as PowerBookG4 suggested.
The only time I have tried to use it was to merge to Mac. I mounted my old Windows computer over the network, and wanted to take every mp3 on my old computer and see if it was in my iTunes library. If so, ignore, if not, add it to the library. Never actually got it to work because Automator was just too simplistic (no conditional statements that I could find, had to use AppleScript).