MG3DSEBCA200 -BASICS OF DATA ANALYTICS USING SPREADSHEET

UNIT 1
DATA
Data refers to basic facts and figures. It is generally raw information that simply includes basic
numbers or texts. In computers, it may be in the form of different types of files, such as images,
text, graphics, and videos. In computer science, data refers to raw, unorganized facts and figures
that can be processed by a computer. It is the fundamental building block of information, and
computers use it to perform calculations, store information, and facilitate various
operations. Data can come in different forms, including numerical data, text, images, and other
digital representations. Data plays a crucial role in understanding the business trends. Many
organizations generate and process huge volumes of data. This huge and complex data is referred
to as "Big Data". Thus Data and Big Data include huge volume, high velocity, and extensible
variety of data.
Qualitative Data: Qualitative data refers to descriptive information that cannot be easily
measured but can be observed or categorized. It often involves words, descriptions, or
attributes rather than numbers. In computer science, qualitative data might include user
feedback on a new interface, descriptions of software bugs, or the nature of network
vulnerabilities.
Quantitative Data:
Quantitative data involves numerical values that can be counted, measured, or expressed as
numbers. In computer science, quantitative data might include the number of errors in a
code, the speed of a network connection, or the performance of an algorithm.

DIFFERENT TYPES OF DATA

 structured data,
 semi structured data, and
 Unstructured data.
Structured Data
Any data that can be stored, accessed and processed in the form of fixed format is termed as a
'Structured' data. Over the period of time, talent in computer science have achieved greater
success in developing techniques for working with such kind of data. Structured data is generally
stored in tables in the form of rows and columns. Structured data in these tables can form
relations with other tables. Humans and machines can easily retrieve information from structured
data. This data is meaningful and is used to develop data models.
o Structured data is used by many business organizations. Companies apply data
visualization techniques on the structured data to extract some meaningful
insights from that data and develop data models. Machine learning algorithms are
applied on this data so that they can predict the future outcomes based on this.
o Data present in a Relational Database is the best example for structured data and
this data can be accessed using a structured query language (SQL).
 o Structured data is highly secured and requires low storage space. About 20% of
the data is [Link] used on structured data are MySQL, PostgreSQL,
SQLite, etc.
Following are the advantages of maintaining structured data:
o It is easy to search for data
o Less storage space is required
o More data analytics tools can be used
o Data is highly secured
And, listed below are the disadvantages of keeping the data in a structured manner:
o Data is not flexible
o Its storage options are limited
Example
Employee record
Product record

Unstructured Data
Any data with unknown form or the structure is classified as unstructured data. In addition to the
Size being huge, un-structured data poses multiple challenges in terms of its processing for
deriving value out of it. Typical example of unstructured data is, a heterogeneous data source
containing a combination of simple text files, images, videos etc. Unprocessed and unorganized
data is known as unstructured data. This type of data has no meaning and is not used to develop
data models. Unstructured data may be text, images, audio, videos, reviews, satellite images, etc.
Almost 80% of the data in this world is in the form of unstructured data.
 Unstructured data needs a lots of storage space. Here, data is not secured. It is difficult to
search this data as it is not organized properly. This data is stored in NoSQL databases as
they can't be managed using relational databases. It is very difficult to get insights from
this data.
  Text files, Emails, data from social media applications, IoT, media etc., are examples of
human generated unstructured data. Satellite images, scientific data etc., are examples of
machine generated unstructured data.
 Tools used on unstructured data are MongoDB, Hadoop, DynamoDB, Azure, etc. Data
visualization is best for analyzing unstructured data as they show hidden meaning of that
data.
Following are the advantages of using unstructured data:
 Data is flexible.
 This data can be used for a wide range of purposes as it is in its original form.
The disadvantages of using unstructured data are as follows:
 It requires more storage space.
 There is no security for data.
 Searching for data is a difficult process.
 There are limited tools available to analyze this data.
Example
Social media reviews, satellite images, polling results,

Semi-Structured Data
Semi-structured data can contain both the forms of data. We can see semi-structured data as a
Structured in form but it is actually not defined with e.g. a table definition in relational DBMS.
Semi structured data is organized up to some extent only and the rest is unstructured. Hence, the
level of organizing is less than that of Structured Data and higher than that of Unstructured Data.
Key Characteristics:
 Not strictly structured:
Unlike structured data, semi-structured data doesn't conform to a fixed schema or predefined data
model.
 Some level of organization:
