📱 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)

📊 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 (section 2)
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

1. Ajoute une colonne Nom du forfait (colonne G) : va chercher, pour chaque client, le nom de son forfait dans l'onglet forfaits.
2. Ajoute une colonne Prix mensuel (FCFA) (colonne H) : va chercher le prix du forfait de chaque client.
3. Sous le tableau (à partir de la ligne 23), calcule ces indicateurs de synthèse : nombre total de clients, clients actifs, clients inactifs, taux d'inactivité, CA mensuel estimé (somme des prix mensuels).

=RECHERCHEV(F2; forfaits!$A:$B; 2; 0)   → Nom du forfait
=RECHERCHEV(F2; forfaits!$A:$C; 3; 0)   → Prix mensuel

É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. Insère un graphique en barres depuis ce TCD

À toi de lire, dans ton TCD, quel forfait rassemble le plus de clients et lequel génère le plus de chiffre d'affaires — ce n'est pas forcément le même.

🗄️ Partie 2 — SQL

Crée une base SQLite avec les 3 tables, puis réponds aux questions. Pour chaque question : essaie d'écrire la requête toi-même avant d'ouvrir la correction.

Mise en place

sqlCREATE 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)
);

Insère ensuite les lignes des 3 tables avec des INSERT INTO (à partir des données de la section 2).

Question 1 — Liste des clients actifs avec leur forfait

Affiche : nom du client, ville, type de contrat, nom du forfait, prix. Trie du forfait le plus cher au moins cher.

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;

Question 2 — Chiffre d'affaires par mois

Pour chaque mois : le CA total et le nombre de factures.

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

Question 3 — Forfait le plus populaire

Compte le nombre de clients par forfait, du plus choisi au moins choisi.

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;

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

Les 5 clients qui ont généré le plus de chiffre d'affaires sur la période.

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;

Question 5 — Clients inactifs à relancer

Liste les clients inactifs avec leur forfait et le manque à gagner mensuel (le prix de leur forfait).

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;

Question 6 — Consommation moyenne de data par forfait

Pour chaque forfait : la data incluse, la data moyenne réellement consommée, et le taux d'utilisation (%).

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;

🐍 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)}")

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()}")
print(f"Clients inactifs  : {(clients['statut']=='Inactif').sum()}")
print(f"Taux inactivité   : {(clients['statut']=='Inactif').mean():.0%}")
print(f"CA mensuel total  : {df['prix_fcfa'].sum():,} FCFA")
print(f"CA moyen/client   : {df['prix_fcfa'].mean():,.0f} FCFA")

# 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)

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")
cat("Clients inactifs :", sum(clients$statut == 'Inactif'), "\n")
cat("CA mensuel       :", sum(df$prix_fcfa), "FCFA\n")

# CA par forfait
df %>%
  group_by(nom) %>%
  summarise(nb_clients = n(), ca_mensuel = sum(prix_fcfa)) %>%
  arrange(desc(ca_mensuel))

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

✅ Auto-évaluation

Pas besoin d'un corrigé chiffré : ton travail est juste si les 4 outils (Excel, SQL, Python, R) donnent exactement les mêmes résultats. C'est le meilleur test de cohérence qui soit — et c'est aussi comme ça qu'on vérifie une analyse dans la vraie vie.

Vérifie que tu sais répondre, tes chiffres à l'appui, à ces questions :

• Combien de clients actifs / inactifs ? Quel taux d'inactivité ?
• Quel est le CA mensuel total ? Et le CA sur les 2 mois ?
• Quel forfait rassemble le plus de clients ? Lequel génère le plus de CA ? (ce n'est pas le même — c'est tout l'intérêt de l'analyse !)
• Quelle ville concentre le plus de clients ?
• Quel est le manque à gagner mensuel des clients inactifs ?
• Quels forfaits sont les mieux « rentabilisés » par les clients (taux d'utilisation de la data) ?

💡 Si Excel, SQL, Python et R ne tombent pas sur les mêmes nombres, cherche l'erreur : c'est presque toujours une donnée mal saisie ou une jointure incomplète.

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