Data Analysis And Business Intelligence in Real World

Introduction and summary 2, data collections tutorial

IDENTIFYING OBJECTIVES This lecture helps you understand that objective or goals is the key reason why data analytics is important.

SELECTING METHODS This lesson will help you choose specific approaches, techniques or strategies to achieve a particular goals or conduct research, analysis or problem-solving.

CHOOSE DATA SOURCES means selecting the origin or repository of data that will be used for analysis, decision-making or research.Carefully selecting data is crucial for ensuring the validity and effectiveness of any analysis or project outcome.

ENSURING QUALITY DATA  will help you learn how to maintain high standards in products, services or research outputs. It focuses on how to enhance customer satisfaction, reduce errors and improve overall performance.

GATHERING DATA This lesson is a process of collecting information from various sources to address specific research question, analyze a situation or support decision making.

ORGANIZING DATA This lesson helps you to structure and arrange collected information in a way that makes it easy to access, analyze and interpret.

Introduction and summary for section 3, excel tutorial

SEQUENCE can be use in creating numbered lists, performing calculations or analyzing data trends

COUNTIF is use to count number of cells that meet a specific conditions within a given range.Its particulaty useful in analyzing data and extracting meaningful information based on certain criteria

COUNTBLANK counts the number of empty(blank)cells in a specified range.Its useful for quickly determining how many cells in a dataset do not contain any data.

FILTER This function is powerful tool that allows you to extract a subset of data from a range based on specified criteria. Its helps in dynamically filtering data without needing to create separate tables or use manual filtering methods

PIVOT TABLE allows you to summarize, analyze, and present large datasets in a console and interactive way. It enables you to reorganize and aggregate data to gain insights quickly, making it easier to identify trends, patterns, and comparisons.

CONDITIONAL FORMATTING  allows you to automatically apply formatting-such as colors ,fonts and styles- to cells based on specific conditions or criteria. This helps in visually analyzing data, highlighting rends, and drawing attention to important information.

DUPLICATES is important for data cleaning, analysis and ensuring the accuracy of reports. It helps maintain high-quality data which is crucial for successful analysis and decision-making

OUTLIERS refers to a data point that significantly differs from other observations in a dataset. It can be unusually  high or low values compared to the rest of the data and can skew analysis, affect statistical calculations and lead to misleading conclusions.

IRRELEVANT DATA refers to the information that does not contribute meaningfully to the analysis or insights you are tyring to derive from a dataset. Including irrelevant data can clutter your spreadsheets, make analyses less effective and lead to incorrect conclusions. Identifying and removing irrelevant data quality and improving the efficiency of data analysis.

TYPOS AND ERRORS refers to mistakes in data entry or formulas that can lead to incorrect information, calculations or analyses. This mistakes can stem from various sources, including human error, software glitches, or data import issues. Identifying and correcting these error is crucial for maintaining data integrity and ensuring accurate results.

DATA REDUNDANCY refers to the unnecessary duplication of data with a dataset. This can occur when the same piece of information is stored in multiple locations, which can lead to various issues such as increased file size, data inconsistency, and difficulties in data management.

NON STANDARDIZED DATA refers to information that is not consistently formatted or structures across a dataset. This inconsistency can lead to challenges in data analysis, reporting and interpretation. Standardization is crucial for ensuring that data can be compared, aggregated and analyzed effectively.

LACK OF DOCUMENTATION refers to the absence of written records or notes that explain the purpose, structure and processing of data within excel workbooks. This can include missing explanations for formulas, data sources, assumptions and methodologies used in analyses. Proper documentation is essential for ensuring clarity, reproductivity and effective collaboration within data-driven projects.

Please refer to EXCEL SEQUENCE video tutorial for reference.

Please refer to EXCEL COUNTIF video tutorial for reference.

Please refer to EXCEL COUNTBLANK video tutorial for reference.

Please refer to EXCEL FILTER video tutorial for reference.

Please refer to EXCEL CONDITIONAL FORMATTING video tutorial for reference.

Please refer to EXCEL PIVOT TABLE video tutorial for reference.

Please refer to EXCEL IRRELEVANT DATA video tutorial for reference.

Please refer to EXCEL NON REDUNDANCY video tutorial for reference.

Please refer to EXCEL TYPOS AND ERRORS video tutorial for reference.

Please refer to EXCEL NON STANDARDIZED video tutorial for reference.

Please refer to LACK OF DOCUMENTATION video tutorial for reference.

Introduction and summary of section 5, MYSQL tutorial

DISTINCT is a keyword used to select unique values from a database query, eliminating duplicate entries in the result set. It ensures that the output contains only distinct(non-repeating) records based on the specified columns,

DUPLICATES tables in SQL is crucial for maintaining data integrity, reducing redundancy and ensuring efficiency database operations. Regular audits and clear documentation practices can help prevent and address issues related to duplicate tables.

