The actual files will end up going to My Documents\SimCity 4\Plugins in the end. Assuming you're on Windows, mods for the Steam version work pretty much the way they do for all the other versions (though Origin's historical tendency to sell an unpatched/unpatchable copy fouls things up in-game-allegedly, that's changed, though I've yet to see proof).
