This is a great thread -- I've "liked" some of the most helpful posts. Here are some assorted comments and ideas to add.
1. Comments about the problems in getting to the stadium are spot on. The train has worked for me, but it's really not great -- and you'll miss the tailgating and Patriot Place. The Walpole + taxi idea is brilliant (clever guys, these Aussies!)
2. If it were me, I'd be seriously looking into the idea of staying in Providence and setting up a ride with someone. Like Joker, I'd be astonished if there weren't someone who could fit in a nice, polite Glaswegian -- especially one who brought along a wee bevvy. And I really liked Providence. Attractive place with good food and drink. But I wouldn't want to stay there too long -- compared with Boston, it's pretty small.
3. Boston hotel prices are wicked (and I don't mean that in the Bostonian sense of "wicked"). But you'll obviously want to spend some time there. There's lots to do and see.
4. Transatlantic flying. You'll obviously be buying on price and I REALLY doubt that flying to Providence makes sense. But Logan (Boston) is fine. Its main advantage is that it's very easy to get into town. At present the shuttle bus that takes you to South Station and onwards on the public transport system (the "T") is actually free. There's also (I'm told) a pretty good network of buses to elsewhere in New England. And if you were going to Providence, it would be easy to take the shuttle to South Station and get a train (see below).
Warning. American, United and Delta seem to be in a competition to be the worst transatlantic airline (I think they're pretty much neck-and-neck). If it's only a few bucks difference, I take Virgin or BA. But otherwise ... what the heck?
5. Given you're making a holiday of it, you might want to consider flying in and out of New York. I'm no expert, but the few times I've done it transatlantic I've found Newark to be the least bad of the airports. And if you're going to take the train to/from New England, it's on the East Coast line (I think).
6. Trains/buses. Trains in the U.S. aren't always great, but the East Coast line (Boston/Providence/New Haven/NYC) has decent and very frequent service. There are lots of delays, though, so I wouldn't want to rely on its punctuality if I had a flight to catch. If you're feeling brave, there are also the buses (though the most notorious has been shut down for safety reasons).
7. Holiday destination. Everyone I know (Europeans and Brits) who knows the Cape thinks it's wonderful.
Anything I can help with, feel free to PM me.