Fintech Cohort Retention Analysis

Analyze 3M+ fintech transactions with SQL cohort analysis and Python charts.

Introduction

30 Second Summary

Every fintech app, digital bank, and subscription service tracks how many customers come back month after month. When retention drops, revenue follows. Data analysts who can pinpoint exactly where and why customers disappear are the ones companies fight to hire.

In this project, you will load real fintech transaction data into PostgreSQL, write advanced SQL queries to compute month-over-month cohort analysis retention rates, and visualize your findings with Python to produce a portfolio-ready business case.

What You'll Build

A complete cohort retention and revenue analysis using 3.1 million transactions from a real Colombian fintech company's dataset.

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

  • A PostgreSQL database loaded with cleaned fintech transaction data and an analytics view that filters invalid records.
  • Production-quality SQL queries using CTEs and window functions that compute cohort retention rates and revenue-per-cohort metrics.
  • A retention heatmap and revenue chart generated with Python, plus a business-framed README ready for your portfolio.
  • Secret Mission: Add RFM segmentation to identify which customer segments are most at risk of churning.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, GROUP BY, JOIN) and basic Python (pandas, matplotlib). You also need PostgreSQL installed and running on your machine.

Set Up Your Project and Install Dependencies

Every data analysis project starts with the same question: where does the data live, and how do you get it into a shape you can query?

In this step, you will create your project folder, install the Python libraries you need, download real fintech transaction data from Hugging Face, and create a PostgreSQL database ready to receive it.

In this step, get ready to:
  • Create a project directory with a Python virtual environment and install packages.
  • Download the COFINFAD dataset CSV files into your project.
  • Create a PostgreSQL database and verify the connection.
Create your project directory and virtual environment

A virtual environment keeps your project's Python packages isolated from the rest of your system. This prevents version conflicts between different projects.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir fintech-cohort-analysis
cd fintech-cohort-analysis
  • Create a Python virtual environment by running:
python3 -m venv venv
  • Activate the virtual environment by running:

macOS

source venv/bin/activate

Windows

venv\Scripts\Activate

You should see (venv) appear at the beginning of your terminal prompt.

Don't see (venv) in your prompt?

Make sure you ran the activate command from inside the fintech-cohort-analysis folder. If you get a "file not found" error, double-check the venv folder was created by running ls (macOS) or dir (Windows) to see it listed.

help me activate my Python virtual environment

  • Install the four packages your project needs by running:
pip install psycopg2-binary pandas matplotlib seaborn

What are these packages?

  • psycopg2-binary lets Python talk to your PostgreSQL database.
  • pandas handles tabular data (think spreadsheets in code).
  • matplotlib creates charts and visualizations.
  • seaborn adds statistical visualization styles on top of matplotlib (like heatmaps).

You should see output ending with "Successfully installed" followed by a list of package names.

Install failing with an error?

If you see a permissions error, make sure your virtual environment is activated (you should see (venv) in your prompt). If psycopg2-binary fails, check that PostgreSQL is installed on your system.

help me fix my pip install error for psycopg2-binary pandas matplotlib seaborn

Download the COFINFAD dataset

The COFINFAD dataset contains 12 months of real transaction data from a Colombian fintech company. It has 48,723 customers and over 3.1 million transactions.

  • Download customer_data.csv by running this command:
curl -L -o customer_data.csv "https://huggingface.co/datasets/Kengyla/cofinfad/resolve/main/customer_data.csv"
  • Download transactions_data.csv by running this command:
curl -L -o transactions_data.csv "https://huggingface.co/datasets/Kengyla/cofinfad/resolve/main/transactions_data.csv"

What is the COFINFAD dataset?

COFINFAD stands for Colombian Fintech Financial Analytics Dataset. It contains demographic data for each customer plus their individual transaction records from January to December 2023.

The customer_data.csv file has demographic and behavioral columns. The transactions_data.csv file has individual transaction records with customer_id, date, amount, and type.

  • Confirm both files downloaded by running:
ls *.csv

You should see customer_data.csv and transactions_data.csv listed in the output.

Files not appearing or download stalled?

The transactions_data.csv file is about 119 MB and may take a minute to download on slower connections. If curl is not found on Windows, use PowerShell's equivalent:

Invoke-WebRequest -Uri "https://huggingface.co/datasets/Kengyla/cofinfad/resolve/main/customer_data.csv" -OutFile customer_data.csv
Invoke-WebRequest -Uri "https://huggingface.co/datasets/Kengyla/cofinfad/resolve/main/transactions_data.csv" -OutFile transactions_data.csv

help me download the COFINFAD CSV files from Hugging Face

Create the PostgreSQL database and verify the connection

Your analysis needs a dedicated database. You will write a short Python script that connects to PostgreSQL and creates the fintech_analytics database.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Select the fintech-cohort-analysis folder on your Desktop, then click Open.
  • Create a new file by clicking File then New File.
  • Save it as setup_db.py by pressing Cmd+S (macOS) or Ctrl+S (Windows) and typing the filename.
  • Copy the following code into setup_db.py:
import psycopg2
from psycopg2 import sql

# Connect to default postgres database to create our analytics database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="postgres",
    user="postgres",
    password="your-password-here"
)
conn.autocommit = True
cur = conn.cursor()

# Create the fintech_analytics database
cur.execute("DROP DATABASE IF EXISTS fintech_analytics")
cur.execute("CREATE DATABASE fintech_analytics")
print("Database 'fintech_analytics' created successfully.")

cur.close()
conn.close()

What does this code do?

  • psycopg2.connect() opens a connection to your local PostgreSQL server using the default postgres database.
  • conn.autocommit = True is required because you cannot create a database inside a transaction block.
  • DROP DATABASE IF EXISTS ensures a clean slate if you run the script more than once.
  • CREATE DATABASE fintech_analytics creates your new empty database ready for data.

