I’ve talked a lot about what skills you should learn in some of my blog posts, specifically my How to Become a Data Analyst (Ultimate Guide). These are: SQL, Excel and a data visualization tool like Tableau or Power BI. However, I often get asked what tools I actually use in my day-to-day work as a data analyst, and funny enough, they aren’t the same as the ones I recommend.
Overview
Roughly here’s the breakdown of how often I use the following tools on a day-to-day basis:
Google BigQuery (aka SQL): 40%
Python: 30%
Google Looker Studio (Looker): 20%
Excel: 10%
This breakdown is just an estimate, since some days I spend more time in Looker building reports, while on others I might be creating summary spreadsheets for stakeholders. On average, though, this is fairly accurate.
Tools
Below are the tools I use, how I use them, and specifically the methods/concepts I use for each.
SQL
I use this to extract data from our database. I primarily use BigQuery which is what connects to our reports (more on that in the Looker section below). The queries I write are used in 3 main ways:
Connect to Looker Report: Using Google Cloud Platform I create data sources with specific SQL queries that connect to Looker Reports. Sometimes these reports join a few tables and returns the raw data, then aggregated in the Report. Other times, the reports require more complex queries, so I create custom ones tailored to the specific requirements of the project.
Self Serve Queries: These are queries that stakeholders can run themselves to extract data. Not everything I create needs to be monitored constantly (aka it doesn’t need to be a Looker report). Sometimes a stakeholder has a request that’s temporary or because of a specific project, in these cases I create queries that they can use. Often with variables they can use to change and compare data like having a date filter.
Analysis: Lastly, I’m tasked with analyzing data on my own. This is when I use SQL to extract the data and then perform either an analysis within BigQuery or more often than not using a tool like Python (more on that below).
What I use in SQL:
Aggregated functions
Date functions
UNIONs
CTEs & subqueries
Windows functions (this is more rare)
Python
Python is what I use when I’m looking for any trends/insights. Primarily for projects that don’t evolve into self-serve queries or reports. For these analytical requests, I analyze the data, gather my insights, and then visualize it to present to stakeholders with my suggestions. Most of my Python work uses SQL, specifically for extracting data from the BigQuery database (how I connect SQL and Python: How to Run SQL in Python: Connecting to PostgreSQL and BigQuery). Then I load it into Python to analyze and/or visualize the data. Here are some examples of what I’ve used in Python:
Created a churn prediction model (My First Predictive Model: Explanation (Part 1) and My First Predictive Model: Code (Part 2))
Automated a weekly report (How I Saved 10 Minutes with a Python Script and How I Automated My Weekly Python Script).
What I use in Python:
Lists
Dictionaries
Functions
Variables
Libraries:
Pandas
BigQuery Connect
NumPy
Matplotlib
Seaborn
Datetime functions
Scikit-learn (for my machine learning)
Looker
This is a visualization tool using Google Cloud Platform (Google Looker Studio) instead of local data sources like Tableau or PowerBI. It can create dashboards and reports. This is used to create reports that our stakeholders can use. Like I mentioned in the SQL section, these are populated by queries I write in BigQuery that connect to Looker. Here’s a few examples of reports I would create:
Player retention by month
Average active users
Number of registrations by source (e.g. Google) by month.
Tableau and PowerBI are the most popular data visualization tools, but I’ve noticed a growing demand for Looker. It’s easy to get started with, and you can quickly connect a Google Sheet as a data source to a report. I’ve use Looker for my personal projects like: Deep Work Dashboard 2023. I highly recommend trying it out if you want a quick way to start practicing data visualization.
What I use in Looker:
Line charts
Bar graphs
Combo graphs: line charts & bar graphs
KPIs
Tables
Pivot tables
Excel
Lastly, I use Excel mostly when stakeholders ask me to summarize data using a spreadsheet. I always joke that no matter what fancy tool I use or cool visualization I create, it all ends up back in Excel. While I personally prefer using the tools I mentioned above, I often get requests from stakeholders to aggregate or present data in Excel. In these cases I just use Excel. The weekly report I automated using Python (see more on that in the Python section) is an Excel spreadsheet.
What I use in Excel:
Formula & Functions:
COUNT / COUNTIF
MEDIAN
SUM / SUMIFS
Charts
Pivot tables
How to Learn These
If you want to know how I stay up-to-date on these skills check out my blog post: How to Stay Up-to-Date on Data Skills. But if you’re personally interested in learning Python, SQL or Excel check out my courses below:
These are all affiliate links so if you decide to purchase the additional course materials I may get a small commission at no extra cost to you. If none of these courses interest you (no offense taken!) you can check out the courses I’ve personally taken to learn these skills: Data Analytics Resources.
Conclusion
Now, this is pretty much all the tools on a daily (or at least weekly) basis. I use all of the major tools that other analysts recommend to learn: SQL, Excel, and a data viz tool. I use Python frequently, but I know plenty of great data analysts who don’t. It really depends on the role. Hopefully this gives you an idea of the tools I use and how I use them. If you want to know what I do on a day-to-day basis check out this article: A Day in the Life of a Data Analyst.