I've loved many a mod over the years, Counterstrike, Dota, Natural Selection, Desert Combat, but the one that i feel is criminally underrepresented is Fall from Heaven II for Civilisation 4. It's a fantasy themed mod that is probably my favorite strategy game of all time. There are so many ways to play this game it's ridiculous, i have put in over 150 hours and am still discovering new strategies.
One game i rushed towards researching werewolves who can convert enemies to my side, resulting in an incredible snowball effect across the map. Another game i spawned near a significant amount of mana resources and spent the whole game training up an army of fireball wielding mages. There's an elf race, a pirate race, a vampire race, and heaps more, all allowing for really unique play styles.
There is a magic system, a hero/rpg system, an entirely expanded religious and morality system, an item system... Heck, there is even a god-damn optional card game inserted in there, all built on top of the extremely solid civilization 4 base mechanics.
I found it to be so much more interesting than civilisation 5, and it didn't cost me a cent.