firstName

lastName

email

message

updatesConsent

Let’s talk... 💬

Personal

Nunc rutrum felis dui, ut consequat sapien scelerisque vel. Integer condimentum dignissim justo vel faucibus.

Construction of a Datamart for Analysis

Story telling using visualizations based on data on COVID19, Games, Sales and Financials

# INTRODUCTION

To create these dashboards, a range of technical skills is required, particularly in the ETL (Extract, Transform, Load) process. **Extraction** involves importing data from various sources, such as live data streams, SQL Azure Data Studio, CSV files, and Excel, into Power BI Query Editor. **Transformation** includes data cleaning tasks like removing errors, handling duplicates and nulls, transposing tables, and performing operations such as joining and appending tables. After creating fact and dimension tables, the data is **loaded** into Power BI for analysis. At this stage, I conducted data modeling, which included defining relationships, creating measures using DAX (Data Analysis Expressions), building hierarchies, and establishing a date table for time-based analysis. In the visualization pane, I designed and customized visuals tailored to the data type and analysis requirements, ensuring the dashboard provides clear and actionable insights.



### PROJECT 1. Defining Marketing Strategy based on COVID19 Data - Power BI

Due to health concerns for their staff, the top managemenet of a company which has branches spread in several states in Nigeria is looking for real time information on COVID 19 that will inform them on the best business strategies to consider in a bid to minimize loss. To achieve this, real time data was obtained from <http://covid19.ncdc.gov.ng/> and loaded into Power BI. A report was created using visuals like maps, graphs, charts and sliders. Among the 37 states observed, a quarter million cases of COVID 19 was registered and over 3000 deaths, predominantly in Lagos, FCT and Rivers States. I recommended that stricter sanitary measures be observed at the branch offices in these cities.

###### ![](https://res.cloudinary.com/fulechib/image/upload/v1726713646/COVID19_Nigeria_1_ubfsmp.jpg)Click here to access the PowerBI file: [https://github.com/FuleChi/Power-BI/blob/main/COVID19%20Nigeria.pbix](https://github.com/FuleChi/Power-BI/blob/main/COVID19%20Nigeria.pbixPROJECT)



### PROJECT 2. Visualizing Financials of A Company

This project highlights key performance metrics, showing gross sales of $127.9M, total profit of $16.9M, and an average profit of $24.1K. A notable profit increase of 165.29% occurred between September 2013 and December 2014, with the Government segment contributing the highest profit of $3M for the Paseo product. Profit growth was particularly strong from July to December 2014, where it jumped from $923K to $2M.

Profit trends, represented by a line graph, reveal significant spikes in November 2013, May 2014, and November 2014. On a geographical scale, France contributed the highest profit, 30.04% higher than Mexico, which had the lowest. Product-wise, Paseo and Amarilla performed well across segments, especially within the Government sector, driving overall profitability.

###### ![](https://res.cloudinary.com/fulechib/image/upload/v1726713646/Financials_Report_vzhx80.jpg)Access the PowerBI file here: <https://github.com/FuleChi/Power-BI/blob/main/Coca-cola%20Sales.pbix>



### PROJECT 3. Visualizing Premier League 2021/2022

This project provides a detailed overview of the 2021/2022 Premier League season. The total points accumulated across all teams was 1052, with 292 wins, 292 losses, 176 draws, and 1071 goals scored. Teams such as Brighton and Crystal Palace led in the number of draws, with 15 and 14 draws respectively, while teams like Arsenal and Watford had fewer draws. The dashboard also highlights goals for and against each team, with additional insights into wins, losses, and points per team, providing a clear picture of the league's overall performance.

###### ![](https://res.cloudinary.com/fulechib/image/upload/v1726713646/Premier_League_Assignment_guwvga.jpg)The PowerBI file is accessible here: <https://github.com/FuleChi/Power-BI/blob/main/Premier%20League%20Dashboard.pbix>



### PROJECT 4. Visualizing the Salary of Employees

