I've used Automator to create several different Finder contextual menu items to perform tasks that would usually require multiple steps. For example, I like using iPhoto, but it doesn't allow changing the file name when importing. So, I used Automator to create a Finder plug-in that imports the photos from the camera, prompts me to batch rename the photos in a numbered sequence and subsequently imports them into iPhoto.
I have created several other Finder plug-ins. Basically, Automator is an easy-to-use, graphical scripting interface. You can use it to, well, uh, you know, like, automate stuff. Hence, the name.
Whether it will be useful to you depends on what you use your computer for. If all you do is, say, surf and send emails, then Automator will likely be of little use to you.