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: 
As card sleeves are quite cheap, it's just a matter of personal preference. Some people like sleeves (I do) others don't (some of my friends).

One case I could argue for sleeves is when you have a card game and plan on maybe buying an expansion later. The cards from the base set would be rugged from use, while the expansion would be pristine and shiny. It will be visible. In case of sleeves, you just put all the cards into new sleeves and voila, every card looks the same.
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.)