Does this only make sense for huge traffic spikes?

The savings are most dramatic during a spike, but the logic applies any time a site is media-heavy — scroll-based image sequences, video backgrounds, large galleries, high-resolution photography. If a few heavy assets dominate your bandwidth, moving them to a CDN keeps costs predictable whether you get 6,000 requests or 6 million.