Just made diamond. Is it possible at any hotel to make back the room rate in perks?
 

So we get two free drinks, two free breakfasts, a welcome amenity, a free pair of socks from the Fairmont fit gear, some shampoo and lotions to take home, a couple of Nespresso pods.

Off season Fairmonts in b-tier Canadian cities sometimes dip sub-$200, and there's government/residents rates that sometimes make it even cheaper. But it looks even at the cheapest rate + the most expensive menu items (in Edmonton) doesn't fully cancel out.

Is there any hotel where it could theoretically?

Accor is a business...
answer is clearly no.

Arguably, the only tangible benefit of Diamond over Platinum is the weekend breakfast and 100 Euros worth of dining certificates per year. I wouldn't go out of my way to even jump from Platinum to Diamond, much less think you could somehow earn back the room rate in stay benefits! I consider it more of a meh status you just happen to hit through planned stays, rather than something one actually strives for.

I beg to disagree. :p

If one were to value the benefits at what they would otherwise cost, the value of the perks roughly equals:

2 x $20 welcome drinks
2 x $40 breakfast buffet incl. tax/tips
~$10 welcome amenity
~$10 other amenities
~8.8% ROI in terms of the value of the ALL points earnt, possibly more during a double/triple points promotion

...and of course the room could be paid for using Fairmont GCs purchased at a ~16% discount.

Rates in the ~$150/night range aren't that common these days, but if you have access to the right government/corporate rate, it's not impossible. The lowest regular published rates tend to be closer to $200/night these days though.

Tip may be excluded from the breakfast voucher and I'm afraid discounted Fairmont GCs are a thing of the past.

Even if you assign those items value as you have done, I think that's still a stretch to make it work.

Assume a $200 room rate, and 15% fees and taxes on top. Total spend is $230.

You'll get back 8.8% of $200 in points, or $17.60.
And if the gift card discount comes back in the spring, that would be 16.67% of $230, or $38.34.
And then your $80 + $40 + $20 in breakfast, drink and other amenities (and I'd be hard pressed to really assign any value to a welcome amenity) you have $140.

Total "value" back of $196 on a spend of $230. That also assumes you don't have to incur cost to travel to this city where you can get a a room for that price. And, in order to get a room for that price, you'd probably have to subject yourself to a staying a night in a base-level room in Winnipeg or Edmonton in the middle of February!

These kind of calculations on ultra-edge cases are rather meaningless. Cheaper properties tend to have cheaper breakfasts and drinks so calculations would have to take this into account. I am sure that, by searching hard, you will find a property in a place nobody who would have a choice would want to go where you can make this work. But this is meaningless. Drawing conclusions from this is the equivalent of concluding that, because someone can win a jackpot in a lottery, playing lotteries is a sensible, lucrative financial move.

That's not a problem. We live in Edmonton so the purpose of this question is to figure out how to convince my husband to stay in a hotel 10 minutes from our house as a date night.

In Canada it would be quite hard to achieve, but I have seen hotels in Asia (Red Hot Deals - Accor Plus) where I was wondering how the hotel can make a profit. Two drinks + two breakfasts even if the cheap variety, when the hotel room is 20 USD there is not a lot of margin left for the hotel...

Suppose also how much of the status stuff comes from the room rate and how much of the status stuff is paid for by Accor.

Actually, it's possible to a certain extent. I am not Diamond, only Platinum, but I more than make back the room rates either by suite talking or using the suite upgrades vouchers at specific high end (Raffles, etc.) hotels.

I fail to see how any room upgrade, no matter how great, contributes to earning back the cost of the room. The OPs question was not about the overall value of status, but whether one could book a room, and receive tangible benefits (like a free breakfast) that make the room essentially free.