It'd good for things like OpenOffice.org, Gaim, Firefox etc. which are fairly standalone desktop apps that are updated frequently. Most distros will only backport security fixes into these apps, so for example if your distro shipped with OpenOffice 1.1 you're usually stuck with that until there's another release of the distro, whereas if OO.org made an autopackage it would be easy to install the latest release. Its not a replacement for RPMs, DEBs, ebuilds and the package managers (yum/urpmi/yast, apt, portage) that go with them but it could be very useful.
Check out the Gaim autopackage:
http://gaim.sourceforge.net/downloads.php