Lachezar Arabadzhiev

Data analysis is vital for the success of any business. It’s the process of collecting, modeling, and analyzing data to obtain useful insights for decision-making. Without data analysis tools, it would be hard for businesses to make effective and data-driven decisions. 

Luckily, we are in a digital era with plenty of options available. Although it’s good to have many options, most businesses end up using the wrong combination of tools or simply get overwhelmed. This article will guide you through the best five data analysis tools accompanied by relevant data analytic techniques that can help your business grow.

1. Google Data Studio (data visualization)

Google data studio

Google Data Studio is a free data analytics tool that turns data into fully customizable interactive dashboards and reports that are easy to read and share. The tool integrates automatically with many popular data sources, making it one of the most sought data visualization platforms.

Dynamic Dashboards and Data Analysis with Data Studio – 2021

Last Updated October 2021

  • 54 lectures
  • All Levels
4.6 (630)

Learn how to build powerful data visualizations and unlock insights with ease in Google Data Studio | By Lachezar Arabadzhiev, SkildLabs Inc.

Explore Course

Features

Google Data Studio allows you to connect, visualize, and share data easily.

Business scenario

A digital marketer or data analyst might face a hard challenge in turning raw data from different sources into one easy-to-read and shareable report. In the past, businesses did this task manually by hiring programmers to link reports every time. However, thanks to Google Data Studio, merging data from different sources now takes a split second.

Google Data Studio does a lot more than just data visualization. It contains features that allow you to transform data to obtain valuable information for decision-making. For example, you can use formulas for advanced analytics such as calculated formulas and CASE Statements to manipulate data as needed.

Some data sources such as Google Analytics may provide ambiguous raw data. As such, you might need to group different elements to get a cleaner report. You can use Google Data Studio CASE statements to do this.

For example, you can group different Facebook traffic channels into one using this CASE statement:

CASE
WHEN Source IN ("facebook", "m.facebook.com", "l.facebook.com", "facebook.com", "lm.facebook.com") THEN "Facebook"
ELSE "Not Facebook"
END

Or differentiate between paid social traffic and organic social traffic using this CASE statement in Google Data Studio:

CASE WHEN Medium IN ("social-ad") THEN "Paid Social"
WHEN Medium IN ("social") THEN "Organic Social"
ELSE "_Other"
END

2. Google BigQuery (data analysis)

Google Big query

Google BigQuery is a fully managed data analytics and warehouse platform that uses a serverless computing model and a built-in query engine to process petabytes of data in minutes. 

It’s a flexible, powerful, and fast data warehouse integrating with other Google Cloud Platform services. Google BigQuery is, in simple terms, a platform that you can use to analyze a huge pool of data. It features an inbuilt machine learning capability and supports ANSI SQL syntax querying.

Features

Business scenario

When comparing a company’s performance, you need to analyze a ton of data. This can consume a lot of time, even if you use a powerful CPU. For example, you may want to sort data from a table with millions of rows and export filtered data to a new CSV file. Since Google BigQuery operates on the Google Cloud, the process will compute in a matter of minutes.

As a test, you can use the following PHP code below to export your table to a CSV file: 

use Google\Cloud\BigQuery\BigQueryClient;
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';
// $tableId	= 'The BigQuery table ID';
// $bucketName = 'The Cloud Storage bucket Name';
 
