Before purchasing Lightroom, I tried to learn what about it makes it so great. The answers I found were totally unsatisfying. Lightroom is generally described as a tool that "improves your whole workflow", from organizing to editing and publishing your photos. That's all true, but a statement like that doesn't tell you how it does any of those things.
After using it for a while, here's what I think makes it so amazing.
The main reason has got to be that it's actually designed for photography. As a result, it is far more intuitive to use than Photoshop, while still giving you most of the power of Photoshop.
The reason for this is that Photoshop is designed for more than just adding saturation and contrast. It's designed for photoshopping images. As someone described it on flickr, "You need Photoshop if you're going to paste in unicorns and rainbows"--you can't do that in Lightroom. Photoshop is for "compositing" images.
Lightroom also does some of the same things that you typically need Photoshop for, but with a much more intuitive interface. For example, one of the most important features of Photoshop over a simpler editor like Picasa is that it allows you to make adjustments to specific parts of an image. You can lighten someone's face, for example, or darken the background. You do this using layers and masks, and only Photoshop and the GIMP have support for layers and masks. While powerful, managing the layers and masks can be cumbersome, and it requires thinking of your image as a stack of layers, which isn't always intuitive.
Lightroom simplifies this with one of its coolest features, a tool called the adjustment brush. The adjustment brush allows you to literally paint an adjustment on to different parts of your image. This is a far more intuitive and manageable approach, and the interface they've created for it is brilliant. Take a look at this video tutorial on the brush, and I think you'll see why this is so exciting.
As for "improving your workflow", Lightroom has a very interesting approach to managing your photos that I really like. When you make adjustments to your photos in Lightroom, it does not actually make any changes to the original image. Instead, all of the adjustments you make to any of your photos are stored in a single database file. When you're ready to print or publish an image to the web, you export it and the adjustments are applied. This way, you don't end up with a bunch of copies of the same image--for example, the original image, your Photoshop or GIMP image which stores your edits, and the final image. Lightroom manages this for you, so you don't have to come up with some clever file naming and folder scheme to keep it all organized.
One of the greatest things you can say about Lightroom is that it costs less than half as much as Photoshop, and it may be all you need for Photography. With the power of the adjustment brush in Lightroom, there are far less things that you need Photoshop for. And if you ever do really need to do something in Photoshop, there's always the GIMP out there for free. I've also heard the argument that many of the features that you need Photoshop for, such as HDR, panorama stitching, and noise reduction, are done better by other smaller and cheaper pieces of independent software.
This video tutorial shows how Photoshop integrates with Lightroom, and in the process, he talks a bit about what you still use Photoshop for. The edit he does is pretty interesting--he uses the "warp" tool to basically slim down a model's hips. It's kind of scary to think that they can do that so easily!
If you're impressed by what you see of Lightroom, you should give it a try. Adobe offers a fully-functional 30-day trial of Lightroom on their website. Give it a spin and fall in love!