I always played games primarily on the PC, so I'm pretty agnostic as to the console itself.

I think that the one that I liked the most was the PS2 controller, the Dual Shock.

Had rumble motors.
Was wired. I am fine with wireless, but I don't gain much from it, since I'm sitting at a desk normally. It adds weight and potentially radio interference and charging issues.
It did not have analog stick drift, at least that I experienced. I don't entirely understand why, but essentially every gamepad I've gotten in recent years -- and I've gone through about six, for different platforms -- with potentiometer-based analog sticks has had issues with analog stick drift. Maybe changes were made to manufacturing processes on potentiometers or something. I recently finally gave up on potentiometer-based gamepads and got an 8bitdo Bluetooth Ultimate pad -- which uses Hall effect sensors, which don't experience stick drift problems. Note that the non-Bluetooth version of this gamepad uses potentiometers. Unfortunately that pad doesn't have rumble motors...
The D-pad is more-gentle on my fingers than some.
Pretty durable. Eventually, the surface on the analog sticks started to wear through, but it lasted quite some years.