There are some fun things you can do with it like terminal image viewing, etc. The notcurses library uses it, some of their demo videos are really neat.
I think it was necroed by a Japanese Twitter addict(@tsutsuii) to display Twitter user icons without X on NetBSD/luna68k, then others started doing all sorts of unholy things with it from w3m with images to terminal-based RDP client.
There aren't a lot of "legitimate" use cases for now, I believe, but it's neat, and IMO an image mode extension for tty kind of makes it whole.
Are Sixel images actually used very often these days? Is support in Windows Console a big deal, or just lots of fun?