PS3 controller connected via USB, or PS2 controller connected via USB adapter.
Using Motion InJoy for PS3 drivers, also for Xbox 360 controller emulation
XPadder for button mapping
AutoIT for scripting specific controls or methods that aren't native to the gamepad or Xpadder.
I won't highlight Games in which using a gamepad and mapping keys are simple, just the ones that are a little tricky to adapt to a Gamepad.
Diablo 3 / Torchlight 2 - 100% (Xpadder + AutoIT)
Using the left analog stick I wrote/modded an AutoIT script that rotates the mouse cursor around your character and also holds down the dedicated move key, this enables full 360 degree movement. The script also recognizes when you hold down the SHIFT key and disables movement, but increases the distance that the cursor rotates around your character, this enables ranged characters to Root and fire from a distance.
This takes some time to get used to but this setup has already made me better than mouse/keyboard setup. There's no automation here, just simple mapping of controls, it allows for very complex and fluid changes between movement and attacks. I have also scripted the D-Pad to move the mouse cursor specific distances to allow for quick inventory browsing.
I have made it to Act 2 Hell on 2 characters with this setup.
Guild Wars 2 - 100% (Xpadder)
Mapped left analog to WASD, and right analog to mouse cursor. Pushing the right analog button toggles the right mouse button which enables/disables camera control. And the way Guild Wars 2 targeting is done this makes for extremely fluid and enjoyable combat.
I have made one character to level 64, and several other mid 20's so far with this setup.
Civilization V - 80% (Xpadder + AutoIT)
I have mapped the left analog to the arrow keys which control screen movement, and the AutoIT script is simple in that when you use the left analog stick it automatically places the mouse cursor in the middle of the CivV map. This works great for map and unit direction, but it's a bit sensitive. And there's too many keyboard shortcuts to map out on the gamepad, but most of them aren't necessary.
Right analog stick is set to mouse cursor and navigating the UI is not as difficult as it sounds. Most of your time is spent on the map UI so the setup works well for 75% of the situations.
I have only made it to turn 500, so late game still needs to be tested.
Steam Big Picture - 100%
Using the Motion InJoy Xbox 360 controller emulation, it works perfectly with big picture and games that support the gamepad work natively with it.