I think in general, the more you know about a Trek enemy, the less scary they become. As a monolithic, seemingly unstoppable force of nature
that couldn't be remotely reasoned with, I think the Borg were scary as hell in "Q, Who?". They were running the Enterprise
down and only divine intervention literally saved her.
The moment you start to talk to the Borg and have a civilized conversation with them about their wants and desires, it's over. It could be said that the "de-fanging" of the Borg began with "Best of Both Worlds" when the Borg suddenly needed Picard to be their spokesman for the assimilation of the Human race. From that point on, the Borg ceased to be a true group entity as Picard was clearly an individual among them as "the Borg King" in that story. In that regard, a Borg Queen might have been inevitable at some point no matter what as a female version of Locutus.
The Borg were more or less originally written as a race of space zombies--and zombies don't have a leader. They simply come at you either individually or in a mob. Having them take on a hive-like command structure with a designated leader, makes them not too unlike other Trek villians, IMO. The Borg Queen becomes essentially another moustache-twirling nemesis and the Borg drones her fairly easily felled foot soldiers...