$bigQuery = new BigQueryClient([
	'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
$destinationUri = "gs://{$bucketName}/{$tableId}.json";
// Define the format to use. If the format is not specified, 'CSV' will be used.
$format = 'NEWLINE_DELIMITED_JSON';
// Create the extract job
$extractConfig = $table->extract($destinationUri)->destinationFormat($format);
// Run the job
$job = $table->runJob($extractConfig);  // Waits for the job to complete
printf('Exported %s to %s' . PHP_EOL, $table->id(), $destinationUri);

3. Microsoft Excel (data analysis)

Microsoft Excel

Microsoft Excel is a data analysis and visualization software developed by Microsoft. Excel makes use of spreadsheets to organize and analyze numbers. Besides, it also features graphing tools, calculation pivot tables, and macro codes to help analyze and visualize data seamlessly. Microsoft Excel is a widely used platform by companies of all sizes around the world. According to Statista, Office 365, which features Excel, controls 38% of the global office suite market share.

Features

Business scenario

You can analyze data in Excel by using the Analyze Data button. In a business, financial operations are critical. You need to calculate profits, losses, means, interests, and so much more. 

Excel is the powerhouse of all these business calculations. It features thousands of formulas, functions, and macro codes to help you get the job done fast. You can also visualize data as line graphs, pie charts, and bar graphs, to name a few.

Excel uses VBA code which makes data analysis easy. The VBA code can format your spreadsheets, search for items, filter items, send emails, and much more. Let’s look at some useful VBA codes for data analysis.

VBA code for creating a chart

Sub CreateChart()

Dim MChart As ChartObject

Set MyChart = ActiveSheet.ChartObjects.Add(Top:=60, Left:=100, Width:=500, Height:=300)
MyChart.Chart.SetSourceData Range("C4:D10")

End Sub

VBA code for analyzing negative numbers 

Sub highlightNegativeNumbers()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsNumber(Rng) Then
If Rng.Value < 0 Then
Rng.Font.Color= -16776961
End If
End If
Next
End Sub

4. Microsoft Power BI (data visualization)

Microsoft Power BI

Microsoft Power BI is a data analysis and visualization platform that integrates apps, software services, and connectors to analyze and share insights. BI stands for Business Intelligence. Power BI allows organizations to visualize and share data easily, and the best thing about Power BI is that it comes as an Android app, desktop application, and an online SaaS.

Features

Business scenario

Power BI is one of the most useful data analysis tools for business. You can study the insights, trends, and business intelligence. This tool features artificial intelligence that allows you to form queries using your own words. 

Besides, it also automatically discovers interesting insights from your data, schedules reports, and sends notifications if anything changes. You can use data analysis expressions to solve data analysis problems and calculations. 

Data analysis expressions, popularly referred to as DAX, are a collection of constants, functions, and operators. You can use them in an expression or formula to generate new insightful information.

Examples of useful DAX functions in Microsoft Power BI are as follows:

The FILTER function

Expression - FILTER(<table>,<filter>)

For example, you want to find the number of shipped products above $300 from your list of products. In this case, you will combine two functions. The first is the COUNTROWS function that counts the number of rows. Next, add the FILTER function to complete the expression.

COUNTROWS(FILTER('Shipped', 'Shipped'[Shipped] > 300))

The ALL function

Expression - ALL(<table> or <column>

Like the filter function, the ALL function can work together with another function for more specific results. This function overrides any applied filters and returns all the values in the rows or columns in a given table.

The RELATED function

Expression - RELATED(<column>)

If you have two tables with related data, you can use the RELATED function to track the relationship. This function executes a LOOKUP function to evaluate values in the other table regardless of whether another filter has been applied.  However, this only works in a row context.

5. Looker (data analysis)

Looker

Looker is a browser-based business intelligence software (BI) that helps businesses analyze data and develop insightful visualizations. It is a cloud-based platform that provides businesses with a unified source of business analytics. Looker embeds custom data apps, workflow integrations, and analytics to provide truthful and powerful sources. It is compatible with databases such as MySQL and Oracle.

Features

Dynamic Dashboards and Data Analysis with Data Studio – 2021
Lachezar Arabadzhiev, SkildLabs Inc.
4.6 (630)
Google BigQuery for Marketers and Agencies – 2021
Lachezar Arabadzhiev, SkildLabs Inc.
4.3 (339)
Introduction to Supermetrics for Data Studio and BigQuery
Lachezar Arabadzhiev, SkildLabs Inc.
4.2 (43)
Introduction to Modern Marketing Analytics
Lachezar Arabadzhiev, SkildLabs Inc.
4.8 (7)
How to Create an Effective Measurement Plan?
Lachezar Arabadzhiev, SkildLabs Inc.
4.3 (14)

Courses by Lachezar Arabadzhiev

Business scenario

Looker generates and submits SQL queries against a database connection. Fundamentally, the SQL queries are generated based on a LookML project. A lookML project describes relationships between columns and tables in the database.  

You don’t have to be a guru in SQL to generate a powerful Looker model, and you only need a slight understanding of SQL to write LookML. It is easy to learn even for a non-tech-savvy person.

Here is a video tutorial for more LookML details from the Looker team. It’s easy to import local and remote projects using LookML.

LookML to import a local project

# This project
project_name: "my_project"
 
# The project to import
local_dependency: {
  project: "my_other_project"
}

LookML to import a remote project

remote_dependency: ga360_block {
  url: "https://github.com/llooker/google_ga360"
  ref: "master"
  override_constant: connection {
	value: "importing_project_connection"
  }
}

Choosing the best data analysis tools

There are many data analysis tools available today, which makes it difficult for businesses to choose the best ones. If you want to make the best choice, you need to understand your needs first before picking a specific data stack. Look for features that will make your work easy to collect, transform, share, and visualize data. Remember, it is not about the number of tools you have at your disposal but the quality of insights you produce, which can help you grow your business.

Page Last Updated: November 2021

Top courses in Data Analysis

The Data Science Course 2021: Complete Data Science Bootcamp
365 Careers, 365 Careers Team
4.6 (99,468)
Bestseller
Microsoft Excel - Advanced Excel Formulas & Functions
Maven Analytics, Chris Dutton
4.7 (56,035)
Bestseller
Learning Python for Data Analysis and Visualization
Jose Portilla
4.4 (16,885)
Bestseller
Data Analysis with Pandas and Python
Boris Paskhaver
4.7 (15,142)
Bestseller
Data Analysis Essentials Using Excel
Symon He, Travis Chow
4.5 (7,668)
Complete Introduction to Google Data Studio 2021 Edition
Ian Littlejohn
4.6 (3,254)
Bestseller
Data Analysis Bootcamp™ 21 Real World Case Studies
Rajeev D. Ratan, Nidia Sahjara
4.4 (604)

More Data Analysis Courses

Data Analysis students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo

Courses by Lachezar Arabadzhiev

Dynamic Dashboards and Data Analysis with Data Studio - 2021
Lachezar Arabadzhiev, SkildLabs Inc.
4.6 (630)
Google BigQuery for Marketers and Agencies - 2021
Lachezar Arabadzhiev, SkildLabs Inc.
4.3 (339)
Introduction to Supermetrics for Data Studio and BigQuery
Lachezar Arabadzhiev, SkildLabs Inc.
4.2 (43)
Introduction to Modern Marketing Analytics
Lachezar Arabadzhiev, SkildLabs Inc.
4.8 (7)
How to Create an Effective Measurement Plan?
Lachezar Arabadzhiev, SkildLabs Inc.
4.3 (14)

Courses by Lachezar Arabadzhiev