Replace your-password-here with the password you set when you installed PostgreSQL. If you never set one, try postgres as the password.

  • Save setup_db.py.
  • Return to your terminal and run the script:
python3 setup_db.py

You should see the message: Database 'fintech_analytics' created successfully.

Seeing a connection error?

If you see "connection refused", make sure PostgreSQL is running. On macOS, check if the PostgreSQL app is open or run brew services start postgresql. On Windows, open Services and confirm the PostgreSQL service is started.

If you see "authentication failed", double-check the password in your script matches your PostgreSQL password.

help me fix my PostgreSQL connection error in setup_db.py

✔️ Awesome, I've got everything!

Great. Double check you have saved setup_db.py and confirmed the database was created.

ⓧ I'd like to double check the full code

Here is the complete setup_db.py file:

import psycopg2
from psycopg2 import sql

# Connect to default postgres database to create our analytics database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="postgres",
    user="postgres",
    password="your-password-here"
)
conn.autocommit = True
cur = conn.cursor()

# Create the fintech_analytics database
cur.execute("DROP DATABASE IF EXISTS fintech_analytics")
cur.execute("CREATE DATABASE fintech_analytics")
print("Database 'fintech_analytics' created successfully.")

cur.close()
conn.close()

Your environment is ready with all the tools you need. Next up, you will load the CSV data into PostgreSQL tables and create a clean analytics view.

Load and Clean the Data in PostgreSQL

Your empty fintech_analytics database is ready and waiting.

In this step, you will create tables to hold the COFINFAD dataset, bulk-load 48,723 customers and over 3 million transactions from your CSV files, and then create a clean analytics view that filters out invalid records before any analysis begins.

In this step, get ready to:
  • Create tables and load the CSV data into PostgreSQL.
  • Create an analytics view that filters out invalid records.
  • Verify the cleaned data with summary queries.
Create tables and load the CSV data

Before you can query any data, it needs to live inside PostgreSQL tables with defined column types.

You will write a Python script that creates the table schemas and then uses PostgreSQL's COPY command to bulk-load the CSV data.

  • Create a new file called load_data.py in your project directory.
  • Add the database connection and table creation code by pasting the following:
import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="fintech_analytics",
    user="postgres",
    password="your-password-here"
)
cur = conn.cursor()

# Create customers table
cur.execute("""
    CREATE TABLE IF NOT EXISTS customers (
        customer_id INTEGER PRIMARY KEY,
        age INTEGER,
        gender VARCHAR(20),
        location VARCHAR(100),
        income_bracket VARCHAR(50),
        acquisition_channel VARCHAR(50),
        customer_segment VARCHAR(50),
        active_products INTEGER,
        app_logins_frequency INTEGER,
        credit_utilization_ratio FLOAT,
        tx_count INTEGER,
        avg_tx_value FLOAT,
        total_tx_volume FLOAT,
        first_tx DATE,
        last_tx DATE,
        customer_tenure FLOAT,
        churn_probability FLOAT,
        customer_lifetime_value FLOAT
    )
""")

# Create transactions table
cur.execute("""
    CREATE TABLE IF NOT EXISTS transactions (
        customer_id INTEGER,
        date DATE,
        amount FLOAT,
        type VARCHAR(50)
    )
""")

conn.commit()

What does this code do?

The script connects to your fintech_analytics database using psycopg2, the Python adapter for PostgreSQL.

It then creates two tables. The customers table has 18 columns covering demographics, behavior, and lifetime value metrics. The transactions table stores each individual transaction with its customer, date, amount, and type.

  • You won't see a change yet. The next chunk loads the actual data from CSV.
  • Below the conn.commit() line, add the CSV loading and verification code:
# Load data from CSV files
with open("customer_data.csv", "r") as f:
    next(f)  # Skip header
    cur.copy_expert(
        "COPY customers(customer_id, age, gender, location, income_bracket, "
        "acquisition_channel, customer_segment, active_products, "
        "app_logins_frequency, credit_utilization_ratio, tx_count, "
        "avg_tx_value, total_tx_volume, first_tx, last_tx, customer_tenure, "
        "churn_probability, customer_lifetime_value) "
        "FROM STDIN WITH CSV",
        f
    )

with open("transactions_data.csv", "r") as f:
    next(f)  # Skip header
    cur.copy_expert(
        "COPY transactions(customer_id, date, amount, type) "
        "FROM STDIN WITH CSV",
        f
    )

conn.commit()

# Verify load
cur.execute("SELECT COUNT(*) FROM customers")
print(f"Customers loaded: {cur.fetchone()[0]}")

cur.execute("SELECT COUNT(*) FROM transactions")
print(f"Transactions loaded: {cur.fetchone()[0]}")

cur.close()
conn.close()

What does this code do?

The copy_expert method streams each CSV file directly into PostgreSQL using the COPY ... FROM STDIN command. This is far faster than inserting rows one by one.

The next(f) call skips the header row so PostgreSQL only receives data rows. After loading, the script prints the row counts to confirm everything landed.

  • Save load_data.py.
  • Run the script by executing this command in your terminal:
python load_data.py
  • You should see output showing the row counts for both tables (approximately 48,723 customers and over 3 million transactions).

Seeing a connection or permission error?

  • Check that your PostgreSQL server is running. If you stopped it since the last step, restart it.
  • Make sure you replaced your-password-here with your actual PostgreSQL password in load_data.py.
  • If you see a file-not-found error, confirm that customer_data.csv and transactions_data.csv are in the same folder as load_data.py.
  • Help me debug my load_data.py script

✔️ Awesome, I've got everything!

