Depends heavily on your campaign. Sometimes you're fighting a big powerful monster, sometimes you're fighting an invading army.

From level 5-7, I could see myself throwing a dozen CR 1/2 hobgoblins at a party looks a.

At level 9, a CR 5 boss, a pair of CR 2 lieutenants and 8 CR 1/2 minions would also be a fun fight. I'm pulling both these numbers from page 68 of Forge of Foes, a great 3rd party suppliment.

If I suspected the PC was itching to use fireball, I'd give them a killer use for it soon after they hit level 5, such as the aforementioned 8 wide, 4 deep phalanx of hobgoblins that could be incinerated in a single blast from this. If you're not familiar with lightning rods, if the idea of specific things to make the PCs feel cool, undead for the cleric to turn, mundane archers for the mink to deflect, or dense, weak enemies for the sorcerer to fireball. The mother of all lightning rods is the one you set up soon after they get the ability to show how it shines.

Also if you use the minion rules such as those created by MCDM, then at basically any level, those mooks will be instantly killed by this effect, it's great empowerment for the player and if it's budgeted for in your encounter creation, not fun spoiling.