###### Youtube Analytics Report (Looker Studio)

<div style="text-align: center">![](/images/yt%20db-589777c9.png)</div>

###### Electronics Store Orders and Sales Dashboard Report (Looker Studio)

<div style="text-align: center">![](/images/es%20db.png)</div>

###### Hardware store branches report (multi paged) (Looker Studio)

<div style="text-align: center">![](/images/hs%20db.png)</div>



Hall of Dashboard Reports

### Objective:

The objective of this project is to automate the process of creating excel reports, in this case, a “Daily Sales” report generated at the end of every business day. The report includes the total number of sales and total number of orders for that day, based on date prompt. The report should be generated in excel, converted to PDF format, and sent via email using Microsoft Outlook.

### Problem Statement:


The manual process of creating daily sales report is time consuming and prone to errors. By automating this process using VBA, we aim to save time, improve accuracy and streamline the reporting and mailing process.

#### Requirements:

*   Excel 2016 and above

*   Microsoft Outlook

### Deliverables:

1.  VBA code that creates a daily sales report based on the provided date prompt

2.  A new Excel sheet containing the daily sales report, including the total sales and total number of orders.

3.  Conversion of the daily sales report sheet to PDF format.

4.  An email containing the PDF attachment sent to the specified recipient using Microsoft Outlook.

## Master Data Sheet: 

This is our data sheet containing all necessary data

![](/images/vba1.png)

Upon running the VBA macro, the date input prompt pops up …

### 
Prompt for Date Input: 

The program asks the user to input a specific date for which the daily sales report needs to be generated.

<div style="text-align: center">![](/images/vba%20date.png)</div>

#### Search for Date in Master Data: 

The VBA code applies a filter in the master sheet for the corresponding column using the date input as criteria

#### Calculate Total Sales and Total Number of Orders: 

Using the filtered data, the VBA calculates the total sales by summing up the sales values, and also counts the total number of orders for the specified date as well.

#### Creating a New Sheet for Daily Sales Report: 

The VBA code cleares the filter in the master sheet and creates a new sheet in the the Excel workbook, to hold the daily sales report.

#### Populating the Report Sheet: 

The VBA populates the newly created sheet with the following information.
•	Date: the specified date
•	Total Sales: calculated total sales for the specified date.
•	Total Number of Orders: The calculated total number of orders for the specified date.
It also properly formats all cells, figures and columns.

#### Convert the Daily Sales Report to PDF: 

The VBA code converts the created sheet into PDF format ready to be mailed.

#### Send Email Via Outlook:


•	The VBA code uses Microsoft Outlook Application to send an email to the recipient.
•	The email includes the PDF file as an attachment
•	The recipient’s email address is specified in the code.



![](/images/vba%20report%20and%20prompt-52537890.png)

<div style="text-align: center">*Populated Report in New sheet and Prompt showing successful completion of all steps***</div>

<div style="text-align: center">![](/images/vba%20email.jpg)</div>

<div style="text-align: center">*Displayed Sent Email in Recipient’s Inbox***</div>

<div style="text-align: center">![](/images/vba%20pdf%20report.jpg)</div>

<div style="text-align: center">*PDF Attachment Showing Daily Sales Report for Specified Date*</div>



#### Further Development:


The current code implementation can be extended to handle more complex requirements and accommodate additional functionalities. Such as:

1.  Date Range Input:
    The code could be further developed to accept date ranges as input, allowing the generation of sales reports for a specific range of dates. This would provide a more comprehensive view of sales trends and performance over a specific period.

2.  Sending Emails to Multiple Recipients:
    To increase the flexibility of the system, the code could be improved to support sending the daily sales report to multiple recipients. This would enable efficient distribution of the report to different stakeholders or departments within the organization.

3.  More complicated report types.

#### VBA Code:

```
Sub GenerateDailySalesReport()
Dim inputDate As Date
Dim wsData As Worksheet
Dim wsTemp As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim filteredRange As Range
Dim totalSales As Double
Dim orderCount As Long
```

