Now that the beadboard ceiling is down on the old front porch the reason for the problem has become a little less mysterious. For no particular reason, it appears that whoever built it ripped off the moldings from around the base of the roof and box gutters. This left an enormous gap all the way around the perimeter of the bay, which opened directly into the floor joists of the second floor, explaining both the floor of ice in the guest bedroom and the frozen pipes. This whole section was closed off only by the new porch roof above, and the beadboard ceiling butting up to the bay below where the moldings should have been.
Obviously we had hoped to have this demoed and repaired in the fall. While the demo work could easily get done over the winter, there are a lot of elements that need repair once the porch is gone. In addition to the new shingles over the little bay, there are also bricks to repair where pockets were created for the joists, repairing the box gutter and downspout and refinishing the doors.
So in the meantime I crept up the ladder before the current storm hit to try and close up what I could. When I went out it was 63 degrees, when I came in two hours later it was 40, raining, and windy. You know what spray foam doesn't like? The cold. Also, the wind. The spray foam I had planned on using wasn't curing, and the draft blowing into the house was so strong it was sucking the foam through. If I had had more time I would have cut foam insulation sheets down to size, but I was in a rush. And when in a rush one stuffs fiberglass batts wherever they can and hopes for the best. It has to be better than nothing right?