The reason transports are not very good is because of how AA is balanced. It has huge range and damage to shut down bombers into mid-late game. This means everything that is behind enemy lines is ALSO under AA. A dead zone of no AA cover that is behind enemy lines is the perfect place to drop with transports: Especially if there is AA cover over the vital structures that the dropped units can then move to and attack. But AA coverage is so good you basically don't get this.
The other reason is that there are so many boundry-penetrating units: Spiders, Jumpers, Amphs, Hovers, that you need defenses basically everywhere, and there are heaps of ways to get over impassable terrain. So not only are areas you might want to drop usually defended anyway, but the other options feel better.
The main reason though is they're just a bit finnicky to use. I actually think that well done early drops of things like Outlaws, Warriors or Zeus could be seriously OP, but nobody does it- this is why we haven't buffed Valk any more (Since BA, we've buffed it by like a factor of 5, yet it is used LESS).