Étude de cas · Risque de portefeuille
J’ai utilisé BigQuery et Looker Enterprise pour valider 270 299 prêts, créer des tables analytiques réutilisables et examiner le rôle du statut, de la géographie, de la finalité et de l’année d’octroi dans l’analyse du risque.
Un cas de formation simulant une mission d’analyse en trésorerie. Mon travail couvrait la validation des sources, la modélisation SQL, un tableau de bord Looker et un cadre d’examen documenté.
Le montant calculé dépasse le seuil illustratif de 3,00 milliards USD. Les mesures de concentration et de statut orientent la suite de l’investigation.
Au-dessus du seuil interne illustratif de 3,00 Md USD.
Part du montant hors Fully Paid détenue par les cinq États les plus importants.
Montant initial des prêts de consolidation de dettes, hors Fully Paid.
Montant Charged Off ou Default ÷ total accordé aux petites entreprises.
Looker Enterprise
Le tableau de bord Looker relie les indicateurs aux États, cohortes d’octroi, finalités et emprunteurs fictifs. Ces captures montrent le rapport terminé ; l’explorateur ci-dessous utilise ses résultats agrégés validés.
L’image apparaîtra lorsque le fichier correspondant sera disponible sur le site.
Angles d’analyse du risque
La vue par statut utilise les montants initiaux plutôt que les soldes restants. Charged Off et Default sont inclus dans le dénominateur.
87,89 % du montant hors Fully Paid est classé Current. Les statuts défavorables restent visibles séparément ; leurs montants ne mesurent pas la perte financière nette.
Sélectionnez une ligne pour en comprendre le sens. Les barres comparent les montants ou les parts dans la vue choisie.
Source : exports CSV analytiques et vue Looker originale des statuts. Les montants utilisent les dollars des résultats de formation. Les parts sont arrondies. La priorité croise les quartiles de montant et de taux de classement en perte ; ce cadre est descriptif.
| Mesure | Valeur | Définition |
|---|---|---|
| Prêts enregistrés | 270 299 | Identifiants de prêts distincts dans les données de formation validées |
| Montant total accordé | 4 166 072 400 USD | Valeur initiale loan_amount pour tous les statuts |
| Montant hors Fully Paid | 3 080 553 000 USD | Valeur initiale loan_amount hors Fully Paid, avec Charged Off et Default |
| Montant classé en perte | 279 669 425 USD | Valeur initiale loan_amount pour Charged Off ou Default |
| Taux de classement en perte du portefeuille | 6,71 % | Montant classé en perte ÷ montant total accordé |
| Part des cinq premiers États | 41,81 % | Montants des cinq premiers États ÷ montant total hors Fully Paid |
| Juridictions représentées | 51 | Juridictions distinctes dans les prêts ; la table de correspondance contient 52 entrées |
| Statut du prêt | Part |
|---|---|
| En cours | 87,89 % |
| Passé en perte | 9,07 % |
| Retard de 31 à 120 jours | 1,72 % |
| Délai de grâce | 0,90 % |
| Retard de 16 à 30 jours | 0,42 % |
| Défaut | 0,01 % |
| État | Montant hors Fully Paid | Part | Taux de classement en perte | Priorité |
|---|---|---|---|---|
| Californie | 419 531 275 USD | 13,62 % | 6,93 % | Priorité 2 |
| Texas | 268 687 125 USD | 8,72 % | 6,63 % | À suivre |
| New York | 247 618 650 USD | 8,04 % | 7,44 % | Priorité 1 |
| Floride | 223 337 275 USD | 7,25 % | 7,06 % | Priorité 2 |
| Illinois | 128 796 600 USD | 4,18 % | 6,14 % | À suivre |
| New Jersey | 119 291 450 USD | 3,87 % | 7,63 % | Priorité 1 |
| Ohio | 102 053 125 USD | 3,31 % | 7,09 % | Priorité 2 |
| Géorgie | 101 972 625 USD | 3,31 % | 5,24 % | À suivre |
| Finalités | Montant hors Fully Paid | Taux de classement en perte |
|---|---|---|
| Consolidation de dettes | 1 832 403 625 USD | 7,26 % |
| Carte de crédit | 743 918 225 USD | 5,42 % |
| Travaux dans le logement | 194 797 125 USD | 6,32 % |
| Autres | 134 245 825 USD | 6,49 % |
| Achat important | 55 560 975 USD | 6,86 % |
| Petite entreprise | 33 691 500 USD | 9,31 % |
| Frais médicaux | 23 958 800 USD | 7,4 % |
| Logement | 21 889 925 USD | 4,46 % |
| Automobile | 18 165 525 USD | 5,39 % |
| Déménagement | 11 120 725 USD | 8,53 % |
| Vacances | 9 354 200 USD | 6,92 % |
| Énergies renouvelables | 1 213 675 USD | 6,56 % |
| Mariage | 232 875 USD | 8,59 % |
| Année d’octroi | Montant hors Fully Paid |
|---|---|
| 2012 | 8 011 000 USD |
| 2013 | 53 897 825 USD |
| 2014 | 180 481 175 USD |
| 2015 | 206 154 925 USD |
| 2016 | 566 835 950 USD |
| 2017 | 525 088 150 USD |
| 2018 | 743 363 125 USD |
| 2019 | 796 720 850 USD |
Méthode et qualité des données
J’ai conservé une ligne par prêt, documenté les statuts et créé des tables réutilisables avant de concevoir le tableau de bord. Chaque résultat principal peut être retracé dans le SQL et ses champs sources.
Examiner les types de données, les champs imbriqués des demandes et la couverture des tables sources.
Vérifier les lignes, les identifiants distincts, les valeurs nulles et les correspondances géographiques.
Créer des indicateurs de statut explicites et documenter les dénominateurs des montants.
Construire des tables réutilisables avec jointures, agrégations, LAG, NTILE et classements.
Relier les filtres Looker, le filtrage croisé et la mise en forme des seuils.
Éléments BigQuery
L’examen du schéma a établi les champs disponibles, la structure imbriquée des demandes et les types avant la rédaction des transformations.
L’image apparaîtra lorsque le fichier correspondant sera disponible sur le site.
Les fonctions de fenêtre permettent de comparer les cohortes et de définir des priorités descriptives. L’archive SQL comprend les contrôles sources, transformations et résultats de présentation.
CREATE OR REPLACE TABLE fintech.loan_risk_mart AS
SELECT
l.loan_id,
l.customer_id,
l.loan_status,
l.loan_amount,
l.state,
sr.subregion,
sr.region,
l.int_rate,
CAST(l.issue_year AS INT64) AS issue_year,
COALESCE(NULLIF(TRIM(l.application.purpose), ''), 'Unknown') AS purpose,
l.loan_status != 'Fully Paid' AS is_outstanding,
l.loan_status IN (
'Late (16-30 days)',
'Late (31-120 days)',
'In Grace Period'
) AS is_delinquent,
l.loan_status IN ('Charged Off', 'Default') AS is_loss
FROM fintech.loan AS l
LEFT JOIN fintech.state_region AS sr
ON l.state = sr.state;Constats et suivi proposé
Le résultat est un cadre d’examen documenté. Il s’agit d’étapes analytiques proposées, sans preuve de changements mis en œuvre par un prêteur.
Le montant par statut de 3,08 Md USD dépasse le seuil illustratif de 3,00 Md USD.
Utiliser les vues reliées de statut, d’État et de finalité pour expliquer le signal avant de proposer une réponse.
La Californie présente le plus grand montant ; New York et le New Jersey sont en priorité 1 dans le cadre combiné par quartiles.
Examiner la concentration des cinq premiers États avec les montants et les taux de classement en perte, plutôt que de classer les priorités par montant seul.
La consolidation de dettes est le plus grand segment ; les petites entreprises ont le taux de classement en perte le plus élevé, à 9,31 %.
Distinguer les questions de concentration et de statuts défavorables. Comparer des dénominateurs équivalents et la maturité des cohortes.
La cohorte 2019 contribue pour 796,72 M USD au montant hors Fully Paid dans cette observation.
Ajouter des observations répétées et des données de remboursement avant d’étudier les migrations, la dégradation ou les prévisions.
Périmètre et interprétation
Une analyse descriptive de données de formation, avec des définitions transparentes et une distinction claire entre constats et prolongements proposés.
Le jeu de données est pédagogique et ne représente pas le portefeuille réel d’un prêteur.
Ce n’est ni une limite réglementaire de capital ni une référence universelle du secteur.
La source fournit loan_amount, sans capital restant dû vérifié. Les montants Charged Off et Default ne tiennent pas compte des recouvrements.
Les cohortes 2012–2019 ont des âges différents. Des observations répétées et les remboursements sont nécessaires pour étudier migrations, évolution des pertes et prévisions.
Livrables du projet
Examinez les résultats exportés et le SQL avec le tableau de bord original. Les définitions de cette page précisent les termes utilisés dans les fichiers du projet.
Synthèse du portefeuille, cohortes, taux par finalité, priorités des États, statuts régionaux et détail d’emprunteurs fictifs.
SQL · Scripts BigQueryValidation des sources, correspondances géographiques, création de la table de risque, synthèses de direction et requêtes analytiques.
PDF · Synthèse de directionPrésentation originale, constats et recommandations. Le terme « outstanding » doit être lu selon la définition par statut expliquée ci-dessus.