Become an AI-Powered Certified Data Analyst with Excel & Power Bi

As students of Data analysis you will be introduced to data analysis softwares and techniques.

Data Cleaning

What you’ll learn:
1. Removing duplicates and handling missing values.
2. Standardizing data formats (dates, numbers, text).
3. Correcting data entry errors and inconsistencies.
4. Split and merge columns as needed.

Text Functions
What you’ll learn:
1. Introduction to Text functions: Trim, Left, Right, Substitute, etc…
2. Practice using the text functions on sample datasets.

Basic Functions
What you’ll learn:
1. Introduction to Basic arithmetic functions: SUM, AVERAGE, MIN,  MAX.
2. Practice using the basic arithmetic functions on sample datasets.

Background:
Netflix Inc. aims to optimize its content recommendation algorithm and improve customer satisfaction by ensuring that its content library data is clean, accurate, and ready for analysis. The company has observed inconsistencies in its data, such as missing values, duplicate records, and incorrect classifications, which may affect the performance of its recommendation system.

Task Overview:
As a data analyst at Netflix, you are tasked with cleaning and preparing the company’s dataset for further analysis. The dataset includes information about movies and TV shows, such as titles, genres, release dates, ratings, and user interaction metrics.

Your Responsibilities:

1. Determine which columns have missing data.

2. Decide on appropriate strategies to handle missing values, such as imputation, filling with default values, or removal of rows/columns.

3. Identify and remove any duplicate records in the dataset to ensure each entry is unique.

4. Check for and correct inconsistencies in data formats (e.g., dates, ratings).

5. Standardize categorical data, such as genre and country names, to maintain consistency.

6. Verify that numeric data (e.g., duration, user ratings) is within a reasonable range.

7. Cross-check dates and other time-based data for logical consistencies.

What you’ll learn 

• IF function: Basic usage and syntax.
• Creating more complex conditional logic by nesting IF functions.
• Introduction to the IFS function as an alternative to nested IFs.

Advanced Logical Functions

ACTIVITIES:

• Understanding and applying the SUMIF, COUNTIF, AVERAGEIF functions.
• Use SUMIF with multiple criteria (by combining with SUMIFS).
• Use COUNTIF with multiple criteria (by combining with COUNTIFS).
• Use AVERAGEIF with multiple criteria (by combining with AVERAGEIFS).

Background:
A global retail company wants to optimize its marketing campaigns to increase sales and improve customer engagement. The marketing team has collected sales data from various campaigns over the last year, including information about customers, sales transactions, and campaign-specific details. Your task is to analyze this data using logical functions to identify trends, segment customers, and recommend strategies to improve future campaigns.

Your Responsibilities:

1. Use logical functions to segment customers based on various criteria such as age group, purchase amount, and loyalty program membership.
2. Identify high-value customers who consistently make large purchases or frequently engage with marketing campaigns.
3. Determine the effectiveness of different campaigns by calculating total sales, average purchase amount, and the number of transactions for each campaign.
4. Use logical functions to filter and compare the performance of campaigns based on specific conditions, such as region, discount application, and sales channels.
5. Use logical functions to identify trends in sales data, such as peak purchase times, popular product categories, and regional preferences.
6. Analyze the behavior of loyalty program members compared to non-members using logical conditions.
7. Based on your analysis, recommend strategies to optimize future marketing campaigns, such as targeting specific customer segments, adjusting discount strategies, or focusing on high-performing channels and regions.
8.  

What you’ll learn

• Introduction to VLOOKUP
• Practicing basic VLOOKUP with simple datasets.
• Nesting VLOOKUP within other formulas for advanced functionality.
• Explore alternative lookup functions like INDEX and MATCH.
• Learning how to combine INDEX and MATCH to create powerful lookup formulas.
• Understanding the advantages of using INDEX and MATCH over VLOOKUP.

Background:
A retail company is looking to enhance its sales reporting by integrating additional data from various sources to gain deeper insights into sales performance. The company has data on sales transactions, customer information, and product details stored in separate datasets. Your task is to use lookup functions to consolidate this data and perform a comprehensive sales analysis.

Your Responsibilities:

1. Use lookup functions to match and combine data from the Sales Transactions dataset with the Customer and Product datasets.

2. Create a comprehensive sales report that includes the customer name, product name, category, region, and loyalty program membership for each transaction.

3. Use lookup functions to categorize sales data by product category.

4. Leverage lookup functions to segment sales data by customer region and loyalty program membership.

5. Analyze the sales contribution from different regions and compare the purchasing behavior of loyalty program members versus non-members.

6. Use lookup functions to link sales data with product suppliers.

7. Calculate important sales metrics such as average order value, total units sold, and revenue per customer using the consolidated data.

8. Suggest strategies for targeting high-value customers, optimizing inventory, and enhancing supplier relationships.

What you’ll learn 

• Design dashboard layout and structure.
• Use PivotTables, Pivot Charts, and slicers for interactivity.
• Include key performance indicators (KPIs) and metrics.

PRESENTATION

• Working in teams to deliver presentation
• Practice delivering the presentation.
• Gather feedback and make adjustments.

