Your question hasn't been asked before, to my knowledge. Congrats, YDV!
Tessio might have been smarter than Clemenza, but he lacked insight and loyalty--two essential qualities in an effective consigliere. Insight: Tessio wasn't sharp enough to see through Michael's apparent passivity in the face of Barzini's inroads. All Tessio saw and cared about were the incursions on his territory, the threats to his livelihood. Loyalty: he put his own interests before those of the family. A human enough failing in a caporegime, but not the stuff of consiglieres. Admittedly, he'd have to have been a big-hearted, far-seeing guy to have done otherwise, but he wasn't. That's why he wouldn't have made a good consigliere.

BUT: No one would have made a good consigliere for Michael Corleone, the hyper-controller, the master manipulator, the supreme distruster. Only Michael could serve as his consigliere.