```
' Ask for the input date
inputDate = InputBox("Enter the order date (dd/mm/yyyy):")

' Set the data sheet
Set wsData = ThisWorkbook.Worksheets("Master Sales Data")

' Set the report sheet or create a new one
On Error Resume Next
Set wsReport = ThisWorkbook.Worksheets("Daily Sales Report")
On Error GoTo 0

If wsReport Is Nothing Then
    Set wsReport = ThisWorkbook.Worksheets.Add
    wsReport.Name = "Daily Sales Report"
Else
    wsReport.Cells.Clear
End If

' Turn off the filter
wsData.AutoFilterMode = False

' Create a temporary sheet
Set wsTemp = ThisWorkbook.Worksheets.Add
wsTemp.Name = "Temp"

' Find the last row in the data sheet
' lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

' Filter the data for the input date and copy to the temporary sheet
wsData.Range("A1:R9801").AutoFilter Field:=3, Criteria1:=inputDate
wsData.Range("A1:R9801").SpecialCells(xlCellTypeVisible).Copy Destination:=wsTemp.Range("A1")

' Get the filtered range in the temporary sheet
Set filteredRange = wsTemp.UsedRange

' Calculate the total sales and order count from the filtered range
totalSales = Application.WorksheetFunction.SumIf(filteredRange.Columns("R"), ">0")
orderCount = filteredRange.Columns("B").Cells.Count - 1

' Turn off the filter
wsData.AutoFilterMode = False

' Create the report headers
wsReport.Range("A1").Font.Bold = True
wsReport.Range("A2:B2").Font.Bold = True

wsReport.Range("A1") = "Daily Sales Report for " & Format(inputDate, "mm/dd/yyyy")
wsReport.Range("A2") = "Total Sales"
wsReport.Range("B2") = "Number of Orders"

' Insert the calculated values
wsReport.Range("A3").NumberFormat = "$#,##0.00"
wsReport.Range("A3") = totalSales
wsReport.Range("B3") = orderCount

' Autofit columns
wsReport.Columns("A:B").AutoFit
```

```
' Save the report as PDF
Dim fileName As String
fileName = "Daily_Sales_Report_" & Format(inputDate, "yyyymmdd") & ".pdf"
Dim filePath As String
filePath = Environ("TEMP") & "" & fileName ' Use the user's temporary folder path
```

```
wsReport.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath, Quality:=xlQualityStandard

' Create Outlook objects
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem

' Initialize Outlook
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

' Compose the email
With OutlookMail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "Dear Sir" & "<br>" & "<br>" & "Please find the attached file." & "<br>" & "<br>" & .HTMLBody
    .To = "email@gmail.com"
    ' .CC = "xyz@gmail.com"
    .Subject = "Daily Sales Report - " & Format(inputDate, "mm/dd/yyyy")
    .Attachments.Add filePath ' Attach the PDF from the temporary folder
    .Send
End With

' Release the Outlook objects from memory
Set OutlookMail = Nothing
Set OutlookApp = Nothing

' Delete the temporary sheet
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True


' Show a message with the file path
MsgBox "Daily sales report created and sent via email.", vbInformation
```

```
End Sub
```



Thank you.


Automated Excel Reporting and Mailing with VBA

### Introduction

Africa, a continent abundant in natural resources, continues to grapple with significant economic and social challenges, making it one of the most disadvantaged regions worldwide. The persistence of poverty, inadequate healthcare, and low life expectancy are among the critical issues confronting African nations. While progress has been made in recent years, urgent attention is required to address these persistent challenges.

Poverty, life expectancy, and Gross Domestic Product (GDP) are pivotal indicators that shed light on a country's economic and social conditions. In Africa, these indicators exhibit substantial variation among countries, making it complex to comprehend the intricate relationship between poverty, life expectancy, and GDP. This case study endeavors to delve deeply into the correlation among these indicators within African countries.

### Questions answered in this analysis

