They were. In TNG.
Right. Then First Contact happened. Then Voyager happened.
The Borg lost their luster after BOBW.
Aside from the Queen, the 2 biggest mistakes with the Borg in my opinion where giving them the ability to travel through time (for obvious reasons) and Transwarp.
Part of what made them threatening in early TNG was that 'they will be coming', but because the initial encounter was thousands of light years away, they could only speculate as to when a Cube would eventually turn up on the doorstep.
Little did they know the Borg had an exit aperture from a Transwarp hub less than a lightyear from Earth and the only reason they haven't sent a fleet of ships to assimilate us is because they don't work Wednesday's...