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.
Analyze Digital Marketing Spend in Tableau
Liens
Compétences & Outils
Description du projet
Visualiser les KPIs Marketing suivants :
- Les Ventes par Région
- Le Coût d’Acquisition Client
- Le Taux de Conversion
- La Valeur Vie Client
Objectifs de l'analyse
- Analyser et Présenter les KPIs Marketing sous forme de Dashboard Interactifs
- Découverte du dataset
Extrait - Superstore
| Row ID | Order ID | Order Date | Ship Date | Ship Mode | Customer ID | Customer Name | Segment | Country | City | State | Postal Code | Region | Product ID | Category | Sub-Category | Product Name | Sales | Quantity | Discount | Profit |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | CA-2016-152156 | 8/11/2016 | 11/11/2016 | Second Class | CG-12520 | Claire Gute | Consumer | United States | Henderson | Kentucky | 42420 | South | FUR-BO-10001798 | Furniture | Bookcases | Bush Somerset Collection Bookcase | 261.96 | 2 | 0 | 41.9136 |
| 2 | CA-2016-152156 | 8/11/2016 | 11/11/2016 | Second Class | CG-12520 | Claire Gute | Consumer | United States | Henderson | Kentucky | 42420 | South | FUR-CH-10000454 | Furniture | Chairs | Hon Deluxe Fabric Upholstered Stacking Chairs, Rounded Back | 731.94 | 3 | 0 | 219.582 |
| 3 | CA-2016-138688 | 12/6/2016 | 16/6/2016 | Second Class | DV-13045 | Darrin Van Huff | Corporate | United States | Los Angeles | California | 90036 | West | OFF-LA-10000240 | Office Supplies | Labels | Self-Adhesive Address Labels for Typewriters by Universal | 14.62 | 2 | 0 | 6.8714 |
| 4 | US-2015-108966 | 11/10/2015 | 18/10/2015 | Standard Class | SO-20335 | Sean O'Donnell | Consumer | United States | Fort Lauderdale | Florida | 33311 | South | FUR-TA-10000577 | Furniture | Tables | Bretford CR4500 Series Slim Rectangular Table | 957.5775 | 5 | 0.45 | -383.031 |
| 5 | US-2015-108966 | 11/10/2015 | 18/10/2015 | Standard Class | SO-20335 | Sean O'Donnell | Consumer | United States | Fort Lauderdale | Florida | 33311 | South | OFF-ST-10000760 | Office Supplies | Storage | Eldon Fold 'N Roll Cart System | 22.368 | 2 | 0.2 | 2.5164 |
| 6 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | FUR-FU-10001487 | Furniture | Furnishings | Eldon Expressions Wood and Plastic Desk Accessories, Cherry Wood | 48.86 | 7 | 0 | 14.1694 |
| 7 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | OFF-AR-10002833 | Office Supplies | Art | Newell 322 | 7.28 | 4 | 0 | 1.9656 |
| 8 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | TEC-PH-10002275 | Technology | Phones | Mitel 5320 IP Phone VoIP phone | 907.152 | 6 | 0.2 | 90.7152 |
| 9 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | OFF-BI-10003910 | Office Supplies | Binders | DXL Angle-View Binders with Locking Rings by Samsill | 18.504 | 3 | 0.2 | 5.7825 |
| 10 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | OFF-AP-10002892 | Office Supplies | Appliances | Belkin F5C206VTEL 6 Outlet Surge | 114.9 | 5 | 0 | 34.47 |
| 11 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | FUR-TA-10001539 | Furniture | Tables | Chromcraft Rectangular Conference Tables | 1706.184 | 9 | 0.2 | 85.3092 |
| 12 | CA-2014-115812 | 9/6/2014 | 14/6/2014 | Standard Class | BH-11710 | Brosina Hoffman | Consumer | United States | Los Angeles | California | 90032 | West | TEC-PH-10002033 | Technology | Phones | Konftel 250 Conference phone - Charcoal black | 911.424 | 4 | 0.2 | 68.3568 |
| 13 | CA-2017-114412 | 15/4/2017 | 20/4/2017 | Standard Class | AA-10480 | Andrew Allen | Consumer | United States | Concord | North Carolina | 28027 | South | OFF-PA-10002365 | Office Supplies | Paper | Xerox 1967 | 15.552 | 3 | 0.2 | 5.4432 |
| 14 | CA-2016-161389 | 5/12/2016 | 10/12/2016 | Standard Class | IM-15070 | Irene Maddox | Consumer | United States | Seattle | Washington | 98103 | West | OFF-BI-10003656 | Office Supplies | Binders | Fellowes PB200 Plastic Comb Binding Machine | 407.976 | 3 | 0.2 | 132.5922 |
| 15 | US-2015-118983 | 22/11/2015 | 26/11/2015 | Standard Class | HP-14815 | Harold Pawlan | Home Office | United States | Fort Worth | Texas | 76106 | Central | OFF-AP-10002311 | Office Supplies | Appliances | Holmes Replacement Filter for HEPA Air Cleaner, Very Large Room, HEPA Filter | 68.81 | 5 | 0.8 | -123.858 |
| 16 | US-2015-118983 | 22/11/2015 | 26/11/2015 | Standard Class | HP-14815 | Harold Pawlan | Home Office | United States | Fort Worth | Texas | 76106 | Central | OFF-BI-10000756 | Office Supplies | Binders | Storex DuraTech Recycled Plastic Frosted Binders | 2.544 | 3 | 0.8 | -3.816 |
| 17 | CA-2014-105893 | 11/11/2014 | 18/11/2014 | Standard Class | PK-19075 | Pete Kriz | Consumer | United States | Madison | Wisconsin | 53711 | Central | OFF-ST-10004186 | Office Supplies | Storage | Stur-D-Stor Shelving, Vertical 5-Shelf: 72"H x 36"W x 18 1/2"D | 665.88 | 6 | 0 | 13.3176 |
| 18 | CA-2014-167164 | 13/5/2014 | 15/5/2014 | Second Class | AG-10270 | Alejandro Grove | Consumer | United States | West Jordan | Utah | 84084 | West | OFF-ST-10000107 | Office Supplies | Storage | Fellowes Super Stor/Drawer | 55.5 | 2 | 0 | 9.99 |
| 19 | CA-2014-143336 | 27/8/2014 | 1/9/2014 | Second Class | ZD-21925 | Zuschuss Donatelli | Consumer | United States | San Francisco | California | 94109 | West | OFF-AR-10003056 | Office Supplies | Art | Newell 341 | 8.56 | 2 | 0 | 2.4824 |
| 20 | CA-2014-143336 | 27/8/2014 | 1/9/2014 | Second Class | ZD-21925 | Zuschuss Donatelli | Consumer | United States | San Francisco | California | 94109 | West | TEC-PH-10001949 | Technology | Phones | Cisco SPA 501G IP Phone | 213.48 | 3 | 0.2 | 16.011 |
| 21 | CA-2014-143336 | 27/8/2014 | 1/9/2014 | Second Class | ZD-21925 | Zuschuss Donatelli | Consumer | United States | San Francisco | California | 94109 | West | OFF-BI-10002215 | Office Supplies | Binders | Wilson Jones Hanging View Binder, White, 1" | 22.72 | 4 | 0.2 | 7.384 |
| 22 | CA-2016-137330 | 9/12/2016 | 13/12/2016 | Standard Class | KB-16585 | Ken Black | Corporate | United States | Fremont | Nebraska | 68025 | Central | OFF-AR-10000246 | Office Supplies | Art | Newell 318 | 19.46 | 7 | 0 | 5.0596 |
| 23 | CA-2016-137330 | 9/12/2016 | 13/12/2016 | Standard Class | KB-16585 | Ken Black | Corporate | United States | Fremont | Nebraska | 68025 | Central | OFF-AP-10001492 | Office Supplies | Appliances | Acco Six-Outlet Power Strip, 4' Cord Length | 60.34 | 7 | 0 | 15.6884 |
| 24 | US-2017-156909 | 16/7/2017 | 18/7/2017 | Second Class | SF-20065 | Sandra Flanagan | Consumer | United States | Philadelphia | Pennsylvania | 19140 | East | FUR-CH-10002774 | Furniture | Chairs | Global Deluxe Stacking Chair, Gray | 71.372 | 2 | 0.3 | -1.0196 |
| 25 | CA-2015-106320 | 25/9/2015 | 30/9/2015 | Standard Class | EB-13870 | Emily Burns | Consumer | United States | Orem | Utah | 84057 | West | FUR-TA-10000577 | Furniture | Tables | Bretford CR4500 Series Slim Rectangular Table | 1044.63 | 3 | 0 | 240.2649 |
- Calcul des Données
- Ventes par Région
- Coût d'Acquisition Client
Pour les besoins de l’analyse, il faudra au préalable calculer certaines données non fournies.
On commence par calculer le Nombre Total de Clients Uniques
On peut maintenant calculer le Coût d’Acquisition Client (avec 10000 comme donnée fournie)
- Taux de Conversion
On peut ensuite calculer le Taux de Conversion (avec 1000000 comme donnée fournie)
- La Valeur Vie Client
Pour calculer la Valeur Vie Client, il faudra tout d’abord calculer :
- La Valeur Moyenne d’Achat
- La Fréquence d’Achat
- La Durée de Vie Moyenne du Client
On peut ensuite calculer la Valeur Moyenne d’Achat via la formule :
On peut ensuite calculer la Fréquence d’Achat via la formule :
La Durée de Vie Moyenne du Client est une donnée fournie égale à 1.5
On peut enfin calculer la Valeur Vie Client via la formule :
- Création des Visuels et du Dashboard
