How can I improve this rough concrete fountain?

Alrighty then! I wish you'd told us that you were interested in creating a different look in the 1st place lol! I am just about to foray into working with concrete. First things first - DO you want to fix the leak so the fountain works? Do you ALSO want to make it look different?

Fix the leak before you start working on decor! You'd hate to create something kewl and then damage it trying to work on the leak. For the leak, I would try the foam (Great Stuff is the name) in a small area to be sure it stops the water and stays attached to the concrete. The fact that it's rough may be helpful for that! Great Stuff will be most helpful if you've got a large hole or crack. Silicone caulk may come to play. It will work alongside the foam but let the foam set and do your cutting first. You could also try to stuff thick quickcrete or similar but concrete doesn't always just "attach" to dry material.

To change the look, one thing that would be fabulous imho is this:

https://www.infarrantlycreative.net/fabric-concrete-planters/

You do the steps but then drape the concrete fabric over the tops of parts of the fountain and then fold/drape it to hang down the sides. Like this:

https://boredomtherapy.com/towel-in-a-bucket/

You could experiments to get REALLY fancy and try to dip lace sewn along the edges for some extra special beauty. http://sproutsandstuff.blogspot.com/2016/01/create-cement-lace-using-doilies-and.html

IDK if this would work w/o doing hypertufa which is whole 'nother ballgame. https://www.pinterest.com/pin/827184656528608960/?lp=true

Barb gives fabulous instructions!

https://www.madebybarb.com/2017/08/30/tips-and-tricks-for-concrete-crafting/