###### ![](https://res.cloudinary.com/fulechib/image/upload/v1726713645/Salary_analytics_quh87s.jpg)Access the PowerBI file here: [https://github.com/FuleChi/Power-BI/blob/main/Salary%20Dashboard.pbix](https://github.com/FuleChi/Power-BI/blob/main/Salary%20Dashboard.pbixcONCLUSIONThe)





Data Visualization using Microsoft Power BI

name

email-address

updates

Commentaires

Partagez votre avis!

Major challenge : Analyzing binary data in Microsoft SQL server (presented in French). This is my version of a project realized with  Ghislain Fepessi Kewou and Eveline Carine Kougang Soh.

### Description

Ce projet a été réalisé avec une base de données d'une compagnie qui effectue des campagnes en ligne sur son site internet afin d'augmenter la visite de ce site, dans le but de favoriser les achats aussi bien en ligne qu'en magasin.

Les données captent les réactions de la clientèle face aux cinq campagnes publicitaires en ligne couvrant la période de juillet 2012 à juin 2014.

Leur taux de succès est mesuré par le nombre de visites du site web, le nombre d'achats par campagne et la rapidité avec laquelle l'achat a été effectué après la lecture de la publicité. Il s'agit de la vente de six grandes catégories de produits, y compris de l'or, des sucreries, du poisson, de la viande, des fruits et du vin.

Les caractéristiques socio-économiques de la clientèle sont captées par leur année de naissance, niveau d'éducation, statut marital, revenu familial, et le nombre d'enfants et d'adolescents à la maison. Les clients proviennent de sept pays dont l'Allemagne, l'Espagne, le Canada, l'Afrique du Sud, les USA, l'Australie et l'Inde.



### Structure des données

*   Nombre de colonnes : 28

*   Nombre de lignes : 2240

######

##### **Table 1. Noms des variables et types de données**

| Nom de colonne      | Description                                                        | Type de donnée avant | Data de donnée après |
| ------------------- | ------------------------------------------------------------------ | -------------------- | -------------------- |
| ID                  | Unique identifier for each customer                                | smallint             | smallint             |
| Year\_Birth         | Year of birth of the customer                                      | smallint             | smallint             |
| Education           | Customer's level of education                                      | nvarchar(50)         | nvarchar(50)         |
| Marital\_Status     | Customer's marital status                                          | nvarchar(50)         | nvarchar(50)         |
| Income              | Customer's yearly household income                                 | nvarchar(50)         | int                  |
| Kidhome             | Number of small children in customer's household                   | tinyint              | tinyint              |
| Teenhome            | Number of teenagers in customer's household                        | tinyint              | tinyint              |
| Dt\_Customer        | Date of customer's enrollment with the company                     | date                 | date                 |
| Recency             | Number of days since the last purchase                             | tinyint              | tinyint              |
| MntWines            | Amount spent on wines in the last 2 years                          | smallint             | smallint             |
| MntFruits           | Amount spent on fruits in the last 2 years                         | tinyint              | tinyint              |
| MntMeatProducts     | Amount spent on meat products in the last 2 years                  | smallint             | smallint             |
| MntFishProducts     | Amount spent on fish products in the last 2 years                  | smallint             | smallint             |
| MntSweetProducts    | Amount spent on sweet products in the last 2 years                 | smallint             | smallint             |
| MntGoldProds        | Amount spent on gold products in the last 2 years                  | smallint             | smallint             |
| NumDealsPurchases   | Number of purchases made with discount                             | tinyint              | tinyint              |
| NumWebPurchases     | Number of purchases made through company's website                 | tinyint              | tinyint              |
| NumCatalogPurchases | Number of purchases made using catalog                             | tinyint              | tinyint              |
| NumStorePurchases   | Number of purchases made directly in stores                        | tinyint              | tinyint              |
| NumWebVisitsMonth   | Number of visits to company's website in the last month            | tinyint              | tinyint              |
| AcceptedCmp3        | 1 if customer accepted the offer in the 3rd campaign, 0 otherwise  | bit                  | bit                  |
| AcceptedCmp4        | 1 if customer accepted the offer in the 4th campaign, 0 otherwise  | bit                  | bit                  |
| AcceptedCmp5        | 1 if customer accepted the offer in the 5th campaign, 0 otherwise  | bit                  | bit                  |
| AcceptedCmp1        | 1 if customer accepted the offer in the 1st campaign, 0 otherwise  | bit                  | bit                  |
| AcceptedCmp2        | 1 if customer accepted the offer in the 2nd campaign, 0 otherwise  | bit                  | bit                  |
| Response            | 1 if customer accepted the offer in the last campaign, 0 otherwise | bit                  | bit                  |
| Complain            | 1 if customer complained in the last 2 years                       | bit                  | bit                  |
| Country             | Customer's country                                                 | nvarchar(50)         | nvarchar(50)         |

