The single biggest reason why I say protective is so useless for human players is because I'm a firm believer that the best defense is a good offense. I hardly ever wait until the enemy starts to bombard and assault my cities. I try to take them out in advance on my terms. In my opinion drill 1 for gunpowder units is something to sneeze at. I'd rather just bring more siege weapons.
Advancing on "my terms" means that I have to have the resources to overpower whatever the enemy has either on relatively even terms in the field, or in whatever defensible terrain there is around my territory. Sometimes it takes a tremendous amount of resources to maintain forces to do what you're talking about, and depending on the game you're playing, keeping those kinds of forces around and updated might be more resources than you really want commit, or can even afford to commit. Piece said it - if you don't see *real* enemy SOD's coming at you ever, chances are you're playing on a lower difficulty than you can manage. There's nothing wrong with this, as the purpose of the game is fun and that's sometimes just the way to go about pursuing this end, but this situation artificially strips protective of its use. It's much like the people who say "Well, industrious sucks because I can get all the wonders without it." Sure, but when you're on a higher difficulty and scrambling for any wonders at all, industrious can make a big difference.
Cities have defensive bonuses - some that can be stripped, some that can't, all of which can be leveraged against an enemy. Protective is the ultimate trait for leveraging these already existing bonuses against an advancing enemy - or cramming them down the enemy's throat in their recently captured cities. If the enemy is attacking you with gumption, there are going to be lots of units, and lots of siege - the core of the whole SoD concept. One of the obvious, but often hard to manage, counters to this is high drill units with an odds advantage - they take very little siege damage, and take very little (if any) damage from enemies attacking them with lower odds. Protective units, in a city, are absolutely ideal for killing hordes of enemy units because A) siege doesn't soften them, B) enemies at an odds disadvantage don't soften them, B) the combination of drill and city garrison means that anything that attacks them without a notable tech advantage will be at an odds disadvantage. Pair these together and... I've come to count on protective longbows surviving 4 to 1 odds on city defense, meaning that I can keep a relatively small military, focus on relatively few military techs, and devote the resources required to "fight on my own terms" to other things. And really, I'm setting my own terms... If an enemy wants to take a city, they have to run the brutal gauntlet I've set for them. The AI doesn't pillage much, and I can usually afford to just sit in my cities and wait for them to dump huge resources into attacking me.
When you have these super-elite defensive units, sea invasions which usually succeed with you using other general-military philosophies often fail because they can't even take out your two or three stock coastal defenders. SoDs need to be better planned than the CPU does, or just be gratuitously big, to hold a candle to a city loaded with protective longbowmen. Attack forces composed of 75% exceptional generalist archery/gunpowder units work very well, even on higher difficulties.
I'm an Immortal player - huge, 15 or so civs, marathon speed. I've never found protective weak - in fact, I consider it the strongest of the straight military techs for its combination of leveraging the best odds situation I'll ever get to its maximum, focusing on the strength of the resource free jack-of-all-trades units, and for the economy of techs I have to get to really take advantage of it. I know it only fits a particular playstyle, and I'm not even suggesting that people who think it's the weakest trait are wrong because it may well be the weakest for the way they choose to play (and that's not a comment on difficulty played so much as style). Just be aware, some people make this trait earn its money even on higher difficulties. Protective is the economists or the peacemonger's military trait, and it is exceptional in this role, and has tremendous strengths to be leveraged against the enemy.
Though, I will say, everyone who says it is weak, keep on doing so... If in the next patch Protective gets a buff, wow, will I ever be on cloud nine - half the guys I play now are protective

Now give me pro/org already Firaxis!!!