Recommend
3 
 Thumb up
 Hide
6 Posts

BoardGameGeek» Forums » Everything Else » Chit Chat

Subject: Data-crunching problem [Hivemind] rss

Your Tags: Add tags
Popular Tags: [View All]
If it falls on me and pins me underneath it, does that still count as seizing the day?
United States
Kansas City
Missouri
flag msg tools
badge
I think that all right-thinking people in this country are sick and tired of being told that ordinary, decent people are fed up in this country with being sick and tired. I’m certainly not, and I’m sick and tired of being told that I am.
Avatar
mbmbmbmbmb
My supervisor (Dan) at the warehouse where I work knows I used to do programming, so he will occasionally ask computer-related questions. Yesterday he asked one that goes beyond my personal knowledge, so I’m turning to the hivemind.

Dan apparently wants to do some data analysis on our company’s sales figures. (We do B2B fasteners: nuts, bolts, screws, tape, adhesives, zip ties, etc.) He has a copy of all the sales data for 2015 (2016 is not complete yet) in Excel format -- about 50,000 lines of it, according to him. He has watched some YouTube videos about setting up ‘dictionaries’ to manipulate the data, but he’s running into some inherent constraints for that method.

What he would like to be able to do is query/filter the data so that, for instance, he can generate a list of all the customers who have made purchases of $5 or less, then maybe find out how many of those size purchases each of those customers made, and so forth. He wants to be able to really drill down on this data.

We have a real mix of sales. Some customers might only spend a couple hundred dollars per year, while we have others that regularly buy thousands of dollars of stuff per month. Also, the data he has is for our location only -- we have two satellite locations in our division, plus there are two other divisions, so he wants the solution to be scalable so that it will work for both the individual locations and the whole company.

I told him I thought he would probably be better off using a relational database, maybe something like Access since our company runs Windows. (My work computer is still using Windows XP, and his is either using XP or possibly 7.) Dan says that if he needs to, he is happy to learn a new package or method for extracting the info he wants. Whatever he’s doing, he’s keeping it on the downlow until he can complete his analysis.

So, does anyone have a suggestion for a product or method that will allow Dan to export sales data from Excel, manipulate it, drill down on it, and is scalable for the entire company?

(I may not have explained this very well, so feel free to ask for clarification.)
2 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Bryan Thunkd
United States
Florence
MA
flag msg tools
badge
Avatar
mbmbmbmbmb
Excel pivot tables will do what he wants. It'd be better in a database, but there's a lot more cognitive overhead in learning how to do that.
9 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Josh Jennings
United States
San Diego
CA
flag msg tools
badge
Avatar
mbmbmbmb
Bryan is right. My caveat would be that depending on how flexible he wants it to be, it may be that when it is run over 50,000 lines it results in some slow tables. We recently ran a pivot table over all of the software requirements of our program (on about the same scale as your spreadsheet) and it worked fine, but if you try to sort the pivot table it would freeze up because of the sheer number of entries.

So, I would say try the pivot tables and if that doesn't work out well, then plop them into access of mySQL and maybe right some scripts so that your supervisor can do queries on the data without having to learn SQL. Or you can teach him basic SQL, haha.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt Brown
United States
Okemos
Michigan
flag msg tools
badge
Avatar
mbmbmbmbmb
As much as other databases would get the nod from me, Access is obviously the one built around importing Excel. Just learning how to do queries in Access will be enough although given the product a decent interface could be developed as well. This way the data itself could be managed using Access versus Excel. Excel is good for what it does, but once you start going "We need to track data about our customers," a database is way better. +1 to MySQL if you go outside of Access. I assume the company would have a website, and whoever develops it, should be able to create an interface for it.
1 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Justin
United States
Springfield
Pennsylvania
flag msg tools
badge
Avatar
mbmbmbmbmb
A problem for someone who isn't super savvy could be the auto-save in Access. You could alter or delete something without realizing, and it would be tricky to track the change.

I'd add columns to the end of the data and make simple if statements to flag things you're looking for (eg - indicator if sale is greater than $50). Then use a pivot table. As mentioned, there is some overhead getting comfortable with manipulating a pivot table, but it's not as hard as programming SQL or tracing Access queries.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Matt
United States
Central Coast
California
flag msg tools
0110100110010110
badge
Avatar
mbmbmbmbmb
Pivot Tables will get almost all of it done, but an even simpler option might be a Table (Insert->Table, it's right next to Pivot Table).

Once you convert data to a table you get column headers that allow you to filter the entries by value, (extra, logical function columns at the end of the table can make this easier for non-categorical data, like "individual sale over $5") and you can attach simple subtotal (or max, or min, etc.) functions to the bottom that automatically respect the filters that you've turned on.

So, you can easily do things like "Total number of sales over $5" or "Largest sale of 3/4in. washers to one client" or any other simple task.

This can help get a good feel for the data before you dive into more complicated contortions.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.