FINDING MISSING DATA in SQL involves identifying records where certain values are absent or null in a dataset. This is crucial for data integrity, reporting and analysis.

COMMIT AND ROLLBACK These commands are use to manage transactions which are sequences of operations performed as a single logical unit of work. These commands help ensure data integrity and consistency in the database.

NULLS is a special marker used to indicate that a data value does not exist in the database. It represents missing, undefined or unknown data and is different from an empty string or zero. Understanding how null works is crucial for proper data management and query formulation.

COALESCE is a function that returns the first non-null value from a list of arguments. It is particularly useful for handling null values in your queries, allowing you to provide default values or to combine multiple columns while ensuring that you get a valid result.

REPLACE is a function use to replace all occurrences of a specified substring within a string with another substring. This function is particularly useful for data cleaning, formatting strings or modifying text within columns.

TRIM is used to remove specified prefixes or suffixes(or both) from a string, with the most common usage being to remove whitespace characters from the beginning and end of the string. This is particularly useful for cleaning up text data that may have been improperly formatted or entered with extra spaces.

CONCATENATE refers to a the process of combining two or more strings into a single string. This is often done using specific functions provided by the SQL dialect you are using

PRIMARY KEY is a field or a combination of fields in a table that are uniquely identifies each record in that table. No two records can have the same primary key value and it cannot contain "null" values.

AUTO INCREMENT means each time a new user is added, the database will automatically assign the next available integer(e.g. 1,2,3....)

SUBSTRING is a function used to extract a portion of a string. This function allows you to specify the starting position and the length of the substring you want to retrieve from the original string.

