The real-world reasons behind the Borg's transition from invincible, coldly logical "ultimate users" interested only in consuming technology to more easily-beatable moustache-twirling bionic villains whose sole purpose is to assimilate living beings are well known (hint: bad writing, mostly) but what about an in-universe explanation? I'm sure lots of people have taken a stab at this, and here's my attempt:
The Borg is/are a single mind. Originally, that mind's sole purpose was the acquisition of new technology.That was all it did: acquire new technology, then use that technology to acquire even more technology. The life-forms it encountered were beneath notice, not because the Borg were arrogant, but because they were no threat to the Borg and had nothing to do with Borg's directive to acquire technology. The Borg ignored the life-forms they destroyed for the same reason we ignore dust mites. At some point, however, the Borg's great complex uni-mind developed a schizoid personality. Maybe it was the result of a computer virus planted by a society they attacked, I don't know. This schiziod personality was the Queen. Unlike the coldly logical Borg mind, the Queen personality had many of the flaws common to humanoid minds. She craved power for its own sake. She could be overconfident, petty, vicious, and greedy. She arrogantly believed herself to be perfect.
Over time the Queen personality began to gain more influence over the Borg uni-mind. The first manifestation of this came in the form of a change in their prime directive: suddenly, they sought to assimilate other life-forms in addition to their technology. When the Borg returned to Federation space six years after their first invasion, the Queen's personality had completely taken over the Borg, to the point that she could accurately be seen as the personification of the Collective. Where the "old" Borg would have simply returned to Federation space in overwhelming numbers and stamped out any resistance, under the Queen's influence they arrogantly sent only a single cube, with a convoluted "backup plan" of traveling back in time to assimilate Earth in the past if their initial assault failed. By now, the Borg's purpose had changed so that assimilation of life-forms was their main interest. Instead of simply scooping all the technology off a planet with tractor beams, as they had done in the past,they now assimilated the planets. Whereas the old Borg uni-mind wanted only to consume technology, the Queen wanted to hold territory, to conquer and rule. The Queen's lust to consume life-forms, coupled with her desire to conquer new territory, led the Borg to pick a disastrous fight with Species 8472, something the "old" Borg never would have done. Interestingly, as the Queen grew more powerful, the Borg grew weaker and more easily defeatable. Perhaps the Queen's arrogance and overconfidence dampened the Borg's ability to adapt to new forms of attack. Of course, the Queen made no distinction between the Borg as they existed before and after she came into being. She pretended, as many power-mad kings and dictators have, that she was the sole author of the Borg's victories, even the ones that happened in the distant past.
Now, I'm sure there's a line in a Voyager episode somewhere that completely blows this theory to bits. But I think it fits the broad strokes of what we know about the Borg, and how they changed between "Q-Who" and "Endgame". What do y'all think?