The best tacked-on multiplayer I've seen was the ME3 multiplayer, which retained my interest far longer than the actual game did. The free DLC doesn't hurt it. They could conceivably release it as a F2P standalone. A close second would be the Assassin's Creed multiplayer, which was creative and incredibly engaging.
The worst was easily the Spec Ops multiplayer. I played one match out of curiosity. It was like a shitty, laggy Gears of War with godawful Call of Duty XP mechanics forced into it. After playing the game itself, it just...it's insane that it even existed. The cognitive dissonance between the singleplayer and the multiplayer was excruciating. It would be as if Martin Luther King gave a speech on racial equality, walked down the street and through a loading screen, then put on a KKK hood and started burning crosses.
The missed-opportunity would be the Space Marine multiplayer. It was actually pretty good, but it suffered from an over-reliance on said Call of Duty XP mechanics and a health bar that was waaay to fragile given how clumsy the movement scheme was. You would die in two shots from a Stalker boltgun while lazily strolling to the left towards cover, it was ridiculous. It was like fistfighting while submersed in honey. Considering how awesome a Space Marine multiplayer could have been, I was really upset that I got the bastard love-child of Gears of War and Call of Duty.
The other ones I've played - Max Payne 3 comes to mind - were boring as all get-out.
Basically, it all depends on how good the multiplayer is. Because it's tacked-on, it tends to be crap, so good tacked-on multiplayer is the exception rather than the rule.