Great. Double check you saved load_data.py and your tables are loaded.

ⓧ I'd like to double check the full code

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="fintech_analytics",
    user="postgres",
    password="your-password-here"
)
cur = conn.cursor()

# Create customers table
cur.execute("""
    CREATE TABLE IF NOT EXISTS customers (
        customer_id INTEGER PRIMARY KEY,
        age INTEGER,
        gender VARCHAR(20),
        location VARCHAR(100),
        income_bracket VARCHAR(50),
        acquisition_channel VARCHAR(50),
        customer_segment VARCHAR(50),
        active_products INTEGER,
        app_logins_frequency INTEGER,
        credit_utilization_ratio FLOAT,
        tx_count INTEGER,
        avg_tx_value FLOAT,
        total_tx_volume FLOAT,
        first_tx DATE,
        last_tx DATE,
        customer_tenure FLOAT,
        churn_probability FLOAT,
        customer_lifetime_value FLOAT
    )
""")

# Create transactions table
cur.execute("""
    CREATE TABLE IF NOT EXISTS transactions (
        customer_id INTEGER,
        date DATE,
        amount FLOAT,
        type VARCHAR(50)
    )
""")

conn.commit()

# Load data from CSV files
with open("customer_data.csv", "r") as f:
    next(f)  # Skip header
    cur.copy_expert(
        "COPY customers(customer_id, age, gender, location, income_bracket, "
        "acquisition_channel, customer_segment, active_products, "
        "app_logins_frequency, credit_utilization_ratio, tx_count, "
        "avg_tx_value, total_tx_volume, first_tx, last_tx, customer_tenure, "
        "churn_probability, customer_lifetime_value) "
        "FROM STDIN WITH CSV",
        f
    )

with open("transactions_data.csv", "r") as f:
    next(f)  # Skip header
    cur.copy_expert(
        "COPY transactions(customer_id, date, amount, type) "
        "FROM STDIN WITH CSV",
        f
    )

conn.commit()

# Verify load
cur.execute("SELECT COUNT(*) FROM customers")
print(f"Customers loaded: {cur.fetchone()[0]}")

cur.execute("SELECT COUNT(*) FROM transactions")
print(f"Transactions loaded: {cur.fetchone()[0]}")

cur.close()
conn.close()
Create a clean analytics view

Raw transaction data often contains invalid records. Rows with missing customer IDs, zero-value amounts, or null dates would skew your cohort analysis.

A database view lets you define a reusable filter once, then query it like a regular table without duplicating the data.

  • Create a new file called create_views.sql in your project directory.
  • Add the view definition by pasting the following:
-- Clean transactions view: filter invalid records
CREATE OR REPLACE VIEW clean_transactions AS
SELECT
    customer_id,
    date,
    amount,
    type
FROM transactions
WHERE customer_id IS NOT NULL
  AND amount > 0
  AND date IS NOT NULL;

What does this view do?

The clean_transactions view selects only valid rows: those with a non-null customer_id, a positive amount, and a non-null date.

Every query you write in later steps will reference clean_transactions instead of the raw transactions table. This keeps your analytics queries free of data-quality checks.

  • You won't see a change yet. The next substep adds the verification query and runs the file.
Verify the cleaned data with summary queries

After creating the view, you need to confirm the data looks right. A quick summary query will show you the total transactions, customer count, date range, and average value.

  • Below the CREATE OR REPLACE VIEW statement in create_views.sql, add the verification query:
-- Verify the clean view
SELECT
    COUNT(*) AS total_transactions,
    COUNT(DISTINCT customer_id) AS unique_customers,
    MIN(date) AS earliest_date,
    MAX(date) AS latest_date,
    ROUND(AVG(amount)::numeric, 2) AS avg_transaction_value
FROM clean_transactions;

What does this query check?

  • Total transactions tells you how many valid records remain after filtering.
  • Unique customers confirms the breadth of your dataset.
  • The date range (earliest to latest) confirms the time span of the data (January to December 2023).
  • Average transaction value gives you a quick sanity check on the amounts.

✔️ Awesome, I've got everything!

Great. Double check you saved create_views.sql with both the view and the verification query.

ⓧ I'd like to double check the full code

-- Clean transactions view: filter invalid records
CREATE OR REPLACE VIEW clean_transactions AS
SELECT
    customer_id,
    date,
    amount,
    type
FROM transactions
WHERE customer_id IS NOT NULL
  AND amount > 0
  AND date IS NOT NULL;

-- Verify the clean view
SELECT
    COUNT(*) AS total_transactions,
    COUNT(DISTINCT customer_id) AS unique_customers,
    MIN(date) AS earliest_date,
    MAX(date) AS latest_date,
    ROUND(AVG(amount)::numeric, 2) AS avg_transaction_value
FROM clean_transactions;
  • Save create_views.sql.
  • Run the SQL file against your database by executing this command:
psql -d fintech_analytics -U postgres -f create_views.sql

What does this command do?

The psql tool is PostgreSQL's command-line client. The -d flag specifies the database to connect to, -U specifies the username, and -f tells it to execute the commands from a file.

  • You should see a results table with columns for total_transactions, unique_customers, earliest_date, latest_date, and avg_transaction_value.
  • Confirm the date range spans from early 2023 to late 2023, and that the customer count is in the tens of thousands.

Seeing a "relation does not exist" error?

  • Make sure you ran load_data.py successfully in the previous substep. The view depends on the transactions table existing first.
  • If psql prompts for a password, enter your PostgreSQL password and press Enter.
  • On Windows, if psql is not recognized, make sure the PostgreSQL bin directory is in your system PATH.
  • Help me troubleshoot running my SQL file in psql

