I'm in no position to answer this because I only have Ambitions, but Pets is the only other one that appeals. I'd only want Generations for the spiral staircase. I'm not too bothered about extra family stuff because I'm just anxious for my sims to get to an age where they're useful. I have WA for iPhone, which I know isn't the same as having it for real, but what I get from that doesn't really appeal to me. Still, it's lame that my sims never get a break from work besides the weekend. I'm not bothered about night life stuff. I had Sims 2 Pets for console--I liked the idea of it, but the pets were always starving and miserable, and I really felt like my sim lived for no other purpose than to look after them all day, every day. So if Pets for Sims 3 is less...depressing...it could appeal. At least if the pets are controllable, they might not run away because they're starving, despite the house being loaded with food dishes.
ETA: One reason not to get Pets is another downside that Sims 2 Pets had--I spent more time playing with virtual cats than with my own.