“And I feel, so much depends on the weather…
So is it raining in your bedroom retail?”
Example: 800 Days of Weather in New York City
820 Days of Weather Data from New York City, Pulled From DataMarket
(Temps in F, Precipitation in Inches)
Come on admit it. It’s very likely that you would have a use for data like this, whether it was from a single location or for a range of locations, as long as the locations(s) were relevant to your work and the data was easy (and cheap) to get.
Good news: I’m gonna show you how to get this same data for the location(s) you care about, for free, and make it easy for you. Read on for the weather workbook download link and instructions.
First: A Practical Application to Whet the Appetite
As I said in the last post, I think there’s a lot of important things to be learned if we only cross-referenced our data with other data that’s “out there.”
I happen to have access to two years of retail sales data for NYC, the same location that the weather data is from. To disguise the sales data, I’m going to filter it down to sales of a single product, and not reveal what that product is. Just know that it’s real, and I’m going to explore the following question:
If the weather for a particular week this year was significantly better or worse than the same week last year, does that impact sales?
Let’s take a look at what I found:
RESULTS: Weather This Year Versus Last Year (Yellow = “Better”, Blue = “Worse”),
Compared to Sales This Year Versus Last (Green = Higher, Red = Lower)
I don’t have a fancy measure yet that directly ties weather and sales together into a single correlative metric. I’m not sure that’s even all that feasible, so we’re gonna have to eyeball it for now.
And here is what I see: I see a band of weeks where sales were WORSE this year than last, and the weather those weeks was much BETTER this year than last.
And the strongest impact seems to be “number of snow days” – even more than temperature, a reduction in snow this year seems to correlate strongly with worse sales of this product.
Does that make sense? I mean, when the weather is good, I would expect a typical retail location to do MORE business, especially in a pedestrian-oriented place like NYC. And we are seeing the reverse.
Aha, but this is a product that I would expect people to need MORE of when the weather is bad, so we may in fact be onto something. In fact this is a long-held theory of ours (and of the retailer’s), but we’ve just never been able to test it until now.
All right, let’s move on to how you can get your hands on data for your location(s).
Download the Workbook, Point it at Your Location(s)
Step 1: Download my workbook from here.
Step 2: Open it and find a location that interests you from this table on the first worksheet:
Nearly Four Thousand Cities Are Available on the First Sheet
(Pick One and Write Down the Location ID)
Step 3: Open the PowerPivot Window, Open the connection for editing:
Step 4: Replace the Location ID and Fill in your Account Key:
ALSO IMPORTANT: Make sure the “Save My Account Key” Checkbox is Checked!
Don’t Have A DataMarket Account Key?
No problem, it’s easy to get and you only have to do it once, ever.
Step 1: Go to https://datamarket.azure.com/ [link removed due to 404]
Step 2: Click the Sign In button in the upper right to create your account:
Step 3: Follow the Wizard, finish signing up. (Yes it asks for payment info but you won’t be charged anything to grab the data I grabbed.)
Step 4: Go to “My Account” and copy the account key:
Next: Subscribe to the Free Version of the Weather Feed
Go to the [link removed due to 404] data feed page for “WT360” and sign up for the free version:
I’ve Gone a Bit Crazy With This Service and Already Exhausted my Free Transactions,
But That Was NOT Easy to Do
Back in the Workbook…
Now that you’ve entered your account key, set the LocationID, subscribed to the feed, and saved the connection, you can just hit the Refresh button:
Send Me Your Mashups!
Or at least screenshots of them. I’m Rob. At a place called PowerPivotPro. DotCom.
And let me know if you are having problems, too.
I’ll have some more details on Tuesday of things you can modify and extend, like how I added a “Snow Days” calculated column.