It contains structural elements like tags, metadata, or hierarchical structures that help organize
and access the data.
 More flexible than structured data:
It's more adaptable and can accommodate different data types and formats.
 Easier to analyze than unstructured data:
The presence of some structure makes it easier to extract insights and perform analysis compared
to unstructured data.
Examples:
 JSON (JavaScript Object Notation): A popular format for data exchange, often used in
APIs and web applications.
 XML (eXtensible Markup Language): A markup language used to store and transport
data.
 CSV (Comma-Separated Values): A simple text format for storing tabular data.
  HTML (Hypertext Markup Language): The markup language used to create web pages.
 NoSQL Databases: Databases that store data in a non-relational format, often using semi-
structured data formats like JSON or XML.
 Email: While the body of an email is unstructured, the headers, sender, recipient, and
timestamp are structured, making it a semi-structured example.

Data analysis

Data analysis is at the heart of any scientific investigation.

1. Data collection is the systematic recording of information; data analysis involves working
to uncover patterns and trends in datasets; data interpretation involves explaining those
Patterns and trends.
2. Scientists interpret data based on their background knowledge and experience; thus,
different scientists can interpret the same data in different ways.
3. By publishing their data and the techniques they used to analyze and interpret those data,
scientists give the community the opportunity to both review the data and use them in
future research.
Data Analytics