GROUP BY is a statement use to arrange identical data into group. This is often used in conjunction with aggregate functions like(sum().count(),avg(),min(),max(), to perform calculations on each group of data.

HAVING CLAUSE is used to filter records after the data has been grouped by the "Group By" clause. It allows you to specify conditions on aggregate functions which is not possible with the 'where' clause, as 'Where' is used to filter records before any grouping occurs.

PARTITION BY is used in conjunction with windows functions to divide the result set into partitions or group. This allows you to perform without collapsing the results into a single output row. Its particularly useful for running totals, average and other aggregate calculations that need to be computed on a subset of data.

SUBQUERIES (or nested query) is a a query embedded within another SQL query. It is used to perform operations that require results from one query to be used in another. Subqueries can be placed in various parts of SQL statement including the "SELECT", FROM", "WHERE" and "HAVING" clause.

CASE STATEMENT is a conditional expression that allows you to evaluate a set of conditions and return a specific value based on which condition is met. It is similar to an "if-then-else" statement in programming. The 'Case' statement can be used in various SQL commands such as "SELECT", "UPDATE" and "ORDER BY".

CONSTRAINT is a rule or restriction that is applied to a column or a set of columns in a database table. Its is also to enforce data integrity, ensuring that the data entered into a table adheres to certain accuracy and reliability of the data in the database.

NTILE is a window functions sued to distribute the rows in a result set into a specified number of buckets or tiles. It assigns a bucket/tile number to each row based on the order defined in the "ORDER BY" clause. This is particularly useful for diving data into particular groups for analysis such as quartiles , deciles or percentiles.

ROW NUMBER is a window function that assigns a unique sequential integer to rows within a partition of a result set. This is particularly useful for scenarios where you need to identify the order of rows based on specific criteria such as sorting or ranking.

RANK is used to assign a unique rank to each row within a partition of a result set with gaps in the ranking for ties.

DENSE_RANK assigns ranks to rows within a partition of a result set without gaps in the ranking values. This means that if two or more rows are tied for a rank the next rank will be immediate next integer.

Please refer to the MYSQL DUPLICATE AND DISTINCT video tutorial for reference.

Please refer to MYSQL NULL video tutorial for equivalent  reference.

Please refer to the MYSQL COALESCE video tutorial for reference

Please refer to the MYSQL REPLACE video tutorial for reference.

Please refer to the MYSQL TRIM AND CONCATENATE video tutorial for reference.

Please refer to the MYSQL SUBSTRING video tutorial for reference.

Please refer to the   MYSQL PRIMARY KEY AND AUTO INCREMENT video tutorial for reference.

Please refer to the  MYSQL CASE STATEMENT video tutorial for reference.

Please refer to the  MYSQL SUBQUEIRIES video tutorial for reference

Please refer to the MYSQL PARTITION BY video tutorial for reference.

Introduction to data cleaning in PYTHON

DUPLICATES generally refer to reaped elements in a collection such as a list, tuple or set.

ISNA is functions use to identify missing values in a data frame

ISNULL function is use to detect the missing values in a data frame.

NOTNA is use to identify non missing values in a data frame.

DROPNA is use to remove missing values from a data frame. This is particularly useful in cleaning dataset by eliminating rows or columns that contains NAN values.

SAVING FILES This is different way of saving files using EXCEL, CSV AND PICKLE and reading it. Its useful when you wan to have all the cleaning and manipulation you perform.

FILLNA is use to fill the missing values in a data frame or a series that contains NAN. This is important steps in data preprocessing as it allows you to handle missing data without losing rows or columns.

ASTYPE is use to cast a pandas object to a specific data type. This is useful in converting data types to ensure that your data is in correct format or analysis or to optimize memory usage.

REPLACE is use to replace specific values in a data frame or series with new values. This method is useful particularly for data cleaning and preprocessing, allowing you to handle inconsistencies and incorrect vales in your dataset.

PD TO DATETIME is a function to transform date  and time information from various formats into a standard datetime format pandas can work with effectively.

RENAME Refers to the action of changing the name of a variable, function, file or any other identifier. It is use to improve code readabilit, organization or compliance with standards.

DROP ROWS AND COLUMNS refers to the process of removing specific rows and or columns from a data structure such as a table or a data source such as a table or data frame in programming.

SPLIT  can refer to a few different concepts depending on the context but generally it means to divide into  smaller parts, manageable or useful parts.

AGGREGATE refers to the process of combining or collecting multiple items into a single group or total. It is a  key concept in data analysis and various fields, allowing for the simplification and summarization of data, making it easier to understand trends and patterns.

Contains is often used when checking if an item exists within a collection, such as a list, tuple or string. The most common way to check for containment is by using the 'in' keyword.

Please refer to the  Python video tutorial for reference.

Please refer to the  Python video tutorial for reference.

Please refer to the  Python video tutorial for reference.

Summary of section 9, standard deviation -python

STANDARD DEVIATION OF QUANTITY refers to the statistical measure that quantifies the variation or dispersion of a set of quantity values such as the number of items sold, products or stocked over a specific period. It helps to understand how much individual quantity measurements differ from the average quantity.

STANDARD DEVIATION OF GROSS SALES refers to the statistical measures that quantities the amount of variation or dispersion in the gross sales figures over a specific period. Gross sales represent the total revenue generated by a company before any deductions such as returns, allowances or discounts.

STANDARD DEVIATION OF GROSS PROFIT refers to the statistical measure that quantifies the variation or dispersion of gross profit figures over specific period. Gross profit is calculated as total revenue minus the cost of good sold(COGS) representing the profit a company makes after deducting the cost associated with producing its goods.

STANDARD DEVIATION OF DISCOUNTS refers to the statistical measure that quantities the variation or dispersion in the discount amounts offered by a business over specific period. Discounts can vary widely based on factors such as promotions ,sales strategies and market conditions.

KPI  means key performance indicator. It is a measurable value that demonstrates how effectively an organization, team, or individual is achieving key business objectives. Organizations use KPIs to evaluate their success at reading targets.

EDA stands for exploratory data analysis. Its a critical step in the data analysis process where analysis and data scientists explore datasets to summarize their main characteristics, often using visual method. EDA helps in understanding the data's underlying patterns, identifying anomalies and generating hypothesis.

DATA VISUALIATION refers to the graphical representation of information and data. By using visual elements like charts, graphs, maps, and dashboards, data visualization tools provide an accessible way to see and understand trends, outliers and patterns in data.

KPI EXCEL SALES FREQUENCY, Calculate the frequency sales per item or category.

KPI-EXCEL-SALES OF DAY- calculate the sales within a week, monday thru friday or 12 to12am.

KPI GROSS SALES refer to the total revenue generated from sales before any deductions such as returns, allowances or discounts. KPI related to gross sales can help businesses assess their overall sales performance and growth.

KPI-EDA  GROSS SALES BY PRODUCT, we will calculate the dataset using MYSQL and we will use excel for Data Visualization,

KPI-EDA-MYSQL CATEGORY MIN ,MAX ,SUM ,COUNT. We will aggregate our dataset using MYSQL and use data visualization it in EXCEL

KPI-EDA-PYTHON GROSS SALES we will manipulate the data using python and visualize it using TABLEAU.

KPI-EDA-PYTHON W GROSS SALES we will use dataset to calculate and visualize it using TABLEAU.

KPI-EDA-PYTHON TOTAL TRANSACTION we will manipulate the dataset using Python and use TABLEAU as data visualization.

KPI-EDA- CATEGORY we will use Category to showcase different data visualization in  MATPLOTLIB  and SEABORN plot.

If you need help in data visualization, this portion will lead you to more options using matplotlib and seaborn plot. thank you.

Watch the KPI dashboard and use this as pattern to make your own Portfolio project.

FINAL STATEMENT