Your data is loaded and cleaned. Next up, you will write the cohort retention SQL that turns this raw transaction data into month-over-month retention rates.

Build Cohort Retention SQL with CTEs and Window Functions

Your database is loaded with clean fintech transaction data. Now you can answer the core business question: which customer cohorts stick around, and which ones churn?

Cohort analysis groups customers by the month they first transacted, then tracks how many remain active in each subsequent month. In this step, you will write a single SQL query using CTEs (Common Table Expressions) and window functions to compute month-over-month retention for every signup cohort.

In this step, get ready to:
  • Write a CTE that assigns each customer to their first transaction month cohort.
  • Write a second CTE that calculates the cohort index and counts active customers per month.
  • Use the FIRST_VALUE window function to calculate retention percentages and save the query.
Write the customer cohorts CTE

The first building block of cohort analysis is assigning each customer to a cohort. A cohort is defined by the month a customer made their very first transaction.

  • Create a new file called cohort_retention.sql in your project directory.
  • Paste the following code into the file:
-- Cohort Retention Analysis using CTEs and Window Functions
WITH customer_cohorts AS (
    -- Assign each customer to their first transaction month cohort
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(date))::DATE AS cohort_month
    FROM clean_transactions
    GROUP BY customer_id
),

What does this code do?

This CTE groups all transactions by customer_id and finds their earliest transaction date using MIN(date). Then DATE_TRUNC('month', ...) truncates that date to the first of the month.

The result is one row per customer with their cohort_month. A customer whose first transaction was on March 15 gets assigned to the 2023-03-01 cohort.

You will not see a change yet. The next chunk completes the query so it can run.

Write the cohort activity CTE

Now you need to track what each cohort does over time. The second CTE joins transactions back to cohorts and calculates how many months have passed since each customer's first transaction.

  • In cohort_retention.sql, add the following code directly below the trailing comma of the first CTE:
cohort_activity AS (
    -- Track monthly activity for each cohort
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.date)::DATE AS activity_month,
        (EXTRACT(YEAR FROM DATE_TRUNC('month', t.date)) - EXTRACT(YEAR FROM c.cohort_month)) * 12
        + (EXTRACT(MONTH FROM DATE_TRUNC('month', t.date)) - EXTRACT(MONTH FROM c.cohort_month)) AS cohort_index,
        COUNT(DISTINCT t.customer_id) AS active_customers,
        SUM(t.amount) AS revenue
    FROM clean_transactions t
    JOIN customer_cohorts c ON t.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.date)::DATE, cohort_index
)

What does this code do?

This CTE joins every transaction to its customer's cohort and calculates cohort_index. The cohort index is the number of months between the customer's first transaction month and the current transaction month. Month 0 means the customer transacted in their signup month, Month 1 means one month later, and so on.

It also counts distinct active_customers and sums revenue for each cohort at each month index. This gives you the raw numbers you need for retention calculation.

You will not see a change yet. The final SELECT in the next chunk makes this query runnable.

Add the retention calculation with FIRST_VALUE

The last piece is the final SELECT that turns raw counts into retention percentages. The FIRST_VALUE window function grabs the cohort size at Month 0, so you can express each subsequent month as a percentage of the original group.

  • In cohort_retention.sql, add the following code directly below the closing parenthesis of the cohort_activity CTE:
-- Calculate retention percentage using FIRST_VALUE window function
SELECT
    cohort_month,
    cohort_index,
    active_customers,
    revenue,
    FIRST_VALUE(active_customers) OVER (
        PARTITION BY cohort_month ORDER BY cohort_index
    ) AS cohort_size,
    ROUND(
        100.0 * active_customers /
        FIRST_VALUE(active_customers) OVER (
            PARTITION BY cohort_month ORDER BY cohort_index
        ), 2
    ) AS retention_pct,
    ROUND((revenue / NULLIF(active_customers, 0))::numeric, 2) AS revenue_per_customer
FROM cohort_activity
ORDER BY cohort_month, cohort_index;

What does this code do?

FIRST_VALUE(active_customers) OVER (PARTITION BY cohort_month ORDER BY cohort_index) looks at all rows for a given cohort and returns the value from cohort_index = 0. That is the original cohort size.

Dividing each month's active_customers by that cohort size gives you the retention percentage. NULLIF(active_customers, 0) prevents division-by-zero errors in the revenue-per-customer calculation.

  • Save cohort_retention.sql.
  • Run the query against your database by executing this command:
psql -d fintech_analytics -U postgres -f cohort_retention.sql

You should see a table of results with columns including cohort_month, cohort_index, active_customers, retention_pct, and revenue_per_customer. Each row represents one cohort at one month offset.

Seeing a "relation clean_transactions does not exist" error?

The clean_transactions view from the previous step must exist in your fintech_analytics database. Switch back to the create_views.sql file and re-run it to create the view.

Also confirm you are connecting to the correct database. The -d fintech_analytics flag targets your analytics database specifically.

Why is my cohort retention query failing?

✔️ Awesome, I've got everything!

Great. Double check you have saved cohort_retention.sql in your project directory.

ⓧ I'd like to double check the full code

