5 Data Sources Every BI Analyst Should Know
As a BI analyst, during your career, you’ll work with loads of different data sources and types.
Considering that there are so many data sources that potential employers could need you to analyse, you might be worrying whether the fact that you’ve never worked with them before will negatively impact your chances of landing a particular job.
Well, the good news is that they probably won’t expect you to have worked with or even be familiar with all of the data sources they use.
However, there are some that pretty much every employer will expect any BI analyst they hire to know well. Ones that are ubiquitous. In this article, I’ll break down the top 5 data sources and types you’ll need to know to land your first job as a BI analyst. Let’s jump in.
Excel
Now, this first one is pretty obvious and it’s more of a data type than a source. An Excel file can, obviously, contain any data in it from sales to HR to finance, really anything at all.
All businesses use Excel to some extent and whereas you won’t be expected to be familiar with all of the metrics and dimensions contained in all of their Excel files (after all, that would be impossible), you will be expected to know how to format, structure and clean Excel data in order to prepare it to be loaded into the BI tool you’ll be working with.
A lot of people new to BI, who’ve been using Excel for years, tend to think that, because they were able to analyse and visualise their data in Excel, all they would need to do to start analysing and visualising that same data in a BI tool is to simply upload the file as it is. Without understanding that data needs to be structured, formatted and optimised in a particular way for it to work properly with a BI tool and get the best results.
I’ve actually already written an article on best practices for preparing Excel data for BI tools so be sure to check that out if you’re unsure or just need a refresher. Moving on.
SQL
Next up is SQL. SQL has been around since the early 1970s and it’s not going anywhere anytime soon. Even though we now have semi-structured and unstructured data types, so much of the data generated by businesses today is collected in relational SQL databases and warehouses. Mainly because it is such an efficient model for storing and querying data.
So the majority of potential employers will expect you to know SQL to a relatively high level.
That being said, BI analysts are not data architects or engineers so, most of the time, you won’t be expected to know how to do the tasks that they perform.
The kind of SQL that you’ll be expected to know focuses on data analysis. So you’ll need to know how to write queries to select data from multiple tables, join that data together and aggregate it in such a way that it serves the analysis and dashboards you’ll need to create. I call this level 1 SQL knowledge.
Level 2 is knowing how to use SQL functions within your select queries to manipulate, transform and clean data.
Then level 3 would be knowing how to write queries to create and manage relational databases and tables. Then load and modify the data within them.
If you know how to do all of the above then you’re pretty much set. Like I said before, you’re not being hired as a data architect or engineer so the SQL necessary for those roles won’t be expected of you.
If you’re unfamiliar with SQL, I’ve created a 3 part learn basic SQL in 15 minutes video series that covers the 3 levels I just mentioned. I’ll put a link to them in the description so you can check them out.
Google Analytics
I could also have called this data source simply “Web Analytics” but, because Google Analytics is by far and away the most used web analytics platform, this is the one you should know and be familiar with.
When I worked for a SaaS BI tool start-up many years ago, the tool had connectors to around 50 different data sources. I was able to see the stats of all the data connectors most used by our client base.
You’ll probably guess that number one was Excel. Number 2 was CSV and number 3 was Google Analytics.
It makes sense. Most businesses have some form of web presence or app that Google Analytics collects data for.
So if you don’t know your way around Google Analytics, the metrics and dimensions, and the existing reports available in the Analytics interface that you might need to recreate, then you won’t be taken very seriously.
To practice working with Google Analytics data in a BI tool, I would normally have suggested you connect to the Demo data that Google makes available using Google’s Looker (previously Data) Studio.
But recently, Google crippled this option by imposing really strict quota limits on the Analytics API that now makes it unusable.
Instead, you now need to find a way of extracting your Analytics data into a different source like BigQuery or by using a third-party data hub like Power My Analytics or Supermetrics.
Whichever way you do it, you will need to familiarise yourself with how Google Analytics is structured and the combinations of different metrics and dimensions needed to create different reports.
You’ll need to know things like what constitutes an engaged session, the different events that get collected and how to differentiate website data from app data within properties.
PPC Data
The next data source type you’ll need to be familiar with is PPC (Pay-Per-Click), otherwise known as paid advertising.
Like with web analytics, the clear market leader in PPC is Google, with Google Ads so that is where I recommend you get started.
When it comes to PPC, you’ll again need to be familiar with all the metrics and dimensions available and how they combine to create different reports.
But more importantly, you simply need to know how PPC works. For example, how a Google Ads account is structured, what ad groups are, the different kinds of ad campaigns, placements, bid strategies, creatives etc. Then you’ll need to understand metrics like CTR, CPC, ACOS and ROAS.
Unfortunately, unlike with Analytics, Google doesn’t have a demo data account you can practice with.
What I’d suggest is to just learn as much as you can about the things I just mentioned. Just do a YouTube search for “Google Ads for beginners”.
Then, if you were wanting to build a demo dashboard for your portfolio, you could maybe build a data source yourself in Excel.
To make it even more interesting, you could mix dummy data from other PPC sources like Bing Ads, AdRoll, Facebook, Instagram, LinkedIn etc. For ideas check out PowerMyAnalytics.com which offers connectors to several PPC data sources.
If you don’t know how to create a dummy data set, I’ll be making a video for that soon so don’t forget to subscribe to the YouTube channel and turn on notifications so you don’t miss it.
Email Marketing
The final data source type you’ll need to be familiar with is Email Marketing. There are lots of different tools on the market for this but some of the most used are Mailchimp, Active Campaign, Campaign Monitor and HubSpot.
It really doesn’t matter which one you choose, they all have very similar metrics and dimensions.
Analysing email marketing data is fairly straightforward. It’s all about the number of emails sent in a campaign, the number of bounces, opens and clicks.
But then, normally, these email marketing tools are linked to other tools such as a website so that you can track the number of visits and sales made as a direct result of someone clicking on an email they received from a specific campaign.
That's where the real value lies for any business and what business intelligence is all about, joining the dots between different data sources and tools.
Not all businesses use email marketing tools like this but a lot do so it’s definitely something you need to familiarise yourself with.
You could always take a 30-day free trial of MailChimp and take a look at what the built-in reports look like. Just click on the pop-out banner here.
If you want to learn more about what it takes to land your first BI analyst job, why not check out this article here giving you a handy 7-step guide to doing just that?