There's a big difference between $5 (40 clicks or however much it was) and 10,000 clicks $500. I'm on a small budget and personally I prefer to give campaigns about a 100 clicks, so 1%. This whole time I'll be tweaking my campaign, add/delete keywords edit bids, ads, etc..., and then if still nothing I'll just go to something else most likely not test more than 200 clicks, but I have spent more than that in hopes of getting the campaign to work.
It all really depends on your budget and how much you want to spend, you're really just testing and tweaking the whole time in hopes to get a performing campaign. So, if you have an extra $500 to spend and don't care, then it's up to you. If I've spent about $20-$30 and not getting anything and tried to do everything I could possibly try, then I'll just move on to something else. Or just put it aside until I can think of an idea or method that I think would work.
I don't think the real issue here is money or the amount of clicks, it's more that you should be doing everything you can to tweak your campaign and get it to perform for you until you just feel like you can't think of anything else or do anything else, then just move on to something else.
Theoretically, you should be able to make any niche or affiliate program work since, somebody out there is making it work or the merchants/networks wouldn't be in business, correct? It just depends on if you know what you're doing, or if you can find that secret keyword, audience, etc.. etc..
So in closing, just keep testing and tweaking for what your budget will allow or as much as you want to spend, since somebody else out there is making it work. You just have to find out how to make it work for you.