One anecdote: I "cheated" at EVE Online by writing an elaborate set of modding tools. Most of it was automation for really finicky tedious stuff like drone management, or automation for things like broadcasting your current target to other players in your party. I also hacked in workarounds for bugs in the official client. It enhanced my experience with the game a lot. On the other hand, lots of players were just botting.

I also maintained a browser addon for a while that had 100k+ weekly active users that added various features to a browser-based game. Eventually that game had such bad problems with botting and cheating that they had to introduce an anti-cheat system, and we basically got into a little arms race for a year or so where they'd add a new detection system and I'd circumvent it. Similar to the EVE Online modding it was things like workarounds for bugs in the game, improved UI, keyboard shortcuts, etc. Eventually they drew a line in the sand and said anyone using addons of any kind would get a permanent ban, so that was that.

I think the vast majority of cheaters are just in it to ruin other people's fun but sometimes people are violating ToS for a better or different experience with the game. It's unfortunate that the prevalence of malicious cheating means that anti-cheat technology also has to basically ban modding for fun.