Quite the vague question. Also subject to favoritism and personal preference at an extreme level, considering any game is allowed here.
I will go with one mod that I just saw a ton of quality work put into: Rome Total Realism. A full conversion mod made for Rome Total War, it may not have had all the little things that made everyone happy, but the amount of work that went into making it unique was tremendous. They even created their own soundtrack for it. An original soundtrack for a mod. It was a really great mod in terms of what great work went into it.