Okay, maybe this is not an amazing card, but I wouldn't say its anywhere near as bad as people describe it to be.
The main downside about this is that it can be sometimes hard to time it correctly to get the most out of it, but the thing is, the ??? icons will be most useful and important precisely toward the end of the act/scenario, and by that time there is usually 6 or more doom in play. And if not, it can usually safely give you two ?? icons, just like Unexpected Courage. With the amount of luck/gamble reliant cards, and the newer Dilemma cards I think Prophesy fares pretty good, compared to other cards that are situational or require luck.
So most of the time you'll be running it with ??. The card's single ? might be a bit of a pain especially early on, but it's ??? might also save you at times, especially later on. In particular, if you have access to Practice Makes Perfect then Prophesy changes from average to a pretty good card. On the other side though, you also have practiced cards like Promise of Power which outshines both Prophesy and Unexpected Courage combined.
It's the second campaign (almost at the end now) I'm playing with these cards instead of UC and there were only two times where the situation was sticky because of a single ? instead of ??. I know this might not be statistically significant, and I admit I didn't run the numbers on this card, but ultimately I had fun having it in my deck and i think this matters the most.
So ultimately that's what it boils down to, having fun. And gambling. Generally having this in a deck is a bit of a gain or a bit of a risk, so if you want more stability and predictability you should probably aim for the safer Unexpected Courage. If you want to take the chance of losing one ? for the additional ? and try to time it and just gamble it, Prophesy will work just fine.