"Do they all have goals or are some just basically glorified delivery missions?"
All of them have goals, but only some will affect the galactic story. Most have filler text to create the goal and those goals can be any thing from decreasing criminal activity in a system or supplying the contraband for a party, in these cases a description will be followed up in Galnet, but nothing actually happens in the game. Once they finish they are basically forgotten.
Others, such as building Obsidian Orbital in Maia have lasting affects on the galaxy. And some have long running story arcs behind them.
"Last question, what decides if a CG is successful? They Prism CG barely made it to level three, would that be a success?"
Anything that reaches tier 1 will get a success story published in Galnet. Community Goals used to have tier unlocks, so the success of the CG could be gauged by the tier reached, but it's been many months since a tiered CG has happened.