I'm a bit torn on that trade, but Ainge would and that is all that matters. Ainge didn't sign Horford to build around the Brooklyn picks, he signed Horford to go into a win now mode and trading the 1st pick for Cousins is the quintessential win now trade.
I heard Ainge say on NBA network that personnel decisions are made jointly by himself, Stevens and Wyc (the ownership). That means Stevens has significant input to personnel decisions. Which is probably why Zeller is still around. My impression is Stevens sees no need for a big inside rebounder. I'm not sure Ainge agrees, having played for the Celtics and knowing their winning tradition better.
What I'm saying is maybe Stevens doesn't want a player like Cousins. Or any of the other athletic bigs that play near the basket who Ainge has passed up in drafts and trades. That could include Noel. If so, I think the Celtics will have a hard time getting to the next level.
I wouldn't say that Stevens sees no need for a big inside rebounder, but it's not his top priority. People keep harping on the idea that Stevens wants perimeter bigs and I think they fundamentally misunderstand Stevens. He is very much a defense-first coach. So, first off, he wants big men who prioritize defense over rebounding. We've heard reports that the Celtics have cooled on the idea of acquiring Kevin Love (although I think Ainge will jump at any chance to acquire him at a bargain), probably because Love is known to pad his rebounding stats by positioning himself early for the rebound instead of defending 100%. Stevens is also not going to like offense-only big men who don't try hard on defense. He can sometimes compensate for a lack of ability if the effort is there, but someone with the physical tools to be a good defender who doesn't use those tools is likely to end up on the bench.
On defense, he cares more about his big men being able to defend against the pick and roll than defending in the post because he knows that a post-up is generally an inefficient offensive possession.
So, if you are looking to add a rebounder to the roster, you should look at the available big men, eliminate anyone from the list who is a poor fit for Stevens' defensive schemes, then figure out the best rebounder who is left.
As for Cousins, if Stevens doesn't want him, I think it has more to do with a belief that Cousins' attitude is unfixable, that he is part of why Sacramento is dysfunctional and not a victim of the dumpster fire, and less to do with Cousins being a bad fit for an up-tempo team.