Certainly, Rodriguez is an upgrade for the Yankees, for whom Jose Molina has been a hole in the lineup.
When you look deeper, though, you can see that this trade isn't quite that special for the Yankees, although the price was right, and it wasn't that bad for the Tigers, who won't miss Rodriguez and were desperately in need of bullpen help.
Consider that Rodriguez and Molina are very similar players in type: excellent defensive catchers with little speed, middling power, and a tendency to swing at everything. Molina hasn't hit lefties very well this year, but he has a fair track record of doing so—.268/.311/.405 for his career, and over .300 with good power since 2004. It is a skill he possesses, and in a platoon role, would be acceptable.
Pudge has produced nine runs above replacement this season in 328 PAs, Molina five runs below in 218. Over the last two months, the offensive upgrade for the Yankees won't be worth more than that 14-run gap, and with the defense a wash, this trade is a one-win upgrade, not nearly enough to get excited about. Rodriguez makes the number-eight spot in the lineup a little better, especially against righties, but the idea that adding him is a coup is misguided, largely because he has a reputation and Molina doesn't.
The skill sets are similar.
The Yankees didn't really need a guy like Pudge. They needed a left-handed-hitting catcher with some OBP skills.