📱 Mini-Projet B — Tableau de bord Télécom MobiCI

Acte IV — Projet intégrateur
Niveau : Débutant · Durée estimée : 4h00
Outils : Excel · SQL · Python · R

---

---

🌍 Contexte — MobiCI, Abidjan

MobiCI est un opérateur mobile ivoirien fictif basé à Abidjan. Tu es recruté(e) comme Data Analyst junior pour analyser la base clients et le chiffre d'affaires.

Le Directeur Commercial, M. Coulibaly, te remet le brief suivant :

"On a 20 clients sur notre dataset de test. J'ai besoin de comprendre : qui sont nos clients ? Quels forfaits se vendent le mieux ? Quel est notre CA ? Y a-t-il des clients inactifs à relancer ? Donne-moi un tableau de bord complet."

Tu vas répondre à ces questions avec Excel, SQL, Python et R.

---

---

📂 2. Les données — 3 tables

Table 1 — clients (20 lignes)

---

Table 2 — forfaits (5 lignes)

---

Table 3 — consommations (40 lignes — 2 mois × 20 clients)

CA total : 150 000 FCFA/mois × 2 = 300 000 FCFA sur la période

---

---

📊 Partie 1 — Excel / Google Sheets

Objectif

Créer un fichier Excel propre avec les 3 tables et calculer les premiers indicateurs.

Étape 1 — Mise en forme

1. Crée 3 onglets : clients, forfaits, consommations
2. Saisis les données de chaque table
3. Applique un style tableau (Insertion → Tableau) sur chaque onglet
4. En-têtes en gras, colonnes ID en bleu clair

Étape 2 — Formules sur l'onglet clients

Ajoute une colonne Nom du forfait (colonne G) en utilisant RECHERCHEV :

=RECHERCHEV(F2; forfaits!$A:$B; 2; 0)

Ajoute une colonne Prix mensuel (FCFA) (colonne H) :

=RECHERCHEV(F2; forfaits!$A:$C; 3; 0)

Ajoute des indicateurs synthèse (sous le tableau, à partir de la ligne 23) :

Étape 3 — Tableau croisé dynamique

1. Depuis l'onglet clients, insère un TCD sur un nouvel onglet Analyse
2. Lignes : Nom du forfait
3. Valeurs : Nombre de clients + Prix mensuel (Somme)
4. Résultat attendu :

5. Insère un graphique en barres depuis ce TCD

---

---

🗄️ Partie 2 — SQL

Crée une base SQLite avec les 3 tables et réponds aux questions suivantes.

Mise en place

sql-- Créer les tables
CREATE TABLE forfaits (
    forfait_id INTEGER PRIMARY KEY,
    nom        TEXT,
    prix_fcfa  INTEGER,
    data_go    INTEGER,
    minutes    INTEGER
);

CREATE TABLE clients (
    client_id   INTEGER PRIMARY KEY,
    nom         TEXT,
    ville       TEXT,
    type_client TEXT,
    forfait_id  INTEGER,
    statut      TEXT,
    FOREIGN KEY (forfait_id) REFERENCES forfaits(forfait_id)
);