Data analytics and analytics, by contrast, are general terms used to describe the field and a
comprehensive collection of associated methods. All these terms tend to be used for the
application of analytic methods to data that large organizations generate or have available ("big
data"). Data analytics is the process of examining, cleaning, transforming, and interpreting
complex datasets to extract valuable insights, draw conclusions, and support decision-making.
It involves various techniques from statistics, mathematics, and computer science to uncover
patterns, trends, correlations, and other useful information within the data. The primary goal of
data analytics is to gain a deeper understanding of data, solve problems, and aid in strategic
decision making. Data analytics is the method of using data to identify patterns and trends and
extract valuable business insights, which can further contribute to decision-making and strategy
formulation.
Data Analyst
Data analysts collect process and perform statistical analyses of data. Their skills may not be as
advanced as data scientists (e.g. they may not be able to create new algorithms), but their goals
are the same - to discover how data can be used to answer questions and solve problems.
Data Analyst Responsibilities
1. Work with IT teams, management and/or data scientists to determine organizational
goals
2. Mine data from primary and secondary sources
3. Clean and prune data to discard irrelevant information
4. Analyze and interpret results using standard statistical tools and techniques
5. Pinpoint trends, correlations and patterns in complicated data sets
6. Identify new opportunities for process improvement
7. Provide concise data reports and clear data visualizations for management
8. Design, create and maintain relational databases and data systems
9. Triage code problems and data-related issues
10. Data analysts are sometimes called "junior data scientists" or "data scientists in
training."
Data Science vs. Data Analysis
It's very important to know that data science and Data analysis are little similar but, there are
so many differences between them.
Data Science Data Analysis
*Providing strategic actionable insights *Providing operational observations into
into the world issues

*Mathematical, technical and strategic * Data analysis and visualization skills

knowledge are mandatory required

*Deal with big data * Not necessarily deal with big data

Types of Data Analytics

There are four major types of data analytics:

1. Descriptive
2. Diagnostic
3. Predictive
4. Prescriptive
 5.
1. Descriptive Analytics
This type focuses on summarizing and describing past data to understand what has
happened. It answers questions like "What happened?" or "How many?" by using techniques
like data mining and business intelligence. Descriptive analytics is the simplest type of analytics
and the foundation the other types are built on. It allows you to pull trends from raw data and
succinctly describe what happened or is currently [Link] analytics answers the
question, ―What happened?‖Descriptive analytics is used when the organization has a large data
set on past events or historical events. For this data to be useful, it must be simplified and
summarized so that it is understandable to the audience to which it is intended to be
[Link] analytics involves using large data sets that are cleaned, transformed,
and visualized understandably usually in the form of dashboards, bar graphs, pie charts,
infographics, and [Link] example, imagine you’re analyzing your company’s data and find
there’s a seasonal surge in sales for one of your products: a video game console. Here,
descriptive analytics can tell you, ―This video game console experiences an increase in sales in
October, November, and early December each year‖.Data visualization is a natural fit for
communicating descriptive analysis because charts, graphs, and maps can show trends in data—
as well as dips and spikes—in a clear, easily understandable way.
Applications of Descriptive Analytics
– An example of descriptive analytics is all the shopping information a supermarket chain
collects daily. If you only look at the number of records produced daily, it is difficult to know
how the business has operated with respect to certain attributes. With descriptive analysis tools,
we will be able to know, for example, which products were the most sold, in which geographical
areas certain items have sold the best, or if the marketing campaigns were successful compared
to previous ones. Moreover, the organization can more effectively plan its inventories with that
data.
2. Diagnostic Analytics
This type delves deeper to understand why something happened. It explores the root causes of
events by analyzing past data and identifying patterns and relationships. Diagnostic Analytics
addresses the next logical question, ―Why did this happen?‖Diagnostic analytics is the process of
determining trends from the available data and establishing correlations between variables.
Diagnostic analysis can be performed using MS Excel, algorithms, or even [Link] the
analysis a step further, this type includes comparing coexisting trends or movement, uncovering
correlations between variables, and determining causal relationships where [Link]
the aforementioned example, you may dig into video game console users’ demographic data and
find that they’re between the ages of 8 and 18. The customers, however, tend to be between the
ages of 35 and 55. Analysis of customer survey data reveals that one primary motivator for
customers to purchase the video game console is to gift it to their children. The spike in sales in
the fall and early winter months may be due to the holidays that include gift-giving.
Diagnostic analytics is useful for getting at the root of an organizational issue.
Applications of Diagnostic Analytics
 Examining market demand based on consumer attributes and behaviour
 Explaining the user behaviour and further improving products and user experience
 Determining the cause of software or technology failures, also referred to as running
diagnostics

3. Predictive Analytics
This type uses statistical models and machine learning to forecast future outcomes based on
historical data. It aims to answer questions like "What is likely to happen?" by identifying
trends and predicting future events. Predictive analytics is used to make predictions about future
trends or events and answers the question, ―What might happen in the future?‖
By analyzing historical data in tandem with industry trends, you can make informed predictions
about what the future could hold for your [Link] analytics applies techniques such
as statistical modelling, forecasting, and machine learning to the results of descriptive and
diagnostic analytics to predict future [Link] predictive analytics do not predict the
future 100%, because this type of analysis is probabilistic, they predict what might happen.
Predictive analytics understand the correlations between variables and their behaviour in the
future. Applying machine learning in predictive analytics has led to a certain degree of reliability
in predicting an [Link] instance, knowing that video game console sales have spiked in
October, November, and early December every year for the past decade provides you with ample
data to predict that the same trend will occur next year. Backed by upward trends in the video
game industry as a whole, this is a reasonable prediction to [Link] predictions for the
future can help your organization formulate strategies based on likely scenarios.
Applications of Predictive Analytics
Aerospace – Aircraft engine health monitoring increases aircraft uptime and reduces
maintenance costs.
Finance – Predictive analytics helps forecast the organisation's overall financial health by
projecting expenses, sales, and profits to create a picture that helps in overall business decision-
making.
Health – Analyzes the patients' overall health and devises an action plan for further treatment.
Industrial Machinery and Automation – Predicting machine failures, thereby preventing loss
of life and costs, reducing downtime, and minimizing waste.
Retail – Predictive analytics is used to forecast the characteristics of potential customers who
might be interested in purchasing certain products.

4. Prescriptive Analytics
This type goes beyond prediction to recommend actions that can optimize outcomes. It uses
data, statistical models, and machine learning to suggest optimal solutions and strategies.
Finally, Prescriptive Analytics answers the question, ―What should we do next?‖Prescriptive
analytics takes into account all possible factors in a scenario and suggests actionable takeaways.
This type of analytics can be especially useful when making data-driven [Link] out
the video game example: What should your team decide to do given the predicted trend in
seasonality due to winter gift-giving? you decide to increase marketing efforts in September with
holiday-themed messaging to try to extend the spike into another month.
Applications of Prescriptive Analytics
 Calculate the sales forecast of a product to calculate the replacement orders of the
merchandise.
 A customer’s buying propensity for certain items can be used to calculate which
campaigns are best to launch for this customer.
 Using a customer’s credit score when evaluating the granting of a loan

1. Descriptive 2. Diagnostic 3. Predictive 4. Prescriptive

Analytics Analytics Analytics Analytics

 Purpose:  Purpose:  Purpose:  Purpose:

What Why did it What happen What should
happened? happen? in future? be done?
 Example:  Example:  Example:  Example:
Monthly Drop in sales Sales Recommend
sales reports, analysis forecasting, er systems
website  Tools: SQL, risk  Tools:
traffic. R, Python modeling. Optimization
 Tools: Excel,  Tools: algorithms,
 Tableau, Machine AI,
Power BI learning, simulation
Python, R,
SAS

Importance Of Data Analytics

Data analytics is crucial for organizations because it provides valuable insights that drive
informed decision-making, optimize operations, enhance customer experience, and identify new
opportunities. By analyzing data, businesses can gain a better understanding of their customers,
processes, and the market, leading to improved efficiency, reduced costs, and increased revenue.
1. Improved Decision-Making:
 Evidence-Based Choices:
Data analytics provides concrete, actionable insights, allowing businesses to make decisions
based on facts rather than assumptions.
 Predictive Capabilities:
By analyzing historical data, organizations can predict future trends and outcomes, enabling
them to anticipate changes and take proactive measures.
 Understanding Customer Behaviour:
Data analytics helps businesses understand customer preferences and behaviour, allowing
them to tailor their products, services, and marketing efforts.
2. Increased Efficiency and Reduced Costs:
 Optimizing Operations:
Data analysis identifies bottlenecks and inefficiencies in business processes, enabling
organizations to streamline their operations and reduce costs.
 Improving Resource Allocation:
By analyzing data on resource utilization, businesses can identify areas where they can
optimize resource allocation and reduce waste.
 Identifying Cost-Saving Opportunities:
Data analytics can help organizations identify opportunities to reduce costs in areas such as
supply chain management, inventory, and production.
3. Enhanced Customer Experience:
 Personalized Experiences:
By understanding individual customer preferences and behaviours, businesses can create
more personalized experiences that increase customer satisfaction and loyalty.
 Improved Customer Service:
Data analytics can help organizations identify areas where they can improve customer
service and enhance the overall customer experience.
 Targeted Marketing:
 By analyzing customer data, businesses can develop more targeted marketing campaigns
that resonate with their target audience and increase conversion rates.
4. Identifying New Opportunities:
 Uncovering Hidden Trends:
Data analysis can reveal hidden trends and patterns that can lead to the identification of new
market opportunities and avenues for expansion.
 Developing New Products and Services:
By understanding customer needs and preferences, businesses can develop new products
and services that meet those needs and create a competitive advantage.
 Improving Competitive Position:
Data analytics can help organizations understand their competitors' strengths and
weaknesses, allowing them to develop strategies that give them a competitive edge.
5. Other Benefits:
 Risk Mitigation:
Data analytics can help organizations identify and mitigate potential risks before they
become problematic.
 Regulatory Compliance:
In certain industries, data analytics can be used to ensure compliance with regulations.
 Driving Revenue Growth:
By optimizing operations, improving customer experience, and identifying new
opportunities, data analytics can drive revenue growth.

Applications of data analytics

Data analytics is a powerful tool that can be used to solve various business problems.
Organizations of all sizes increasingly turn to data analytics to gain insights, improve decision-
making, and optimize operations.
Data analytics involves collecting, organizing, and analyzing data to draw meaningful
insights and trends. It allows organizations to discover valuable information to foster informed
decision-making and improve business outcomes. Additionally, data analytics employs different
techniques and tools to process unstructured and structured data, discover hidden patterns, and
provide actionable recommendations.

 Banking. Banks employ data analytics to manage risks, gather insights into customer
behaviour, and personalize financial services. Using data analytics, banks and credit card
companies can customize their offerings, identify potential fraud, and assess potential
client creditworthiness by analyzing customer demographics, transaction data, and credit
histories. Data analytics also helps banks spot money laundering activities and boost
regulatory compliance.
 Cybersecurity. Data analytics is pivotal in cyber security by detecting and preventing
cyber-attacks and similar threats. Security systems analyze user behaviour, network
traffic, and system logs to locate anomalies and possible security breaches. By leveraging
data analytics, businesses and other organizations can proactively enhance their security
measures, detect threats, respond to them in real-time, and protect sensitive data.
 E-commerce. E-commerce platforms use data analytics to understand customer
behaviour, optimize marketing campaigns, and personalize shopping experiences. E-
commerce companies offer personalized product recommendations, target specific
customer segments, and improve customer satisfaction and retention by analyzing
customer preferences, purchase histories, and browsing patterns.
 Finance. Data analytics plays a vital role in investment strategies, fraud detection, and
risk assessment. Banks and other financial institutions analyze vast volumes of data to
predict creditworthiness, spot suspicious transactions, and optimize their investment
portfolios. Data analytics allows finance companies to offer personalized financial advice
and develop creative financial products and services.
 Healthcare. Data analytics positively changes the healthcare industry by offering better
patient care, disease prevention, and resource optimization. Hospitals can analyze patient
data to spot high-risk individuals and provide personalized treatment plans. Data
analytics also helps find disease outbreaks, monitor treatment effectiveness, and improve
healthcare operations.
 Internet Searches. Data analytics powers Internet search engines, letting users find
relevant information accurately and quickly. Search engines analyze colossal amounts of
data (e.g., web pages, user queries, click-through rates) to deliver the most relevant
search results. Additionally, data analytics algorithms continuously learn and adapt to
individual user behaviours, providing increasingly accurate and personalized search
results.
 Logistics. Data analytics is essential in managing fleet operations, optimizing
transportation routes, and improving overall supply chain efficiency. Logistics companies
can reduce delivery times, minimize costs, and increase demand forecasting, inventory
management, and customer satisfaction by analyzing delivery times, routes, and vehicle
performance data.
 Manufacturing. Data analytics revolutionizes manufacturing by optimizing production
processes, improving predictive maintenance, and enhancing product quality. Data
analytics allows manufacturers to analyze sensor data, machine performance, and
historical maintenance records to predict equipment failures, minimize downtime, and
guarantee efficient operations. Data analytics also allows manufacturers to monitor
production lines in real time, leading to higher productivity and savings.
 Retail. Data analytics is changing the retail industry by optimizing pricing strategies,
offering insights into customer preferences, and improving inventory management.
Retailers can use customer feedback, analyze sales data, and market trends to identify
 popular products, personalize offers, and forecast future demand. Data analytics helps
retailers improve their marketing efforts, increase customer loyalty, and optimize store
layouts.
 Risk Management. Risks are common in the commercial world, and data analytics is
essential in risk management across many industries, such as finance, insurance, and
project management. Organizations can use data analysis to assess risks, develop sound
mitigation strategies, and make informed decisions by analyzing market trends, historical
data, and external factors.
 Supply Chain Management. The recent global pandemic has cast supply chains in a
new light. Consequently, data analytics improves supply chain management by reducing
costs, optimizing inventory levels, and enhancing overall operational efficiency. Using
data analytics, organizations can analyze supply chain data to forecast demand, identify
bottlenecks, and improve logistics and distribution processes. Data analytics also
enhances transparency throughout the supply chain.
Popular Data Analytics Tools

The right tools are an essential part of practicing data analytics successfully.
 Excel. Microsoft Excel is one of the oldest, most well-known software applications for
data analysis. In addition to providing spreadsheet functions to manage and organize vast
data sets, Excel offers graphing tools and computing capabilities such as automated
summation (AutoSum). Excel also includes the Analysis ToolPak, which includes data
analysis tools that perform regression, variance, and statistical analysis. Furthermore,
Excel’s versatility and simplicity make it a robust data analysis tool ideal for sorting,
filtering, cleaning, managing, analyzing, and visualizing data. Every aspiring data analyst
should be proficient in Excel.
 Jupyter Notebook. Jupyter Notebook is a web-based interactive environment where data
analysts can share computational documents or ―notebooks.‖ Data analysts use Jupyter
Notebooks to clean data, write and run code, conduct data visualization, perform machine
learning and statistical analysis, and function with many other kinds of data analysis.
 MySQL. MySQL is a popular open-source relational database management system (also
called RDBMS) that stores application data, primarily web-based. Popular websites like
Facebook, Twitter, and YouTube have used MySQL. Structured Query Language (or
SQL) is most often used for relational database management system management,
typically employing relational databases often structured into tables.
 Python. Python is consistently ranked as one of the most popular programming
languages in the world. Unlike other programming languages today, Python is relatively
simple to learn and can be employed in many different tasks, including software, web
development, and data analysis.
 R. Ris an open-source programming language typically used for statistical computing
and graphics. R is regarded as a relatively easy-to-learn programming language like the
 Python above. R is usually used for data visualization, statistical analysis, and data
manipulation.
 Tableau. Tableau is a data visualization application for business analytics and business
intelligence. Tableau is one of the most popular data visualization platforms in today’s
business world, mainly because it boasts an easily understood user interface and
smoothly turns data sets into understandable graphics.