-- Cohort Retention Analysis using CTEs and Window Functions
WITH customer_cohorts AS (
    -- Assign each customer to their first transaction month cohort
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(date))::DATE AS cohort_month
    FROM clean_transactions
    GROUP BY customer_id
),
cohort_activity AS (
    -- Track monthly activity for each cohort
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.date)::DATE AS activity_month,
        (EXTRACT(YEAR FROM DATE_TRUNC('month', t.date)) - EXTRACT(YEAR FROM c.cohort_month)) * 12
        + (EXTRACT(MONTH FROM DATE_TRUNC('month', t.date)) - EXTRACT(MONTH FROM c.cohort_month)) AS cohort_index,
        COUNT(DISTINCT t.customer_id) AS active_customers,
        SUM(t.amount) AS revenue
    FROM clean_transactions t
    JOIN customer_cohorts c ON t.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.date)::DATE, cohort_index
)
-- Calculate retention percentage using FIRST_VALUE window function
SELECT
    cohort_month,
    cohort_index,
    active_customers,
    revenue,
    FIRST_VALUE(active_customers) OVER (
        PARTITION BY cohort_month ORDER BY cohort_index
    ) AS cohort_size,
    ROUND(
        100.0 * active_customers /
        FIRST_VALUE(active_customers) OVER (
            PARTITION BY cohort_month ORDER BY cohort_index
        ), 2
    ) AS retention_pct,
    ROUND((revenue / NULLIF(active_customers, 0))::numeric, 2) AS revenue_per_customer
FROM cohort_activity
ORDER BY cohort_month, cohort_index;

Why CTEs instead of subqueries?

CTEs let you name each logical step of your analysis and reference it later. This makes complex queries readable and debuggable. You can test each CTE independently by turning it into a standalone query.

Hiring managers reviewing portfolio SQL look for CTE fluency as a signal that the candidate can write production-quality analytics code.

Your cohort retention query is working. Next up, you will extend it to compute revenue metrics and month-over-month growth using the LAG window function.

Compute Revenue Metrics by Cohort

Your retention query already tracks how many customers stay active each month. The next question is: how much money does each cohort actually generate over time?

In this step, you'll write a new SQL query that uses the LAG() window function to measure month-over-month revenue changes. Then you'll export both your retention and revenue results to CSV files that Python can visualize.

In this step, get ready to:
  • Write a revenue growth query using the LAG() window function.
  • Export both cohort result sets to CSV files for visualization.
Review the revenue metrics in your retention query

Your cohort_retention.sql from the previous step already calculates two revenue metrics for each cohort-month combination.

  • Open cohort_retention.sql in VS Code.
  • Notice the SUM(t.amount) AS revenue line inside the cohort_activity CTE. This totals all transaction amounts for each cohort in each month.
  • Notice the revenue_per_customer column in the final SELECT. This divides total revenue by active customers to show average spend.

Why track revenue per customer?

Total revenue alone can be misleading. A cohort might generate less revenue simply because fewer customers are left. Revenue per customer tells you whether the remaining customers are spending more or less over time.

These metrics tell you how much each cohort is worth. Next, you'll build a query that tracks how revenue changes month over month.

Write the revenue growth query with LAG()

Knowing total revenue per month is useful, but spotting trends requires comparing each month to the previous one. The LAG() window function lets you look back one row within each cohort's timeline to compute month-over-month growth.

  • Create a new file called revenue_growth.sql in your project directory.
  • Paste the following query into revenue_growth.sql:
-- Month-over-month revenue growth by cohort using LAG
WITH customer_cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(date))::DATE AS cohort_month
    FROM clean_transactions
    GROUP BY customer_id
),
monthly_revenue AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.date)::DATE AS activity_month,
        SUM(t.amount) AS revenue
    FROM clean_transactions t
    JOIN customer_cohorts c ON t.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.date)::DATE
)
SELECT
    cohort_month,
    activity_month,
    revenue,
    LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month), 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY cohort_month, activity_month;

What does this query do?

This query uses two CTEs and the LAG() window function to track revenue momentum:

  • customer_cohorts assigns each customer to their first transaction month, the same logic as your retention query.
  • monthly_revenue sums total revenue per cohort per activity month. Unlike the retention query, this CTE focuses only on revenue totals without counting customers.
  • LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month) reaches back one row within the same cohort to grab the previous month's revenue.
  • mom_growth_pct calculates the percentage change between the current and previous month. NULLIF prevents division-by-zero errors when there is no prior month.
  • Save revenue_growth.sql.
  • Test the query by running it in psql. Connect to your database by running:
psql -U postgres -d fintech_analytics
  • Run the contents of your SQL file by entering:
\i revenue_growth.sql

You should see rows with columns cohort_month, activity_month, revenue, prev_month_revenue, and mom_growth_pct. The first row of each cohort will have NULL for prev_month_revenue since there is no earlier month to compare against.

Seeing an error about clean_transactions?

Make sure you created the clean_transactions view in Step 2. You can re-run it by entering \i create_views.sql in psql.

If you see a syntax error, check that you copied the full query including the closing semicolon.

Help me debug my revenue growth SQL query.

  • Exit psql by typing \q and pressing Enter.

✔️ Awesome, I've got everything!

Great. Double check you have saved revenue_growth.sql in your project directory.

ⓧ I'd like to double check the full code

-- Month-over-month revenue growth by cohort using LAG
WITH customer_cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(date))::DATE AS cohort_month
    FROM clean_transactions
    GROUP BY customer_id
),
monthly_revenue AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', t.date)::DATE AS activity_month,
        SUM(t.amount) AS revenue
    FROM clean_transactions t
    JOIN customer_cohorts c ON t.customer_id = c.customer_id
    GROUP BY c.cohort_month, DATE_TRUNC('month', t.date)::DATE
)
SELECT
    cohort_month,
    activity_month,
    revenue,
    LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month))
        / NULLIF(LAG(revenue) OVER (PARTITION BY cohort_month ORDER BY activity_month), 0),
        2
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY cohort_month, activity_month;
Export both result sets to CSV

Your visualization script in the next step will read directly from the database. Exporting to CSV now gives you a quick sanity check and a portable copy of both analyses.

  • Create a new file called export_data.py in your project directory.
  • Paste the following Python script into export_data.py:
import psycopg2
import pandas as pd

