It does sound like stats one way or another, but as your non-ANSI joins are not simple equality checks, you could easily end up with a more stable plan by converting these to ANSI joins. I've given a quick example below - please check the results before using:

SELECT pixel ,

syndicationOutletId ,

outlet ,

geo.[metro-code] ,

dma.metroName ,

dma.regions ,

SUM(imps) impressions ,

COUNT(imps) uniques

FROM #temp4 t

INNER JOIN VINDICOSUITE_NETACUITY.dbo.GeoDatabase geo WITH ( NOLOCK) ON t.ipNumber BETWEEN geo.ipFrom AND geo.ipTo

INNER JOIN VINDICOSUITE_NETACUITY.dbo.Metro dma ON geo.[metro-code] = dma.metroKey

WHERE geo.[metro-code] NOT LIKE 'metro-code0'

GROUP BY syndicationOutletId ,

geo.[metro-code] ,

dma.metroName ,

outlet ,

pixel ,

dma.regions

I'd also suggest not using the NOLOCK clause if you can help it. It's pretty dangerous unless you don't mind the results being inaccurate.