Build a Consumer Complaint Dashboard

Turn CFPB complaint data into a Power BI operations dashboard.

Introduction

30 Second Summary

When a service team receives thousands of complaints, the loudest story can distract from the biggest pattern. Leaders need evidence that shows where closer investigation will have the most value.

In this project, you will build a decision-support dashboard that helps a fictional operations leader prioritize deeper reviews of public Virginia deposit-account complaints. A reproducible Python pipeline turns official records into evidence for an interactive Power BI report.

What You'll Build

You change a report filter and watch every relevant chart update to reveal the complaint issues that deserve the next operational review.

By the end of this project, you'll have:

A one-command refresh that produces every data file needed for the analysis from the official complaint extract.
A repeatable SQL analysis that prints executive evidence in four labeled PowerShell tables.
A recruiter-ready report package that you can filter live or share as a PDF. Your public GitHub repository will present the evidence trail.
Secret Mission: Add an issue-level drillthrough page that takes reviewers from a priority chart to the filtered complaint records behind it.

Are there any prerequisites?

You need a Windows computer that supports Power BI Desktop plus a GitHub account. The project walks you through installing every other tool.

Before We Start

A strong analysis starts with a clear decision question. This checkpoint commits you to using public complaint patterns for deeper review without turning company counts into rankings.

Set Up the Windows Analytics Toolkit

A reliable dashboard begins with a verified local toolkit on Windows. Unverified dependencies can stop the complaint workflow before the analysis begins.

You'll prepare Power BI Desktop, Python, Visual Studio Code, and Git for the project. You'll verify every command from a new PowerShell session.

In this step, get ready to:

Confirm that Windows meets the Power BI Desktop requirement.
Install the required Python runtime.
Prepare Visual Studio Code plus Git for Windows.

Confirm Windows and install Power BI Desktop

Power BI Desktop requires Windows 10 or newer. The winver utility shows the Windows release installed on your computer.

Press the Windows key to open Windows Search.
Type winver into the search field.
Press Enter to open the Windows version dialog.
Read the version shown in the dialog.

✔️ I see Windows 10 or newer

Your computer meets the operating-system requirement for the current Power BI Desktop release.

Open the official Power BI Desktop download page in your browser.
Select the Microsoft Store installation option.
Complete the installation from the Power BI Desktop Store page.
Press the Windows key to open Windows Search.
Type Power BI Desktop into the search field.
Press Enter to launch Power BI Desktop.
Close any startup dialog that covers the blank report canvas.

You should see a blank report canvas with the report-building panels around it. That canvas is ready to become the visual layer of your complaint analysis.

ⓧ I see an older version

This computer does not meet the current Power BI Desktop requirement. A supported Windows release is required before you continue.

Move to a computer running Windows 10 or newer.
Repeat the winver check on the supported computer.
Return to the success tab after the supported Windows version appears.

Power BI Desktop not opening?

Confirm that the Power BI Desktop installation finished before searching for the app.
Restart Windows if the completed Store installation does not appear in Windows Search.
Still stuck? Help me troubleshoot my Power BI Desktop installation on Windows.

Install and verify Python

Python runs the data pipeline that you build later. This project uses Python 3.15.0 so every learner starts from the same runtime.

Press the Windows key to open Windows Search.
Type PowerShell into the search field.
Press Enter to open PowerShell.
Check the available Python version by running this command:

python --version

What does this command check?

The check asks the active Python executable to report its version. The result reveals whether the required runtime is already available from this PowerShell session.

✔️ I see Python 3.15.0

Your Python runtime matches the project requirement. One of the most important setup checks is now complete.

ⓧ I see an older version

The existing Python runtime is older than the project requirement. The Python Install Manager provides the supported installation path for the required release.

Open the official Python downloads page in your browser.
Install the Python Install Manager from python.org or the Microsoft Store.
Use the Python Install Manager to install Python 3.15.0.
Close the current PowerShell window.
Press the Windows key to open Windows Search.
Type PowerShell into the search field.
Press Enter to open a new PowerShell session.
Confirm the updated runtime by running this command:

python --version

What does this command confirm?

The new PowerShell session reloads the command locations available to Windows. The version check confirms that the required Python installation now responds.

You should now see Python report version 3.15.0.

ⓧ Command not found

Windows cannot find a Python installation from the current PowerShell session. Installing the Python Install Manager gives Windows a supported path to the required runtime.

Open the official Python downloads page in your browser.
Install the Python Install Manager from python.org or the Microsoft Store.
Use the Python Install Manager to install Python 3.15.0.
Close the current PowerShell window.
Press the Windows key to open Windows Search.
Type PowerShell into the search field.
Press Enter to open a new PowerShell session.
Confirm the installed runtime by running this command:

python --version

What does this command confirm?

The new PowerShell session can detect programs added during installation. The version check confirms that Windows can now launch the required Python runtime.

You should now see Python report version 3.15.0.

Python version still unchanged?

Close every PowerShell window before opening another session.
Check the Python Install Manager to confirm that Python 3.15.0 is installed.
Still stuck? Help me make Python 3.15.0 available in PowerShell.

Install Visual Studio Code and Git

Visual Studio Code gives you a place to edit the project files. Git records the changes that later become your public portfolio history.

Open the official Visual Studio Code Windows setup page in your browser.
Download the recommended Windows User setup.
Run the downloaded Visual Studio Code setup.
Approve the Windows permission prompt if it appears.
Keep the recommended option on each setup screen.
Complete the Visual Studio Code installation.

Why use the User setup?

The User setup is the recommended Visual Studio Code installation for most people. It also adds the editor command to your Windows path after you start a new console session.

Press the Windows key to open Windows Search.
Type Visual Studio Code into the search field.
Press Enter to launch Visual Studio Code.

You should see the Visual Studio Code welcome window. This confirms that the editor installation can launch.

Open the official Git for Windows download page in your browser.
Download the latest Git for Windows x64 setup.
Run the downloaded Git for Windows setup.
Approve the Windows permission prompt if it appears.
Keep the recommended option on each setup screen.
Complete the Git installation.

Windows shells load available command locations when they start. A fresh PowerShell session gives all three installed commands a clean final test.

Close every open PowerShell window.
Press the Windows key to open Windows Search.
Type PowerShell into the search field.
Press Enter to open a new PowerShell session.

Before you run the final checks, do you expect all three commands to resolve in this fresh PowerShell session?

Verify the complete command-line toolkit by running these commands:

python --version
code --version
git --version

What do these checks prove?

The first line confirms that the required Python runtime is available.
The second line confirms that Visual Studio Code added its command to the Windows path.
The third line confirms that Git for Windows is available for version control.

Each command should print a version. Python should report 3.15.0.

Switch back to Power BI Desktop from earlier.
Confirm that the blank report canvas is still visible.

Your blank Power BI report and all three version outputs confirm that the analytics toolkit is ready. That's the setup hurdle cleared before any project files enter the picture.

A version command is missing?

Restart Windows if an installed command remains unavailable in a new PowerShell session.
Rerun the affected installer if the corresponding application does not appear in Windows Search.
Still stuck? Help me troubleshoot the missing Windows toolkit command.

Your Windows analytics toolkit is ready. Next, you'll use it to create the project workspace and pull the first live complaint extract.

Pull the Raw Complaint Extract

Your Windows analytics toolkit is ready. The next challenge is giving the complaint analysis a repeatable local home.

A raw extract from the CFPB Consumer Complaint Database API gives you an immediate data win. However, an untested CSV cannot support a production dashboard.

In this step, get ready to:

Create a local Git workspace with the required folder structure.
Set up an activated Python virtual environment with pandas 3.0.6.
Download the official complaint extract to expose its raw-data risks.

Create the project workspace

A Git repository tracks the source files you build throughout the project. A Python virtual environment keeps this project's packages separate from other work on your computer.

Switch back to the PowerShell window from the previous step.
Create the project on your Desktop by running these commands:

Set-Location -Path ~/Desktop
New-Item -Path virginia-deposit-complaint-intelligence -ItemType Directory
Set-Location -Path .\virginia-deposit-complaint-intelligence -PassThru

What do these commands do?

The first command moves PowerShell to your Desktop.
The second command creates the virginia-deposit-complaint-intelligence folder.
The final command enters the new folder and prints its path.

PowerShell shows a path ending in Desktop\virginia-deposit-complaint-intelligence.

Project folder already exists?

PowerShell may report that the folder already exists if you previously attempted this project. Remove the incomplete folder or use the existing empty folder before running the commands again.

Still stuck? Help me create and enter this project folder in PowerShell.

Initialize the repository and create its folder structure by running these commands:

git init -b main
New-Item -Path data, docs, powerbi, reports, sql, src -ItemType Directory
New-Item -Path data\raw, data\processed -ItemType Directory
code .

How is the workspace organized?

The first command initializes a local Git repository on the main branch.
The next two commands create separate locations for data, documentation, report files, SQL, and Python source code.
The final command opens the current project folder in Visual Studio Code.

Select Yes, I trust the authors if Visual Studio Code shows the Workspace Trust dialog.

VS Code opens with virginia-deposit-complaint-intelligence at the top of the Explorer. You can see the seven project folders underneath it.

Return to the PowerShell window from earlier.
Create and activate the virtual environment by running these commands:

python -m venv .venv
.\.venv\Scripts\Activate.ps1

What does activation change?

The first command creates an isolated Python environment inside .venv. The activation command makes that environment the current session's source for Python packages.

Your PowerShell prompt now begins with (.venv). That prefix confirms the project environment is active.

Activation blocked by PowerShell?

Your Windows execution policy may block the activation script. This setting applies to your Windows user account.

Allow locally created PowerShell scripts by running this command:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Confirm the policy change when prompted. Run the activation command again after PowerShell returns to the prompt.

Still stuck? Help me activate this Windows virtual environment.

Add the reproducible project files

Generated data belongs outside version control because the pipeline can rebuild it. A pinned pandas version gives every future run the same data-processing dependency.

