In the last few months I’ve worked for a few different (smaller) companies who are still early days with their data. Absolutely nothing wrong with that. Something that caught my attention mostly is data management. Therefore I decided to write a bit more about how to manage data and how I work on that.
Organise your data
Most (smaller) businesses may have their data (excel sheets, csv, invoices) stored on an external hard drive, a laptop, a cloud or something else. It’s not a surprise. Any kind of business at some point starts with one spreadsheet, one software system to use, and so on. It all starts somewhere, it’s just some files saved and you have some systems collecting data, but at some point when you grow, you focus on your work and the money coming. Actively thinking about what data you collect, where you store it and what you can do with it, is probably not high on your priority list. And suddenly 5 years later it’s all over the place and you wouldn’t know where to start doing something with it.
I can assure you that you’re not the only one. Even multi million dollar companies are experiencing the same. They spend big money on clouds, and storing data in data lakes but it often all ends up in the same mess. That information not really helping you, but knowing that this is actually happening, I decided to share my knowledge about it, which might help you.
Where to start
- Create an overview of csv, excels, software that you use that contains data for you and/or your business.
- Create a folder/map for data that is not stored in software (excels, word, csv), preferably on a google drive or something comparable (depending on the size of your business and the amount of data).
- Decide what you do with all the data that’s there (data available via the softwares you use and the data in the folders/maps you created). What could you use, what is not useful. This is important for the next steps.
When you’ve done this, you’re actually a whole lot further than most businesses. The next steps are a bit more technical:
- Decide what data needs to be automatically renewed on a regular basis (e.g. every day, once a week, once a month) and what data can be considered as a one off.
- Transform the (static) excels/csvs to google sheets. Google sheets has a good connection with BigQuery, which makes it easier to upload.
- Make sure you set up an account in BigQuery and that you link your google sheets with it (google is your best friend to find out how).
- Upload the data to BigQuery (could also be mySQL, whatever you prefer).
- Ideally you want to upload your data that is stored in your software straight to BigQuery. How this works will depend on the software you work with. In most cases it is possible to set up a connection between them.
- Almost every upload can be done automatically by (for example) a python script, which can be automated via some software on your laptop that can automatically run this python script whenever you want.
- You might have some loose ends left that don’t exactly match with the above. It is likely that those loose ends could be connected in roughly the same way as explained above.
- If you would like to pay to make things a little bit easier, Zapier is a very good paid alternative with thousands of existing connections, easy to set up.
Once your data is in BigQuery, you can basically do whatever you want with it. Again, maybe you have other software that you prefer over BigQuery, which is fine too. I’m just explaining the main process funneling data down into one system which enables you (or any data analyst) to work with the data. Keep in mind that you can only upload 10TB for free into BigQuery monthly. Now you’ve created a big funnel yourself and all the available data can be analyzed or visualized.
Doing it this way, you’re not only setting your data up just for an analysis once, but it provides long term insights, because data is collected every day, week or month and can be forwarded to your database so you keep adding data to it, which can help you gain insights long term.
If you have any questions, wanna talk about it or want me to help you structure and organize your data, feel free to contact me and we’ll find out what’s possible for you.