It's not for everyone, but people who are into encoding their own video and ripping their own HD content will find it incredibly useful.
My iMac is my TV in my room, I have an Elgato EyeTV as my DVR. Every night while I sleep it records and then encodes the Daily Show and Colbert into iPod format, puts it on my iPod, and I watch it at the gym. It does the same things with all the shows I watch, Lost, Heroes, Sarah Silverman, South Park, etc.
Every five days or so I run an automator script I wrote that backs up all my video onto an external harddrive and I don't even have to think about it.
What Apple TV would let me do is extend this same setup to my living room. I could go downstairs, fire up the Aquos, and have access to two years of TV and my entire movie collection in 480p (DVDs) and 1080p (when BluRay burners get to 4x dual layer and under $300).
For people who are as organized with their media and as computer centric as I am, and for people who don't want to spend the money on a dedicated media center, its a great option. Also, it has the potential to be really hackable, imagine what you could do with a $300 HD streamer with an internal hdd once you start messing with it.