My first introduction to the Borg (and Trek in general, actually) was watching First Contact just before bed as a child, and while it was terrifying for me I loved it and mostly always enjoyed the Borg episodes of TNG/Voy when they happened.
I do think their writing was a bit all over the place, of course. But because of that they're interesting for different reasons at different times. In TNG they were the unknown, inescapable threat they weren't ready for, which transitioned into a more known enemy with clear flaws they could exploit later in the series.
In First Contact they were more like an old enemy, and their lore had been expanded upon with the introduction of the Borg Queen. Personally, I love it, but it does completely change the nature of why the Borg should be scary. Before they were a mindless, endless, adaptive swarm; now it has a face and can be negotiated with. With that comes a different set of weaknesses and capabilities, which shakes things up, and I can see why some people wouldn't like that. Personally I think it makes sense, and the writers can still have plenty of situations which don't involve the Borg queen's influence.
Then we get Voyager, where the Borg are once again old enemies but also a familiar tether to the known Trek universe. Their lore is expanded on yet again and in my opinion, parts of it start to make more sense because of this; their primary hub is in a really disorganized part of the galaxy. The Delta quadrant doesn't have many organized federations with the scale of those we see in the Alpha quadrant. And instead of the unstoppable, unknown force we see them as in their TNG introduction, we see their more insect like nature up close in their natural habitat.
They are just scavengers taking what they want from a disorganized mess of smaller races and civilizations in the delta quadrant, being the largest and most organized force there by far. We see that there are other, stranger forces holding the Borg semi-captive in this region of space, acting as a suppressive force. The parallels to Ant behavior were always there with the Borg, but here they actually make sense and I think a lot of the different ways the Borg have been shown to us start to support each other.