How effective actually was it?

This is really hard to measure since there is no objective standard against which to compare. It is also worth considering that Dazzle camouflage isn't technically a camouflage at all, it's more like a precursor to modern passive counter measures. Dazzle camouflage makes a ship rather easy to find and as such does absolutely nothing from a camouflage point of view.

What it does is make it hard to identify a certain ship type, range (more on this in a bit) and speed/heading. Dazzle camouflage breaks up what might otherwise be an easily recognizable ship into a blur of geometric shapes. (in theory)

On range: Range finders used to operate on a principle of aligning two images and calculating range based on that. On a regular object, this is easy, it's like tearing a picture in two and then aligning the pieces again. What dazzle camouflage does is make it harder to aling the pictures and therefore, the range.

In theory, dazzle camouflage should also make it harder to determine speed and heading of a ship though there was and is some debate on whether or not this was actually the case.