I'd been debating on the purchase of an Apple TV for quite some time. I have a pretty substantial media library that is already in mp4/H264 format viewable on the iPod or in iTunes on my PC.
I was happy with the option of connecting my iPod to the TV using a converter cable until recently. I just got tired of not having the ability to navigate the content or FF or REW from my couch without a VERY long converter cable. I was also starting to be unhappy with the picture quality on my 57" HDTV.
The decision was made for me when I received a 40GB AppleTV for xmas. I used it "as-is" for about the first 2 weeks while investigating the different ways to mod or hack it. There is a ton of information out there with quite a few newbie guides. I've also got the added convenience of working for a company supporting numerous MacBook Pro's that I've got access to, so creating the "patchstick" to enable the extra functionality was a breeze. This also made my decision to hack it easier since I could always restore it to factory state if anything got bunged up. If you don't have access to an Intel-based mac, the hacks are a little bit more challenging, but still possible.
Now, I've got a 250GB capacity AppleTV (thanks
awkwardtv.org and
appletvhacks.net) due to the USB drive hack plus the ability to play divx and avi files using Perian and Sapphire. I also have the ability to sync a large portion of my music/tv show/movie library thanks to the upgraded drive.
The added bonus to the AppleTV is that my wife and daughter can pick up the remote, navigate to the content they want to see and it just works. No having to constantly fiddle with it to get the functionality they want (which is pretty much plug and play). No having to explain to them how to use the device. It's very intuitive, but has the ability to be geeky enough for me to be happy to play with it outside of it's intended use.
I'm the technogeek in my house, not my wife so she was sold on how easy and convenient it was to just be able to ask me to sync a particular movie or TV show to the Apple TV and she could sit down and watch it with little to no delay.
As has been stated before, unless you are OK with using iTunes pretty much exclusively for your media content, an Apple TV out of the box is not the best choice for a media center device. but if you don't mind getting your hands a little dirty and are reasonably tech proficient, and willing to research it, hacking it is one of the easier things to do. If you don't have an HDTV or at minimum a TV with composite inputs, it may also not be the best solution.
I just recently convinced a buddy to consider an AppleTV as a media center for his newly purchased 42" LCD HDTV. He was looking at building himself a media center pc running XP Media Center edition and could not find a solution that he was happy with for less than $1K. When I showed him what I'd done with my AppleTV and how it does pretty much everything he was looking for, he immediately started shopping for one and has made me promise to help him hack it once he gets it.