Question: Card sleeves are probably be a no-brainer for board game collectors who want to keep their games in pristine condition for as long as possible, or for CCGs where an individual card may be very difficulty to replace. 

For non-collectors playing games like Agricola, 7 Wonders, Mage Knight, Pandemic, etc, when does it make sense to use card sleeves and when doesn't it make sense?

Answer: 
Sleeves are good for games that have you shuffle a *lot*. Roborally, for example, shuffles a single deck of cards many times every game, and my cards were significantly worn after a year or two of playing occasionally. Similarly, if the cards just don't seem to be very durable, sleeves might be a good idea.

They're usually mostly pointless for games that don't shuffle much (once per game). The cards shouldn't wear that fast, so unless you prefer shuffling with sleeves or are very protective, there's not really much need.

And sleeves are counterproductive for extremely large decks of cards (think Arkham Horror with expansions) where the stack of sleeved cards would be way too high to be stable.
I think sleeves are only really needed for a game where the cards will be shuffled a lot, and you expect to play the game often. (How often is ‘often’ depends on the amount of shuffling.)

If there are going to be expansions, or there are cards where a subset gets shuffled into the main deck, you might want to get sleeves just to prevent the cards from having different amounts of wear. (So, I should probably get sleeves for my Cosmic Encounter set, even though I don’t get to play very often.)