Northampton were threatening to recover from two goals down when Agogo burst clear in the final minute and exchanged passes with Lee Thorpe before coolly slotting the ball past Lee Harper.
Rovers fans behind the goal went wild, with relief as much as anything, as their side had been forced to withstand heavy pressure from the visitors, with Pirates goalkeeper Ryan Clarke in inspired form.
It looked to be straightforward for the home side when two former Northampton players combined to put them ahead in the 38th minute.
Ali Gibb crossed from the right and midfielder James Hunt stooped to head firmly past Harper.
The lead was doubled six minutes into the second half when Harper failed to deal with an inswinging corner. He could only palm the ball out to Christian Edwards who fired home from eight yards.
It was only then that Northampton came to life. Clarke made a fine save from Lee Williamson, while a Josh Low shot was blocked by Steve Elliott and Scott McGleish volleyed narrowly wide.
Ten minutes from time, substitute Eric Sabin set up a frantic finish by firing home from close range after Trevor Benjamin's shot had come back off the crossbar.
Northampton piled men forward but could not create a clear chance as Rovers defended with great determination.
In stoppage time the pacy Agogo, who had been introduced as a seventh minute substitute for the injured Dave Savage, produced the dramatic climax.
It was no more than Rovers deserved after having the better of the first half. But Clarke, playing because of an injury to first-choice keeper Kevin Miller, was undoubtedly their man of the match.