CREATE TABLE consommations (
    conso_id      INTEGER PRIMARY KEY,
    client_id     INTEGER,
    mois          TEXT,
    data_go_conso REAL,
    minutes_conso INTEGER,
    montant_fcfa  INTEGER,
    FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

Question 1 — Liste des clients actifs avec leur forfait

Affiche : nom du client, ville, type de contrat, nom du forfait, prix.

sqlSELECT
    c.nom          AS client,
    c.ville,
    c.type_client,
    f.nom          AS forfait,
    f.prix_fcfa
FROM clients c
JOIN forfaits f ON c.forfait_id = f.forfait_id
WHERE c.statut = 'Actif'
ORDER BY f.prix_fcfa DESC;
-- 17 lignes (3 inactifs exclus)

Question 2 — Chiffre d'affaires par mois

sqlSELECT
    mois,
    SUM(montant_fcfa)  AS ca_total,
    COUNT(*)           AS nb_factures
FROM consommations
GROUP BY mois
ORDER BY mois;

--   mois    | ca_total | nb_factures
-- 2024-01   | 150 000  | 20
-- 2024-02   | 150 000  | 20

Question 3 — Forfait le plus populaire

sqlSELECT
    f.nom          AS forfait,
    f.prix_fcfa,
    COUNT(c.client_id) AS nb_clients
FROM forfaits f
JOIN clients c ON f.forfait_id = c.forfait_id
GROUP BY f.forfait_id, f.nom, f.prix_fcfa
ORDER BY nb_clients DESC;

--  forfait  | prix_fcfa | nb_clients
--  Starter  |     1 000 |          5
--  Basic    |     2 500 |          4
--  Premium  |    10 000 |          4
--  Illimité |    20 000 |          4
--  Standard |     5 000 |          3

Question 4 — Top 5 clients par CA total (2 mois)

sqlSELECT
    c.nom      AS client,
    c.ville,
    f.nom      AS forfait,
    SUM(co.montant_fcfa) AS ca_total
FROM consommations co
JOIN clients  c ON co.client_id  = c.client_id
JOIN forfaits f ON c.forfait_id  = f.forfait_id
GROUP BY c.client_id, c.nom, c.ville, f.nom
ORDER BY ca_total DESC
LIMIT 5;

--  client            | ville   | forfait  | ca_total
--  Bamba Salimata    | Abidjan | Illimité |   40 000
--  Coulibaly Assata  | Abidjan | Illimité |   40 000
--  Fofana Raïssa     | Abidjan | Illimité |   40 000
--  Djedje Olivia     | Abidjan | Illimité |   40 000
--  Diabaté Fatou     | Abidjan | Premium  |   20 000

Question 5 — Clients inactifs à relancer

sqlSELECT
    c.nom,
    c.ville,
    c.type_client,
    f.nom       AS forfait,
    f.prix_fcfa AS manque_a_gagner_par_mois
FROM clients c
JOIN forfaits f ON c.forfait_id = f.forfait_id
WHERE c.statut = 'Inactif'
ORDER BY f.prix_fcfa DESC;

--  nom              | ville        | type_client | forfait | manque_a_gagner
--  Aka Brice        | Man          | Postpayé    | Premium |          10 000
--  Kouamé Jean      | Yamoussoukro | Prépayé     | Basic   |           2 500
--  Diallo Mamadou   | Korhogo      | Prépayé     | Starter |           1 000
-- Manque à gagner total : 13 500 FCFA/mois si réactivés

Question 6 — Consommation moyenne de data par forfait

sqlSELECT
    f.nom                         AS forfait,
    f.data_go                     AS data_incluse,
    ROUND(AVG(co.data_go_conso), 1) AS data_moyenne_consommee,
    ROUND(AVG(co.data_go_conso) * 100.0 / f.data_go, 0) AS taux_utilisation_pct
FROM consommations co
JOIN clients  c ON co.client_id = c.client_id
JOIN forfaits f ON c.forfait_id = f.forfait_id
GROUP BY f.forfait_id, f.nom, f.data_go
ORDER BY f.prix_fcfa;

--  forfait  | data_incluse | data_moy | taux_%
--  Starter  |            1 |      0.6 |     60
--  Basic    |            3 |      2.1 |     71
--  Standard |            8 |      6.6 |     83
--  Premium  |           20 |     14.1 |     71
--  Illimité |           50 |     41.0 |     82

---

---

🐍 Partie 3 — Python

3.1 Charger les données

pythonimport pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_theme(style="whitegrid")

# Table clients
clients = pd.DataFrame({
    'client_id':   list(range(1, 21)),
    'nom':         ['Kouassi Ama','Traoré Seydou','Diabaté Fatou','Kouamé Jean',
                    'Bamba Salimata','Ouédraogo Moussa','Soro Aminata','Koné Ibrahim',
                    'Yao Adjoua','Diallo Mamadou','Coulibaly Assata',"N'Guessan Éric",
                    'Touré Mariam','Keita Souleymane','Fofana Raïssa','Gbagbo Kossia',
                    'Konaté Drissa','Zoro Ange','Aka Brice','Djedje Olivia'],
    'ville':       ['Abidjan','Bouaké','Abidjan','Yamoussoukro','Abidjan','Abidjan',
                    'San-Pédro','Abidjan','Abidjan','Korhogo','Abidjan','Abidjan',
                    'Abidjan','Bouaké','Abidjan','Daloa','Abidjan','Abidjan','Man','Abidjan'],
    'type_client': ['Postpayé','Prépayé','Postpayé','Prépayé','Postpayé','Prépayé',
                    'Postpayé','Postpayé','Prépayé','Prépayé','Postpayé','Postpayé',
                    'Prépayé','Prépayé','Postpayé','Prépayé','Postpayé','Prépayé',
                    'Postpayé','Postpayé'],
    'forfait_id':  [3,1,4,2,5,1,3,4,2,1,5,4,2,1,5,2,3,1,4,5],
    'statut':      ['Actif','Actif','Actif','Inactif','Actif','Actif','Actif','Actif',
                    'Actif','Inactif','Actif','Actif','Actif','Actif','Actif','Actif',
                    'Actif','Actif','Inactif','Actif']
})

# Table forfaits
forfaits = pd.DataFrame({
    'forfait_id': [1,2,3,4,5],
    'nom':        ['Starter','Basic','Standard','Premium','Illimité'],
    'prix_fcfa':  [1000, 2500, 5000, 10000, 20000],
    'data_go':    [1, 3, 8, 20, 50]
})

# Table consommations
import io
conso_csv = """conso_id,client_id,mois,data_go_conso,minutes_conso,montant_fcfa
1,1,2024-01,6.2,98,5000\n2,2,2024-01,0.8,25,1000\n3,3,2024-01,15.5,245,10000
4,4,2024-01,1.2,35,2500\n5,5,2024-01,38.0,410,20000\n6,6,2024-01,0.6,20,1000
7,7,2024-01,7.1,115,5000\n8,8,2024-01,18.2,280,10000\n9,9,2024-01,2.5,55,2500
10,10,2024-01,0.3,12,1000\n11,11,2024-01,45.0,520,20000\n12,12,2024-01,12.0,200,10000
13,13,2024-01,2.8,58,2500\n14,14,2024-01,0.9,28,1000\n15,15,2024-01,42.0,480,20000
16,16,2024-01,1.8,45,2500\n17,17,2024-01,5.5,105,5000\n18,18,2024-01,0.7,22,1000
19,19,2024-01,8.5,150,10000\n20,20,2024-01,35.0,395,20000
21,1,2024-02,7.8,110,5000\n22,2,2024-02,0.5,18,1000\n23,3,2024-02,19.0,290,10000
24,4,2024-02,0.9,22,2500\n25,5,2024-02,41.0,450,20000\n26,6,2024-02,0.4,15,1000
27,7,2024-02,6.8,108,5000\n28,8,2024-02,16.5,260,10000\n29,9,2024-02,2.1,50,2500
30,10,2024-02,0.2,8,1000\n31,11,2024-02,48.0,540,20000\n32,12,2024-02,14.5,220,10000
33,13,2024-02,2.2,48,2500\n34,14,2024-02,0.8,24,1000\n35,15,2024-02,39.0,460,20000
36,16,2024-02,2.0,52,2500\n37,17,2024-02,6.0,112,5000\n38,18,2024-02,0.9,26,1000
39,19,2024-02,9.5,170,10000\n40,20,2024-02,37.0,420,20000"""
consommations = pd.read_csv(io.StringIO(conso_csv))

print(f"Clients : {len(clients)} | Forfaits : {len(forfaits)} | Consommations : {len(consommations)}")
# → Clients : 20 | Forfaits : 5 | Consommations : 40

3.2 Statistiques clés

python# Jointure clients + forfaits
df = clients.merge(forfaits, on='forfait_id')

# Indicateurs globaux
print(f"Clients actifs    : {(clients['statut']=='Actif').sum()}")       # 17
print(f"Clients inactifs  : {(clients['statut']=='Inactif').sum()}")     # 3
print(f"Taux inactivité   : {(clients['statut']=='Inactif').mean():.0%}") # 15%
print(f"CA mensuel total  : {df['prix_fcfa'].sum():,} FCFA")             # 150,000
print(f"CA moyen/client   : {df['prix_fcfa'].mean():,.0f} FCFA")         # 7,500

# CA par type de forfait
df.groupby('nom_y')['prix_fcfa'].agg(['count','sum']).rename(
    columns={'count':'nb_clients','sum':'ca_mensuel'}
).sort_values('ca_mensuel', ascending=False)
#           nb_clients  ca_mensuel
# Illimité           4      80 000
# Premium            4      40 000
# Standard           3      15 000
# Basic              4      10 000
# Starter            5       5 000

3.3 Visualisations

pythonfig, axes = plt.subplots(2, 2, figsize=(13, 10))
fig.suptitle('Tableau de bord MobiCI — Analyse clients', fontsize=15, fontweight='bold')

# 1. CA par forfait (barres)
ca_forfait = df.groupby('nom_y')['prix_fcfa'].sum().sort_values(ascending=False)
axes[0,0].bar(ca_forfait.index, ca_forfait.values,
              color=['#1565C0','#1976D2','#1E88E5','#42A5F5','#90CAF9'])
axes[0,0].set_title('CA mensuel par forfait (FCFA)')
axes[0,0].set_ylabel('FCFA')
for i, v in enumerate(ca_forfait): axes[0,0].text(i, v+500, f"{v:,}", ha='center', fontsize=8)

# 2. Répartition Postpayé / Prépayé (camembert)
types = clients['type_client'].value_counts()
axes[0,1].pie(types, labels=types.index, autopct='%1.0f%%',
              colors=['#1565C0','#90CAF9'], startangle=90)
axes[0,1].set_title('Répartition Postpayé / Prépayé')

# 3. Clients par ville (barres horizontales)
villes = clients['ville'].value_counts()
axes[1,0].barh(villes.index, villes.values, color='#1976D2')
axes[1,0].set_title('Nombre de clients par ville')
axes[1,0].set_xlabel('Clients')

# 4. Statut actif/inactif (barres)
statuts = clients['statut'].value_counts()
axes[1,1].bar(statuts.index, statuts.values, color=['#2E7D32','#C62828'])
axes[1,1].set_title('Statut des clients')
for i, v in enumerate(statuts): axes[1,1].text(i, v+0.1, str(v), ha='center', fontweight='bold')

plt.tight_layout()
plt.savefig('dashboard_mobici.png', dpi=150)
plt.show()

---

---

📈 Partie 4 — R

4.1 Charger et joindre les données

rlibrary(dplyr)
library(ggplot2)

clients <- data.frame(
  client_id   = 1:20,
  nom         = c('Kouassi Ama','Traoré Seydou','Diabaté Fatou','Kouamé Jean',
                  'Bamba Salimata','Ouédraogo Moussa','Soro Aminata','Koné Ibrahim',
                  'Yao Adjoua','Diallo Mamadou','Coulibaly Assata',"N'Guessan Éric",
                  'Touré Mariam','Keita Souleymane','Fofana Raïssa','Gbagbo Kossia',
                  'Konaté Drissa','Zoro Ange','Aka Brice','Djedje Olivia'),
  ville       = c('Abidjan','Bouaké','Abidjan','Yamoussoukro','Abidjan','Abidjan',
                  'San-Pédro','Abidjan','Abidjan','Korhogo','Abidjan','Abidjan',
                  'Abidjan','Bouaké','Abidjan','Daloa','Abidjan','Abidjan','Man','Abidjan'),
  type_client = c('Postpayé','Prépayé','Postpayé','Prépayé','Postpayé','Prépayé',
                  'Postpayé','Postpayé','Prépayé','Prépayé','Postpayé','Postpayé',
                  'Prépayé','Prépayé','Postpayé','Prépayé','Postpayé','Prépayé',
                  'Postpayé','Postpayé'),
  forfait_id  = c(3,1,4,2,5,1,3,4,2,1,5,4,2,1,5,2,3,1,4,5),
  statut      = c('Actif','Actif','Actif','Inactif','Actif','Actif','Actif','Actif',
                  'Actif','Inactif','Actif','Actif','Actif','Actif','Actif','Actif',
                  'Actif','Actif','Inactif','Actif')
)

forfaits <- data.frame(
  forfait_id = 1:5,
  nom        = c('Starter','Basic','Standard','Premium','Illimité'),
  prix_fcfa  = c(1000, 2500, 5000, 10000, 20000),
  data_go    = c(1, 3, 8, 20, 50)
)

# Jointure
df <- clients %>% left_join(forfaits, by = 'forfait_id')

4.2 Statistiques

r# Indicateurs globaux
cat("Clients actifs   :", sum(clients$statut == 'Actif'), "\n")     # 17
cat("Clients inactifs :", sum(clients$statut == 'Inactif'), "\n")   # 3
cat("CA mensuel       :", sum(df$prix_fcfa), "FCFA\n")              # 150 000

# CA par forfait
df %>%
  group_by(nom) %>%
  summarise(nb_clients = n(), ca_mensuel = sum(prix_fcfa)) %>%
  arrange(desc(ca_mensuel))
# Illimité : 4 clients, 80 000 FCFA
# Premium  : 4 clients, 40 000 FCFA
# Standard : 3 clients, 15 000 FCFA
# Basic    : 4 clients, 10 000 FCFA
# Starter  : 5 clients,  5 000 FCFA

4.3 Graphiques ggplot2

r# CA par forfait
df %>%
  group_by(nom) %>%
  summarise(ca = sum(prix_fcfa)) %>%
  mutate(nom = reorder(nom, -ca)) %>%
  ggplot(aes(x = nom, y = ca, fill = nom)) +
    geom_col() +
    geom_text(aes(label = scales::comma(ca)), vjust = -0.5, size = 3.5) +
    scale_fill_brewer(palette = 'Blues', direction = -1) +
    labs(title = 'CA mensuel par forfait — MobiCI',
         x = 'Forfait', y = 'CA (FCFA)') +
    theme_minimal() + theme(legend.position = 'none')

ggsave('ca_par_forfait.png', dpi = 150)

# Clients par ville
clients %>%
  count(ville) %>%
  arrange(desc(n)) %>%
  ggplot(aes(x = reorder(ville, n), y = n)) +
    geom_col(fill = '#1976D2') +
    geom_text(aes(label = n), hjust = -0.3) +
    coord_flip() +
    labs(title = 'Clients par ville — MobiCI',
         x = 'Ville', y = 'Nombre de clients') +
    theme_minimal()

ggsave('clients_par_ville.png', dpi = 150)

---

---

📦 Livrables attendus

---

---

✅ Corrigé — Indicateurs clés à vérifier

Chiffres de référence

Résultats SQL à vérifier

---

Bootcamp Data Analyst — From Zero to Hero | Niveau Débutant · Mini-Projet B