Pillaging campains can be worthwhile. The force you need will depend on your objectives - i.e. how deep you want to get onto enemy land...
The point - imho - is not to be able to pillage many tiles per turn - if you try this, you will take - say a Impi (great one if you happen to be Shaka) - it will move in pillage 2 tiles in one turn, and most likely die to an axe on the next turn. While sometimes worthwhile - if you pillage a important tile - this does not make for a real "Pillaging Campain"
What you could try instead is making your stack survive long enought to get to the really juicy tiles... You can field more than one pillaging stack if you want to speed things up.
I often take a Spear (Combat/Medic), one or two axes (Combat/Shock) - those are for stack protection - and two or sometimes three faster units - often chariots. In medieaval ages those are other units, but the same idea - a mix of fast pillagesrs and slower protectors to counter different attacks... I rarely go pillaging post medieval... Not sure why...
The stack moves slowly because of the protectors. But it can still pillage one tile per turn (The faster units can move and pillage the same turn... and 3 are enought to burn a Village down to nothing - giving good money...).
At the same time the units are somewhat protected...
Such a stack often can move deeply into enemy land, not only porviding good money, but it will often make the enemy divert some troops to it, making the work a bit easier for your units on the front...
If planning to pillage a strategic ressource, you should also concider taking additional protector-type units, which then can fortify on the pillaged tile, preventing the enemy from rebuilding, while the main stack moves on.