Select the New File icon at the top of the VS Code Explorer.
Enter .gitignore as the file name.
Keep generated files out of Git by pasting this configuration:

.venv/
__pycache__/
*.pyc
data/raw/
data/processed/
data/*.db
reports/*.pbix

What does this file protect?

The virtual environment and Python cache files stay local to your computer.
Generated complaint data and database files stay out of the repository.
The Power BI project file stays local because it contains imported public data.

Save .gitignore.
Confirm .gitignore appears at the top level of the VS Code Explorer.

Can't see the .gitignore file?

Confirm that you created the file inside virginia-deposit-complaint-intelligence. Check that the name begins with a full stop.

Still stuck? Help me create the .gitignore file in VS Code.

Select the New File icon at the top of the VS Code Explorer.
Enter requirements.txt as the file name.
Pin the project's dependency by pasting this line:

pandas==3.0.6

Why pin the pandas version?

The exact version keeps the project's data-processing behavior reproducible. It also matches the Python runtime you installed in the previous step.

Save requirements.txt.
Return to the activated PowerShell window.

The package download can take a minute on a slower connection. PowerShell keeps printing progress while the installation runs.

Install pandas 3.0.6 into the active environment by running:

python -m pip install pandas==3.0.6

Where is pandas installed?

The active .venv environment receives pandas and its required packages. Your system-wide Python installation remains separate.

The installation finishes without an error. Your PowerShell prompt still begins with (.venv).

Package installation failed?

Confirm that your PowerShell prompt starts with (.venv). A network interruption can also stop the package download.

Still stuck? Help me install pandas in this virtual environment.

Expand the src folder in the VS Code Explorer.
Select the New File icon beside the src folder.
Enter prepare_data.py as the file name.
Add the download source and project paths by pasting this first code chunk:

from pathlib import Path
from urllib.request import urlretrieve

import pandas as pd

API_URL = (
    "https://www.consumerfinance.gov/data-research/consumer-complaints/"
    "search/api/v1/?date_received_min=2025-01-01"
    "&date_received_max=2026-01-01"
    "&state=VA"
    "&product=Checking%20or%20savings%20account"
    "&format=csv"
)

ROOT = Path(__file__).resolve().parents[1]
RAW_DIR = ROOT / "data" / "raw"
PROCESSED_DIR = ROOT / "data" / "processed"
RAW_PATH = RAW_DIR / "complaints_raw.csv"

What does this code set up?

The imports provide file paths, URL downloads, and CSV processing.
The API URL requests Virginia checking and savings complaints received during 2025.
The path constants keep generated files inside the named project folders.

Save src\prepare_data.py.
Confirm the file remains visible under src in the VS Code Explorer.

Seeing red underlines in the imports?

Confirm that the pandas installation completed inside the active virtual environment. Check that every quote and parenthesis matches the code block.

Still stuck? Help me check the imports and path constants.

Place the cursor two blank lines below the RAW_PATH line.
Add the download and preview logic by pasting this code chunk:

def main() -> None:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    PROCESSED_DIR.mkdir(parents=True, exist_ok=True)

    print("Downloading the official CFPB CSV extract...")
    urlretrieve(API_URL, RAW_PATH)

    df = pd.read_csv(RAW_PATH, dtype="string", na_values=["None"])
    print(f"Raw rows: {len(df)}")
    print(f"Raw headers: {[str(column) for column in df.columns]}")
    print("Issue counts:")
    print(df["Issue"].value_counts())


if __name__ == "__main__":
    main()

What does the first pipeline do?

The directory calls make sure both data folders exist before the download starts.
The URL download saves the official extract as complaints_raw.csv.
The CSV reader loads every column as text and recognizes the source's literal missing marker.
The print statements expose the row count, source headers, and issue distribution.

Save src\prepare_data.py.
Compare all three project files with the reference below.

Seeing a Python syntax warning?

Confirm that main() begins at the left edge. Keep each statement inside the function indented by four spaces.

Still stuck? Help me check the structure of prepare_data.py.

✔️ Awesome, I've got everything!

Your configuration files are saved. Your raw-download script is ready to test.

ⓧ I'd like to double check the full code

Compare .gitignore with this complete reference:

.venv/
__pycache__/
*.pyc
data/raw/
data/processed/
data/*.db
reports/*.pbix

What should match?

Every generated environment, data, database, and Power BI pattern should appear on its own line.

Compare requirements.txt with this complete reference:

pandas==3.0.6

What should match?

The file should contain the single pinned pandas dependency.

Compare src\prepare_data.py with this complete reference:

from pathlib import Path
from urllib.request import urlretrieve

import pandas as pd

API_URL = (
    "https://www.consumerfinance.gov/data-research/consumer-complaints/"
    "search/api/v1/?date_received_min=2025-01-01"
    "&date_received_max=2026-01-01"
    "&state=VA"
    "&product=Checking%20or%20savings%20account"
    "&format=csv"
)

ROOT = Path(__file__).resolve().parents[1]
RAW_DIR = ROOT / "data" / "raw"
PROCESSED_DIR = ROOT / "data" / "processed"
RAW_PATH = RAW_DIR / "complaints_raw.csv"


def main() -> None:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    PROCESSED_DIR.mkdir(parents=True, exist_ok=True)

    print("Downloading the official CFPB CSV extract...")
    urlretrieve(API_URL, RAW_PATH)

    df = pd.read_csv(RAW_PATH, dtype="string", na_values=["None"])
    print(f"Raw rows: {len(df)}")
    print(f"Raw headers: {[str(column) for column in df.columns]}")
    print("Issue counts:")
    print(df["Issue"].value_counts())


if __name__ == "__main__":
    main()

What should match?

The complete script should contain the official query, project paths, download logic, CSV preview, and main guard.

Run and inspect the raw extract

This first run answers one question. Can your local project reproduce an official complaint-data download?

Before you run the script, do you expect the source data to arrive with analysis-ready names and values?

Run the raw-download pipeline from the activated PowerShell window:

python src\prepare_data.py

What does this command prove?

Python executes the saved script from the current project folder. A successful run proves that the API request, local paths, pandas installation, and CSV reader work together.

You see a Raw rows value above zero. You also see official headers such as Date received and Timely response? followed by issue counts.

Download or script failed?

Confirm that the PowerShell prompt starts with (.venv). Confirm that PowerShell is still inside virginia-deposit-complaint-intelligence.

A network interruption can prevent the official CSV from downloading. Run the command again after your connection is stable.

Still stuck? Help me debug the raw complaint download.

That first live pull is working. Your project can now retrieve the official complaint extract on demand.

Switch back to VS Code.
Expand the data folder in the Explorer.
Expand the raw folder.
Select complaints_raw.csv to inspect the downloaded source file.
Find a header containing spaces or punctuation.
Search the raw file for the literal value None.
Inspect the values under Date received.
Record whether the current script tests uniqueness in Complaint ID.

What did the raw extract expose?

Source headers contain spaces and punctuation that complicate later code.
The raw download uses literal None values to represent missing data.
The date fields remain text with no validity checks.
The script has not tested whether Complaint ID values are repeated.

The issue counts provide an early analytical signal. The untested fields make the raw file unsuitable for direct dashboard use.

You now have a reproducible live extract and a concrete list of its risks. Next, you'll turn that raw evidence into validated dashboard-ready data.

Build the Validated Data Pipeline

Your raw extract now downloads from the official source. However, its unchecked fields can distort every chart built from it.

This step enforces a schema with Python. It records every data-quality result before SQLite stores the clean table.

In this step, get ready to:

Enforce the expected source schema.
Record explicit data-quality checks.
Export the analytical table, quality report, and indexed database.

Define the validated schema

The pipeline needs named paths for each output. It also needs a fixed column contract so unexpected source changes cannot pass silently.

In src\prepare_data.py, find the import block at the top of the file.
Add the SQLite import below the Path import:

import sqlite3

Why Add This Import?

The sqlite3 module creates the local analytical database. It is part of Python's standard library.

Save src\prepare_data.py.
Confirm the new import loads successfully by running:

python src\prepare_data.py

What Does This Check Prove?

The command runs the current pipeline with the new database dependency loaded. A successful run proves Python can import sqlite3 before the database logic is added.

You should still see the raw-row count, source headers, and issue counts.

Does the Import Fail?

Check that import sqlite3 starts at the left edge of the file. Confirm that its spelling matches the snippet.

Still stuck? Help me diagnose the SQLite import in my Python pipeline.

In src\prepare_data.py, find RAW_PATH = RAW_DIR / "complaints_raw.csv".
Add the output paths and column contracts directly below that line:

CLEAN_PATH = PROCESSED_DIR / "complaints.csv"
QUALITY_PATH = PROCESSED_DIR / "quality_report.csv"
DATABASE_PATH = ROOT / "data" / "complaints.db"

REQUIRED_COLUMNS = {
    "date_received", "product", "sub_product", "issue", "sub_issue",
    "company_public_response", "company", "state", "zip_code", "tags",
    "submitted_via", "date_sent_to_company", "company_response_to_consumer",
    "timely_response", "complaint_id",
}

OPTIONAL_TEXT_COLUMNS = [
    "sub_product", "sub_issue", "company_public_response", "zip_code", "tags",
]

OUTPUT_COLUMNS = [
    "complaint_id", "date_received", "received_month", "date_sent_to_company",
    "product", "sub_product", "issue", "sub_issue", "company", "state",
    "zip_code", "tags", "submitted_via", "company_public_response",
    "company_response_to_consumer", "timely_response", "timely_flag",
]

What Do These Contracts Control?

The path constants give each generated artifact one predictable location.
The REQUIRED_COLUMNS set defines the fields that must exist before processing continues.
The OPTIONAL_TEXT_COLUMNS list identifies categorical fields where missing values can be replaced safely.
The OUTPUT_COLUMNS list fixes the order of the final analytical table.

Save src\prepare_data.py.
Confirm the column contracts do not interrupt the raw download by running:

python src\prepare_data.py

Why Run the Pipeline Again?

This run checks that every new path and column name is valid Python. The raw preview should complete as before.

You should see a nonzero raw-row count followed by the source headers and issue counts.

In src\prepare_data.py, find the blank space above def main() -> None:.
Add the header-normalization and quality-check helpers in that space:

def normalize_header(name: str) -> str:
    return name.strip().lower().replace("?", "").replace("-", "_").replace(" ", "_")


def make_check(name: str, passed: bool, value: int | str, details: str) -> dict:
    return {"check_name": name, "status": "PASS" if passed else "FAIL", "value": value, "details": details}

What Do These Helpers Do?

The normalize_header() function converts source headers into lowercase names that work cleanly in Python and SQL.
The make_check() function gives every quality result the same four-field structure.

Save src\prepare_data.py.
Confirm both helper definitions load successfully by running:

python src\prepare_data.py

What Does This Run Confirm?

Python reads both helper definitions before it calls main(). Reaching the issue counts confirms that both definitions are syntactically valid.

Seeing a Syntax Error?

Confirm that both def lines start at the left edge. Check that each return line has four spaces of indentation.

Still stuck? Help me fix the helper functions in my data pipeline.

Normalize and audit the extract

A quality check needs evidence captured before cleaning changes the data. These audit values preserve the original counts while the pipeline prepares standardized fields.

In src\prepare_data.py, find print(df["Issue"].value_counts()) inside main().
Add the normalization and audit logic directly below that line:

    df.columns = [normalize_header(str(column)) for column in df.columns]
    missing_columns = sorted(REQUIRED_COLUMNS - set(df.columns))
    if missing_columns:
        raise ValueError(f"Missing required source columns: {missing_columns}")
    received_ts = pd.to_datetime(df["date_received"], errors="coerce", utc=True)
    sent_ts = pd.to_datetime(df["date_sent_to_company"], errors="coerce", utc=True)
    duplicate_count = int(df.duplicated(subset=["complaint_id"]).sum())
    missing_id_count = int(df["complaint_id"].isna().sum())
    invalid_received_count = int(received_ts.isna().sum())
    invalid_sent_count = int(sent_ts.isna().sum())
    unexpected_timely_count = int((~df["timely_response"].isin(["Yes", "No"])).sum())
    state_scope_ok = all(value == "VA" for value in df["state"])
    product_scope_ok = all(value == "Checking or savings account" for value in df["product"])
    year_scope_ok = all(value == 2025 for value in received_ts.dt.year)

How Does the Audit Work?

Normalized headers turn fields such as Complaint ID into complaint_id.
The required-column comparison stops the pipeline when the source schema changes.
Date parsing converts invalid values into missing timestamps that can be counted.
The remaining counters capture missing IDs, repeated IDs, unexpected timely values, and records outside the requested scope.

Save src\prepare_data.py.
Test the normalized schema and audit calculations by running:

python src\prepare_data.py

What Does a Successful Audit Look Like?

The script should reach the end of the raw preview without reporting a missing-column exception. That confirms the expected source fields are available after normalization.

Seeing a Missing-Column Exception?

Compare the column named in the exception with REQUIRED_COLUMNS. Check the spelling in normalize_header() before changing the contract.

Still stuck? Help me diagnose a missing source column after header normalization.

The audit now knows where the source data falls short. The next block creates consistent analytical fields before exporting those findings.

In src\prepare_data.py, find year_scope_ok = all(value == 2025 for value in received_ts.dt.year).
Add the transformations and exported quality checks directly below that line:

    df["date_received"] = received_ts.dt.strftime("%Y-%m-%d")
    df["date_sent_to_company"] = sent_ts.dt.strftime("%Y-%m-%d")
    df["received_month"] = received_ts.dt.strftime("%Y-%m")
    df["timely_flag"] = [1 if value == "Yes" else 0 for value in df["timely_response"]
    df[OPTIONAL_TEXT_COLUMNS] = df[OPTIONAL_TEXT_COLUMNS].fillna("Not provided")
    df = df.drop_duplicates(subset=["complaint_id"], keep="first", ignore_index=True)
    remaining_duplicates = int(df.duplicated(subset=["complaint_id"]).sum())
    df = df[OUTPUT_COLUMNS]
    checks = [
        make_check("Required columns present", not missing_columns, len(REQUIRED_COLUMNS), "All expected CFPB export columns are available after normalization."),
        make_check("Complaint IDs present", missing_id_count == 0, missing_id_count, "Value is the number of missing complaint IDs."),
        make_check("Received dates valid", invalid_received_count == 0, invalid_received_count, "Value is the number of unparseable received dates."),
        make_check("Sent dates valid", invalid_sent_count == 0, invalid_sent_count, "Value is the number of unparseable sent-to-company dates."),
        make_check("Timely values allowed", unexpected_timely_count == 0, unexpected_timely_count, "Allowed source values are Yes and No."),
        make_check("Virginia scope", state_scope_ok, "VA", "Every record must have state VA."),
        make_check("Deposit product scope", product_scope_ok, "Checking or savings account", "Every record must match the selected CFPB product."),
        make_check("Calendar-year scope", year_scope_ok, 2025, "Every received date must fall in calendar year 2025."),
        make_check("Duplicate complaint IDs removed", remaining_duplicates == 0, duplicate_count, "Value is the number of repeated IDs removed from the raw extract."),
    ]
    quality_df = pd.DataFrame(checks)
    df.to_csv(CLEAN_PATH, index=False)
    quality_df.to_csv(QUALITY_PATH, index=False)

What Does This Processing Stage Produce?

The date fields become consistent calendar values.
The received_month field supports monthly analysis.
The timely_flag field converts source responses into values that can be aggregated.
The exported quality report preserves every check as a reviewable record.

Save src\prepare_data.py.
Generate the two processed CSV files by running:

python src\prepare_data.py

What Does This Run Create?

The pipeline writes the standardized analytical rows to complaints.csv. It writes the audit results to quality_report.csv.

Return to the file sidebar in the VS Code window from earlier.
Expand data\processed.
Confirm that complaints.csv appears in the folder.
Confirm that quality_report.csv appears in the folder.

You now have a clean analytical table plus a separate record of every rule applied to it.

Processed Files Missing?

Confirm that the export lines remain indented inside main(). Check the PowerShell output for the first exception shown above the prompt.

Still stuck? Help me find why my processed CSV files were not created.

Store and verify the validated data

CSV files make the outputs portable. A relational table adds reusable SQL access plus indexes for complaint IDs and reporting months.

In src\prepare_data.py, find quality_df.to_csv(QUALITY_PATH, index=False).
Add the database write and terminal summary directly below that line:

    connection = sqlite3.connect(DATABASE_PATH)
    try:
        df.to_sql("complaints", connection, if_exists="replace", index=False)
        connection.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_complaints_id ON complaints (complaint_id)")
        connection.execute("CREATE INDEX IF NOT EXISTS idx_complaints_month ON complaints (received_month)")
        connection.commit()
    finally:
        connection.close()
    print(f"Clean rows: {len(df)}")
    for check in checks:
        print(f"{check['status']}: {check['check_name']} (value={check['value']})")
    print(f"Clean CSV: {CLEAN_PATH}")
    print(f"Quality report: {QUALITY_PATH}")
    print(f"SQLite database: {DATABASE_PATH}")

How Is the Database Protected?

The complaints table receives the same validated rows as the clean CSV.
The unique idx_complaints_id index prevents repeated complaint IDs in the database.
The idx_complaints_month index supports queries grouped or filtered by reporting month.
The finally block closes the database connection even when a write fails.

Save src\prepare_data.py.

Predict whether every critical check will report PASS before you run the completed pipeline.

Run the completed download, validation, and storage pipeline with this command:

python src\prepare_data.py

What Does the Completed Command Do?

The command replaces the raw extract with the latest scoped download. It rebuilds every generated analytical artifact from that source.

You should see every critical quality check report PASS. The final lines should show the clean CSV, quality report, and SQLite database paths.

That closes the raw-data gap. Your dashboard inputs now come from a tested pipeline with reviewable evidence.

Does a Quality Check Fail?

Read the first FAIL line before editing the pipeline. Its value identifies the affected IDs, dates, categories, or scope rule.

Confirm that your code matches the full reference below. Keep the validation rule intact so unexpected source data remains visible.

Still stuck? Help me diagnose the failed data-quality check.

✔️ Awesome, I've got everything!

Great work. Save src\prepare_data.py before moving on.

ⓧ I'd like to double check the full code

from pathlib import Path
import sqlite3
from urllib.request import urlretrieve

import pandas as pd

API_URL = (
    "https://www.consumerfinance.gov/data-research/consumer-complaints/"
    "search/api/v1/?date_received_min=2025-01-01"
    "&date_received_max=2026-01-01"
    "&state=VA"
    "&product=Checking%20or%20savings%20account"
    "&format=csv"
)

ROOT = Path(__file__).resolve().parents[1]
RAW_DIR = ROOT / "data" / "raw"
PROCESSED_DIR = ROOT / "data" / "processed"
RAW_PATH = RAW_DIR / "complaints_raw.csv"
CLEAN_PATH = PROCESSED_DIR / "complaints.csv"
QUALITY_PATH = PROCESSED_DIR / "quality_report.csv"
DATABASE_PATH = ROOT / "data" / "complaints.db"

REQUIRED_COLUMNS = {
    "date_received", "product", "sub_product", "issue", "sub_issue",
    "company_public_response", "company", "state", "zip_code", "tags",
    "submitted_via", "date_sent_to_company", "company_response_to_consumer",
    "timely_response", "complaint_id",
}

OPTIONAL_TEXT_COLUMNS = [
    "sub_product", "sub_issue", "company_public_response", "zip_code", "tags",
]

OUTPUT_COLUMNS = [
    "complaint_id", "date_received", "received_month", "date_sent_to_company",
    "product", "sub_product", "issue", "sub_issue", "company", "state",
    "zip_code", "tags", "submitted_via", "company_public_response",
    "company_response_to_consumer", "timely_response", "timely_flag",
]


def normalize_header(name: str) -> str:
    return name.strip().lower().replace("?", "").replace("-", "_").replace(" ", "_")


def make_check(name: str, passed: bool, value: int | str, details: str) -> dict:
    return {"check_name": name, "status": "PASS" if passed else "FAIL", "value": value, "details": details}


def main() -> None:
    RAW_DIR.mkdir(parents=True, exist_ok=True)
    PROCESSED_DIR.mkdir(parents=True, exist_ok=True)
    DATABASE_PATH.parent.mkdir(parents=True, exist_ok=True)
    print("Downloading the official CFPB CSV extract...")
    urlretrieve(API_URL, RAW_PATH)
    df = pd.read_csv(RAW_PATH, dtype="string", na_values=["None"])
    raw_rows = len(df)
    raw_headers = [str(column) for column in df.columns]
    print(f"Raw rows: {raw_rows}")
    print(f"Raw headers: {raw_headers}")
    print("Issue counts:")
    print(df["Issue"].value_counts())
    df.columns = [normalize_header(str(column)) for column in df.columns]
    missing_columns = sorted(REQUIRED_COLUMNS - set(df.columns))
    if missing_columns:
        raise ValueError(f"Missing required source columns: {missing_columns}")
    received_ts = pd.to_datetime(df["date_received"], errors="coerce", utc=True)
    sent_ts = pd.to_datetime(df["date_sent_to_company"], errors="coerce", utc=True)
    duplicate_count = int(df.duplicated(subset=["complaint_id"]).sum())
    missing_id_count = int(df["complaint_id"].isna().sum())
    invalid_received_count = int(received_ts.isna().sum())
    invalid_sent_count = int(sent_ts.isna().sum())
    unexpected_timely_count = int((~df["timely_response"].isin(["Yes", "No"])).sum())
    state_scope_ok = all(value == "VA" for value in df["state"])
    product_scope_ok = all(value == "Checking or savings account" for value in df["product"])
    year_scope_ok = all(value == 2025 for value in received_ts.dt.year)
    df["date_received"] = received_ts.dt.strftime("%Y-%m-%d")
    df["date_sent_to_company"] = sent_ts.dt.strftime("%Y-%m-%d")
    df["received_month"] = received_ts.dt.strftime("%Y-%m")
    df["timely_flag"] = [1 if value == "Yes" else 0 for value in df["timely_response"]
    df[OPTIONAL_TEXT_COLUMNS] = df[OPTIONAL_TEXT_COLUMNS].fillna("Not provided")
    df = df.drop_duplicates(subset=["complaint_id"], keep="first", ignore_index=True)
    remaining_duplicates = int(df.duplicated(subset=["complaint_id"]).sum())
    df = df[OUTPUT_COLUMNS]
    checks = [
        make_check("Required columns present", not missing_columns, len(REQUIRED_COLUMNS), "All expected CFPB export columns are available after normalization."),
        make_check("Complaint IDs present", missing_id_count == 0, missing_id_count, "Value is the number of missing complaint IDs."),
        make_check("Received dates valid", invalid_received_count == 0, invalid_received_count, "Value is the number of unparseable received dates."),
        make_check("Sent dates valid", invalid_sent_count == 0, invalid_sent_count, "Value is the number of unparseable sent-to-company dates."),
        make_check("Timely values allowed", unexpected_timely_count == 0, unexpected_timely_count, "Allowed source values are Yes and No."),
        make_check("Virginia scope", state_scope_ok, "VA", "Every record must have state VA."),
        make_check("Deposit product scope", product_scope_ok, "Checking or savings account", "Every record must match the selected CFPB product."),
        make_check("Calendar-year scope", year_scope_ok, 2025, "Every received date must fall in calendar year 2025."),
        make_check("Duplicate complaint IDs removed", remaining_duplicates == 0, duplicate_count, "Value is the number of repeated IDs removed from the raw extract."),
    ]
    quality_df = pd.DataFrame(checks)
    df.to_csv(CLEAN_PATH, index=False)
    quality_df.to_csv(QUALITY_PATH, index=False)
    connection = sqlite3.connect(DATABASE_PATH)
    try:
        df.to_sql("complaints", connection, if_exists="replace", index=False)
        connection.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_complaints_id ON complaints (complaint_id)")
        connection.execute("CREATE INDEX IF NOT EXISTS idx_complaints_month ON complaints (received_month)")
        connection.commit()
    finally:
        connection.close()
    print(f"Clean rows: {len(df)}")
    for check in checks:
        print(f"{check['status']}: {check['check_name']} (value={check['value']})")
    print(f"Clean CSV: {CLEAN_PATH}")
    print(f"Quality report: {QUALITY_PATH}")
    print(f"SQLite database: {DATABASE_PATH}")


if __name__ == "__main__":
    main()

How to Use This Reference

This is the complete cumulative pipeline for the step. Compare each section with your saved src\prepare_data.py file.

Your validated dataset is ready for analysis. Next, you will use SQL to turn these controlled records into operational findings.

Analyze Complaint Patterns with SQL

Your validated SQLite database now gives you a trustworthy foundation for analysis. The next goal is to turn those clean complaint records into evidence that an operations leader can use.

A total row count can hide issue concentration or a short-lived monthly spike. You will use SQL to compare volume over time. You will also measure response timeliness plus relief outcomes.

In this step, get ready to:

Create four SQL queries for executive metrics plus operational patterns.
Build a Python query runner that prints each result as a labeled table.
Turn the current results into evidence-based findings plus recommendations.

Create the first query and query runner

The first query establishes the report's executive baseline. It measures complaint volume plus the response outcomes that frame the deeper analysis.

In the VS Code Explorer sidebar, create a file named analysis.sql inside the sql folder.
Add the executive KPI query by pasting this code:

-- QUERY: Executive KPIs
SELECT
    COUNT(*) AS total_complaints,
    COUNT(DISTINCT company) AS distinct_companies,
    ROUND(100.0 * SUM(timely_flag) / COUNT(*), 1) AS timely_response_rate_pct,
    ROUND(100.0 * SUM(CASE WHEN company_response_to_consumer IN ('Closed with monetary relief', 'Closed with non-monetary relief') THEN 1 ELSE 0 END) / COUNT(*), 1) AS relief_response_rate_pct
FROM complaints;

What Does This Query Measure?

The first two expressions measure the number of complaint records plus represented companies.
The timely-response calculation converts the binary timely_flag values into a percentage.
The relief calculation counts monetary relief plus non-monetary relief responses.
The -- QUERY: marker gives the result table its label.

Save sql/analysis.sql.
Confirm that analysis.sql is listed inside the sql folder in the Explorer sidebar.

Cannot Find the SQL File?

Check that analysis.sql sits directly inside the project's sql folder. A file created elsewhere will not match the path used by the runner.

Still stuck? Help me locate my SQL analysis file in this project.

A reusable runner keeps the analysis reproducible. It reads every labeled query from analysis.sql before executing each one against the same validated database.

In the VS Code Explorer sidebar, create run_analysis.py inside the existing src folder.
Add the paths plus query-loading function by pasting this code:

from pathlib import Path
import sqlite3

ROOT = Path(__file__).resolve().parents[1]
DATABASE_PATH = ROOT / "data" / "complaints.db"
SQL_PATH = ROOT / "sql" / "analysis.sql"
QUERY_MARKER = "-- QUERY:"


def load_queries(path: Path) -> list[tuple[str, str]]:
    text = path.read_text(encoding="utf-8")
    queries = []
    for section in text.split(QUERY_MARKER)[1:]:
        lines = section.strip().splitlines()
        title = lines[0].strip()
        sql = "\n".join(lines[1:]).strip()
        queries.append((title, sql))
    return queries

How Does Query Loading Work?

The path constants point to the generated database plus the SQL file.
The load_queries() function splits the file at each -- QUERY: marker.
Each section becomes a title plus an executable SQL statement.

Save src/run_analysis.py.
Confirm that the file now shows the database path plus the complete load_queries() function.

Does the Function Look Incomplete?

Check the indentation beneath the for statement. Every line that processes one query section must remain inside that loop.

Still stuck? Help me check the structure of my query loader.

Complete the result printer plus execution flow by adding this code below load_queries():

def print_result(title: str, cursor: sqlite3.Cursor) -> None:
    headers = [column[0] for column in cursor.description]
    rows = cursor.fetchall()
    print(f"\n{title}")
    print(" | ".join(headers))
    print("-" * 80)
    for row in rows:
        print(" | ".join("" if value is None else str(value) for value in row))


def main() -> None:
    if not DATABASE_PATH.exists():
        raise FileNotFoundError("Database not found. Run python src\\prepare_data.py first.")
    queries = load_queries(SQL_PATH)
    connection = sqlite3.connect(DATABASE_PATH)
    try:
        for title, sql in queries:
            cursor = connection.execute(sql)
            print_result(title, cursor)
    finally:
        connection.close()


if __name__ == "__main__":
    main()

What Does the Runner Do?

The print_result() function reads the query's column names before printing every returned row.
The database check stops the script with a useful instruction when the generated database is missing.
The main() function opens one database connection before executing every loaded query.
The finally block closes the connection after execution.

Save src/run_analysis.py.

Before you run the analysis, make a quick prediction about the columns that the executive table will contain.

Return to the activated PowerShell terminal from earlier.
Execute the first query through the runner by running this command:

python src\run_analysis.py

What Should You See?

You should see a table labeled Executive KPIs. Its columns show total complaints plus distinct companies.

The same table also shows timely-response plus relief-response percentages. This proves the runner can load a labeled query from the SQL file.

Query Runner Failed?

Confirm that the activated terminal is still inside virginia-deposit-complaint-intelligence.
Check that data/complaints.db still exists.
Check that the first line of analysis.sql begins with the exact query marker.
Still stuck? Help me debug my SQL query runner.

✔️ The Executive KPIs table printed

Your query runner is connected to the validated database. Keep the terminal open for the next queries.

ⓧ I'd like to double check the full code

from pathlib import Path
import sqlite3

ROOT = Path(__file__).resolve().parents[1]
DATABASE_PATH = ROOT / "data" / "complaints.db"
SQL_PATH = ROOT / "sql" / "analysis.sql"
QUERY_MARKER = "-- QUERY:"


def load_queries(path: Path) -> list[tuple[str, str]]:
    text = path.read_text(encoding="utf-8")
    queries = []
    for section in text.split(QUERY_MARKER)[1:]:
        lines = section.strip().splitlines()
        title = lines[0].strip()
        sql = "\n".join(lines[1:]).strip()
        queries.append((title, sql))
    return queries


def print_result(title: str, cursor: sqlite3.Cursor) -> None:
    headers = [column[0] for column in cursor.description]
    rows = cursor.fetchall()
    print(f"\n{title}")
    print(" | ".join(headers))
    print("-" * 80)
    for row in rows:
        print(" | ".join("" if value is None else str(value) for value in row))


def main() -> None:
    if not DATABASE_PATH.exists():
        raise FileNotFoundError("Database not found. Run python src\\prepare_data.py first.")
    queries = load_queries(SQL_PATH)
    connection = sqlite3.connect(DATABASE_PATH)
    try:
        for title, sql in queries:
            cursor = connection.execute(sql)
            print_result(title, cursor)
    finally:
        connection.close()


if __name__ == "__main__":
    main()

How to Use This Reference

Compare the imports plus paths first. Then compare each function's indentation with your saved file.

Add trend, concentration, and priority queries

A common table expression gives a query a named intermediate result. A window function can then compare each monthly row with the row before it without collapsing the result.

In sql/analysis.sql, place the cursor after the executive KPI query.
Add the monthly movement query by pasting this code:

-- QUERY: Monthly trend with month-over-month change
WITH monthly AS (
    SELECT received_month, COUNT(*) AS complaint_count
    FROM complaints
    GROUP BY received_month
),
with_previous AS (
    SELECT received_month, complaint_count, LAG(complaint_count) OVER (ORDER BY received_month) AS previous_month
    FROM monthly
)
SELECT
    received_month,
    complaint_count,
    previous_month,
    ROUND(100.0 * (complaint_count - previous_month) / NULLIF(previous_month, 0), 1) AS mom_change_pct
FROM with_previous
ORDER BY received_month;

How Does the Monthly Comparison Work?

The first common table expression counts complaints for each received_month.
The LAG window function brings the prior month's count into the current row.
The final calculation expresses the movement as mom_change_pct.
The first month has no previous month for comparison.

Save sql/analysis.sql.
Print the updated result set by running this command:

python src\run_analysis.py

What Changed in the Output?

You should now see a second table labeled Monthly trend with month-over-month change. The table includes previous_month plus mom_change_pct.

Monthly Query Not Printing?

Confirm that the monthly query starts with its own -- QUERY: marker. The runner only loads sections that follow this marker.

Still stuck? Help me debug the monthly SQL query.

Monthly movement shows when volume changed. Issue share reveals whether a small number of complaint categories dominate the extract.

In sql/analysis.sql, place the cursor after the monthly query.
Add the issue concentration query by pasting this code:

-- QUERY: Issue concentration
WITH issue_counts AS (
    SELECT issue, COUNT(*) AS complaint_count
    FROM complaints
    GROUP BY issue
)
SELECT
    issue,
    complaint_count,
    ROUND(100.0 * complaint_count / SUM(complaint_count) OVER (), 1) AS complaint_share_pct
FROM issue_counts
ORDER BY complaint_count DESC;

How Is Issue Share Calculated?

The common table expression counts records for each issue. The aggregate window divides each issue count by the count across all issues.

Sorting by complaint count puts the largest concentration at the top. That first row becomes a starting point for root-cause review.

Save sql/analysis.sql.
Print the issue concentration table by running this command:

python src\run_analysis.py

What Changed in the Output?

You should now see an Issue concentration table beneath the monthly results. Its first row identifies the leading issue plus its share of the scoped extract.

Issue Shares Look Wrong?

Check that the denominator uses SUM(complaint_count) OVER (). Leaving out the empty window changes the calculation.

Still stuck? Help me check the issue-share calculation.

Concentration identifies high-volume categories. The operational priority view adds timely-response plus relief-response evidence so volume is not interpreted alone.

In sql/analysis.sql, place the cursor after the issue concentration query.
Add the operational priority query by pasting this code:

-- QUERY: Operational priority view
SELECT
    issue,
    COUNT(*) AS complaint_count,
    ROUND(100.0 * SUM(timely_flag) / COUNT(*), 1) AS timely_response_rate_pct,
    ROUND(100.0 * SUM(CASE WHEN company_response_to_consumer IN ('Closed with monetary relief', 'Closed with non-monetary relief') THEN 1 ELSE 0 END) / COUNT(*), 1) AS relief_response_rate_pct
FROM complaints
GROUP BY issue
HAVING COUNT(*) >= 20
ORDER BY complaint_count DESC;

How Does This Support Prioritization?

The query groups complaint volume by issue.
The timely-response rate adds an operational response signal.
The relief-response rate adds outcome context.
The minimum count keeps very small categories from dominating the priority view.

Save sql/analysis.sql.

Before you run the complete analysis, make a quick prediction about which issue will appear first in the priority table.

Print all four result tables by running this command:

python src\run_analysis.py

What Should You See Now?

You should see four labeled result tables. The monthly table should contain previous_month plus mom_change_pct columns.

The final table should compare complaint count plus both response rates for issues with enough records. You now have the evidence needed to write operational findings.

Only Three Tables Printed?

Check that the operational query has its own marker on a separate line. Also confirm that the issue concentration query ends before the next marker begins.

Still stuck? Help me find the missing labeled SQL section.

✔️ All four result tables printed

Your SQL analysis now covers executive metrics plus monthly movement. It also covers issue concentration plus operational priority.

ⓧ I'd like to double check the full code

-- QUERY: Executive KPIs
SELECT
    COUNT(*) AS total_complaints,
    COUNT(DISTINCT company) AS distinct_companies,
    ROUND(100.0 * SUM(timely_flag) / COUNT(*), 1) AS timely_response_rate_pct,
    ROUND(100.0 * SUM(CASE WHEN company_response_to_consumer IN ('Closed with monetary relief', 'Closed with non-monetary relief') THEN 1 ELSE 0 END) / COUNT(*), 1) AS relief_response_rate_pct
FROM complaints;

-- QUERY: Monthly trend with month-over-month change
WITH monthly AS (
    SELECT received_month, COUNT(*) AS complaint_count
    FROM complaints
    GROUP BY received_month
),
with_previous AS (
    SELECT received_month, complaint_count, LAG(complaint_count) OVER (ORDER BY received_month) AS previous_month
    FROM monthly
)
SELECT
    received_month,
    complaint_count,
    previous_month,
    ROUND(100.0 * (complaint_count - previous_month) / NULLIF(previous_month, 0), 1) AS mom_change_pct
FROM with_previous
ORDER BY received_month;

-- QUERY: Issue concentration
WITH issue_counts AS (
    SELECT issue, COUNT(*) AS complaint_count
    FROM complaints
    GROUP BY issue
)
SELECT
    issue,
    complaint_count,
    ROUND(100.0 * complaint_count / SUM(complaint_count) OVER (), 1) AS complaint_share_pct
FROM issue_counts
ORDER BY complaint_count DESC;

-- QUERY: Operational priority view
SELECT
    issue,
    COUNT(*) AS complaint_count,
    ROUND(100.0 * SUM(timely_flag) / COUNT(*), 1) AS timely_response_rate_pct,
    ROUND(100.0 * SUM(CASE WHEN company_response_to_consumer IN ('Closed with monetary relief', 'Closed with non-monetary relief') THEN 1 ELSE 0 END) / COUNT(*), 1) AS relief_response_rate_pct
FROM complaints
GROUP BY issue
HAVING COUNT(*) >= 20
ORDER BY complaint_count DESC;

How to Use This Reference

Compare every query marker plus query ending with your saved file. The runner depends on each marker remaining directly above its matching statement.

Turn the results into operational findings

A strong finding separates observed evidence from interpretation. It also states what the public complaint data cannot prove.

In the VS Code Explorer sidebar, create findings.md inside the docs folder.
Add the findings structure by pasting this content:

# Findings and Recommendations

## Observations

1. Monthly pattern: The learner replaces this sentence with the largest observed monthly movement and its supporting counts from the current SQL output.
2. Issue concentration: The learner replaces this sentence with the leading issue and its share of the scoped extract.
3. Response outcome: The learner replaces this sentence with timely-response and relief-response evidence most relevant to the leading issue.

## Interpretations

1. The learner explains what the leading issue suggests should be investigated by an operations or customer-experience team.
2. The learner explains whether the monthly pattern appears persistent or concentrated in a limited period.

## Recommended follow-up actions

1. The learner recommends a root-cause review naming the issue, process owner, and evidence to examine next.
2. The learner recommends a monitoring view or control that can distinguish a recurring pattern from a one-time surge.

## Limitations

- Complaint records are not a statistical sample of all consumer experiences.
- Company complaint counts are not normalized for company size or market share.
- The data shows complaint categories and company responses, not verified misconduct or customer harm.
- Date sent to company describes CFPB routing timing, not the company's resolution duration.
- Results reflect the public database and its publication rules at the time the pipeline is run.

How Should You Use This Structure?

The observations capture values that appear directly in the current SQL output. The interpretations explain why those values deserve operational attention.

The recommendations identify a practical next investigation. The limitations keep the conclusions proportional to what complaint records can support.

Replace the three observation prompts with values from your current SQL tables.
Replace the two interpretation prompts with conclusions supported by those observations.
Replace the two action prompts with a named root-cause review plus a monitoring control.
Keep the limitation about company size or market share in the completed file.
Save docs/findings.md.
Confirm that every observation includes a specific count or percentage from the current output.

Findings Feel Too General?

Start each observation with a value from the terminal. Follow it with the issue or month that value describes.

Still stuck? Help me turn my SQL output into evidence-based findings.

Before the final check, make a quick prediction about whether the same four table labels will still appear after documenting your findings.

Run the complete analysis one final time with this command:

python src\run_analysis.py

Final Verification

You should see four labeled tables. The monthly table should show previous_month plus mom_change_pct.

Compare the printed values with docs/findings.md. Every observation in the file should trace back to visible evidence in these tables.

That is the analytical layer working from end to end. Your validated database now produces reproducible evidence plus a documented operational interpretation.

Your complaint patterns are now backed by reproducible SQL plus explicit limitations. Next, you will turn that evidence into a Power BI report and a public portfolio package.

Build and Publish the Power BI Portfolio

Your validated data and SQL findings now answer the operations question with evidence. This step turns that evidence into a report that leaders can scan quickly.

You'll use Power BI Desktop to build the visual narrative. You'll package the methodology for reviewers. You'll then publish the finished portfolio through GitHub.

In this step, get ready to:

Import the validated complaint data and create the DAX measures.
Build a two-page report and export its tracked PDF.
Complete the portfolio documentation and publish the repository.

Import the validated data and create measures

The processed CSV gives Power BI the same validated records used by your SQL analysis. DAX measures turn those records into reusable totals and rates that respond to report filters.

Press the Windows key to open Windows search.
Type Power BI Desktop into Windows search.
Press Enter to launch Power BI Desktop.
Create a blank report from the Power BI Desktop start screen.
Select Home in the ribbon.
Select Get data.
Select Text/CSV.
Choose data\processed\complaints.csv from your virginia-deposit-complaint-intelligence folder.

Why Import the Processed CSV?

The processed CSV has already passed your schema checks and scope checks. Importing it keeps the report aligned with the evidence in data\complaints.db.

This approach also avoids an extra database driver. The report stays easier for another reviewer to reproduce.

Select Load in the CSV preview.
Confirm that the complaints table appears in the Fields pane.
Expand the complaints table to confirm that fields such as complaint_id and received_month are available.

CSV Not Loading?

Confirm that you selected the processed complaints.csv file. Rerun the preparation script if that file is missing.

Still stuck? Help me troubleshoot a Power BI CSV import from my processed project data.

Use the Explorer sidebar in Visual Studio Code to create powerbi\measures.dax.
Add the complaint-count measure group by pasting this code into powerbi\measures.dax:

Total Complaints =
COUNTROWS ( 'complaints' )

Distinct Companies =
DISTINCTCOUNT ( 'complaints'[company] )

Timely Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[timely_response] = "Yes"
)

Untimely Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[timely_response] = "No"
)

What Do These Measures Do?

The first measure counts the complaint records that remain after the current report filters.
The second measure counts the represented companies once each.
The final two measures split complaints by the official timely-response category.

Save powerbi\measures.dax.
Return to Power BI Desktop.
Select the complaints table in the Fields pane.
Select New Measure from the Home ribbon.
Paste the Total Complaints definition into the formula bar.
Press Enter to create the measure.
Repeat the same measure flow for Distinct Companies plus Timely Responses plus Untimely Responses.
Confirm that all four measures appear under the complaints table.

Measure Showing an Error?

Confirm that the imported table is named complaints. Check that the field names match the definitions exactly.

Still stuck? Help me fix my first Power BI DAX measures.

Append the response-rate measure group to powerbi\measures.dax by pasting this code at the end of the file:

Timely Response Rate =
DIVIDE ( [Timely Responses], [Total Complaints], 0 )

Relief Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[company_response_to_consumer] = "Closed with monetary relief"
        || 'complaints'[company_response_to_consumer] = "Closed with non-monetary relief"
)

Relief Response Rate =
DIVIDE ( [Relief Responses], [Total Complaints], 0 )

How Do the Rates Work?

The timely-response rate divides timely complaints by the filtered complaint total.
The relief measure includes monetary relief plus non-monetary relief.
The final measure converts relief responses into a rate that reacts to slicers.

Save powerbi\measures.dax.
Return to Power BI Desktop.
Create the three remaining measures through the same New Measure flow.
Select the Timely Response Rate measure.
Enter 0.0% as its model-level custom format string.
Select the Relief Response Rate measure.
Enter 0.0% as its model-level custom format string.
Confirm that all seven measures appear under the complaints table.

Rates Not Formatting Correctly?

Confirm that you applied the percentage format to the two rate measures. The Relief Responses measure remains a count.

Still stuck? Help me format my two Power BI rate measures as percentages.

✔️ Awesome, I've got everything!

Your DAX reference is saved. Power BI now contains all seven measures used by the report.

ⓧ I'd like to double check the full code

Compare powerbi\measures.dax with this complete reference.

Total Complaints =
COUNTROWS ( 'complaints' )

Distinct Companies =
DISTINCTCOUNT ( 'complaints'[company] )

Timely Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[timely_response] = "Yes"
)

Untimely Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[timely_response] = "No"
)

Timely Response Rate =
DIVIDE ( [Timely Responses], [Total Complaints], 0 )

Relief Responses =
CALCULATE (
    [Total Complaints],
    'complaints'[company_response_to_consumer] = "Closed with monetary relief"
        || 'complaints'[company_response_to_consumer] = "Closed with non-monetary relief"
)

Relief Response Rate =
DIVIDE ( [Relief Responses], [Total Complaints], 0 )

Build and verify the two-page report

A clear visual hierarchy moves from headline measures to supporting patterns. The first page gives leaders the overview. The second page gives analysts the detail needed for root-cause review.

Rename the first report page Executive Overview.
Add a card using Total Complaints.
Add a card using Distinct Companies.
Add a card using Timely Response Rate.
Add a card using Relief Response Rate.
Add a monthly trend chart using received_month with Total Complaints.
Add an issue-ranking chart using issue with Total Complaints.

What Does the Overview Prove?

The cards establish scale and response outcomes. The two charts reveal timing plus issue concentration.

Together they answer where investigation should begin. They also keep the evidence visible when filters change.

Add a slicer using received_month.
Add a slicer using sub_product.
Add a slicer using issue.
Add a slicer using sub_issue.
Add a slicer using submitted_via.
Add a slicer using company_response_to_consumer.
Add a slicer using tags.

Keep the Decision Visible

Place the four KPI cards near the top of the page. Keep the trend and issue-ranking charts large enough for their labels to remain readable.

Add a second report page with the + icon.
Rename the second report page Root Cause Review.
Add an issue-detail visual using issue plus sub_issue with Total Complaints.
Add a response-outcome visual using company_response_to_consumer with Total Complaints.
Add a channel-mix visual using submitted_via with Total Complaints.
Add a company-volume visual using company with Total Complaints.
Label the company visual Company Volume for Context.

Why Is Company Volume Contextual?

The complaint database is not a statistical sample. Company counts also lack company-size and market-share denominators.

The company visual supports investigation. It does not establish a quality ranking.

Save the Power BI report as reports\virginia_deposit_complaints.pbix.
Select File in Power BI Desktop.
Select Export.
Select Export to PDF.
Save the export as reports\virginia_deposit_complaints.pdf.

Before you test the report, which visuals do you expect the issue slicer to update?

Select one issue in the issue slicer on Executive Overview.

You should see the relevant cards and charts recalculate for that issue. This proves that the measures respond to filter context.

Clear the selected issue from the slicer.
Switch to the Root Cause Review page.
Confirm that the issue detail and response-outcome visuals contain data.
Open reports\virginia_deposit_complaints.pdf from File Explorer.
Confirm that the PDF contains both report pages.

Visuals Not Responding?

Confirm that every visual uses fields from the same complaints table. Check that the cards use measures instead of unaggregated text fields.

Still stuck? Help me troubleshoot Power BI visuals that do not respond to my issue slicer.

Complete the documentation and publish the repository

A portfolio reviewer needs enough context to understand the decision and reproduce the work. Your documentation connects the stakeholder question to the code and report without publishing the generated complaint data.

Use the Explorer sidebar in Visual Studio Code to create docs\business_requirements.md.
Add the scenario and KPI requirements by pasting this first section:

# Business Requirements

## Scenario

A fictional Director of Consumer Operations wants a concise view of public deposit-account complaint patterns in Virginia. The director needs to decide which issue categories deserve root-cause review, process-owner follow-up, or closer monitoring.

## Primary decision question

Where should operations leadership prioritize deeper review based on complaint concentration, monthly movement, company response timeliness, and relief outcomes?

## Intended audience

- Director of Consumer Operations
- Customer Experience manager
- Operational Risk manager

## Required KPIs

- Total complaints
- Distinct companies represented
- Timely response rate
- Relief response rate
- Monthly complaint count and month-over-month change
- Complaint count and share by issue

What Does This Section Capture?

The scenario names the stakeholder and decision. The KPI list defines the evidence needed to support that decision.

Save docs\business_requirements.md.
Confirm that business_requirements.md appears inside the docs folder.

File in the Wrong Folder?

Confirm that business_requirements.md sits beside findings.md inside the docs folder.

Still stuck? Help me place my business requirements file in the correct project folder.

Append the filters and guardrails to docs\business_requirements.md by pasting this section at the end:

## Required filters

- Received month
- Sub-product
- Issue and sub-issue
- Submission channel
- Company response
- Customer tag

## Acceptance criteria

- The source is restricted to records received from 2025-01-01 through 2025-12-31.
- Every record is scoped to Virginia and the CFPB product `Checking or savings account`.
- Complaint IDs are unique in the analytical table.
- Invalid dates and unexpected timely-response values are reported.
- SQL outputs can be reproduced from the generated SQLite database.
- The report contains an executive page and a root-cause page.
- Company volume is labeled as context, not a quality ranking.

## Out of scope

- Predicting future complaints
- Measuring legal or regulatory violations
- Comparing companies without market-share denominators
- Using confidential employer or customer data
- Publishing consumer narratives or raw complaint data in the repository

Why Document the Boundaries?

Acceptance criteria make the work testable. The out-of-scope list prevents a reviewer from reading raw complaint counts as proof of company quality or misconduct.

Save docs\business_requirements.md.
Confirm that the file includes the Required filters plus Acceptance criteria plus Out of scope headings.

Missing a Documentation Section?

Confirm that the second section was pasted after the KPI list. Check that each Markdown heading begins with the same number of hash characters shown above.

Still stuck? Help me compare my business requirements file with the expected sections.

Use the Explorer sidebar to create docs\portfolio_notes.md.
Add the LinkedIn entry and resume bullets by pasting this first section:

# Portfolio Notes

## LinkedIn project entry

Built a public-data financial-services operations dashboard using Python, pandas, SQLite, SQL, and Power BI. The project downloads an official CFPB extract, applies documented quality checks, analyzes issue concentration and response outcomes with SQL, and presents findings in an interactive report and public GitHub repository.

## Resume bullets

- Built a reproducible financial-services complaint analytics workflow using Python, pandas, SQLite, SQL, and Power BI to analyze public 2025 Virginia checking and savings complaint data.
- Implemented source-schema, date-validity, scope, categorical-value, and duplicate-ID controls, producing a reviewable quality report before dashboard ingestion.
- Developed SQL common table expressions, window-function analysis, and DAX KPIs for complaint trends, issue concentration, relief outcomes, and response timeliness.
- Published business requirements, methodology, limitations, code, SQL, and a portable report through Git and GitHub.

What Makes These Claims Defensible?

Each statement maps to an artifact in the repository. Reviewers can inspect the validation code and SQL before opening the report.

Save docs\portfolio_notes.md.
Confirm that portfolio_notes.md appears inside the docs folder.

Portfolio Notes Missing?

Confirm that the filename uses the underscore shown in portfolio_notes.md. Check that it is saved inside docs.

Still stuck? Help me check my portfolio notes file path and Markdown structure.

Append the interview preparation to docs\portfolio_notes.md by pasting this section at the end:

## Interview preparation

### Why did you choose this problem?

It connects financial-services operations with a public dataset and a realistic decision. The goal is not to rank institutions. It is to show how an analyst can identify where root-cause review should begin while communicating the limits of complaint data.

### Why use Python when Power BI can transform data?

Python makes the extraction and quality checks repeatable outside a dashboard file. The script creates a raw evidence layer, validates schema and scope, removes repeated IDs, and produces the same clean input for SQL and Power BI.

### Why use SQLite?

SQLite demonstrates relational storage and SQL without requiring a database server or paid cloud account. The database can be deleted and rebuilt from the same script.

### How did you validate the data?

I checked required columns, complaint IDs, date parsing, timely-response categories, geographic scope, product scope, calendar-year scope, and duplicate IDs. The checks are written to a separate quality report before analysis.

### What is the most important limitation?

Raw complaint volume is not a measure of company quality because the data is not normalized for company size or market share. I use company volume only as context and focus recommendations on issue patterns and investigation priorities.

### What would you build next?

I would add a scheduled refresh and an external denominator, such as account or market-share data, before attempting company comparisons. I would also add historical years to test whether the identified pattern persists.

Why Include Interview Preparation?

The questions connect technical choices to business reasoning. They also keep your explanation aligned with the report's documented limitations.

Save docs\portfolio_notes.md.
Confirm that the file ends with the What would you build next? section.

Interview Notes Look Incomplete?

Confirm that every question uses a level-three Markdown heading. Check that each answer sits directly below its question.

Still stuck? Help me compare my interview preparation with the expected portfolio notes.

Use the Explorer sidebar to create README.md in the virginia-deposit-complaint-intelligence folder.
Add the business problem and deliverables by pasting this first section:

# Virginia Deposit Complaint Intelligence

A reproducible financial-services analytics portfolio project using the Consumer Financial Protection Bureau Consumer Complaint Database.

## Business problem

A fictional Director of Consumer Operations needs to decide which Virginia deposit-account complaint issues deserve deeper root-cause review. The analysis examines complaint concentration, monthly movement, response timeliness, and relief outcomes without treating raw company counts as a quality ranking.

## Scope

- Geography: Virginia
- Date received: 2025-01-01 through 2025-12-31
- CFPB product: Checking or savings account
- Data source: CFPB Consumer Complaint Database API

## Deliverables

- Reproducible Python extraction and cleaning pipeline
- Data-quality report
- SQLite analytical database
- SQL query portfolio using common table expressions and window functions
- Two-page Power BI report
- Portable PDF report
- Business requirements, findings, limitations, resume bullets, and interview preparation

What Does the README Establish?

The opening tells a reviewer what decision the project supports. The scope and deliverables show what evidence is available.

Save README.md.
Confirm that README.md appears at the same level as requirements.txt.

README Nested in a Folder?

Move README.md to the top level of virginia-deposit-complaint-intelligence if it appears inside docs.

Still stuck? Help me place README.md at the top level of my project.

Append the reproduction and report sections to README.md by pasting this section at the end:

## Reproduce the analysis on Windows

```powershell
python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install pandas==3.0.6
python src\prepare_data.py
python src\run_analysis.py
```

If PowerShell blocks activation, run `Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser` once and try again.

## Power BI report

Open `reports/virginia_deposit_complaints.pdf` for the portable report. The local ignored `.pbix` uses `data/processed/complaints.csv` and the measures documented in `powerbi/measures.dax`.

## Data-quality controls

The pipeline reports required source columns, missing complaint IDs, invalid received and sent dates, unexpected timely-response categories, Virginia/product/calendar-year scope, and repeated complaint IDs removed.

Why Include Reproduction Commands?

The command sequence rebuilds the generated data and analysis from the tracked source. The report section directs reviewers to the portable artifact.

Save README.md.
Confirm that the reproduction section contains five PowerShell commands.

Markdown Commands Rendering Incorrectly?

Confirm that the PowerShell commands remain between the opening and closing code fences. Check that the activation path retains its backslashes.

Still stuck? Help me fix the reproduction code block in README.md.

Append the interpretation and source sections to README.md by pasting this final section at the end:

## Interpretation guardrails

- The complaint database is not a statistical sample of all consumer experiences.
- Company volume is not adjusted for company size or market share.
- Complaint categories and response fields do not prove misconduct or harm.
- The repository excludes raw and processed complaint records.

## Skills demonstrated

Python, pandas, SQLite, SQL, Power BI, DAX, data validation, requirements gathering, operational-risk interpretation, Git, GitHub, and business communication.

## Source

Consumer Financial Protection Bureau, Consumer Complaint Database: https://www.consumerfinance.gov/data-research/consumer-complaints/

How Does the README Protect the Analysis?

The guardrails prevent unsupported conclusions. The source line gives reviewers a direct route to the public dataset behind the project.

Save README.md.
Confirm that the final heading is Source.

README Missing Its Final Sections?

Confirm that the interpretation guardrails appear before the skills section. Check that the CFPB source URL remains on the final line.

Still stuck? Help me compare my README with the expected portfolio structure.

✔️ Awesome, I've got everything!

Your portfolio documentation is complete. Save every open file before you publish the repository.

ⓧ I'd like to double check the full code

Confirm that .gitignore still contains these exclusions.

.venv/
__pycache__/
*.pyc
data/raw/
data/processed/
data/*.db
reports/*.pbix

Compare docs\business_requirements.md with this complete reference.

# Business Requirements

## Scenario

A fictional Director of Consumer Operations wants a concise view of public deposit-account complaint patterns in Virginia. The director needs to decide which issue categories deserve root-cause review, process-owner follow-up, or closer monitoring.

## Primary decision question

Where should operations leadership prioritize deeper review based on complaint concentration, monthly movement, company response timeliness, and relief outcomes?

## Intended audience

- Director of Consumer Operations
- Customer Experience manager
- Operational Risk manager

## Required KPIs

- Total complaints
- Distinct companies represented
- Timely response rate
- Relief response rate
- Monthly complaint count and month-over-month change
- Complaint count and share by issue

## Required filters

- Received month
- Sub-product
- Issue and sub-issue
- Submission channel
- Company response
- Customer tag

## Acceptance criteria

- The source is restricted to records received from 2025-01-01 through 2025-12-31.
- Every record is scoped to Virginia and the CFPB product `Checking or savings account`.
- Complaint IDs are unique in the analytical table.
- Invalid dates and unexpected timely-response values are reported.
- SQL outputs can be reproduced from the generated SQLite database.
- The report contains an executive page and a root-cause page.
- Company volume is labeled as context, not a quality ranking.

## Out of scope

- Predicting future complaints
- Measuring legal or regulatory violations
- Comparing companies without market-share denominators
- Using confidential employer or customer data
- Publishing consumer narratives or raw complaint data in the repository

Compare docs\portfolio_notes.md with this complete reference.

# Portfolio Notes

## LinkedIn project entry

Built a public-data financial-services operations dashboard using Python, pandas, SQLite, SQL, and Power BI. The project downloads an official CFPB extract, applies documented quality checks, analyzes issue concentration and response outcomes with SQL, and presents findings in an interactive report and public GitHub repository.

## Resume bullets

- Built a reproducible financial-services complaint analytics workflow using Python, pandas, SQLite, SQL, and Power BI to analyze public 2025 Virginia checking and savings complaint data.
- Implemented source-schema, date-validity, scope, categorical-value, and duplicate-ID controls, producing a reviewable quality report before dashboard ingestion.
- Developed SQL common table expressions, window-function analysis, and DAX KPIs for complaint trends, issue concentration, relief outcomes, and response timeliness.
- Published business requirements, methodology, limitations, code, SQL, and a portable report through Git and GitHub.

## Interview preparation

### Why did you choose this problem?

It connects financial-services operations with a public dataset and a realistic decision. The goal is not to rank institutions. It is to show how an analyst can identify where root-cause review should begin while communicating the limits of complaint data.

### Why use Python when Power BI can transform data?

Python makes the extraction and quality checks repeatable outside a dashboard file. The script creates a raw evidence layer, validates schema and scope, removes repeated IDs, and produces the same clean input for SQL and Power BI.

### Why use SQLite?

SQLite demonstrates relational storage and SQL without requiring a database server or paid cloud account. The database can be deleted and rebuilt from the same script.

### How did you validate the data?

I checked required columns, complaint IDs, date parsing, timely-response categories, geographic scope, product scope, calendar-year scope, and duplicate IDs. The checks are written to a separate quality report before analysis.

### What is the most important limitation?

Raw complaint volume is not a measure of company quality because the data is not normalized for company size or market share. I use company volume only as context and focus recommendations on issue patterns and investigation priorities.

### What would you build next?

I would add a scheduled refresh and an external denominator, such as account or market-share data, before attempting company comparisons. I would also add historical years to test whether the identified pattern persists.

Compare README.md with this complete reference.

# Virginia Deposit Complaint Intelligence

A reproducible financial-services analytics portfolio project using the Consumer Financial Protection Bureau Consumer Complaint Database.

## Business problem

A fictional Director of Consumer Operations needs to decide which Virginia deposit-account complaint issues deserve deeper root-cause review. The analysis examines complaint concentration, monthly movement, response timeliness, and relief outcomes without treating raw company counts as a quality ranking.

## Scope

- Geography: Virginia
- Date received: 2025-01-01 through 2025-12-31
- CFPB product: Checking or savings account
- Data source: CFPB Consumer Complaint Database API

## Deliverables

- Reproducible Python extraction and cleaning pipeline
- Data-quality report
- SQLite analytical database
- SQL query portfolio using common table expressions and window functions
- Two-page Power BI report
- Portable PDF report
- Business requirements, findings, limitations, resume bullets, and interview preparation

## Reproduce the analysis on Windows

```powershell
python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install pandas==3.0.6
python src\prepare_data.py
python src\run_analysis.py
```

If PowerShell blocks activation, run `Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser` once and try again.

## Power BI report

Open `reports/virginia_deposit_complaints.pdf` for the portable report. The local ignored `.pbix` uses `data/processed/complaints.csv` and the measures documented in `powerbi/measures.dax`.

## Data-quality controls

The pipeline reports required source columns, missing complaint IDs, invalid received and sent dates, unexpected timely-response categories, Virginia/product/calendar-year scope, and repeated complaint IDs removed.

## Interpretation guardrails

- The complaint database is not a statistical sample of all consumer experiences.
- Company volume is not adjusted for company size or market share.
- Complaint categories and response fields do not prove misconduct or harm.
- The repository excludes raw and processed complaint records.

## Skills demonstrated

Python, pandas, SQLite, SQL, Power BI, DAX, data validation, requirements gathering, operational-risk interpretation, Git, GitHub, and business communication.

## Source

Consumer Financial Protection Bureau, Consumer Complaint Database: https://www.consumerfinance.gov/data-research/consumer-complaints/

The generated data and local report contain files that should stay off GitHub. Your existing .gitignore keeps those artifacts local while allowing the source and PDF to be reviewed.

Return to .gitignore in Visual Studio Code.
Confirm that data/raw/ is excluded.
Confirm that data/processed/ is excluded.
Confirm that data/*.db is excluded.
Confirm that reports/*.pbix is excluded.
Confirm that reports\virginia_deposit_complaints.pdf remains visible in the Explorer sidebar.

Publishing makes your work public. The repository contains public-source analysis plus your documentation. It excludes generated complaint records and the local report file.

Use the repository creation page in your GitHub account from earlier.
Create a repository named virginia-deposit-complaint-intelligence.
Set the repository visibility to public.
Leave every repository initialization option disabled.
Create the empty repository.
Copy the repository HTTPS URL into your GitHub repository URL.

Git records the source files and documentation as one reviewable snapshot. Initialize the repository from the PowerShell panel that is already inside your project.

Initialize the local repository on the main branch by running this command:

git init -b main

Confirm that PowerShell reports an initialized Git repository.

Repository Initialization Failed?

Confirm that PowerShell is inside the virginia-deposit-complaint-intelligence folder. Check that git --version still prints the installed version.

Still stuck? Help me initialize my project on the main Git branch.

Replace Your Name with the name you want attached to your commits.
Replace you@example.com with the email address you use for GitHub.
Configure your Git identity by running the edited commands:

git config --global user.name "Your Name"
git config --global user.email you@example.com

What Does Git Identity Control?

Git stores the configured name and email in each commit. These values identify who created the portfolio snapshot.

Confirm that both commands return to the PowerShell prompt without an error.

Identity Command Failed?

Keep quotation marks around a name that contains spaces. Use an email address without quotation marks.

Still stuck? Help me configure my Git name and email on Windows.

Stage the tracked portfolio files and create the first commit by running these commands:

git add .
git commit -m "First commit"

What Gets Committed?

Git stages the source code and SQL plus the DAX reference. It also stages the documentation and PDF.

The ignore rules keep the virtual environment and generated data out of the commit. They also keep the local PBIX file out.

Confirm that the commit output lists created files and reports the First commit message.

Unexpected Files in the Commit?

Stop before pushing if generated CSV files or the PBIX file were included. Compare .gitignore with the full reference above.

Still stuck? Help me check which portfolio files Git staged.

Connect the local repository to your GitHub repository URL by running these commands:

git remote add origin [[YOUR_REPO_URL="your GitHub repository URL"]]
git remote -v

What Does the Remote Do?

The origin remote links your local commit history to the empty GitHub repository. The second command prints the stored fetch and push destinations.

Confirm that PowerShell prints your GitHub URL for both fetch and push.

Remote Already Exists?

Confirm that you are working in the intended project folder. Compare the URL printed by the verification command with your new repository URL.

Still stuck? Help me connect my local repository to the correct GitHub remote.

The first push may open browser authentication through Git Credential Manager. Complete the GitHub approval in that browser window. Never paste a password or access token into your project files.

Push the main branch to GitHub by running this command:

git push -u origin main

Complete the browser authentication if Git Credential Manager prompts you.
Return to PowerShell after GitHub approves the authentication.
Confirm that the push output reports the main branch on origin.

Push Not Reaching GitHub?

Confirm that the empty GitHub repository URL matches the remote printed earlier. Complete any browser authentication window that remains open.

Still stuck? Help me troubleshoot my first GitHub push through Git Credential Manager.

Before you refresh GitHub, which tracked files do you expect to see? Which generated files should remain absent?

Open your public repository URL in the browser.
Confirm that GitHub displays README.md.
Confirm that the src plus sql plus powerbi plus docs folders are visible.
Confirm that reports\virginia_deposit_complaints.pdf is visible.
Confirm that the raw CSV and processed CSV are absent.
Confirm that data\complaints.db is absent.
Confirm that reports\virginia_deposit_complaints.pbix is absent.
Open the PDF from GitHub to confirm that reviewers can access the two-page report.

Strong finish. Your analysis now has a public evidence trail from business requirements through validated data and SQL to an interactive report plus a portable PDF.

Secret mission

Add Issue-Level Drillthrough

Your executive report can identify a priority issue. This challenge adds an Issue Details drillthrough page so reviewers can move from a summary bar to the filtered complaint records behind it.

Clean Up Your Resources

This project has no paid resources or recurring charges. Choose whether to keep your work available, remove the reproducible outputs, or delete the complete project.

Resources you used:

Local Python virtual environment stored in .venv.
Official raw CSV extract stored in data/raw.
Validated dashboard outputs stored in data/processed.
Generated SQLite database stored at data/complaints.db.
Local Power BI Desktop report stored at reports/virginia_deposit_complaints.pbix.
Tracked portfolio files inside virginia-deposit-complaint-intelligence.
Public GitHub repository containing the reviewable portfolio package.

Keep everything running

No action is needed. Choose this if you want to refresh the data or demonstrate the complete project again.

Keep the public GitHub repository available for portfolio reviews.
Retain the local virginia-deposit-complaint-intelligence folder so the complete project stays available.
Use the commands documented in README.md when you want to refresh the ignored data outputs.
Open reports/virginia_deposit_complaints.pbix when you want to demonstrate the interactive report.

Pause - I'll come back to this later

Remove only the reproducible local artifacts to free storage. Your tracked project remains intact.

Remove .venv from the project folder with File Explorer.
Remove data/raw from the project folder with File Explorer.
Remove data/processed from the project folder with File Explorer.
Remove data/complaints.db from the project folder with File Explorer.
Keep reports/virginia_deposit_complaints.pbix for future report demonstrations.
Confirm the four ignored artifacts no longer appear in the project folder.
Recreate the removed artifacts later with the commands documented in README.md.

Delete - I don't want to use this again

Remove the public repository plus the complete local project. Deleting both copies is permanent.

Return to the public GitHub repository in your browser.
Open the repository settings.
Use the repository deletion control inside repository settings.
Complete every confirmation prompt that GitHub presents.
Confirm the repository no longer opens at its former URL.
Close Visual Studio Code.
Delete the virginia-deposit-complaint-intelligence folder from its saved location with File Explorer.
Confirm the project folder no longer appears in File Explorer.

Nice Work!

Outstanding work! You built a recruiter-reviewable complaint intelligence dashboard for a defined operations question. You also packaged the analysis so another person can reproduce the evidence.

You've learned how to:

Build a reproducible data pipeline with Python. Use pandas to download the official CFPB Consumer Complaint Database extract. Verify the source schema. Confirm the geographic scope. Audit the dates. Flag unexpected categories. Remove repeated complaint IDs. Produce clean CSV output plus a SQLite database.
Create SQL evidence with common table expressions. Apply window functions to calculate monthly movement. Measure issue concentration. Compare response timeliness with relief outcomes. Present the findings through DAX measures in a two-page Power BI report.
Publish a public portfolio package through GitHub. Support the project with business requirements. Add evidence-based findings. State clear interpretation limits. Give reviewers a tracked PDF plus interview-ready portfolio notes.
Complete the optional Secret Mission by adding issue-level drillthrough. Transfer the selected issue into complaint-level evidence. Return to the source visual with the Back button.

Ready to quiz yourself?