*   [Question 1: What is the distribution of poverty across African countries?](http://localhost:8888/lab#question1)

*   [Question 2: Which African countries have the highest and lowest GDP?](http://localhost:8888/lab#question2)

*   [Question 3: Which African countries have the highest and lowest life expectancy?](http://localhost:8888/lab#question3)

*   [Question 4: What is the trend for life expectancy in African countries based on historical data?](http://localhost:8888/lab#question4)

*   [Question 5: What is the projected trend for life expectancy in African countries based on historical data and current trends?](http://localhost:8888/lab#question5)

*   [Question 6: How do poverty, life expectancy, and GDP vary across different income groups in all countries?](http://localhost:8888/lab#question6)

*   [Question 7: How does life expectancy affect GDP and Poverty and vise versa](http://localhost:8888/lab#question9)

*   [Conclusions](http://localhost:8888/lab#conclusions)

*   [Recommendations](http://localhost:8888/lab#recommendations)

### Question 1: What is the distribution of poverty across African countries?

##### ![](/images/Aly1.PNG)

Insight

The dataset reveals that there is a wide range of poverty levels across African countries. Some countries, such as the **Democratic Republic of Congo (COD)**, **the Central African Republic (CAF)**, and **Mozambique (MOZ)** and a few others have almost **40-50%** of their population earning a daily income of less than one dollar.

On the other hand, majority of countries in Africa have up to **50% of their total population** earning between one dollar and two dollars and fifteen cents.
These findings highlight the need for more targeted poverty reduction efforts and policies that focus on improving income levels in these countries.

### **Question 2:** Which African countries have the highest and lowest GDP?

##### ![](/images/Aly2.PNG)

Insights

Based on the analysis of the GDP data for African countries in **2021**, we can see that **Nigeria (NGA)** has the highest GDP followed by **South Africa (ZAF)** and **Egypt (EGY)**. These countries are among the most populous on the continent and have some of the largest economies.

On the other hand, countries like **Sao Tome and Principe (STP)**, **Comoros (COM)**, and **Seychelles (SYC)** have the lowest GDPs among African countries in 2021.

### **Question 3:** Which African countries have the highest and lowest life expectancy?

##### ![](/images/Aly3.PNG)

Insights

Based on the data, we can see that some African countries have a relatively high life expectancy at birth, such as **Algeria DZA (76.4 years)**, **Cape Verde CPV (74.1 years)**, and **Morocco MAR (74.0 years)**.
However, other countries, such as **Chad TCD (52 years)**, **Nigeria NGA (53 years)**, and **Lesotho LSO (53 years)**, have much lower life expectancies.

The differences in life expectancy between these countries may be attributed to various factors, including healthcare access, disease prevalence, and lifestyle factors. Countries with higher life expectancies may have better access to healthcare and disease prevention measures, while countries with lower life expectancies may face challenges in providing adequate healthcare and managing infectious diseases.

To improve life expectancy in African countries with lower rates, efforts could be made to improve healthcare infrastructure and disease prevention measures. Additionally, education campaigns to promote healthy lifestyle choices, such as regular exercise and healthy eating, could also help improve overall health outcomes.

### **Question 4:** What is the trend for life expectancy in African countries based on the historical data?

##### ![](/images/Aly4.PNG)

##### Insights

Based on the available data from 1921 to 2021, we can see that there has been a general trend of increasing life expectancy in African countries over the years. **In the 1920s, the average life expectancy was around 25-30 years**, but this number has risen significantly to **an average of 62 years in 2021**.
This increase can be attributed to various factors such as advancements in healthcare, better access to education and information, improved living conditions, and disease prevention efforts.

However, with continued efforts towards improving healthcare infrastructure and education, it is hoped that the trend of increasing life expectancy in African countries will continue in the coming years.

###

**Question 5:** What is the projected trend for life expectancy in African countries based on historical data?

We would put our emphasis on 3 countries with lowest life expectancy (Chad, Nigeria and Lesotho)

![](/images/Aly5.PNG)

**We then made projections for Nigeria**

We predicted for next 10 years

##### ![](/images/Aly6.PNG)

![](/images/Aly7.PNG)

Insights

Based on the analysis of historical data from 1950 to 2021 and the projections made for the next 10 years, it can be observed that there could be a **gradual increase in life expectancy in Nigeria**, which can be attributed to various factors such as improved healthcare facilities, increased access to education and information, better nutrition, and advancements in medical technologies.

However, there are still challenges to be addressed to improve the overall life expectancy rate in Nigeria, such as reducing infant mortality rates, improving access to clean water and sanitation, and increasing public awareness of health issues.

Therefore, it is important for the federal government (FGN) to continue investing in healthcare infrastructure and promoting health education and awareness to sustain the current trend and improve the overall health of the population.

#####

### **Question 6:** How do poverty, life expectancy, and GDP vary across different income groups in all countries?

![](/images/Aly8-820512b5.PNG)

##### **Insights**

Poverty, life expectancy, and GDP vary across different income groups in all countries.

> Wealthier countries tend to have lower poverty rates, higher life expectancies, and higher GDPs.
> The poorest countries have the highest poverty rates, lowest life expectancies, and lowest GDPs.
> There is a great deal of variation within each income group.
>

The relationship between poverty, life expectancy, and GDP is clear. Wealthier countries tend to have lower poverty rates, higher life expectancies, and higher GDPs. This is because wealth can be used to invest in things like healthcare, education, and infrastructure, all of which contribute to improved health and well-being.

### 
**Question 7:** How does life expectancy affect GDP and Poverty and vise versa?


![](/images/Aly9-2f8edb09.PNG)

#### Insights

**GDP and life expectancy are positively correlated.** This means that countries with higher GDPs tend to have higher life expectancies. This is likely due to a number of factors, such as better access to healthcare, education, and nutrition in wealthier countries.

**GDP and poverty are weakly positively correlated.** This means that countries with higher GDPs tend to have slightly lower levels of poverty. This is likely due to the fact that wealthier countries have more resources to invest in social programs and infrastructure, which can help to reduce poverty.

**Poverty and life expectancy are negatively correlated.** This means that countries with higher poverty rates tend to have lower life expectancies. This is likely due to the fact that people who are poor are more likely to live in unhealthy environments, have less access to healthcare, and have lower incomes, which can all contribute to shorter life expectancies.

### 

Conclusions

> Poverty

*   Poverty is a major problem in Africa, with over **40%** of the population living on less than one dollar ninety cents a day.

*   The distribution of poverty is uneven, with some countries, such as the Democratic Republic of Congo and Mozambique, having much higher poverty rates than others.

*   There are a number of factors that contribute to poverty in Africa, including conflict, corruption, and climate change.

> Life expectancy

*   Life expectancy in Africa has been increasing in recent years, but it is still lower than the global average.

*   The highest life expectancies are found in countries with the highest GDPs, such as Algeria and Morocco.

*   The lowest life expectancies are found in countries with the lowest GDPs, such as Chad and Lesotho.

> GDP

*   GDP per capita in Africa has been increasing in recent years, but it is still lower than the global average.

*   The highest GDPs are found in countries with the largest populations, such as Nigeria and South Africa.

*   The lowest GDPs are found in countries with the smallest populations, such as Sao Tome and Principe and Seychelles.

In Conclusion, There is a clear relationship between poverty, life expectancy, and GDP in Africa.


*   Countries with higher GDPs tend to have lower poverty rates, higher life expectancies, and better healthcare and education systems.


*   Countries with lower GDPs tend to have higher poverty rates, lower life expectancies, and poorer healthcare and education systems.

There are a number of challenges that need to be addressed in order to reduce poverty, improve life expectancy, and improve GDP in Africa. These challenges include conflict, corruption, climate change, and lack of access to education and healthcare.

Despite the challenges, there is reason to be optimistic about the future of Africa. Africa has a young and growing population, and it is rich in natural resources. With the right policies and investments, Africa can continue to grow and develop, and it can improve the lives of its citizens.

### 
Recommendations

Africa is a vast and diverse continent with a population of over 1.2 billion people. It is also a continent with a great deal of potential, with abundant natural resources and a young and growing population. However, Africa also faces a number of challenges, including poverty, hunger, disease, and conflict.

There are a number of things that can be done to improve economic and social conditions in Africa. These include:

*   **Investing in education and healthcare:** Education and healthcare are essential for economic growth and development. By investing in these sectors, African countries can improve the skills and productivity of their workforce and reduce the burden of disease.

*   **Promoting good governance:** Good governance is essential for attracting investment and creating a stable and predictable environment for businesses. African countries need to strengthen their institutions and fight corruption in order to promote good governance.

*   **Encouraging trade and investment:** Trade and investment are essential for economic growth. African countries need to open their markets to trade and investment and create an environment that is attractive to businesses.

*   **Addressing climate change:** Climate change is a major threat to Africa. By addressing climate change, African countries can protect their natural resources, reduce the risk of disaster, and improve the lives of their people.

*   **Promoting peace and security:** Peace and security are essential for economic growth and development. African countries need to resolve their conflicts and build strong institutions that can maintain peace and security.

These are just a few of the things that can be done to improve economic and social conditions in Africa. By taking these steps, African countries can create a better future for their people.








A Deep Dive into Poverty, Life Expectancy and GDP (Gross Domestic Product) across African Countries

#### Introduction

In this analysis, we aimed to gain valuable insights from a dataset containing 500,000 rows of Twitter data related to GPT and AI-powered conversational technologies. The goal was to understand public opinion, trends, and potential applications of ChatGPT by analyzing tweet volume, sentiment, user engagement, and the influence of key AI events. By examining various aspects such as sentiment analysis, sentiment trends over time, sentiment by user, word cloud analysis, hashtag analysis, influencer analysis, we were able to extract meaningful information. This analysis provided valuable insights for companies, researchers, and policymakers, enabling them to make informed decisions and shape the future of AI-powered conversational technologies.

### 1) Sentiment Analysis

*   1.1: Understanding the general public opinion towards GPT and AI-powered conversational technologies.
*   1.2: Examining Sentiment trends over time
*   1.3: Examining Sentiment by user
*   1.4: WordCloud Analysis

### 1.1: Overall sentiment Analysis

By consolidating the sentiment expressed in all the tweets, we were able to obtain a comprehensive overview of the overall sentiment and its corresponding percentage.

![](https://preview--olabodeeso-374d3.stackbit.dev/images/NLP1.PNG)

##### **Insights**

Based on the sentiment analysis of the 500,000 tweets about GPT, the majority of tweets **(48.82%) expressed a positive sentiment**, followed by **neutral tweets (37.18%)**, and a smaller proportion of **negative tweets (13.99%)**.
This suggests that overall, the **public perception of GPT is largely positive**, which is encouraging for companies and researchers developing and using AI-powered conversational technologies.
However, it is important to note that this sentiment analysis only captures a snapshot of public opinion at a given time and may not be representative of the entire population

### **1.2: To examine Sentiment trends over time**

By visualizing the total individual sentiment of tweets for each day, we can observe the progression of sentiments over time. Plotting this data on a chart allows us to track the fluctuations and trends in sentiment on a daily basis.

![](/images/NLP2.PNG)

It looked like there was a spike in negative tweets about AI and GPT between March 8 and March 10, we zoomed in to see if we can connect this to an event or occurrence.

![](/images/NLP3.PNG)

We then brought up tweets from March 8 from 1pm which has a negative sentiment

![](/images/NLP4.PNG)

##### **Insights**

Based on the filtered data, it appears that on March 8th, there was an increase in negative sentiment tweets about GPT during the time period from 1:00 PM to 11:59 PM.
This may suggest that a particular event or news related to GPT occurred during this time period that led to a spike in negative sentiment among Twitter users.
Although, insufficient data was not available to determine why this happened.

### **1.3: To examine Sentiment by user**

we grouped the data by each tweeter and calculated the average sentiment score for each user, to allow us identify which users are most positive or negative towards GPT and AI

![](/images/NLP5.PNG)

##### **Insights**

From the analysis of sentiment by tweeters, we can identify tweeters who have an overall positive or negative sentiment towards GPT and AI. For example, we can see which users have a **polarity score of +1**, indicating an extremely positive sentiment towards GPT and AI.
These users may be potential brand ambassadors for GPT and AI, and their positive sentiment may influence others to have a favorable view of GPT and AI. On the other hand, we can also identify users with an extremely negative sentiment towards GPT and AI, with a **polarity score of -1**.
These users may be potential detractors, and their negative sentiment may influence others to have an unfavorable view of GPT and AI.

### **1.4: WordCloud Analysis**

### ![](/images/NLP6.PNG)

We processed the "tweet text" by removing irrelevant elements such as URLs, mentions, and hashtags. Additionally, we eliminated common stop words to focus on the most meaningful words. With the cleaned text, we generated a word cloud representation where the size of each word is determined by its frequency of occurrence in the dataset.

### **2) Hashtag Analysis**

**Goal:** To identify the most frequently used hashtags in tweets related to GPT. This will help us understand the most popular topics and trends related to GPT on social media, and potentially use these hashtags to reach a wider audience.

### ![](/images/NLP7.PNG)

##### **Insights**

The hashtag analysis revealed that people who tweeted about GPT also used related hashtags such as **ChatGPT, AI, OpenAI, technology**, and others. This indicated that GPT was a topic of interest among individuals and organizations involved in the broader field of artificial intelligence and related technologies. By utilizing these hashtags in their social media posts, businesses and organizations had the opportunity to reach a broader audience interested in these specific topics.

###

### **3) Influencer Analysis**

**Goal** The goal of this analysis was to identify the Twitter users who were most influential in discussing GPT and engage with them to potentially increase the reach and visibility of businesses, brands, and companies. By computing metrics such as the number of retweets and likes, we were able to identify the most active and engaged users, allowing us to target them for strategic engagement strategies.

we examined the top tweeters with the highest likes and retweet engagements. By analyzing the number of likes and retweets received by each user

##### ![](/images/NLP9.PNG)

![](/images/NLP10.PNG)

### **Insights**

From our influencer analysis, we can see that there are several Twitter users who have a high number of likes and retweets on their tweets related to GPT. These influencers include **DataChaz, johnvianny, and many others**. By engaging with these influencers, businessess and organizations can potentially reach a wider audience and increase brand visibility. By retweet or mention their tweets, or offer them incentives such as discounts or free trials to further promote business brand.

###

### **Conclusions**

After analyzing the data related to GPT and AI-powered conversational technologies on Twitter, we have gained valuable insights that can improve business strategies and brand visibility.

We started by performing sentiment analysis to understand the general public opinion towards GPT and AI. The analysis revealed that the majority of tweets expressed a positive sentiment towards GPT, with a positivity rate of 48.82%. We also observed a generally positive sentiment trend over time, with occasional fluctuations. Additionally, sentiment analysis by user helped identify individuals with positive and negative sentiments towards GPT and AI.

Furthermore, the WordCloud analysis of the tweets highlighted the most commonly used words, providing a visual representation of the popular topics and discussions surrounding GPT.

In terms of hashtags, our Hashtag Analysis identified the most frequently used hashtags in tweets related to GPT, such as #Chatbot, #AI, #MachineLearning, and #NLP. These hashtags can be leveraged to target a wider audience and tap into popular topics and trends.

The Influencer Analysis identified key users, including influencers like DataChaz and johnvianny, who have a significant impact on the dissemination and engagement of GPT-related tweets. Engaging with these influencers through retweets, mentions, or offering incentives can help businesses increase their brand visibility and reach a wider audience.

Overall, these analyses provide valuable insights into public sentiment, popular topics, and influential users related to GPT on social media. By leveraging these insights, businesses and organizations can enhance their social media strategies, improve engagement, and effectively connect with their target audience.

Full notebook and analysis can be found at <https://github.com/Genbodmas/NLP-Analysis-of-Twitter-GPT-Tweets>


NLP ANALYSIS OF TWITTER GPT TWEETS

## Business Problem

As a data analyst at a news organization, you are asked to visualize data that will help readers understand how countries have
historically performed in the summer Olympic games. You also know that details about the competitors are of interest, so if you come across anything interesting, don't be afraid to bring it up.

The main task is to display historical performance for various countries, with the option of selecting your own country.

Deliverable

*   Create a visual data story or dashboard in PowerBI for users to examine how their country has historically performed in the Olympics.

*   Highlight other interesting details about competitors.

## Dataset Walktrough

*   ID – Competitor ID

*   Name – Competitor’s full name

*   Sex, Age , Height , Weight – Biodata Information of
    competitor

*   NOC – Country Code

*   Games – Olympic Game Year and season

*   City – City where game took place

*   Sport – Type of game played by competitor

*   Event – Specific Event of sport

*   Medal – Type of medal awarded to competitor (NA = No
    medal) 

## Data Transformation Walkthrough

*   Renamed ‘Name’ and ‘NOC’ column to ‘Competitor Name’ and ‘Country Abbreviation’

*   Transformed gender/sex column from ‘M’ and ‘F’ to ‘Male’ and ‘Female’ for better representation.

*   Created new column called ‘Age_Group’ which specifies if the competitor is either (Under 18, 18-25, 25-30 and over 30)

*   Renamed ‘NA’ values in Medal column to ‘Not registered’

*   As per the business requirement, data was filtered to return only summer games.

SQL code for this operation can be found below

```
SELECT
ID,
Name AS 'Competitor Name',

-- Renamed Column
CASE WHEN Sex = 'M' THEN 'Male' ELSE 'Female' END AS Sex,

-- Better Gender Representation
Age,
CASE WHEN Age < 18 THEN 'Under 18' WHEN Age BETWEEN 18
AND 25 THEN '18-25' WHEN Age BETWEEN 25
AND 30 THEN '25-30' WHEN Age > 30 THEN 'Over 30' END AS Age_Group,
-- Created new age group column
Height,
Weight,
NOC AS 'Country abbreviation',

-- Renamed Column for better comprehension
Games,
Year,
Season,
City,
Sport,
Event,
CASE WHEN Medal = 'NA' THEN 'Not Registered' ELSE Medal END AS Medal

FROM
SportsStats..athlete_events
WHERE
Season = 'Summer'

```



The before and after transformation of the data is shown below ;

Before Transformation

![](/images/Picture2.png)

After Transformation

![](/images/Picture3.png)

### Dashboard

The completed dashboard includes visualizations and filters that allow users to navigate the records data through history. Filtering options include filtering by year, country, sport type, and competitor name.

There are also stacked charts that show how many medals were won in each sport category as well as by individual players or
competitors.

![](/images/Olympic%20sportstats%20dashboard-b2c274e6.PNG)









SportStats olympic dataset analysis and visualization using PowerBi

# Goal

Develop a database to Analyze and Visualize Hotel Booking Data

## Deliverable

Build a visual data story or dashboard using PowerBi to present to stakeholders

## Business Questions

*   “Is our hotel revenue growing by year?” – We have two hotel types so it will be great to 
    segment revenue by hotel type

*   “Should we increase our parking lot size” – We want to understand if there is a trend in 
    guests with personal cars

*   “What trends can we see in the data” - Focus on average daily rate and guests to explore 
    seasonality


## Project Pipeline

1.  Build a database

2.  Develo SQL Query

3.  Connect database to PowerBI

4.  Visualize

5.  Summarize findings

![](/images/pipeline.png)














Interactive KPI dashboard of Atrium Hospitality using PowerBI

### Project Objective

The year is 2020, and the stakeholders of G and H have provided us with data from the previous year (12 months). The data contains hundreds of thousands of electronics store purchases broken down by month, product type, cost, purchase address, and so on to create a summary of their performance and product performance, as well as projections and recommendations for the following year.

### Deliverable

Create a summary and recommendation report to present to the stakeholders

### Possible Questions

*   What was the best month for sales? and how much was earned that month?

*   Which city had the highest sales recorded?

*   What time should we display advertisement to maximize likelyhood of customer buying our product?

*   Which products are most often sold together?

*   What product sold the most? Why do you think it sold the most?

### Tool Used

*   Python (pandas, numpy, matplotlib)

### Code

The  full documentation of the project can be found on GitHub here:

 <https://github.com/Genbodmas/Analysis-on-Sales-Performance-and-Improvement>

 



Sales Performance Analysis and Improvment (Python)

### Project Overview

The dataset used in this data wrangling (and analyzing and visualizing) project is the tweet archive of Twitter user @dog_rates, also known as WeRateDogs. WeRateDogs is a Twitter account that rates people's dogs with a humorous comment about the dog. These ratings almost always have a denominator of 10. The numerators, though? Almost always greater than 10. 11/10, 12/10, 13/10, etc. Why? Because "they're good dogs Brent." WeRateDogs has over 4 million followers and has received international media coverage.

Tools Used

*   Python (Matplotlib, seaborn, Tweepy, pandas, numpy)

*   Twitter API

### Wrangling Objective

*   To assess these data and check for quality and tidyness issue.

*   To clean these identified issues, merge and produce a clean data for analysis.

### Data Gathering

Three different data was gathered in this stage, all from three different data sources

*   Directly download the WeRateDogs Twitter archive data (twitter_archive_enhanced.csv): This data was already given to be downloaded locally. This was loaded into the notebook with pandas read_csv command.

*   Tweet image prediction (image_predictions.tsv): This data contains the tweet image predictions of the dog. It is hosted on Udacity's servers and was to be downloaded using the python Requests library, as the url link of the data was given.

*   Additional data via the Twitter API (tweet_json.txt): This data requires me to gather each tweet's retweet count and favorite ("like") count at the minimum. Using the tweet IDs in the WeRateDogs Twitter archive, the Twitter API was queried for each tweet's JSON data using Python's Tweepy library and each tweet's was stored in the form of a JSON data called tweet_json.txt file. 

    To query the Twitter API, this process required me to have a Twitter elevated account authentication details, which i applied for and was approved in a couple of hours

### Data Assessment

This stage, i applied both visual and programmatic assessment to identify and list our various quality and tidyness issue in the data. These are:

### Quality Issues

##### `twitter_archive` table

1.  tweet text column should only contain the text

2.  Some entries are not original tweets (replies and retweets)

3.  Drop columns that are essentially empty

4.  Timestamp column is not in appropriate format

5.  Ratings not extracted correctly (decimal ratings)

6.  Rating_numerator column contain outlier values (1776, 204,0)

7.  Ratings_denominator column has values more or less than 10

8.  Some dogs name were incorrectly entered (a, an, the)

##### `image_pred` table

1.  Get the final prediction of the dog breed from the two predicted values

2.  Capitalize each dog name

##### `tweet_rem` table

1.  rename 'id' column to 'tweet_id'

### Tidiness issues

1.  Dog stages should be combined to one column

2.  Merge three DataFrame to One Master dataFrame

### Code

The full project is hosted on GitHub found here

<https://github.com/Genbodmas/Twitter-API-Data-Wrangling-and-EDA-Udacity-Data-Analytics-Nanodegree>


Sunset fishing is a beautiful time of day to relax and get in touch with nature

Twitter-API-Data-Wrangling-and-EDA-WeRateDogs

#### **Goal and Problem Statement**

The goal of this project is to help the marketing team understand the target customers and plan a strategy accordingly. The manager of the company wants to identify the most important shopping groups based on income, age, and their shopping score. The company wants to divide its mall target market into approachable groups, create subsets of a market based on demographics, and behavior criteria to better understand the target for marketing activities.

#### **Context**

The company wants to analyze customer data and segment the customers into different groups based on their demographic and behavioral patterns. By doing so, the company can better understand its target customers and plan its marketing strategy accordingly.

#### **Objective**

The objective of this project is to segment the customer base into different groups to better understand their behavior patterns, demographics, and target them more effectively with the marketing strategy. The customer segmentation will enable the company to personalize its marketing campaigns and promotions to specific target groups.

#### **Approach**

1.  Perform quick EDA (Exploratory Data Analysis) on the data to understand its structure, quality, and outliers.

2.  Use the KMeans clustering algorithm to segment the customers into different groups.

3.  Create visualizations to better understand the distribution and patterns of the data.

#### **Analysis**

**EDA:**
Exploratory Data Analysis is a crucial step in understanding the structure and quality of the data. Univariate analysis is important in this context because it allows us to examine each feature of the data individually, check for density and outliers, and identify patterns in the data. By doing so, we can better understand the distribution of each feature and its impact on the segmentation. Bivariate analysis involves plotting scatter plots, pair plots, and heat maps to explore the relationship between two variables. By doing so, we can identify correlations and relationships between variables.


All Projects