Here are the best data analytics tools that you can use for free.
We’ll start simple.
Free doesn’t always mean simple, as I’ll show, but it’s a good philosophy to be guided by at first.
There are many complex problems that can be solved with data analytics. For some of them, you may need a data warehouse or machine learning or ‘big data’. But there are also a lot of relatively simple problems. In this article, we’re aiming for use cases that solve relatively simple issues. An example would be: ‘In what circumstances do men buy more sneakers than women?’. You would turn to analytics solutions if a standard report from your sales or ERP did not give you enough information to conduct the analysis.
In typical data analytics use cases we need to build the following pipeline:
- Extract, Transform, Load process (ETL): getting the data from the source to the desired destination
- Storage: the place we hold our data
- Analytics: the tool we use for the analytics. We want to analyse the data, create visualisations and share them with our co-workers.
Tier 1: Microsoft Excel (or Google Sheets)
Excel excels at many things. It’s a perfectly capable analytics tool if you know how to create pivot tables and graphs.
It’s ideal if your dataset for analysis fits into a single table and the file size is measured in up to tens of megabytes. If you have a CSV export from an existing system that already contains somewhat aggregated data, then Excel is a good place to start.
How Excel solves our analytics pipeline:
- The ETL process mainly involves you opening a CSV or XLSX file.
- Storage is within the Excel file itself.
- Analytics happens within Excel.
Where Excel runs into trouble:
- Your dataset does not fit into a single table. If you need to connect your customers and orders table, you end up having to learn functions like XLOOKUP and VLOOKUP, which are neither intuitive nor convenient. In our example use case, we would run into this issue.
- Your dataset becomes too big for Excel to handle.
- You need to allow other people undertaking analytics to access your Excel-based system. It’s very hard to ensure that no one will ruin your perfectly calibrated structure.
Also, Excel is not truly free. If you’re not paying for it, then somebody in your company is. But it is ubiquitous.
The free alternative is Google Sheets, which supports similar features as Excel but takes some time to get used to.
Tier 2: Power BI Free, Tableau Public & Google Data Studio (cloud-based solutions)
Excel is focused on tabular data. Power BI is focused on visualisations. When moving from Excel to Power BI you immediately notice a few things:
- You lose the ability to easily manipulate the data in spreadsheet format.
- On the other hand, the quality you gain in visualisations is amazing.
Unlike with Excel, in Power BI you don’t open files directly. You import and transform data sources via built-in tools. This in turn means no more XLOOKUP, and you can visually connect the orders and product tables, creating a convenient data model. The amounts of data you can handle increase substantially compared to Excel. Although setting up data models may sound difficult coming from Excel, Power BI is quite intuitive and works well for both beginners and experienced users.
Your typical basic set-up in Power BI involves the following:
- Creating the data import and mapping processes.
- Creating and analysing the visualisations until you get the answers to your questions.
- Sharing the results and providing customisation options for your co-workers.
Since we’re using Power BI Free, there are bound to be major limitations. For our basic use case, the primary limitation is sharing. You’re either limited to sharing your report files via e-mail (meaning you have total control, exactly as you do in Excel), or sharing it with the entire world via Power BI’s publishing functionality. There’s no middle ground.
What about Tableau?
Tableau has been on the market for longer than Power BI. Its analytics features are arguably more capable when it comes to advanced use cases, and a lot of professional analysts prefer it. Tableau offers Tableau Public for free. It’s a great tool, but with one additional limitation compared to Power BI: file-saving only happens on the public cloud, your reports will be visible to the entire world as long as anyone knows your username. You can’t store them on your desktop for yourself or send them via e-mail to a selected few.
Google has a tool called Data Studio, which is free of charge.
It works best with different tools in the Google ecosystem. So instead of importing files from your desktop, you are importing them from Google Drive etc. You get to control who sees your reports – which neither Power BI Free nor Tableau Public allow. However, coming from Tableau or Power BI, you immediately notice a lack of features.
Tier 3: Scalable data analytics
All the products in Tier 1 and Tier 2 (mostly) handle ETL, data-storing and visualisation in a single place. By only using them, it’s hard to decouple elements to build a scalable solution. Going one step further requires switching out the ETL and storage part of our simple analytics pipeline. Most cloud providers offer free trials, but there are some options that are permanently free, albeit with caveats.
Databricks is an advanced product which can solve the ETL, storage and analytics part of our problem, or only a subset. Its free version is called Databricks Community Edition. There are serious limitations if you want to do any repetitive analysis. You also need some Python, R, Scala or SQL knowledge, so it doesn’t pass our ‘non-developer’ requirements.
Our choice is Google BigQuery – the Google Data Warehouse platform. Google has generous free quotas for casual analysis. While not as easy in set-up as Power BI or Tableau, it is manageable, and can be handled without any coding. It does only solve ETL and storage, but you can connect any of the Tier 2 tools, which can then access the data. This means your data are stored in the Google Cloud and accessed by the BI tool of your choice, be it Excel, Power BI or Google Data Studio.
Data analytics tools comparison
Summarizing the main plusses and minuses of most used free data analytics tools.
|Power BI Free|
|Google Data Studio|
Analytics set-up – what to do yourself and what to outsource
Typically, you don’t employ an in-house data analyst: YOU are the data analyst. You pose the right questions and analyse the results. If you have good external consultants on board, they help you in the analysing steps.
If you’re running a small business, you’re also the person who sets up the analytics tools, such as Power BI. You should be getting external help if any of the following happens.
- If the alternative cost is getting too high and you can’t focus enough on marketing, sales or running your business, then get some external help to handle the technical set-up.
- Secondly, if your set-up is getting too complex for you to handle – be it due to the scope, the visual requirements or the need for speciality skills such as Python.
A popular usage scenario is that you hire external help to do the initial technical set-up, and then take things over for minor modifications in the future. Hiring professionals might seem costly, but you get what you pay for: they’ll get the tasks done more quickly than you would.