Question: I have a newbie question about focal length, what it actually means for lenses and how I can translate this into "how close to the target it's going to practically take me" (I don't want to use the term zoom here to avoid confusion, perhaps magnifying power is what describes best what I'm after, basically shots from the distance, not macro).

Here's an example:  

I currently have a Sigma 18-200mm lens (therefore approx. 11x zoom), which I use with Canon EOS 400D and also a compact Sony Cyber-Shot DSC-HX60 with focal length 4.3-129mm (approx. 30x zoom).  

Even though 200mm is obviously more than 129mm the Sony camera takes me closer to the target.  

Looking at zoom factor (30x comparing to 11x) this may seem obvious, but it's less obvious when you compare it to other lenses, e.g. 200-500mm lens, which effectively has "only" 2.5x zoom, but actually takes me closer to the target.

How can I make sense of this when looking at other lenses?  

For example let's consider Tamron 16-300mm, its zoom is obviously approx. 20x, but how can I compare it to Sigma 18-200mm or Cyber-Shot 4.3-129mm? Is it going to take me closer to the target, e.g. magnify objects better then both of them or only better than Sigma, but still not as good as Sony? (ignoring quality of picture of course)

Answer: 
Sony Cyber-Shot DSC-HX60 has a small sensor ([6.16x4.62 mm](http://www.digicamdb.com/specs/sony_cybershot-dsc-hx60/)). When talking about "Sigma 18-200mm lens", you don't mention which camera you use it on, but I guess it's an APS-C sensor (24×16 mm), hence 3.8 times bigger. At constant image size on the sensor, the small sensor will only get a tiny portion of the image, hence the subject will appear 3.8 times bigger on your Sony than with your Sigma lens mounted on an APS-C body for the same focal length.

When comparing focal length with different sensor size, it's better to compare in terms of "35mm equivalent", i.e. multiply by 1.5 for APS-C sensors, and by 5.8 for your Sony camera.
Your question cannot be answered that way. There are instead other factors.

Your 18-200 Sigma lens is for a APS size camera, with a relatively large digital sensor.
Your little compact has a very tiny sensor (all compacts and phones do).
These are very important differences regarding the size image they can show.

So to take a "reasonable" picture, showing a "normal" scene width view comparable to what all other cameras normally show, the large sensor can use a longer lens, and the tiny compact sensor must use a vastly shorter lens, very short ... in order to fit all of the normal scene onto the sensor. A much shorter lens than would be usable on the larger camera. In this way, then either camera takes a "normal" picture, showing about the same normal view, regardless that the tiny sensor is so small.

What effects how "close up" the picture can be are two things. Minimum focus distance (about 15 inches for the APS Sigma lens, and perhaps 1/2 inch for most tiny compacts). The closer it focuses, the larger the subject appears. 

Also the focal length is a magnification factor, longer focal length (like telephoto) brings the subject up much closer. 

And then FWIW, the larger sensors allow more enlargement of the picture.

Sigma calls it "macro", because it does focus a little closer than most normal lens, and can do a 1:3 reproduction ratio. But it is NOT a real macro lens that can to 1:1.

Here's the deal: If you want macro benefits, then you should buy a real macro lens for the APS size camera. That this what they do, focus up extremely close (a very few inches), and are optimized for the closer distances. Typically, they can do 1:1 reproduction, 1:1 meaning that the image on the sensor is the SAME size as the actual object (the APS sensor is less than 5/8 inch tall). Meaning, an image of US dime coin will be slightly larger than the APS sensor can fully show. 

Then the larger sensor APS sensor size can enlarge the dickens out of it too.