Was Tuesday bad, or was it raining?

Every operator has had the conversation. Tuesday was down eleven percent, somebody has a theory, and by Thursday the theory is a decision. Sometimes the theory is right. Sometimes it rained. The two need different answers, and twenty minutes tells you which one you have.

What you need. An export from your point of sale, one row per day for at least a year, with the date and net sales. Your restaurant’s latitude and longitude. A weather history. A spreadsheet.

Step one. Your coordinates. Open Google Maps on a computer, right-click your restaurant, and the popup shows latitude and longitude in decimal form. Click them to copy. That is the format everything below wants.

Step two. The weather history. Open Open-Meteo’s historical weather page. Put your coordinates in, set the date range, tick daily precipitation and daily rain, choose inches, set the timezone to your own, and press Download CSV. No account, no key. Four settings decide whether the answer is any good:

• Set the timezone. Leave it off and you get Greenwich days, which cut a North American evening in half and land Tuesday night’s rain on Wednesday.
• Tick rain as well as precipitation. Precipitation counts snow as water. Pull the one column and a January snow day reads as a wet day, and your winter is mislabeled.
• Stop the range about a week short of today. The last few days come from a different model than the rest of the archive and can change when the main one catches up.
• Delete the first three rows. The file opens with three lines of location metadata before the real header, and formulas written over those produce nonsense.

A note on the word free. Open-Meteo’s free service is for non-commercial use; its own plan table says so, and a restaurant working on its own numbers is a gray area. If that bothers you, the United States government publishes the same thing: the National Centers for Environmental Information will hand you daily precipitation as a CSV with no account, public domain, commercial use fine. The catch is that it is by weather station rather than by coordinates, so you find your station first. The weather history in this piece is from Open-Meteo.com.

Step three. Join them. Two columns, one sheet: date, net sales, daily rain. Before you do, look at your export’s date column and work out what it is. Most give you a business date; some give a calendar date. A Friday that closes at two in the morning posts on two calendar dates unless the export says otherwise, and the weather file is strictly midnight to midnight. If those two disagree, your late-night sales get the wrong weather. Then VLOOKUP the rain onto each sales row. Not XLOOKUP, which does not exist in Excel 2019 or 2016, and a lot of back offices are on those. Add a column for the weekday. Add a column that says wet or dry at your threshold.

Step four. The comparison. AVERAGEIFS for the average of one weekday when wet, and the same when dry. COUNTIFS to count how many of each you have. Two numbers and two counts. That is the whole calculation, and you just did something most software charges for.

Now the four ways it lies to you. We pulled a year of daily rain for four cities and counted it ourselves. Here is what it does.

• You have fewer wet days than you think. Counting one weekday at a time, at a tenth of an inch, wet Tuesdays out of 53 came to 13 in Boston, 8 in Dallas, 12 in Seattle and 13 in Toronto. So your comparison is often eight days against forty-five, and one private party on one of those eight moves the answer several points. Under six of either, do not draw a conclusion.
• Whoever picks the threshold picks the answer. Same year, same weekday, counted two ways. Dallas Tuesdays: 26 days had some measurable rain, only 8 had a tenth of an inch or more. Toronto: 38 against 13. Boston: 29 against 13. Seattle: 24 against 12. Run it at two thresholds. If the answer flips, you do not have one.
• A third to a half of wet days were dry at dinner. This is the objection everyone raises and it deserves a number. Taking every day with a tenth of an inch or more and looking only at five, six, seven and eight in the evening: Toronto, 47 of 90 wet days were dry right through service. Dallas 32 of 74. Boston 32 of 90. Seattle 34 of 103. The fix is in the same download: take hourly rain instead, and judge your service window rather than the calendar day.
• Your wet days may all be one season. Dallas, over the same year, had no wet Tuesday at a tenth of an inch or more between August and February. Every one landed between March and July. Compare that against dry Tuesdays from the whole year and you have measured summer against fall. Compare inside a season, or list the dates and look at them.

What to do with the answer. Two things, both small, both worth money. Stop apologizing for weather nights: if rain is worth some number at your restaurant and Tuesday was down more than that, the gap is the story and the rest is the sky. And staff to it. For some rooms rain goes the other way, people come in and stay, and if that is yours a wet forecast is a reason to add somebody, not cut.

The honest limit of doing it by hand. You will do this once. It answers one question, about one weekday, at one restaurant. Next month the question is different, and if you run four restaurants it is four afternoons. That is the argument for having something do it nightly, and plenty of products now do, ours among them. Do it by hand first anyway. You will trust the number more, and you will know exactly what any software is claiming when it tells you the rain explained your Tuesday. Syphor takes the weather and the calendar off every night, for every restaurant you run, before anything on the page is called a problem. The method above is our nightly job, written out by hand.