# Connect to the fintech_analytics database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="fintech_analytics",
    user="postgres",
    password="your-password-here"
)

# Export cohort retention results to CSV
with open("cohort_retention.sql", "r") as f:
    retention_query = f.read()

df_retention = pd.read_sql(retention_query, conn)
df_retention.to_csv("cohort_retention.csv", index=False)
print(f"Exported cohort_retention.csv: {len(df_retention)} rows")

# Export revenue growth results to CSV
with open("revenue_growth.sql", "r") as f:
    growth_query = f.read()

df_growth = pd.read_sql(growth_query, conn)
df_growth.to_csv("revenue_growth.csv", index=False)
print(f"Exported revenue_growth.csv: {len(df_growth)} rows")

conn.close()
print("\nExport complete! Both CSV files are ready for visualization.")

What does this script do?

  • It reads each .sql file as a string, then passes the query to pd.read_sql() which runs it against PostgreSQL and returns a DataFrame.
  • to_csv() writes each DataFrame to a CSV file in your project directory. The index=False argument prevents pandas from adding an extra row-number column.
  • Save export_data.py.
  • Run the export script by running:
python export_data.py

You should see output confirming the row counts for both CSV files.

  • Verify the files were created by running:
ls *.csv

You should see cohort_retention.csv and revenue_growth.csv listed alongside your original data files.

Seeing a connection refused error?

Make sure your PostgreSQL server is still running. If you restarted your computer since Step 1, you may need to start the service again.

If you see a file not found error for one of the .sql files, make sure you are running the script from your project directory where both SQL files live.

Help me debug my export_data.py script.

You now have both your retention and revenue analyses saved as CSV files. Next up, you'll load these results into Python and produce a retention heatmap and revenue chart that tell the story visually.

Visualize Retention Heatmap and Revenue Curves

Your SQL queries produced clean retention percentages and revenue metrics for every cohort. Numbers in a table tell a story, but a visual makes that story impossible to miss.

In this step, you will pull your SQL results into pandas, reshape them into pivot tables, and generate two portfolio-grade visualizations. You will also write a README.md that frames your findings as a business case.

In this step, get ready to:
  • Generate a retention heatmap from your cohort data using seaborn.
  • Plot revenue curves by cohort using matplotlib.
  • Write a portfolio-ready README with business framing and recommendations.
Create the retention heatmap

A heatmap encodes retention percentages as colors, making it instantly clear where customers drop off. You will build a Python script that connects to your database, re-runs your cohort query, and produces this visualization.

  • Create a new file called analyze_and_visualize.py in your project directory.
  • Add the imports and database connection by pasting this code:
import psycopg2
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Connect to database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="fintech_analytics",
    user="postgres",
    password="your-password-here"
)

# Load cohort retention query
with open("cohort_retention.sql", "r") as f:
    retention_query = f.read()

df_retention = pd.read_sql(retention_query, conn)

What does this code do?

Instead of loading the exported CSV, this script reads your cohort_retention.sql file and executes it directly against the database using pd.read_sql(). This guarantees the DataFrame always reflects the latest data.

The result is a DataFrame called df_retention containing columns like cohort_month, cohort_index, and retention_pct ready for visualization.

  • Now add the pivot table and heatmap generation below the existing code:
# Pivot for heatmap: cohort_month as rows, cohort_index as columns, retention_pct as values
pivot_retention = df_retention.pivot_table(
    index="cohort_month",
    columns="cohort_index",
    values="retention_pct"
)

# Format index for readability
pivot_retention.index = pd.to_datetime(pivot_retention.index).strftime("%Y-%m")

# Create retention heatmap
plt.figure(figsize=(14, 8))
sns.heatmap(
    pivot_retention,
    annot=True,
    fmt=".0f",
    cmap="YlOrRd_r",
    vmin=0,
    vmax=100,
    linewidths=0.5,
    cbar_kws={"label": "Retention %"}
)
plt.title("Monthly Cohort Retention Heatmap (Fintech Transactions)", fontsize=14)
plt.xlabel("Months Since First Transaction")
plt.ylabel("Cohort (First Transaction Month)")
plt.tight_layout()
plt.savefig("retention_heatmap.png", dpi=150)
plt.close()
print("Saved: retention_heatmap.png")

What does this code do?

  • pivot_table() reshapes the flat DataFrame into a matrix where each row is a cohort month, each column is a month index, and each cell is a retention percentage.
  • The YlOrRd_r colormap makes high retention green and low retention red, so problem areas pop out immediately.
  • annot=True prints the actual number in every cell, so readers can get exact values alongside the color signal.
  • Save analyze_and_visualize.py.

You will not run the script yet. The revenue chart comes next, and you will run everything together.

Plot monthly revenue curves by cohort

A line chart shows how revenue evolves over time for each cohort. Plotting multiple cohorts on the same axes reveals whether early cohorts generate disproportionate value.

  • Add the revenue chart and connection cleanup below the heatmap code in analyze_and_visualize.py:
# Revenue by cohort line chart
pivot_revenue = df_retention.pivot_table(
    index="cohort_index",
    columns="cohort_month",
    values="revenue"
)

plt.figure(figsize=(12, 6))
for col in pivot_revenue.columns[:6]:  # Plot first 6 cohorts for clarity
    label = pd.to_datetime(col).strftime("%Y-%m")
    plt.plot(pivot_revenue.index, pivot_revenue[col], marker="o", markersize=3, label=label)

plt.title("Revenue by Cohort Over Time", fontsize=14)
plt.xlabel("Months Since First Transaction")
plt.ylabel("Revenue (COP)")
plt.legend(title="Cohort", bbox_to_anchor=(1.05, 1), loc="upper left")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig("revenue_by_cohort.png", dpi=150)
plt.close()
print("Saved: revenue_by_cohort.png")

