Is A Fox Night's Dream playable outside of events? I pulled one from the oni banner. Seems mediocre but maybe it's not.
To answer your particular question, a Fox Night's Dream has two "ok" effects. It's not something you can easily put forth on a servant to make them shine; NP Gain UP CEs need a bit of planning and team comp organization to be useful.
In general, the best CEs are highly specialized effects rather than generalists. CEs with 2-3 effects can also be considered "the best," but looking at the current list, the effects need to be a combination of: NP Charge, Quick/Arts/Buster Up, Crit Damage Up, and NP Damage Up. People want to fire their NPs, and want to do as much damage as possible.
Kaleidoscope - 80% NP Charge
MLB Golden Sumo (upcoming in Onigashima)- 50% NP Charge, 15% attack up
MLB Holy Night Supper - 50% NP Charge, 15% Crit Up, 15% NP Dmg Up
Victor of the Moon - 10% Buster Up, 20% Crit Up
Fragments of 2030 - 8 Crit Stars per turn
MLB Black Grail - 80% NP Dmg up
Volumen Hydragyrum - Invincible 3 times, 200+ damage per card (the invincible is what matters)
Looking ahead to this year's CEs, top tier CEs will include:
MLB Dive to Blue (Summer Race) - 50% NP Charge, 10% Arts Up, 10% NP Dmg Up
MLB Aerial Drive (Halloween 3) - 50% NP Charge, 10% Buster Up, 10% NP Dmg Up
The above is kind of the mindset for categorizing the top CEs. Other CEs are definitely still usable. NP Gain CEs would fall here with upcoming CEs like:
Summer Little (Summer Race) - 25% NP Gain up, 10% NP Dmg up, 10% Crit Dmg up
Trick or Treatment (Halloween 3) - 25% NP Gain up, 15% Crit Dmg up
One other thing to consider is that the cost of CEs matter too. Getting to my point,
a 5 star CE almost always has to be top tier for it to be used because it is so expensive. Fielding multiple 5 star CEs requires sacrifices in your servant rarity or other CEs so you would only consider the very best (not counting event requirements or efficiency). 3 and 4 star CEs actually get more leeway in their shortcomings because they help balance your team costs against your 5 star servants and usually the one or two 5 star CEs you can afford with them.
In the end, you make do with what you have, but as you get more 50% NP Charge CEs and Kaleidoscopes concentrated on your 3 frontline servants, the rest matter far less.