### Source des données

Kaggle: [https://www.kaggle.com/datasets/sahilnbajaj/marketing-campaigns-data-set](https://www.kaggle.com/datasets/sahilnbajaj/marketing-campaigns-data-setAnalyse)



### Analyse des données

Afin d'obtenir des informations pertinentes pour affiner notre stratégie de marketing, j'ai procédé aux requêtes suivantes :

1.  Nombre total de clients par pays dans l'ordre décroissant

2.  Quel status maritals a le revenu moyen le plus élevé?

3.  Le niveau d'education et le nombre des clients  dont les revenus sont supérieurs à la moyenne des revenus de l'échantillon d'analyse

4.  La liste des clients qui ont déjà fait au moins un achat sur le site web de la compagnie

5.  Relation entre le nombre d'enfants et adolescent àla maison et le montant des achats

6.  Le nombre de clients mariés et leur ancieneté de statut  de client avec la compagnie, au 31 décembre 2023

7.  Le nombre de clients depuis leur dernier achat

8.  Le nombre de visites web par mois

9.  Le taux de réponse positif aux campagnes de marketing

10. Statistiques de toutes les campagnes de marketing ('Succès' si le client a acheté pendant la campagne en question et 'Échec' si le client n'a pas fait d'achat pendant la campagne en question)

11. Le nombre de plaintes effectuées par pays

12. Quelle est la moyenne des achats par type de produit ?

13. Le produit le plus vendu et la date de la vente

14. L'année à laquelle on a eu la plus grande vente en ligne

15. Le nombre total des ventes provenant de tous les circuits de vente

16. Le pourcentage des ventes selon le type de circuit de vente

17. Le montant dépensé sur le vin pendant les deux dernières années selon le niveau d'éducation des clients

18. Les caractéristiques socio-économiques des célibataires les plus âgés selon leur pays

19. Le nombre de clients par generation, ainsi que leur âge moyen et leur voie d'achat préférée

Le code SQL de ces 19 requêtes se trouve [ICI](https://github.com/FuleChi/Digital-marketing-campaign/blob/main/TP1_requetes%20Marketing_data_final.sql).



### Resultat de l'Analyse

La majorité des clients proviennent d'Espagne, contribuant ainsi à 49 % des ventes totales. Les clients ayant le statut de 'Absurd' ont le revenu moyen le plus élevé. Attention : ceux-ci ne représentent que 0,1 % de la population. Il serait donc sage de prêter attention plutôt aux mariés, qui constituent 38,6 % de la clientèle.

En termes de niveau d'éducation, les clients ayant un doctorat sont les plus nombreux, suivis par ceux ayant une maîtrise et ceux ayant un niveau collégial en troisième position. Il est néanmoins important de souligner que les clients ayant au moins un niveau collégial constituent la majorité de la clientèle.

La majorité des clients ont un enfant/adolescent à la maison ; cependant, les clients qui n'ont ni enfant ni adolescent à la maison sont responsables de la grande majorité des achats effectués.

Les campagnes publicitaires en ligne, toutes campagnes confondues, ont eu un taux de succès de 30 %, avec la campagne No. 4 ayant le plus grand succès à 7,4 % et la campagne No. 2 ayant le moindre succès à 1,3 %.

Le vin est le produit le plus demandé, avec la plus grande vente effectuée le 22 mai 2013.

La compagnie réalise 12 % de ses ventes en magasin de plus qu'en ligne. La vente par les catalogues et les offres promotionnelles est non négligeable, car elles contribuent à 33 % des ventes totales.

Les Gen X constituent la majorité de la clientèle. Ils ont un âge moyen de 51 ans et préfèrent magasiner davantage en magasin.



### Points Forts du Projet

1.  **Richesse des Données** :

    *   La base de données est riche et diversifiée, contenant des informations détaillées sur les clients, y compris des données démographiques et des comportements d'achat.

    *   Les données couvrent plusieurs pays, ce qui permet des analyses comparatives intéressantes entre les différentes régions.

2.  **Analyse Complète des Campagnes** :

    *   Les données permettent de mesurer le taux de succès des campagnes publicitaires de manière détaillée, y compris le nombre de visites et d'achats, et la rapidité des achats post-publicité.

    *   Les campagnes sont analysées sur une période de deux ans, ce qui permet de discerner des tendances et des évolutions dans les comportements d'achat.

3.  **Segmentation Socio-Économique** :

    *   La segmentation des clients par statut marital, niveau d'éducation, et revenu familial offre des insights précieux pour adapter les stratégies marketing aux différents segments de la clientèle.

4.  **Multicanal** :

    *   Les données incluent différents types de canaux d'achat (en ligne, en magasin, via catalogue), permettant d'analyser la performance de chaque canal et de comprendre les préférences des clients.



### Faiblesses du Projet

1.  **Données Anciennes** :

    *   Les données couvrent une période de juillet 2012 à juin 2014. Elles peuvent ne plus refléter les comportements actuels des consommateurs ou les tendances du marché.

2.  **Manque de Données Qualitatives** :

    *   Les données sont principalement quantitatives. Des informations qualitatives sur la satisfaction des clients ou leurs motivations d'achat pourraient enrichir l'analyse.

3.  **Représentation Inégale des Catégories de Produits** :

    *   Les six catégories de produits analysées peuvent ne pas couvrir l'ensemble de l'offre de la compagnie, limitant ainsi la portée des conclusions.

4.  **Problèmes de Segmentation** :

    *   La segmentation par statut marital inclut une catégorie "Absurd" avec un revenu moyen élevé mais ne représentant que 0,1 % de la population, ce qui pourrait biaiser les analyses.

5.  **Données Sur Les Réclamations** :

    *   Bien que le nombre de plaintes soit collecté, il n'y a pas d'analyse approfondie des raisons des plaintes ou de la satisfaction globale des clients, ce qui est crucial pour l'amélioration continue des services.

**6. Analyse des données:**

Cette analyse se limite aux statistiques descriptives du donnée. Une ètude plus approfondi utilisant les regressions par exemple, pourrais faire resortir la dependance entre les variables et leurs niveaux de correlation.



### Conclusion

Ce projet présente une base solide pour l'analyse des campagnes marketing avec des données détaillées et diversifiées. Toutefois, des mises à jour des données, une inclusion de données qualitatives et une analyse plus approfondie des réclamations des clients pourraient améliorer la pertinence et la profondeur des insights obtenus.


Digital Marketing Campaign Project

This Project was realised with my team mates, Ghislain FEPESSI AND EVELINE SOH

### Introduction

Every leader would agree that their greatest challenge is to design policies that satisfy everybody.

This is no different for the Parliament of India, who in September 2020 passed three farm acts that led to a farmer's protest.

During the pre-internet era, such a situation would be easily brought under control by meeting farmer's unions and leaders in the affected communities.

However, during the internet era, the situation easily spread like wild fire on the internet, making it difficult to manaage.

Without a clue about who's opinion is actually fuelling the uproar, it is difficult and even close to impossible for policy makers to control the situation since chances of having an effective dialogue are low.

Fortunately, with the right analysis of the data collected from Twitter (X) , it is possible to initiate dialogue with the right audience.

### About the Data

The data presents a 2020–2021 Indian farmers' protest by Farmer unions and their representatives, who  have demanded that the laws be repealed and have stated that they will not accept a compromise.

In the light of this protest, social media users have been very active in voicing their opinion about the matter.

The hashtag "#FarmersProtest" is very prevalent on Twitter, with thousands of users tweeting thousands of tweets with the hashtag.

The dataset consists of a JSON collection containing 139,999 documents about the tweets that have the hashtag "#FarmersProtest" (tweeted since February 1, 2021).

You may access the raw dataset here: [source](https://www.kaggle.com/datasets/prathamsharma123/farmers-protest-tweets-dataset-raw-json).

### Procedure

The dataset was uploaded into MongoDB Compass (a collection called protests.json was created) and was interesting queries were written via VS Code.

The "protests" collection is made up of 139,999 documents, 22 variables and several sub fields.

Table showing the variables and their corresponding data types:

| No. | Name of column  | Data Type         |
| --- | --------------- | ----------------- |
| 1   | url             | String            |
| 2   | date            | Date              |
| 3   | content         | String            |
| 4   | renderedContent | String            |
| 5   | \_id            | ObjectId          |
| 6   | user            | Object            |
| 7   | outlinks        | Array             |
| 8   | tcooutlinks     | Array             |
| 9   | replyCount      | Integer           |
| 10  | retweetCount    | Integer           |
| 11  | likeCount       | Integer           |
| 12  | quoteCount      | Integer           |
| 13  | conversationId  | Integer           |
| 14  | lang            | String            |
| 15  | source          | String            |
| 16  | sourceUrl       | String            |
|     |                 |                   |
| 17  | sourceLabel     | String            |
| 18  | media           | Array (Nullable)  |
| 19  | retweetedTweet  | Object (Nullable) |
| 20  | quotedTweet     | Object (Nullable) |
| 21  | mentionedUsers  | Array (Nullable)  |
| 22  | id              | Integer           |

The location information in the data was used to create a new collection named "geoLocation" that grouped cities into countries and countries into continents.

### Key Findings

1.  **Number of protesters in our database**

db.protests.aggregate(\[  {    $group: {      \_id: "$user.username"    }  },  {    $count: "uniqueUsernames"  }])

**Output**: 21,318

**2. The top 10 protesters who have the most retweets using the hashtag #FarmersProtest**

db.protests.aggregate(\[    { $match: { "content": { $regex: "#FarmersProtest", $options: "i" } } },    { $group: { \_id: "$user.username", tweet\_count: { $sum: 1 } } },    { $sort: { tweet\_count: -1 } },    { $limit: 10 }  ])

**Output**: harjot\_tweeting, tasveersandhu, rumsomal, rebelpacifist, shells\_n\_petals, jot\_\_b, with\_kaur, Jass\_k\_G, Iamjazzie96, and DigitalKisanBot

***

**3. The top 10 protesters with the highest number of followers on Twitter**

db.protests.aggregate(\[    {      $group: {        \_id: "$user.username",          totalFollowers: { $sum: "$user.followersCount" },         avgFollowers: { $avg: "$user.followersCount" }      }, { $sort: { retweetCount: -1 } },    }  ])

**Output**: ndtv, htTweets, IndiaToday, ZeeNewsEnglish, timesofindia, ABPNews, EconomicTimes, dna, bsindia and the\_hindu

**4. Top Hashtags Used Alongside #FarmersProtest**

db.protests.aggregate(\[    { $match: { "content": { $regex: "#FarmersProtest", $options: "i" } } },    { $unwind: "$content" },    { $group: { \_id: "$content", count: { $sum: 1 } } },    { $sort: { count: -1 } },    { $limit: 10 }  ])

**Output**: #ReleaseActivists #ReleaseFarmers #IStandWithFarmers #FarmersProtest #ReleaseFrStanSwamyNow #HathrasHorrorShocksIndia #DelhiRiots2020 #ReleaseActivists #ModiAgainstFarmers #300DeathsAtProtest

**5. Top 5 Countries with the highest number of tweets**

db.protests.aggregate(\[    {      $lookup: {        from: "geoLocation",        let: { userLocation: { $toLower: "$user.location" } },        pipeline: \[          {            $unwind: "$cities\_countries"          },          {            $match: {              $expr: {                $eq: \[{ $toLower: "$cities\_countries.City" }, "$$userLocation"]              }            }          }        ],        as: "location\_data"      }    },    {      $unwind: "$location\_data"    },    {      $group: {        \_id: "$location\_data.cities\_countries.Country",        tweetCount: { $sum: 1 }      }    },    {      $sort: { tweetCount: -1 }    }  ])

**Output**: Unknown: 65571, India: 25915, Canada: 10,185, United Kingdom: 1747 and Australia: 1209

**6. Number of retweets according to continent and the corresponding number of protesters involved**

db.protests.aggregate(\[  {        $lookup: {      from: "geoLocation",      localField: "user.location",      foreignField: "cities\_countries.City",      as: "location\_info"    }  },  {       $unwind: "$location\_info"  },  {        $group: {      \_id: "$location\_info.continent",              numRetweets: { $sum: "$retweetCount" },       numberIDs: { $sum: 1 }                      }  },  {        $sort: { numRetweets: -1 }  }])

**Output in (numRetweets, numIDs) pairs**: Unknown (336520, 65416), Asia: (322126, 19174), North America (43847, 10177), Oceania (11659, 1209) and Europe (6503, 1860).

### Observations and Recommendations

Although the farmer's Act that was passed in India was expected to affect only the Indian farmers, many other people around the world took interest in the situation, with Canada  being the second biggest country leading the protest online after India.

Even though large media houses such as ndTV, ZeeNewsEnglish and timesofindia, just to name a few, actively participated in propagating the news around the farmer's protest in India, their opinions were not as appreciated by the public like those of individual protesters like harjot\_tweeting and tasveersandhu.

Although the hashtag #FarmersProtest was the main hashtag used in the study, protesters used other hashtags like #ReleaseActivists and #ReleaseFarmers. The hashtags in themselves reveal more information about the ongoing protest, notably, that protesters were being arrested.

Based on the analyses above, here are a few recommendations to policy makers:

*   putting the scope of them protest in perspective is a smart move, as the opinion of activists around the world has a great influence on the farmers and the protesters in India.

*   identifying the influencers and initiating dialogue with them will help save time and effort, since they can relate with other farmers more.

*   understnading the ramifications of the protest by following associated hashtags should facilitate target topics of discussion by the policy makers.

###

### Limitations of the Project

The project faced several limitations, including data quality issues such as null or empty values in fields like media, outlinks, quotedTweet, and mentionedUsers.

Case sensitivity in textual data and the complexity of fields like content and renderedContent required additional processing. Inconsistencies in city names led to 65,571 "unknown" locations, limiting geographical insights.

Many user accounts were anonymous or lacked sufficient metadata, affecting influencer identification.

The dataset covered a limited time period (February 2021), restricting long-term trend analysis. Biases inherent in social media data may have skewed results toward more vocal groups, excluding underrepresented voices like farmers who are less active online. Additionally, while hashtags were analyzed, conversations outside those specific hashtags may have been missed. These limitations impact the overall accuracy and depth of the analysis.

To address these limitations, future work should improve data cleaning, standardize text and location fields, expand the dataset's timeframe, and incorporate other data sources to reduce bias. Additionally, enhancing language processing and employing advanced methods to identify hidden influencers and conversations would yield more accurate insights.

###

### Conclusion

In conclusion, this project highlights the power of social media in shaping and amplifying public opinion, particularly in complex socio-political issues like the Indian farmers' protest. The data reveals that the protest extended far beyond India's borders, influencing activists and citizens globally. By
analyzing key influencers, hashtags, and geographical trends, policy makers can gain valuable insights into the scope and nature of the movement. Leveraging such data-driven approaches allows for more effective, targeted communication and engagement, ensuring that dialogue is initiated with the right voices and concerns are addressed efficiently. This approach not only enhances decision-making but also fosters a more inclusive and informed policy-making process.


Projects

Construction of a Datamart for Analysis

Data Visualization using Microsoft Power BI

Digital Marketing Campaign Project

MAKING SENSE OF SEMI-STRUCTURED DATA USING MONGODB - A POLICY MAKER'S GUIDE

Let’s talk... 💬