conn.close()
print("\nAnalysis complete! Check retention_heatmap.png and revenue_by_cohort.png")

What does this code do?

  • pivot_revenue.columns[:6] limits the chart to the first 6 cohorts. Plotting all 12 would clutter the legend and make lines hard to distinguish.
  • Each cohort gets its own line with circle markers, making it easy to compare revenue trajectories side by side.
  • conn.close() releases the database connection after both visualizations are complete.
  • Save analyze_and_visualize.py.
  • Run the script by executing this command in your terminal:
python analyze_and_visualize.py
  • Confirm the output files exist by running:
ls *.png

You should see retention_heatmap.png and revenue_by_cohort.png in the output.

Seeing a connection error or empty chart?

  • If you see a connection refused error, make sure your PostgreSQL server is running.
  • If the heatmap is blank or shows no annotations, verify that your clean_transactions view exists in the database by re-running create_views.sql.
  • Double check that cohort_retention.sql is in the same directory as analyze_and_visualize.py.

Help me debug my analyze_and_visualize.py script

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

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

# Connect to database
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="fintech_analytics",
    user="postgres",
    password="your-password-here"
)

# Load cohort retention query
with open("cohort_retention.sql", "r") as f:
    retention_query = f.read()

df_retention = pd.read_sql(retention_query, conn)

# Pivot for heatmap: cohort_month as rows, cohort_index as columns, retention_pct as values
pivot_retention = df_retention.pivot_table(
    index="cohort_month",
    columns="cohort_index",
    values="retention_pct"
)

# Format index for readability
pivot_retention.index = pd.to_datetime(pivot_retention.index).strftime("%Y-%m")

# Create retention heatmap
plt.figure(figsize=(14, 8))
sns.heatmap(
    pivot_retention,
    annot=True,
    fmt=".0f",
    cmap="YlOrRd_r",
    vmin=0,
    vmax=100,
    linewidths=0.5,
    cbar_kws={"label": "Retention %"}
)
plt.title("Monthly Cohort Retention Heatmap (Fintech Transactions)", fontsize=14)
plt.xlabel("Months Since First Transaction")
plt.ylabel("Cohort (First Transaction Month)")
plt.tight_layout()
plt.savefig("retention_heatmap.png", dpi=150)
plt.close()
print("Saved: retention_heatmap.png")

# Revenue by cohort line chart
pivot_revenue = df_retention.pivot_table(
    index="cohort_index",
    columns="cohort_month",
    values="revenue"
)

plt.figure(figsize=(12, 6))
for col in pivot_revenue.columns[:6]:  # Plot first 6 cohorts for clarity
    label = pd.to_datetime(col).strftime("%Y-%m")
    plt.plot(pivot_revenue.index, pivot_revenue[col], marker="o", markersize=3, label=label)

plt.title("Revenue by Cohort Over Time", fontsize=14)
plt.xlabel("Months Since First Transaction")
plt.ylabel("Revenue (COP)")
plt.legend(title="Cohort", bbox_to_anchor=(1.05, 1), loc="upper left")
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.savefig("revenue_by_cohort.png", dpi=150)
plt.close()
print("Saved: revenue_by_cohort.png")

conn.close()
print("\nAnalysis complete! Check retention_heatmap.png and revenue_by_cohort.png")
Write a portfolio README with business framing

Hiring managers scan a README before they look at code. A strong README states the business question, summarizes findings, and makes a recommendation.

  • Create a new file called README.md in your project directory and paste the following content:
# Fintech Cohort Retention & Revenue Analysis

## Business Question

**Which customer cohorts retain best, what drives churn, and where is revenue leaking?**

This analysis examines 12 months of transaction data from a Colombian fintech company (48,723 customers, 3.1M+ transactions) to understand customer retention dynamics and identify intervention opportunities.

## Key Findings

- **Month 1 Drop-off**: Retention drops significantly after the first month across all cohorts, indicating onboarding is the critical retention window.
- **Revenue Concentration**: Early cohorts generate disproportionate revenue due to compounding retention advantages.
- **Seasonal Patterns**: Retention rates show variation by cohort start month, suggesting acquisition channel quality differs by period.

## Methodology

1. **Data Loading**: Raw CSV loaded into PostgreSQL with a clean analytics view filtering invalid records.
2. **Cohort Assignment**: Customers grouped by first transaction month using CTEs.
3. **Retention Calculation**: Month-over-month retention computed using FIRST_VALUE window function.
4. **Revenue Analysis**: Revenue per cohort tracked with LAG for growth rates.
5. **Visualization**: Retention heatmap and revenue curves generated with Python (matplotlib + seaborn).

## Visualizations

### Retention Heatmap
![Retention Heatmap](retention_heatmap.png)

### Revenue by Cohort
![Revenue by Cohort](revenue_by_cohort.png)

## Recommendation

Focus retention efforts on the first 30 days post-signup. A targeted onboarding campaign (push notifications, feature tutorials, first-transaction incentives) could lift Month 1 retention by even a few percentage points, compounding into significant revenue over the customer lifecycle.

## Tech Stack

- **Database**: PostgreSQL
- **Language**: Python 3, SQL
- **Libraries**: pandas, matplotlib, seaborn, psycopg2
- **Techniques**: CTEs, Window Functions (FIRST_VALUE, LAG, NTILE), Cohort Analysis, RFM Segmentation

## How to Run

1. Install dependencies: `pip install psycopg2-binary pandas matplotlib seaborn`
2. Download the COFINFAD dataset from Hugging Face
3. Run `python setup_db.py` to create the database
4. Run `python load_data.py` to load the data
5. Execute SQL files in PostgreSQL to create views
6. Run `python analyze_and_visualize.py` to generate charts

