I have played with the cauldron on beta. And I actually like it. If you expect a huge benefit for free, then sure you will get disappointed, but it is possible to get a small benefit for free. And if you have a lot of spare resources then you can get a bigger benefit. I also expect it will take time to level up the benefits.
Every week you get free witch points that you can use to buy ingredients and level up (some of) the effects. I suggest focus on leveling only one or a few effects.
Yes, you can spend diamonds on buying some of the ingredients, but I have better uses of my diamonds.
And you can spend spell fragments to get a bigger effect - if you have a lot of extra spell fragments, but you don't have to.
And you can spend coins/supplies/goods to level up the effects - if you have extra of any of those, but you don't have to.
I expect to spend some resources on leveling up a few of the effects, at least in my biggest city, but I am definitely not going to spend diamonds, and probably not spell fragments either.
Another thing you can do to increase the chance for getting effects is to try to do at least 2000 points in tourney weekly (or as much as you can) to get more trophy goblets, that is more chances to drink from your potion.
I mostly fight, so my plan is to go for the troop effects and brew the potion just before I do the spire (I usually time boost most of the doors in the spire) and preferably also doing one tourney round (or all if I have zero tourney cooldown). If I get a troop effect then great, I will have a little extra help and save some troops. If not then I hope for better luck next week