Battle wise, Cait Sith isn't that good. His Limits frankly suck (yes, Game Over is awesome, but it's also very rare, and Slots can kill you too). Since stats don't matter that much, I won't go over them here. True, he makes a good manipulator, since the Hypno Crown seems tailor made for the kitty (he wears a crown anyway, and he comes with the Manipulate Materia, just put 2 and 2 together).

Story and character wise, he's great though. Yeah, until you find out he's Reeve, you don't know that much about him, but after that you find that you know quite a bit about him. He is also the only character who is willing to stand up to Barret and tell him to his face why his terrorist actions were wrong (on board the Highwind, right before they go into the Northern Crater). He will do what he has to in order to help out and do his job, even things he finds morally incorrect, like taking Marlene hostage.

So, he's not a great fighter. But he is a fun character.