Background:
A leading bike retailer is looking to improve its business strategy by analyzing its bike sales data. The company wants to understand sales trends, identify key customer segments, and optimize its inventory management. Your task is to analyze the provided bike sales data, create an interactive dashboard, and deliver actionable insights and recommendations to the management team.

Your Responsibilities:

1. Analyze overall sales trends over time, identifying peak sales periods, and seasonal variations.
2. Evaluate the performance of different bike types in terms of sales volume and revenue generated.
3. Segment customers based on demographics such as age, gender, and region to understand purchasing behavior.
4. Create an interactive dashboard that visually represents the key findings from your analysis.
5. Include key performance indicators (KPIs) such as total sales, average order value, top-selling bike types, customer demographics, and regional sales distribution.
6. Implement filters for the dashboard that allow users to drill down into specific time periods, regions, or customer segments.
7. Use a variety of visualizations, such as bar charts, line graphs, pie charts, and heat maps, to present data clearly and effectively
8. Recommend which bike types should be prioritized in marketing and inventory based on sales performance and customer demand.

ACTIVITIES

• Identifying various data sources.
• Importing data into Power BI from various sources (Excel, CSV, databases, etc.).

Data Cleaning and Transformation

ACTIVITIES

• Using Power Query to clean data
• Applying Data types and

Data Modeling

ACTIVITIES

• Creating relationships between tables.
• Defining primary keys and foreign keys.

Background:
Choprite, a retail chain with multiple locations, has accumulated a large volume of sales data over the past year. However, the data is messy and unstructured, with inconsistencies and missing values. The management team wants to leverage this data to understand sales performance, identify trends, and make informed business decisions. Your task is to clean, transform, and analyze the sales data to provide actionable insights.

Your Responsibilities:

1. Identify missing values and decide how to address them (e.g., filling in with mean/median, forward/backward filling, or removing rows/columns with excessive missing data)
2. Identify and remove duplicate records to ensure each transaction is unique
3. Validate that the Total Sale Amount matches the product of Quantity Sold and Unit Price; correct any discrepancies
4. Group products into broader categories if necessary, and create a summary of sales by category
5. Analyze overall sales trends over time, identifying peak sales periods, seasonal variations, and store performance.d on sales volume and revenue
6. Analyze customer purchasing behavior, identify high-value customers, and understand the impact of discounts on sales.

7. Recommend which products or categories should be prioritized based on sales performance and customer demand.

ACTIVITIES

• Creating basic visualizations (bar charts, line charts, pie charts etc.).
• Using visualizations to understand data distributions and trends.

DAX Formulars 

ACTIVITIES

• Creating summary statistics using DAX measures.
• Using DAX for complex measures and calculated columns.
• Using DAX for creating Calculate Functions

Dashboard Creation 

ACTIVITIES

• Building Interactive Dashboards
• Creating key visualizations and adding them to the dashboard.
• Adding interactivity with slicers, filters etc.

Background:
A retail analytics firm has gathered a comprehensive dataset from a large online retailer to understand consumer shopping trends. The firm aims to identify patterns in customer behavior, spot emerging trends, and provide strategic insights to improve the retailer’s marketing, product offerings, and customer experience. Your task is to analyze the shopping trends dataset and provide detailed insights and recommendations.

Your Responsibilities:

1. Analyze customer demographics to segment shoppers by age, gender, income level, and region. Identify which segments are most valuable based on purchase frequency and total spending.

2. Determine which product categories are most popular among different customer segments. Identify any emerging trends in product preferences.

3. Analyze the timing of purchases to identify peak shopping times, including day of the week, time of day, and seasonal trends.

4. Determine which devices (mobile, desktop, tablet) are most commonly used by customers and whether there are differences in purchase behavior depending on the device

5. Analyze payment method preferences across different customer segments. Identify if certain methods are more popular with specific demographics or during particular times of the year.

6. Identify products that are frequently purchased together. Analyze these co-purchase patterns to uncover potential cross-selling or bundling opportunities.

7. Determine which products are often bought by the same customer segments, suggesting affinities between product types.

8. Provide recommendations for targeted marketing campaigns based on customer segmentation, device usage, and purchase timing.

9. Implement visualizations that highlight emerging shopping trends, allowing the retailer to adjust strategies in real-time.

The Artificial Intelligence (AI) is currently shaping processes in every businesses and industries, in this unit, you will learn how to integrate generative AI into data analytics workflows, and help organizations significantly enhance their ability to derive actionable insights, improve data quality, and make more informed decisions.

In this segment, you will have a Ask-Me-Anything session with a top data analyst to guide you further.

In this segment, a top freelancer will share with you a blueprint that guarantees you to make at least $1,000 Monthly working Remotely As A Data Analyst… Even if you know nothing about remote work.

The Skilled Worker visa allows qualifying individuals to come to the UK to work for an approved sponsor in an eligible role. We will give you a Guide on How To Apply for Skilled Worker Visa and Tech Nation Visa.

Register today to enjoy our Workspace with Ultra Fast  Satellite Internet (Starlink) for FREE.