Trebs do double the bombardment damage of Cats, and if there are no Walls, they even beat Cannon, tying with Arty and Mob Arty!!! THAT'S AWESOME!!! Therefore, my Trebs are usually toward the end of my list of things to upgrade.
As has been said, Airships make good city-softeners, with no counters for a while. One or two stacks of 4 and that low-odds Cannon assault you had planned will lose FAR FEWER units. -20% strength is a big difference, especially since it gets multiplied by the other defensive multipliers. That -20% might easily be worth a +50% to your own units when you factor in City Garrison 2, Fortified, and culture defense. Sufficient Airship power means your units take less damage.
I'm starting to try to get into Marines, especially if I play as America. I often build 4-8 of them and use them to hit otherwise inaccessible locations, and in some late-game starts.
I still can't find a use for Paratroopers. Low drop radius, 75% chance to be intercepted, can't attack the turn they're dropped, can't drop on unspotted turf. I usually build 4 in my super military city, but they still die too easily. A couple times I've used them for hard-to-reach places that would take a tank or a boat with tanks forever to reach, but since by the time I get Tanks I can move through the enemy territory almost faster than the Bombers can keep up, Paratroopers are actually SLOWER for me to use. Combine that with non-squeeze city placement, and you can see that even that 4 squares of jump just aren't worth anything since you have to wait 3-4 turns to get them to do anything.
I occasionally find a use for Ironclads. With my around-the-world bonus, and usually getting 2 promotions when they're built, that move of 4 makes a good coastal bombardment unit, better than Frigates.
I think I've built one SotL, just to say I did it. Okay, I built a couple, because I had to kill some enemy Frigates when my tech was going slow and I got the enabling tech early through a trade. Usually I don't get that tech until about the time it's obsolete. Same for Grenadiers. Same for gun-toting cavalry.
I used to never built Triremes, but over the past year, I've needed them to whack enemy Galleys, and they also upgrade to Caravel, enabling me to pre-position a couple Triremes, then upgrade them and race to prove the world is round. I hardly build Galleys either, but I plan on trying an archipelago map soon.
Guided Missiles - Okay, I can see how the advantages are inobvious. First, they're CHEAP! That means any city can build them, which means your good cities can work on important stuff. Second, they're CHEAP, so you can build them in your super production city and get a ton of free gold each turn. (+100% Forge+Factory+Power, +100% Ironworks, +150-185% MilAcad+Heroic military production (+Japanese Police State?) = +350-385% hammers-to-gold!!! Let's see Wall Street do that!) Third, that handful of missiles you lob can mean the difference between taking a city this turn without casualties and not taking it but with heavy casualties. Quicker wars are better. Fourth, no stacking limit! That beats aircraft.
Explorer is definitely a special-purpose unit, but when that purpose comes along, it's THE MAN! It's WAY useful for late-starts when you don't have time to dicker around and don't have money for maps. (And who wants to sell their maps anyway? What're you, crazy?)
Privateer lifespan is pretty short, but build them anyway, then upgrade them to Destroyers with a free Sentry promotion! This in addition to all their other uses. They work best if you beeline Astronomy and Chemistry, then flood the oceans with them.
And anti-tanks - well, this is another unit that really only matters in late-starts. If you start early games and wind up needing these guys, you're either playing high difficulty or you're doing something wrong. I've never seen an AI tank. Ok, not true, but I rarely see them, and usually get Mech by then anyway. But if you start at the Industrial or Modern Era, you should build some of these!
I personally hardly ever build Chariots. It's a PITA to find horses, which aren't even revealed until a LATER tech (WTH?) and 9 times out of 10, I have no horses anywhere near me until someone sells me Horseback Riding, if not later still.
And War Elephants. Seems like 10 times out of 10 I have no Ivory on my side of the world. I can't imagine being Khmer. (Heck, even WITH Ivory, what a waste of a UU! Targeting Mounted should be a War Elephant power, and give the Ballista Elephant collateral or something.) Stupid things are real slow too.
I do build MGs, though. Not like the AI units don't attack your STACKS, and these guys are pretty good. They also make good medics.
