Does anyone have a clue what the best and biggest complete gameplay overhaul mods are? I know I had one a bunch of months ago, which completely redid the social policy trees among other things, but I lost it and can't seem to find anything in the Steam Workshop so far.

not sure what is allowed to be posted with regards to other games, not even made by Bethesda or even Zenimax Media so I'll just point you in the right direction.

Civfanatics.com is the biggest independant forum for all civ games and mods. you'll find plenty in there. I use it all the time for my civ 4 mods.