Mekki MOURADI
Data Analyst
Rabat, Maroc
À Propos
Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.
Create Social Media Dashboards in Tableau
Liens
Compétences & Outils
Description du projet
Visualiser les données d’une campagne publicitaire en :
- Créant un tableau de bord en combinant l’affichage des KPIs fondamentaux ainsi que d’un tableau reprenant l’essentiel des données
Visualiser les données de différentes compagnies aériennes en :
- Créant un Graphique en Bulles
- Créant une Carte de Géovisualisation
- Menant une Analyse de Sentiment
Objectifs de l'analyse
- Analyser et Présenter les données d’une Campagne Publicitaire et d’une Analyse de Sentiment
- Découverte du Dataset
Extrait - Facebook Ad Campaign Results
| ad_id | reporting_start | reporting_end | campaign_id | fb_campaign_id | age | gender | Grilling | BBQ Competitions | Smoked Meats | impressions | clicks | spent | total_conversion | approved_conversion |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 708746 | 17/8/2017 | 17/8/2017 | 916 | 103916 | 30-34 | M | 15 | 17 | 17 | 7350 | 1 | 1.429999948 | 2 | 1 |
| 708749 | 17/8/2017 | 17/8/2017 | 916 | 103917 | 30-34 | M | 16 | 19 | 21 | 17861 | 2 | 1.820000023 | 2 | 0 |
| 708771 | 17/8/2017 | 17/8/2017 | 916 | 103920 | 30-34 | M | 20 | 25 | 22 | 693 | 0 | 0 | 1 | 0 |
| 708815 | 30/8/2017 | 30/8/2017 | 916 | 103928 | 30-34 | M | 28 | 32 | 32 | 4259 | 1 | 1.25 | 1 | 0 |
| 708818 | 17/8/2017 | 17/8/2017 | 916 | 103928 | 30-34 | M | 28 | 33 | 32 | 4133 | 1 | 1.289999962 | 1 | 1 |
| 708820 | 17/8/2017 | 17/8/2017 | 916 | 103929 | 30-34 | M | 29 | 30 | 30 | 1915 | 0 | 0 | 1 | 1 |
| 708889 | 17/8/2017 | 17/8/2017 | 916 | 103940 | 30-34 | M | 15 | 16 | 17 | 15615 | 3 | 4.769999981 | 1 | 0 |
| 708895 | 17/8/2017 | 17/8/2017 | 916 | 103941 | 30-34 | M | 16 | 20 | 18 | 10951 | 1 | 1.269999981 | 1 | 1 |
| 708953 | 17/8/2017 | 17/8/2017 | 916 | 103951 | 30-34 | M | 27 | 31 | 31 | 2355 | 1 | 1.5 | 1 | 0 |
| 708958 | 30/8/2017 | 30/8/2017 | 916 | 103952 | 30-34 | M | 28 | 32 | 31 | 9502 | 3 | 3.159999967 | 1 | 0 |
| 708979 | 30/8/2017 | 30/8/2017 | 916 | 103955 | 30-34 | M | 31 | 37 | 34 | 1224 | 0 | 0 | 1 | 0 |
| 709023 | 29/8/2017 | 29/8/2017 | 916 | 103962 | 30-34 | M | 7 | 8 | 8 | 735 | 0 | 0 | 1 | 0 |
| 709038 | 29/8/2017 | 29/8/2017 | 916 | 103965 | 30-34 | M | 16 | 20 | 22 | 5117 | 0 | 0 | 1 | 0 |
| 709040 | 29/8/2017 | 29/8/2017 | 916 | 103965 | 30-34 | M | 16 | 19 | 21 | 5120 | 0 | 0 | 1 | 0 |
| 709059 | 30/8/2017 | 30/8/2017 | 916 | 103968 | 30-34 | M | 20 | 26 | 23 | 14669 | 7 | 10.28000021 | 1 | 1 |
| 709105 | 30/8/2017 | 30/8/2017 | 916 | 103976 | 30-34 | M | 28 | 30 | 32 | 1241 | 0 | 0 | 1 | 1 |
| 709115 | 30/8/2017 | 30/8/2017 | 916 | 103978 | 30-34 | M | 30 | 34 | 31 | 2305 | 1 | 0.569999993 | 1 | 0 |
| 709124 | 30/8/2017 | 30/8/2017 | 916 | 103979 | 30-34 | M | 31 | 34 | 33 | 1024 | 0 | 0 | 1 | 1 |
| 709179 | 30/8/2017 | 30/8/2017 | 916 | 103988 | 35-39 | M | 15 | 16 | 17 | 4627 | 1 | 1.690000057 | 1 | 0 |
| 709183 | 30/8/2017 | 30/8/2017 | 916 | 103989 | 35-39 | M | 16 | 20 | 22 | 21026 | 4 | 4.630000114 | 2 | 1 |
| 709320 | 17/8/2017 | 17/8/2017 | 916 | 104012 | 35-39 | M | 15 | 21 | 20 | 1422 | 0 | 0 | 1 | 1 |
| 709323 | 17/8/2017 | 17/8/2017 | 916 | 104012 | 35-39 | M | 15 | 17 | 19 | 7132 | 2 | 2.609999895 | 1 | 0 |
| 709326 | 18/8/2017 | 18/8/2017 | 916 | 104013 | 35-39 | M | 16 | 22 | 22 | 12190 | 2 | 3.049999952 | 1 | 0 |
| 709327 | 18/8/2017 | 18/8/2017 | 916 | 104013 | 35-39 | M | 16 | 22 | 20 | 12193 | 2 | 3.059999943 | 1 | 1 |
| 709328 | 18/8/2017 | 18/8/2017 | 916 | 104013 | 35-39 | M | 16 | 19 | 18 | 3332 | 0 | 0 | 1 | 1 |
Extrait - Twitter Airline Sentiment
| tweet_id | airline_sentiment | airline_sentiment_confidence | negativereason | negativereason_confidence | airline | airline_sentiment_gold | name | negativereason_gold | retweet_count | text | tweet_coord | tweet_created | tweet_location | user_timezone |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 5.70306E+17 | neutral | 1 | Virgin America | cairdin | 0 | @VirginAmerica What @dhepburn said. | 24/2/2015 11:35 | Eastern Time (US & Canada) | ||||||
| 5.70301E+17 | positive | 0.3486 | 0 | Virgin America | jnardino | 0 | @VirginAmerica plus you've added commercials to the experience... tacky. | 24/2/2015 11:15 | Pacific Time (US & Canada) | |||||
| 5.70301E+17 | neutral | 0.6837 | Virgin America | yvonnalynn | 0 | @VirginAmerica I didn't today... Must mean I need to take another trip! | 24/2/2015 11:15 | Lets Play | Central Time (US & Canada) | |||||
| 5.70301E+17 | negative | 1 | Bad Flight | 0.7033 | Virgin America | jnardino | 0 | @VirginAmerica it's really aggressive to blast obnoxious "entertainment" in your guests' faces & they have little recourse | 24/2/2015 11:15 | Pacific Time (US & Canada) | ||||
| 5.70301E+17 | negative | 1 | Can't Tell | 1 | Virgin America | jnardino | 0 | @VirginAmerica and it's a really big bad thing about it | 24/2/2015 11:14 | Pacific Time (US & Canada) | ||||
| 5.70301E+17 | negative | 1 | Can't Tell | 0.6842 | Virgin America | jnardino | 0 | @VirginAmerica seriously would pay $30 a flight for seats that didn't have this playing. it's really the only bad thing about flying VA | 24/2/2015 11:14 | Pacific Time (US & Canada) | ||||
| 5.70301E+17 | positive | 0.6745 | 0 | Virgin America | cjmcginnis | 0 | @VirginAmerica yes, nearly every time I fly VX this “ear worm†won’t go away 🙂 | 24/2/2015 11:13 | San Francisco CA | Pacific Time (US & Canada) | ||||
| 5.703E+17 | neutral | 0.634 | Virgin America | pilot | 0 | @VirginAmerica Really missed a prime opportunity for Men Without Hats parody, there. https://t.co/mWpG7grEZP | 24/2/2015 11:12 | Los Angeles | Pacific Time (US & Canada) | |||||
| 5.703E+17 | positive | 0.6559 | Virgin America | dhepburn | 0 | @virginamerica Well, I didn't…but NOW I DO! 😀 | 24/2/2015 11:11 | San Diego | Pacific Time (US & Canada) | |||||
| 5.70295E+17 | positive | 1 | Virgin America | YupitsTate | 0 | @VirginAmerica it was amazing, and arrived an hour early. You're too good to me. | 24/2/2015 10:53 | Los Angeles | Eastern Time (US & Canada) | |||||
| 5.70294E+17 | neutral | 0.6769 | 0 | Virgin America | idk_but_youtube | 0 | @VirginAmerica did you know that suicide is the second leading cause of death among teens 10-24 | 24/2/2015 10:48 | 1/1 loner squad | Eastern Time (US & Canada) | ||||
| 5.7029E+17 | positive | 1 | Virgin America | HyperCamiLax | 0 | @VirginAmerica I <3 pretty graphics. so much better than minimal iconography. 😀 | 24/2/2015 10:30 | NYC | America/New_York | |||||
| 5.7029E+17 | positive | 1 | Virgin America | HyperCamiLax | 0 | @VirginAmerica This is such a great deal! Already thinking about my 2nd trip to @Australia & I haven't even gone on my 1st trip yet! ;p | 24/2/2015 10:30 | NYC | America/New_York | |||||
| 5.70287E+17 | positive | 0.6451 | Virgin America | mollanderson | 0 | @VirginAmerica @virginmedia I'm flying your #fabulous #Seductive skies again! U take all the #stress away from travel http://t.co/ahlXHhKiyn | 24/2/2015 10:21 | Eastern Time (US & Canada) | ||||||
| 5.70286E+17 | positive | 1 | Virgin America | sjespers | 0 | @VirginAmerica Thanks! | 24/2/2015 10:15 | San Francisco, CA | Pacific Time (US & Canada) | |||||
| 5.70282E+17 | negative | 0.6842 | Late Flight | 0.3684 | Virgin America | smartwatermelon | 0 | @VirginAmerica SFO-PDX schedule is still MIA. | 24/2/2015 10:01 | palo alto, ca | Pacific Time (US & Canada) | |||
| 5.70278E+17 | positive | 1 | Virgin America | ItzBrianHunty | 0 | @VirginAmerica So excited for my first cross country flight LAX to MCO I've heard nothing but great things about Virgin America. #29DaysToGo | 24/2/2015 09:42 | west covina | Pacific Time (US & Canada) | |||||
| 5.70277E+17 | negative | 1 | Bad Flight | 1 | Virgin America | heatherovieda | 0 | @VirginAmerica I flew from NYC to SFO last week and couldn't fully sit in my seat due to two large gentleman on either side of me. HELP! | 24/2/2015 09:39 | this place called NYC | Eastern Time (US & Canada) | |||
| 5.70271E+17 | positive | 1 | Virgin America | thebrandiray | 0 | I â¤ï¸ flying @VirginAmerica. ☺ï¸ðŸ‘ | 24/2/2015 09:15 | Somewhere celebrating life. | Atlantic Time (Canada) | |||||
| 5.70268E+17 | positive | 1 | Virgin America | JNLpierce | 0 | @VirginAmerica you know what would be amazingly awesome? BOS-FLL PLEASE!!!!!!! I want to fly with only you. | 24/2/2015 09:04 | Boston | Waltham | Quito | |||||
| 5.70266E+17 | negative | 0.6705 | Can't Tell | 0.3614 | Virgin America | MISSGJ | 0 | @VirginAmerica why are your first fares in May over three times more than other carriers when all seats are available to select??? | 24/2/2015 08:55 | |||||
| 5.70264E+17 | positive | 1 | Virgin America | DT_Les | 0 | @VirginAmerica I love this graphic. http://t.co/UT5GrRwAaA | [40.74804263, -73.99295302] | 24/2/2015 08:49 | ||||||
| 5.70259E+17 | positive | 1 | Virgin America | ElvinaBeck | 0 | @VirginAmerica I love the hipster innovation. You are a feel good brand. | 24/2/2015 08:30 | Los Angeles | Pacific Time (US & Canada) | |||||
| 5.70259E+17 | neutral | 1 | Virgin America | rjlynch21086 | 0 | @VirginAmerica will you be making BOS>LAS non stop permanently anytime soon? | 24/2/2015 08:27 | Boston, MA | Eastern Time (US & Canada) |
- Création du Tableau de Bord de la Campagne Publicitaire (Facebook)
- Analyse de Sentiment des Compagnies Aériennes (Twitter)
- Manipulation des données géographiques
Tableau n’a pas réussi à automatiquement générer des valeurs pour les coordonnées en Latitude et Longitude. De ce fait, nous devons remédier à cela directement à la source.
Il faudra à la fois :
- Gérer les valeurs nulles avec un IF Statement
- Récupérer la Latitude avec un Left
- Récupérer la Longitude avec un Mid
Extrait du dataset modifié
| tweet_coord | latitude | longitude |
|---|---|---|
| [40.74804263, -73.99295302] | 40.74804263 | -73.99295302 |
| [42.361016, -71.02000488] | 42.361016 | -71.02000488 |
| [33.94540417, -118.4062472] | 33.94540417 | -118.4062472 |
| [33.94209449, -118.40410103] | 33.94209449 | -118.404101 |
| [33.2145038, -96.9321504] | 33.2145038 | -96.9321504 |
- Création du Graphique à Bulles des avis négatifs
- Création de la Géovisualisation des avis négatifs
- Création du Tableau de Bord récapitulatif de l'Analyse de Sentiment
