When you download a map, and extract or open the zip archive (or whatever archive format is being used), you will have one of two file types:
*.py: .py files are mapscripts (actually, they're Python scripts, but in this case, they are mapscripts only).
*.ColonizationWBSave: These files are worldbuilder save files (scenarios/pre-made maps).
For both types of maps, installation is the same:
First, find your Colonization directory under Program Files. For me, the directory is:
"C:\Program Files\2K Games\Firaxis Games\Sid Meier's Civilization IV Colonization"
It will probably vary. However, the directory should contain, among other things, a "Colonization.exe" file, an "Assets" folder, and a "PublicMaps" folder (as well as various other files and folders).
The "PublicMaps" folder is the relevant folder here. Copy the map file into the PublicMaps folder.