I think I'm failing to grasp the strengths of this unit. I never really see pillaging as overly useful. On one hand, if youre invading to conquer, you don't want to pillage because it will damage the land you intend to keep. I also don't see a purpose in DOWing for the purpose of pillaging because of the negative diplo penalty. If youre DOW'd without wanting the war, I would imagine you would just turtle up until the AI lost interest, and the pillaging doesn't come in handy.
I used to never pillage, then I played a game as Harald where I tried to pillage as much as possible, and now I can almost not imagine going to war without pillaging. If you plan to raze a city, just pillage everything. You'll get 80-150ish gold a city just from pillage, and its not like the improvements do any good sitting out in unclaimed land anyway.
If I'm planning on puppeting a city, I'll pillage all the farms and mines (that aren't on resources), since I'm probably going to replace them with trading posts anyway. I will generally leave improvements on resources alone, even bonus resources, as these will generally be good tiles for even a puppet to work. I think these ones should probably be worth more gold to give greater incentive to pillage.
Some great ways to effectively pillage, I find, is to create one or two cavalry type units, and generally avoid combat with them. Instead have them run around the cities pillaging everything will your foot soldiers are shored up around the city taking hits and distracting the army and your siege units are firing. This is also a great way to get used to using cavalry to flank enemies and attack weak targets. Also, before you fire a shot with your catapult, or tell your swordsman to attack, pillage the improvement it is on top of first. You have 2 movement points anyway, and you only need one to do either of those things, might as well make a little profit while you are at it.
EDIT: Oh yeah, and if the war is looking to be a long one, try and send some fast units, or maybe paratroopers if its late enough, deep into enemy territory and pillage their strategic resources. The -50% combat penalty that their units will give you a huge advantage and might just win the war for you.