Yes, it seems to keep them out of the pool. We did have some dirt in the pool that was probably brought in by the raccoons and I had not thought about the fact that this has stopped since we put in the fountain.
I have (or had) a raccoon living under the screen porch. There is a small fountain with a small basin right outside that porch. I have no idea if they use it for washing. I do think they went to it, perhaps to wash, perhaps to fish for the lone goldfish in there. The stone coping is not fixed and on one side I'd frequently find a stone tumbled into the water. There is a LOT of mud in the bottom of that basin and so it is likely they used the basin. They always have such dirty feet, based on the prints they leave on the patio furniture if I leave the cushions down.
My fountain has a small 30 gallon basin bought at Lowe's. On top of that is a precast "rock pile" waterfall that has two outlets at the top and a few pools that collect and spill water. The pump is somewhat undersized since the basin is so small, I didn't want to suck up the fish with a fast flow.
I was not that fond of the waterfall, it was discovered tumbled back into the brush as we cleaned out this yard after we bought the house last year. I hope to get it covered with moss so it looks less fake.
Starting fresh would be so much easier. When I did landscape designs, I did a plan for a friend that had a small shallow basin set into a raised flowerbed. Many pond shells have two levels and I see no need to dig a huge hole when you can make a small hole for the central deep part, then use that dirt to create a raised bed around it. You must have the pond shell above the natural grade so you do not get rain runoff into it. Likewise you must not have it such that it catches roof water or it will wash out as it overflows.
You must have water agitation or you will get mosquitoes. That can be a simple pump with a fountain attachment or a small spitting statue. Be aware of splash distance or else you will lose water quickly. For a fountain, allow diameter twice the height of the flow. For a spitter, a bit more in the direction of the flow. Any pump will require a sponge filter and this will require cleaning. The pumps at hardware stores are terrible, try Big Al's Online or Drs. Foster Smith. Most come with fountain attachments and the better ones have a valve to adjust flow.
You must have a GFI protected outlet for the pump. Lighting is nice but don't bother with underwater lighting, odds are it will not be clear enough to enjoy that. With the raccoons in mind, add some flagstone near the edge to keep them out of the dirt.
If you chose to add fish, you must have enough depth for the fish to get away from the raccoons, who will seriously go after them if they can see them. If you add water plants, they help to clear the water but then the fish are open for attack by birds as well as raccoons and snakes. Also, fish demand circulation for oxygen, so you cannot skip the buying of a pump, even if you skip the fountain or spitter. And fish need food, so there will be poop as well as leaf debris to be cleaned out regularly. You must allow at least 30 gallons per goldfish.
For fish, it is OK to go to PetSmart and some Walmarts have good fish,but resist the urge to get the 10 cent feeder fish. Those are so overcrowded they are usually diseased. Get a $2 or $3 fish from a tank were all the fish look healthy. They will give you instructions for introducing it to the pond, and sell you the needed water conditioners to remove chlorine.
Know that it is one more maintenance item, keeping the pump clean and clearing out leaves. But, if you put it where you can enjoy it, it is worth the effort to put it in. We love to listen to the fountain and watch the birds that come there to drink and bathe in the pools of the waterfall.
Houses are too close together and aim is too poor for shoot 'em ups in the backyard. Might miss and shoot a hole in my pool! 
I just don't have the time to take Rocky for a ride in the country. If you don't take them at least 10 miles away they will come back! So I figure the pond will be a nice compromise. That will be one of my projects for this summer.