Why structure a README this way?

The README follows a pattern that mirrors how analysts present findings at work: business question first, key findings up front, methodology for credibility, and a concrete recommendation at the end.

Notice the findings reference patterns you can verify from your heatmap. After running the script, you can update these with the specific numbers your data reveals.

  • Save README.md.
  • Confirm the file exists by running:
ls README.md

You should see README.md listed in the output.

README not rendering properly on GitHub?

  • Make sure the image file names in the markdown exactly match the PNG filenames your script produces. They are case-sensitive.
  • Verify that retention_heatmap.png and revenue_by_cohort.png are in the same directory as README.md.

Help me fix my README image links

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

# Fintech Cohort Retention & Revenue Analysis

## Business Question

**Which customer cohorts retain best, what drives churn, and where is revenue leaking?**

This analysis examines 12 months of transaction data from a Colombian fintech company (48,723 customers, 3.1M+ transactions) to understand customer retention dynamics and identify intervention opportunities.

## Key Findings

- **Month 1 Drop-off**: Retention drops significantly after the first month across all cohorts, indicating onboarding is the critical retention window.
- **Revenue Concentration**: Early cohorts generate disproportionate revenue due to compounding retention advantages.
- **Seasonal Patterns**: Retention rates show variation by cohort start month, suggesting acquisition channel quality differs by period.

## Methodology

1. **Data Loading**: Raw CSV loaded into PostgreSQL with a clean analytics view filtering invalid records.
2. **Cohort Assignment**: Customers grouped by first transaction month using CTEs.
3. **Retention Calculation**: Month-over-month retention computed using FIRST_VALUE window function.
4. **Revenue Analysis**: Revenue per cohort tracked with LAG for growth rates.
5. **Visualization**: Retention heatmap and revenue curves generated with Python (matplotlib + seaborn).

## Visualizations

### Retention Heatmap
![Retention Heatmap](retention_heatmap.png)

### Revenue by Cohort
![Revenue by Cohort](revenue_by_cohort.png)

## Recommendation

Focus retention efforts on the first 30 days post-signup. A targeted onboarding campaign (push notifications, feature tutorials, first-transaction incentives) could lift Month 1 retention by even a few percentage points, compounding into significant revenue over the customer lifecycle.

## Tech Stack

- **Database**: PostgreSQL
- **Language**: Python 3, SQL
- **Libraries**: pandas, matplotlib, seaborn, psycopg2
- **Techniques**: CTEs, Window Functions (FIRST_VALUE, LAG, NTILE), Cohort Analysis, RFM Segmentation

## How to Run

1. Install dependencies: `pip install psycopg2-binary pandas matplotlib seaborn`
2. Download the COFINFAD dataset from Hugging Face
3. Run `python setup_db.py` to create the database
4. Run `python load_data.py` to load the data
5. Execute SQL files in PostgreSQL to create views
6. Run `python analyze_and_visualize.py` to generate charts

You now have a complete, portfolio-ready analysis project. Your heatmap reveals the retention story at a glance, your revenue chart shows where money concentrates, and your README frames both as a business case. Next up, you will tackle the Secret Mission and add RFM segmentation to identify which customers need attention the most.

Secret mission

RFM Segmentation and Churn Risk

Your cohort analysis shows when customers leave. But which customers are most valuable and most at risk? Build an RFM segmentation layer to identify exactly who needs a re-engagement campaign before they churn.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing cloud costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • PostgreSQL database fintech_analytics with tables and views.
  • Project directory on your Desktop containing Python scripts, SQL files, CSV data files, and PNG visualizations.
  • Python virtual environment with installed packages (psycopg2-binary, pandas, matplotlib, seaborn).

Keep everything running

No action needed. Choose this if you are still actively building on your analysis or plan to extend it further.

  • Your fintech_analytics database stays available for new queries.
  • Your Python scripts and visualizations remain in your project directory on the Desktop.
  • Your virtual environment stays ready for additional analysis work.
  • PostgreSQL continues running as a background service on your machine.

Pause - I'll come back to this later

Shut down running processes to free up system memory while keeping your files and database intact.

  • Deactivate your virtual environment by running:
deactivate
  • Stop the PostgreSQL service to free memory. On macOS (if installed via Homebrew):
brew services stop postgresql
  • On Windows, open the Services app, find PostgreSQL, and click Stop.
  • When you come back, restart PostgreSQL and reactivate your virtual environment to pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your analysis work.

  • Drop the PostgreSQL database by opening your terminal and running:
psql -U postgres -c "DROP DATABASE fintech_analytics;"
  • Delete your project directory. On macOS/Linux:
cd ~/Desktop
rm -rf fintech-cohort-analysis
  • On Windows:
cd ~\Desktop
rmdir /s /q fintech-cohort-analysis
  • This removes all Python scripts, SQL files, CSV data, PNG visualizations, the virtual environment, and the README.md.

Nice Work!

Nice Work!

Nice work! You just built a complete fintech cohort retention and revenue analysis, taking 3.1M real transactions from raw CSV files all the way to portfolio-ready visualizations and business recommendations.

You've learned how to:

  • Load and clean real-world fintech data in PostgreSQL, building an analytics view that filters invalid records before analysis begins.
  • Write production-quality SQL using CTEs and window functions (FIRST_VALUE, LAG) to compute month-over-month cohort retention rates and revenue growth metrics.
  • Produce a retention heatmap and revenue-by-cohort chart in Python, then frame findings as a business case with actionable recommendations in a portfolio-ready README.
  • Added RFM segmentation using NTILE window functions to identify at-risk customer segments and recommend targeted re-engagement campaigns.

Ready to quiz yourself?