It was after the 2nd time he didn't get it he ran for mayor of Manchester. Manchester seems happy with the job he's done, and Labour has run out of other popular senior figures, so it turned its lonely eyes to him.
Starmer has become very unpopular. Some of his early decisions - to remove a state pension bonus for winter fuel bills, and to not roll back a Tory limit on child benefit to just two children per family - were very unpopular with the party and typical Labour voters, and he was forced to reverse them. Things aren't going great - such as his appointment of Mandelson to suck up to Trump - and he just doesn't seem to know how to get things back on track. He is often pandering to the right, on immigration issues, and that again is unpopular with the party, and doesn't seem to be pulling many votes back from the awful Nigel Farage and his Reform party. Instead, Labour's been losing votes to the Greens on their left. The Greens won a by-election for Westminster in what used to be a safe Labour seat, and huge numbers of local Labour councillors were voted out at elections in May, when Labour also lost control of the Welsh parliament for the first time ever.
Burnham is somewhat to the left of Starmer (not radically so - the 2nd time he lost the leadership election, it was to Jeremy Corbyn, and it didn't look like he'd get a cabinet job from him, so leaving parliament wasn't that surprising). The hope is he can persuade back the voters drifting to the Greens.
While his previous posts in government were domestic rather than foreign-facing ones, I'm not that worried about how he may handle Trump. Starmer sucked up to Trump too much at first anyway, though he seems to have developed a backbone more recently.