Customer Behavior Analytics Pipeline

Build a Python ETL pipeline merging Redshift and SQLite data for analytics.

Introduction

30 Second Summary

Every business collects customer data, but it usually ends up scattered across different systems. Sales live in one database. Clicks and email opens live in another. The real question is whether all that engagement actually translates into higher spending, or whether it is just noise.

In this project, you will build a Python analytics pipeline that pulls transactional data from Amazon Redshift, generates behavioral data in a local SQLite database, merges both into a unified customer profile, runs statistical hypothesis tests, and visualizes the results in an interactive Amazon QuickSight dashboard.

What You'll Build

When you run your finished pipeline, your terminal prints extraction progress from two databases, transformation summaries, and a final analysis report with p-values and effect sizes. Then you open your QuickSight dashboard and explore customer spending interactively by category, region, and engagement level.

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

A single Python script you can run to extract data from two different database technologies, merge them by user ID, and write analytics-ready results back to Redshift.
Statistical output that tells you whether high-engagement customers genuinely spend more, with p-values and effect sizes that separate real patterns from random noise.
An interactive QuickSight dashboard where you can explore customer spending by region, category, and engagement level without writing any more code.
Secret Mission: Build an RFM customer segmentation model and add a segmentation heatmap to your QuickSight dashboard.

Are there any prerequisites?

You need an AWS account with Redshift Serverless free trial eligibility and basic Python familiarity. The project uses Python 3.10 and VS Code, which you should already have installed.

Before We Start

Before you set up any infrastructure or write any code, take a moment to lock in what you're about to build and why it matters.

Set Up Your Environment and Data Infrastructure

Your analytics pipeline needs to pull data from two different database technologies. The Python packages for database connectivity and statistics are already installed in your customer-analytics folder.

Now you need the cloud data warehouse running and loaded with sample sales data. In this step, you will set up Amazon Redshift Serverless with a pre-loaded sample dataset and verify that all your tools are connected.

In this step, get ready to:

Verify your Python packages can be imported successfully.
Create an Amazon Redshift Serverless workgroup loaded with sample sales data.
Set a daily usage limit to prevent unexpected charges.

Verify your Python packages

Your packages are installed, but a quick import check confirms everything is wired correctly before you depend on them later.

Switch back to your terminal in the customer-analytics folder from earlier.
Before you run the next command, predict: do you think all five imports will succeed, or will one fail?
Verify all packages load correctly by running this command:

python3 -c "import redshift_connector; import pandas; import numpy; import scipy; import sqlite3; print('All packages installed successfully')"

You should see All packages installed successfully printed in your terminal.

Seeing an ImportError?

If any package fails to import, re-run the install command from your customer-analytics folder:

pip install redshift-connector==2.1.15 pandas==2.3.3 numpy scipy

Make sure you are using the same Python interpreter for both install and import. If you have multiple Python versions, use pip3 instead of pip.

Help me debug my Python package import error.

Set up Redshift Serverless and load sample data

Amazon Redshift Serverless is a cloud data warehouse that scales automatically and charges only when queries run. It comes pre-loaded with a sample sales dataset called tickit that tracks ticket sales for a fictional event website.

Open your browser and sign in to the AWS Management Console at console.aws.amazon.com.
In the search bar at the top, type Redshift and select Amazon Redshift from the results.
Click Try Amazon Redshift Serverless free trial on the Redshift landing page.

What is the Redshift free trial?

AWS provides $300 in credits valid for 90 days to cover compute, storage, and snapshot usage on Redshift Serverless. This project stays well within those credits.

Keep the default settings on the configuration page. The defaults create a workgroup and namespace with appropriate capacity for this project.
Scroll to the bottom and click Save configuration.
Wait 2-3 minutes for the workgroup status to show Available. You can refresh the page to check.

Once the workgroup is available, the sample_data_dev database with the tickit schema is already accessible. No data loading is required.

Click Query data in the Serverless dashboard to open Query Editor v2.
In the left panel of Query Editor v2, expand the sample_data_dev database.
Expand the tickit schema. You should see tables including users, sales, and event.

Run a quick test query to confirm the data is there. Paste this into the query editor and click Run:

SELECT COUNT(*) FROM tickit.sales;

You should see a result showing approximately 172,000 rows. This confirms the tickit sales data is accessible.

What is the tickit dataset?

Tickit is a seven-table schema that tracks sales activity for a fictional ticket website. Users buy and sell tickets for sporting events, shows, and concerts. The tables include users, venue, category, date, event, listing, and sales.

Your pipeline will join the sales, event, and users tables to build user-level transaction aggregates.

Don't see sample_data_dev?

If the sample_data_dev database does not appear in the left panel, make sure you connected to your Serverless workgroup in Query Editor v2. Click the connection icon (database with a plug) in the left panel and select your workgroup.

If your workgroup still shows as Creating, wait another minute and refresh.

Help me find the sample_data_dev database in Query Editor v2.

Set a daily usage limit to prevent unexpected charges

Redshift Serverless auto-scales compute resources when queries run. A usage limit ensures the workgroup stops processing queries if it exceeds a threshold you define.

Return to the Redshift Serverless console (close or navigate back from Query Editor v2).
Click on your workgroup name to open its details page.
Select the Limits tab.
In the Usage limits section, click Manage usage limits.
Set Frequency to Daily.
Set the Usage limit (RPU hours) to 1.
Set Action to Turn off user queries.
Click Save changes.

You should see the usage limit appear in the Usage limits section confirming a Daily limit of 1 RPU hour is active.

Why set a usage limit?

Redshift Serverless charges per RPU-hour when queries are running. It can scale up beyond its base capacity during heavy loads and sometimes stay elevated briefly after load drops. A daily limit of 1 RPU hour is more than enough for this project's queries while protecting you from runaway costs.

Note down your Redshift connection details for later. You will need them when writing the pipeline scripts:

Host: Found on the workgroup details page under Endpoint. It follows the format your-workgroup.your-account.your-region.redshift-serverless.amazonaws.com.
Database: sample_data_dev (for reading tickit data) and dev (for writing pipeline results).
User: admin (the default admin user created during setup).
Password: The password you set (or was auto-generated) during workgroup creation.

Keep your credentials handy

Save these four values somewhere accessible. You will paste them into both generate_interactions.py and analytics_pipeline.py in the next steps.

Your cloud data warehouse is running with sample sales data loaded and a cost safety net in place. Next up, you will generate synthetic customer interaction data and load it into a local SQLite database to give your pipeline a second data source to merge.

Generate and Load Customer Interaction Data

Your environment is installed and your Redshift workgroup is loaded with the tickit sample dataset. Now you need a second data source to merge with it.

The tickit dataset has sales transactions, but no behavioral data like page views or email clicks. Without interaction events linked to the same user IDs, your pipeline would have nothing meaningful to merge across sources. In this step, you will write a Python script that pulls real user IDs from Redshift, generates 50,000 synthetic interaction records, and loads them into a local SQLite database.

In this step, get ready to:

Connect to Redshift and retrieve real user IDs from the tickit.users table.
Generate 50,000 weighted interaction records spanning all of 2024.
Bulk insert the records into a local SQLite database in batches.

Query Redshift for real user IDs

The interaction data you generate needs to link back to actual users in Redshift. By pulling the real userid values from tickit.users, every synthetic event will reference a valid user for a clean merge later.

Switch back to VS Code and open generate_interactions.py from the Explorer sidebar.
Add the imports and configuration block by pasting this code:

import random
import datetime
import uuid
import sqlite3
import redshift_connector
import pandas as pd

# --- Configuration ---
REDSHIFT_HOST = "your-workgroup.your-account.your-region.redshift-serverless.amazonaws.com"
REDSHIFT_DATABASE = "sample_data_dev"
REDSHIFT_USER = "admin"
REDSHIFT_PASSWORD = "your-password-here"

SQLITE_DB = "interactions.db"

What does this code do?

The imports bring in libraries for randomization, dates, UUIDs, SQLite access, the Redshift connector, and pandas for DataFrame handling.
REDSHIFT_HOST is the endpoint URL for your Redshift Serverless workgroup.
REDSHIFT_DATABASE points to sample_data_dev where the tickit schema lives.
SQLITE_DB names the local file where interaction records will be stored.

Replace your-workgroup.your-account.your-region.redshift-serverless.amazonaws.com with your actual Redshift Serverless endpoint.
Replace your-password-here with the admin password you set when creating your workgroup.

Find your Redshift endpoint

In the AWS Console, go to Amazon Redshift, then Serverless dashboard, then Workgroup configuration.

Click your workgroup name and find the endpoint in the General information section. Copy just the hostname portion (everything before the colon and port number).

Below the configuration block, add the Redshift connection and user ID query by pasting this code:

# --- Step 1: Get user IDs from Redshift ---
print("Connecting to Redshift...")
conn = redshift_connector.connect(
    host=REDSHIFT_HOST,
    database=REDSHIFT_DATABASE,
    user=REDSHIFT_USER,
    password=REDSHIFT_PASSWORD
)

cursor = conn.cursor()
cursor.execute("SELECT userid FROM tickit.users")
user_ids_df = cursor.fetch_dataframe()
user_ids = user_ids_df["userid"].tolist()
print(f"Retrieved {len(user_ids)} user IDs from Redshift")

conn.close()

What does this code do?

redshift_connector.connect() opens a connection to your Redshift Serverless workgroup using the credentials you configured.
cursor.fetch_dataframe() is a built-in method that returns query results directly as a pandas DataFrame.
The .tolist() call converts the DataFrame column into a plain Python list that the random generator can sample from.
conn.close() releases the Redshift connection immediately since you only need the user IDs in memory.

Before you run the script, what do you expect to see printed in the terminal?

Save generate_interactions.py and run it with this command:

python3 generate_interactions.py

You should see two lines printed: Connecting to Redshift... followed by Retrieved and a number of user IDs (the tickit dataset has around 49,990 users).

Connection refused or timeout?

Double-check that your REDSHIFT_HOST value matches the endpoint shown in your workgroup's General information section. It should end in .redshift-serverless.amazonaws.com with no port number appended.
Confirm your REDSHIFT_PASSWORD matches the admin password you set during workgroup creation.
Make sure your workgroup's security settings allow public access (the free trial default enables this).

Help me troubleshoot my Redshift Serverless connection error in Python

Generate 50,000 synthetic interaction records

Now that you have real user IDs in memory, you can generate behavioral events that mimic what a real e-commerce tracking system would capture. Each record ties an event type, timestamp, and device to one of those Redshift users.

In generate_interactions.py, add the event type definitions and generation setup below conn.close():

# --- Step 2: Generate synthetic interaction data ---
print("Generating synthetic interaction data...")

EVENT_TYPES = ["page_view", "add_to_cart", "purchase", "email_open", "email_click"]
EVENT_WEIGHTS = [0.45, 0.20, 0.10, 0.15, 0.10]
DEVICES = ["desktop", "mobile", "tablet"]
REGIONS = ["US-East", "US-West", "EU-West", "APAC"]
CAMPAIGNS = [None, "summer_sale", "loyalty_program", "new_arrivals", "flash_deal"]

NUM_RECORDS = 50000

interactions = []
start_date = datetime.datetime(2024, 1, 1)
end_date = datetime.datetime(2024, 12, 31)
date_range_seconds = int((end_date - start_date).total_seconds())

What do the weights mean?

EVENT_WEIGHTS controls how often each event type appears. Page views are most common (45%), followed by cart additions (20%), email opens (15%), purchases (10%), and email clicks (10%).

This distribution mimics a real conversion funnel where most visitors browse but few actually buy. Later, your statistical analysis will test whether email engagement (opens + clicks) correlates with higher spending.

Directly below the setup code, add the generation loop that creates all 50,000 records:

for _ in range(NUM_RECORDS):
    user_id = random.choice(user_ids)
    event_type = random.choices(EVENT_TYPES, weights=EVENT_WEIGHTS, k=1)[0]
    timestamp = start_date + datetime.timedelta(seconds=random.randint(0, date_range_seconds))
    session_id = str(uuid.uuid4())[:8]
    device = random.choice(DEVICES)
    region = random.choice(REGIONS)
    campaign_id = random.choice(CAMPAIGNS)

    interactions.append((
        int(user_id),
        event_type,
        timestamp.isoformat(),
        session_id,
        device,
        region,
        campaign_id,
    ))

print(f"Generated {len(interactions)} interaction records")

What does this loop do?

Each iteration picks a random user from the real Redshift user list and assigns them a weighted event type.
random.choices() with the weights parameter makes page views appear far more often than purchases.
Timestamps are spread randomly across the full 2024 calendar year using seconds-based offsets.
Each record is stored as a tuple ready for batch insertion into SQLite.

Save generate_interactions.py and run it again:

python3 generate_interactions.py

You should now see the Redshift retrieval lines from before, followed by Generating synthetic interaction data... and Generated 50000 interaction records.

Script hangs during generation?

The loop generates 50,000 records in memory. On most machines this takes 2-5 seconds. If it seems stuck for more than 30 seconds, press Ctrl+C to stop and check that NUM_RECORDS is set to 50000 (not a much larger number by accident).

Help me debug why my interaction generation loop is running slowly

Bulk insert into SQLite

With 50,000 records in memory, you now need to persist them in a local SQLite database. Inserting in batches of 5,000 keeps memory usage predictable and gives you progress feedback as each batch commits.

In generate_interactions.py, add the SQLite table creation code below the print(f"Generated {len(interactions)} interaction records") line:

# --- Step 3: Insert into SQLite ---
print("Connecting to SQLite...")
sqlite_conn = sqlite3.connect(SQLITE_DB)
sqlite_cursor = sqlite_conn.cursor()

sqlite_cursor.execute("DROP TABLE IF EXISTS interactions")
sqlite_cursor.execute("""
    CREATE TABLE interactions (
        user_id INTEGER,
        event_type TEXT,
        timestamp TEXT,
        session_id TEXT,
        device TEXT,
        region TEXT,
        campaign_id TEXT
    )
""")

What does this code do?

sqlite3.connect(SQLITE_DB) creates the interactions.db file if it does not already exist. No installation needed since sqlite3 is part of Python's standard library.
DROP TABLE IF EXISTS ensures a fresh start each time you re-run the script during development.
The CREATE TABLE schema matches the tuple structure from the generation loop so each field maps to the correct column.

Directly below the table creation, add the batch insert and verification code:

BATCH_SIZE = 5000
for i in range(0, len(interactions), BATCH_SIZE):
    batch = interactions[i:i + BATCH_SIZE]
    sqlite_cursor.executemany(
        "INSERT INTO interactions VALUES (?, ?, ?, ?, ?, ?, ?)",
        batch
    )
    sqlite_conn.commit()
    print(f"  Inserted batch {i // BATCH_SIZE + 1}/{(len(interactions) // BATCH_SIZE) + 1}")

count = sqlite_cursor.execute("SELECT COUNT(*) FROM interactions").fetchone()[0]
print(f"SQLite now contains {count} interaction records")

sqlite_conn.close()
print("Done!")

Why insert in batches?

executemany() sends 5,000 rows per call instead of inserting one at a time. Each batch is committed separately so you see progress printed to the terminal.

The final SELECT COUNT(*) query confirms every record made it into the table before the connection closes.

Before running, predict: how many batch insert messages do you expect to see for 50,000 records at a batch size of 5,000?

Save generate_interactions.py and run the complete script:

python3 generate_interactions.py

You should see the full pipeline output: Redshift retrieval, generation confirmation, 10 batch insert messages, and a final line reading SQLite now contains 50000 interaction records.

Confirm the database independently by running this verification command:

python3 -c "import sqlite3; conn = sqlite3.connect('interactions.db'); print(conn.execute('SELECT COUNT(*) FROM interactions').fetchone()[0]); conn.close()"

You should see 50000 printed on its own line, confirming the data persisted correctly.

Seeing 0 records or a missing file error?

Make sure you are running both commands from inside the customer-analytics folder. The script creates interactions.db in whatever directory your terminal is currently in.
If you see interactions.db but 0 records, check that the sqlite_conn.commit() line is inside the for loop, not after it.
If you get a no such table: interactions error on the verification command, re-run generate_interactions.py first. The table is created fresh each run.

Help me fix my SQLite insertion issue in generate_interactions.py

✔️ Awesome, I've got everything!

Great. Double check you have saved generate_interactions.py and that interactions.db appears in your customer-analytics folder.

ⓧ I'd like to double check the full code

Your generate_interactions.py file should contain these sections in order. Scroll up and confirm each one is present:

Imports and Configuration (6 imports, 4 config constants including SQLITE_DB).
Step 1: Get user IDs from Redshift (connect, execute query on tickit.users, convert to list, close connection).
Step 2: Generate synthetic interaction data (event type lists with weights, NUM_RECORDS = 50000, date range setup, for loop appending tuples, print confirmation).
Step 3: Insert into SQLite (connect, create table with 7 columns, batch insert loop with BATCH_SIZE = 5000, count verification, close connection, print Done!).

You now have two data sources ready to merge: transactional sales in Redshift and behavioral interactions in SQLite. Next up, you will build the ETL pipeline that extracts from both, transforms them into per-user aggregates, and writes a unified analytics table back to Redshift.

Build the ETL Pipeline

Your two data sources are ready, but they speak different languages. Sales data in Redshift has one row per transaction. Interaction data in SQLite has one row per event.

You cannot merge them until both are aggregated to one row per user. In this step, you will build the ETL pipeline that extracts from both databases, transforms the raw records into per-user aggregates, merges them on user_id, and writes the unified result back to Redshift.

In this step, get ready to:

Extract sales records from Redshift and interaction records from SQLite into pandas DataFrames.
Transform both sources into per-user aggregates and merge them with derived features.
Write the merged analytics table back to Redshift for downstream use.

Extract from both sources

The pipeline starts by pulling raw data out of each database. You will connect to Redshift's sample_data_dev database to query sales joined with events and users, then connect to SQLite to read all 50,000 interaction records.

Open analytics_pipeline.py in VS Code and add the imports and configuration by pasting this code:

import redshift_connector
import sqlite3
import pandas as pd
import numpy as np
from scipy import stats

# --- Configuration ---
REDSHIFT_HOST = "your-workgroup.your-account.your-region.redshift-serverless.amazonaws.com"
REDSHIFT_DB_SAMPLE = "sample_data_dev"
REDSHIFT_DB_DEV = "dev"
REDSHIFT_USER = "admin"
REDSHIFT_PASSWORD = "your-password-here"

SQLITE_DB = "interactions.db"

# ============================================================
# ETL PIPELINE
# ============================================================

# --- Extract from Redshift ---
print("=" * 60)
print("ETL PIPELINE")
print("=" * 60)

What does this code do?

The imports bring in redshift_connector for the cloud warehouse, sqlite3 for the local database, pandas for DataFrames, numpy for numeric operations, and scipy.stats for statistical tests you will add later.
The configuration block holds connection details for both Redshift databases (sample_data_dev for reading and dev for writing) and the SQLite file path.
The print lines create a clear header banner when the script runs so you can see where the ETL begins.

Replace the placeholder values in REDSHIFT_HOST, REDSHIFT_USER, and REDSHIFT_PASSWORD with the same credentials you used in generate_interactions.py.
Add the Redshift extraction code below the header by pasting:

print("\nExtracting sales data from Redshift...")
conn = redshift_connector.connect(
    host=REDSHIFT_HOST,
    database=REDSHIFT_DB_SAMPLE,
    user=REDSHIFT_USER,
    password=REDSHIFT_PASSWORD
)
cursor = conn.cursor()

sales_query = """
    SELECT
        s.buyerid AS user_id,
        s.qtysold,
        s.pricepaid,
        s.commission,
        s.saletime,
        e.catid,
        e.eventname,
        u.city,
        u.state
    FROM tickit.sales s
    JOIN tickit.event e ON s.eventid = e.eventid
    JOIN tickit.users u ON s.buyerid = u.userid
"""
cursor.execute(sales_query)
sales_df = cursor.fetch_dataframe()
print(f"  Extracted {len(sales_df)} sales records from Redshift")
conn.close()

What does this query do?

The SQL query joins three tables: tickit.sales (transaction amounts), tickit.event (event category IDs), and tickit.users (city and state).

The result is a single DataFrame where each row represents one sale enriched with the event category and buyer location. cursor.fetch_dataframe() is a built-in redshift-connector method that returns query results directly as a pandas DataFrame.

Add the SQLite extraction below the Redshift block by pasting:

# --- Extract from SQLite ---
print("\nExtracting interaction data from SQLite...")
sqlite_conn = sqlite3.connect(SQLITE_DB)
interactions_df = pd.read_sql_query("SELECT * FROM interactions", sqlite_conn)
print(f"  Extracted {len(interactions_df)} interaction records from SQLite")
sqlite_conn.close()

What does this code do?

pd.read_sql_query() reads the entire interactions table into a DataFrame in one call. Unlike the Redshift connector's custom method, this is the standard pandas approach for SQLite connections.

Save analytics_pipeline.py.
Run the script to verify both extractions succeed:

python3 analytics_pipeline.py

You should see the ETL PIPELINE banner, then two extraction messages confirming the record counts from each source.

Seeing a connection error?

Double check that your REDSHIFT_HOST value matches the endpoint shown in the Redshift Serverless console under your workgroup details.
Confirm the password matches the admin credentials you set during workgroup creation.
Make sure interactions.db is in the same folder where you run the script (customer-analytics).

Still stuck? Help me debug my Redshift connection error in analytics_pipeline.py

Transform and merge into a unified DataFrame

Right now, sales data has one row per transaction and interactions have one row per event. To merge them, you need both at the same granularity: one row per user.

Add the sales aggregation below the SQLite extraction by pasting this code:

# --- Transform: Aggregate sales per user ---
print("\nTransforming data...")
sales_agg = sales_df.groupby("user_id").agg(
    total_spent=("pricepaid", "sum"),
    purchase_count=("pricepaid", "count"),
    avg_ticket_price=("pricepaid", "mean"),
    total_commission=("commission", "sum"),
).reset_index()

top_category = sales_df.groupby("user_id")["catid"].agg(
    lambda x: x.mode().iloc[0] if not x.mode().empty else None
).reset_index()
top_category.columns = ["user_id", "preferred_category"]
sales_agg = sales_agg.merge(top_category, on="user_id", how="left")

user_region = sales_df.groupby("user_id")["state"].first().reset_index()
user_region.columns = ["user_id", "user_state"]
sales_agg = sales_agg.merge(user_region, on="user_id", how="left")

What does this aggregation produce?

groupby("user_id").agg(...) collapses all transactions per user into summary metrics: total spending, number of purchases, average ticket price, and total commission.
The top_category block finds each user's most-purchased category using .mode().
user_region captures each buyer's state so you can later test whether geography predicts category preference.

Add the interaction aggregation below the sales aggregation by pasting:

# --- Transform: Aggregate interactions per user ---
interaction_agg = interactions_df.groupby("user_id").agg(
    page_views=("event_type", lambda x: (x == "page_view").sum()),
    cart_additions=("event_type", lambda x: (x == "add_to_cart").sum()),
    email_opens=("event_type", lambda x: (x == "email_open").sum()),
    email_clicks=("event_type", lambda x: (x == "email_click").sum()),
    total_sessions=("session_id", "nunique"),
).reset_index()

interaction_agg["email_engagement"] = interaction_agg["email_opens"] + interaction_agg["email_clicks"]

What does this aggregation produce?

Each lambda counts specific event types per user. The email_engagement column combines opens and clicks into a single behavioral signal you will use for hypothesis testing in the next step.

Add the merge and derived feature computation below the interaction aggregation by pasting:

# --- Merge ---
print("\nMerging datasets...")
analytics_df = sales_agg.merge(interaction_agg, on="user_id", how="inner")

analytics_df["conversion_rate"] = np.where(
    analytics_df["page_views"] > 0,
    analytics_df["purchase_count"] / analytics_df["page_views"],
    0,
)
analytics_df["engagement_score"] = (
    analytics_df["email_engagement"] * 0.3
    + analytics_df["page_views"] * 0.3
    + analytics_df["cart_additions"] * 0.4
)

print(f"  Merged dataset: {len(analytics_df)} users with both transactional and behavioral data")
print(f"  Columns: {list(analytics_df.columns)}")

Why an inner join?

The inner join keeps only users who appear in both sources. Users who bought tickets but had zero interaction events (or vice versa) are dropped. This mirrors real-world data integration where not every record links across systems.

conversion_rate measures how many page views led to purchases. engagement_score combines email, browsing, and cart activity into a single weighted metric.

Save analytics_pipeline.py.
Run the script again to confirm transformations and the merge succeed:

python3 analytics_pipeline.py

You should now see the extraction messages followed by "Transforming data..." and a line reporting how many users ended up in the merged dataset.

Seeing a KeyError or merge failure?

Confirm both aggregation blocks reference "user_id" as the groupby column. The Redshift query aliases buyerid to user_id, and the SQLite table uses user_id natively.
Check that you did not accidentally place the merge code above the interaction aggregation. The merge depends on both sales_agg and interaction_agg existing first.

Still stuck? Help me debug my pandas merge error in analytics_pipeline.py

Write the merged analytics table back to Redshift

The merged DataFrame lives in memory, but you need it in Redshift so QuickSight can query it later. This final section creates a new table in the dev database and writes the analytics results there.

Add the write-back connection and table creation below the merge section by pasting:

# --- Write back to Redshift ---
print("\nWriting aggregated results to Redshift...")
conn = redshift_connector.connect(
    host=REDSHIFT_HOST,
    database=REDSHIFT_DB_DEV,
    user=REDSHIFT_USER,
    password=REDSHIFT_PASSWORD
)
cursor = conn.cursor()

cursor.execute("DROP TABLE IF EXISTS public.customer_analytics_summary")
cursor.execute("""
    CREATE TABLE public.customer_analytics_summary (
        user_id INTEGER,
        total_spent DECIMAL(10,2),
        purchase_count INTEGER,
        avg_ticket_price DECIMAL(10,2),
        preferred_category INTEGER,
        user_state VARCHAR(50),
        page_views INTEGER,
        cart_additions INTEGER,
        email_engagement INTEGER,
        total_sessions INTEGER,
        conversion_rate DECIMAL(8,4),
        engagement_score DECIMAL(10,2)
    )
""")

What does this code do?

This time the connection targets REDSHIFT_DB_DEV (the dev database) instead of sample_data_dev. You read from the sample data but write results to your own database.
DROP TABLE IF EXISTS ensures a clean slate each time you re-run the pipeline.
The CREATE TABLE defines the exact schema matching the columns in your merged DataFrame.

Add the data write and commit below the table creation by pasting:

write_df = analytics_df[[
    "user_id", "total_spent", "purchase_count", "avg_ticket_price",
    "preferred_category", "user_state", "page_views", "cart_additions",
    "email_engagement", "total_sessions", "conversion_rate", "engagement_score"
]].copy()

cursor.write_dataframe(write_df, "customer_analytics_summary")
conn.commit()
print(f"  Written {len(write_df)} rows to customer_analytics_summary")
conn.close()

What does this code do?

write_df selects only the columns that match the table schema, in the correct order. cursor.write_dataframe() is a built-in redshift-connector method that bulk-inserts a DataFrame into the target table. The conn.commit() finalizes the transaction so the data becomes visible in Query Editor v2.

Save analytics_pipeline.py.

Before you run the full pipeline, what do you expect to see printed at the end?

Run the complete pipeline:

python3 analytics_pipeline.py

You should see the full ETL output: extraction counts from both sources, a "Transforming data..." message, the merged dataset size, and finally a confirmation of how many rows were written to customer_analytics_summary.

Open the Redshift Query Editor v2 in the AWS Console.
Connect to the dev database (not sample_data_dev).
Run this query to confirm the row count matches your terminal output:

SELECT COUNT(*) FROM public.customer_analytics_summary;

The number returned should match the "Merged dataset: X users" count from your terminal output.

Query returns zero rows or table not found?

Make sure you connected Query Editor to the dev database, not sample_data_dev. The table was written to dev.
Confirm your script printed "Written X rows" without errors. If you see a commit error, check that the REDSHIFT_DB_DEV value is set to "dev".
Try refreshing the Query Editor schema tree by clicking the refresh icon next to the database list.

Still stuck? Help me figure out why my customer_analytics_summary table is empty or missing in Redshift

✔️ Awesome, I've got everything!

Great. Double check you have saved analytics_pipeline.py and that your Redshift query returned a matching row count.

ⓧ I'd like to double check the full code

Your analytics_pipeline.py should contain these sections in order. Scroll up and confirm each is present:

Imports and configuration (redshift_connector, sqlite3, pandas, numpy, scipy.stats plus connection variables).
ETL PIPELINE header and Redshift extraction (the multi-table JOIN query fetching into sales_df).
SQLite extraction (reading interactions table into interactions_df).
Sales aggregation (sales_agg with total_spent, purchase_count, avg_ticket_price, preferred_category, user_state).
Interaction aggregation (interaction_agg with page_views, cart_additions, email_opens, email_clicks, total_sessions, email_engagement).
Merge on user_id (inner join) plus conversion_rate and engagement_score derived features.
Write-back to Redshift: connect to dev, create public.customer_analytics_summary, write the DataFrame, commit, and close.

Your ETL pipeline is working end to end. Data flows from two separate databases, gets aggregated to the same grain, and lands in a single Redshift table ready for analysis. Next up, you will run statistical hypothesis tests on this merged data to determine whether email engagement actually drives higher spending.

Run Statistical Analysis

Your merged dataset reveals that some users have high engagement and high spending. But is that a real pattern, or just random noise?

Without statistical tests, you cannot tell stakeholders whether email engagement actually drives revenue. In this step, you will apply rigorous hypothesis testing to answer that question with confidence.

In this step, get ready to:

Segment customers and run an independent samples t-test comparing spending between engagement groups.
Run a Chi-Squared test to check if geography predicts category preference.
Print a formatted analysis summary with p-values and effect sizes.

Segment customers and run an independent samples t-test

A t-test compares the averages of two groups and tells you whether the difference is statistically significant or likely due to chance. You will split your users into high-engagement and low-engagement groups, then test whether the high-engagement group spends more.

In VS Code, open analytics_pipeline.py.
Scroll to the very bottom of the file, after the line conn.close() that ends the ETL section.
Add the statistical analysis section header and t-test code by pasting the following below that last line:

# ============================================================
# STATISTICAL ANALYSIS
# ============================================================
print("\n" + "=" * 60)
print("STATISTICAL ANALYSIS")
print("=" * 60)

print("\n--- Independent Samples T-Test ---")
print("H0: No difference in total_spent between high and low email engagement groups")
print("H1: There is a difference in total_spent between groups")

# Split users at the median engagement level
median_engagement = analytics_df["email_engagement"].median()
high_engagement = analytics_df[analytics_df["email_engagement"] > median_engagement]["total_spent"]
low_engagement = analytics_df[analytics_df["email_engagement"] <= median_engagement]["total_spent"]

# Run the t-test and calculate effect size
t_stat, p_value_t = stats.ttest_ind(high_engagement, low_engagement)

pooled_std = np.sqrt(
    ((len(high_engagement) - 1) * high_engagement.std() ** 2
     + (len(low_engagement) - 1) * low_engagement.std() ** 2)
    / (len(high_engagement) + len(low_engagement) - 2)
)
cohens_d = (high_engagement.mean() - low_engagement.mean()) / pooled_std

print(f"\n  High engagement group: n={len(high_engagement)}, mean=${high_engagement.mean():.2f}")
print(f"  Low engagement group:  n={len(low_engagement)}, mean=${low_engagement.mean():.2f}")
print(f"  T-statistic: {t_stat:.4f}")
print(f"  P-value: {p_value_t:.6f}")
print(f"  Cohen's d: {cohens_d:.4f}")
print(f"  Result: {'SIGNIFICANT' if p_value_t < 0.05 else 'NOT SIGNIFICANT'} at alpha=0.05")

What does this code do?

This code splits your users into two groups based on the median email_engagement value. Users above the median go into the high-engagement group. Everyone else goes into low-engagement.

stats.ttest_ind() runs an independent samples t-test comparing total_spent between the two groups. It returns a t-statistic and a p-value.
The p-value tells you the probability of seeing this difference if there were truly no difference. Below 0.05 means statistically significant.
Cohen's d measures the practical size of the difference. Values of 0.2, 0.5, and 0.8 are commonly interpreted as small, medium, and large effects.

Save analytics_pipeline.py.
Run the pipeline to confirm the t-test executes correctly:

python3 analytics_pipeline.py

After the ETL output you have seen before, you should now see a new STATISTICAL ANALYSIS header followed by the Independent Samples T-Test results, including group sizes, means, the t-statistic, p-value, Cohen's d, and a SIGNIFICANT or NOT SIGNIFICANT conclusion.

Not seeing the statistical output?

Check that the new code is pasted after the final conn.close() line in the ETL section. If it is inside the Redshift connection block, the connection will already be closed and variables like analytics_df might not be in scope.
If you see a NameError for analytics_df, make sure you did not accidentally overwrite or delete the merge section above.
help me debug my t-test code in analytics_pipeline.py

Run a Chi-Squared test for independence

A Chi-Squared test checks whether two categorical variables are independent of each other. You will test whether a customer's geographic region predicts which event category they prefer.

In analytics_pipeline.py, scroll to the bottom of the file (after the t-test print statements you just added).
Add the Chi-Squared test code by pasting the following:

print("\n--- Chi-Squared Test for Independence ---")
print("H0: Customer region and preferred category are independent")
print("H1: Customer region and preferred category are not independent")

# Build a contingency table of region vs category
contingency = pd.crosstab(analytics_df["user_state"], analytics_df["preferred_category"])
chi2, p_value_chi, dof, expected = stats.chi2_contingency(contingency)

# Calculate Cramer's V as the effect size
n = contingency.sum().sum()
min_dim = min(contingency.shape[0], contingency.shape[1]) - 1
cramers_v = np.sqrt(chi2 / (n * min_dim)) if min_dim > 0 else 0

print(f"\n  Contingency table shape: {contingency.shape}")
print(f"  Chi2 statistic: {chi2:.4f}")
print(f"  P-value: {p_value_chi:.6f}")
print(f"  Degrees of freedom: {dof}")
print(f"  Cramer's V: {cramers_v:.4f}")
print(f"  Result: {'SIGNIFICANT' if p_value_chi < 0.05 else 'NOT SIGNIFICANT'} at alpha=0.05")

What does this code do?

pd.crosstab() builds a contingency table counting how many users in each state prefer each event category. This table is the input to the Chi-Squared test.

stats.chi2_contingency() tests whether the distribution of categories differs across states. If it does, region and category are not independent.
Cramer's V is the effect size for Chi-Squared tests. It ranges from 0 (no association) to 1 (perfect association).

Save analytics_pipeline.py.

Print a formatted analysis summary

The final section consolidates both test results into a single summary block that is easy to scan. This is what you would share with stakeholders to communicate your findings.

At the bottom of analytics_pipeline.py, add the summary output by pasting this code:

print("\n" + "=" * 60)
print("ANALYSIS SUMMARY")
print("=" * 60)
print(f"\n  Total users analyzed: {len(analytics_df)}")
print(f"  Total sales records: {len(sales_df)}")
print(f"  Total interactions: {len(interactions_df)}")
print(f"\n  T-Test (engagement vs spending): p={p_value_t:.6f}, d={cohens_d:.4f}")
print(f"  Chi-Squared (region vs category): p={p_value_chi:.6f}, V={cramers_v:.4f}")
print("\nPipeline complete!")

What does this code do?

This prints a condensed report with all the key numbers: total records processed, both p-values, and both effect sizes. The final "Pipeline complete!" line confirms the entire script executed without errors.

Save analytics_pipeline.py.

Before you run the pipeline, what do you expect the ANALYSIS SUMMARY to show? Do you think high-engagement users spend significantly more than low-engagement users?

Run the full pipeline to see all results:

python3 analytics_pipeline.py

Scroll past the ETL output and the individual test results. At the very end you should see the ANALYSIS SUMMARY block with total users analyzed, total sales records, total interactions, both p-values with effect sizes, and "Pipeline complete!" printed on the last line.

How do I read these results?

For each test, look at two things: the p-value and the effect size.

If the p-value is below 0.05, the result is statistically significant. The difference is unlikely to be random noise.
The effect size tells you the practical magnitude. A small Cohen's d (around 0.2) means the groups differ, but not by much. A large Cramer's V (close to 1) means strong association between variables.
Because the interaction data is synthetically generated with random user assignments, you may see small effect sizes or non-significant results. That is expected and realistic.

Seeing an error on the Chi-Squared test?

If you see a ValueError about empty arrays, check that the merge produced rows with non-null user_state and preferred_category values.
If you see ZeroDivisionError, the contingency table may have only one column or row. Confirm that your ETL merge step ran correctly and produced multiple distinct categories.
help me debug the Chi-Squared test in my analytics pipeline

✔️ Awesome, I've got everything!

Great. Double check you have saved analytics_pipeline.py and the terminal shows "Pipeline complete!" at the end.

ⓧ I'd like to double check the full code

Your analytics_pipeline.py file should now contain these sections in order. Scroll through and confirm each is present:

Imports and configuration block (redshift_connector, sqlite3, pandas, numpy, scipy.stats).
ETL PIPELINE section: extract from Redshift, extract from SQLite, transform and aggregate sales, transform and aggregate interactions, merge datasets, write to Redshift.
STATISTICAL ANALYSIS section: independent samples t-test with Cohen's d.
Chi-Squared test for independence with Cramer's V.
ANALYSIS SUMMARY block ending with print("\nPipeline complete!").

Your pipeline now produces statistically rigorous answers about customer behavior. Next up, you will turn these numbers into an interactive dashboard that business stakeholders can explore on their own.

Build the QuickSight Dashboard

Your pipeline now extracts, transforms, merges, and statistically validates customer behavior patterns. The analysis summary prints convincing numbers to the terminal.

Numbers in a terminal are convincing to data engineers but not to business stakeholders. A visual dashboard makes the same insights accessible to anyone, turning your pipeline into a self-service analytics product. In this step, you will connect Amazon QuickSight to your Redshift data and build an interactive dashboard.

In this step, get ready to:

Sign up for the QuickSight 30-day free trial and connect it to your Redshift dev database.
Build four visualizations covering revenue KPIs, category breakdowns, engagement segments, and regional distribution.
Publish a named dashboard accessible via a shareable URL.

Sign up for QuickSight and connect to Redshift

QuickSight needs its own account activation before you can create datasets or dashboards. You will activate the 30-day free trial in the same AWS region as your Redshift workgroup so that QuickSight can autodiscover the connection.

In the AWS Management Console, type QuickSight into the search bar at the top and select Amazon QuickSight from the results.
QuickSight will prompt you to create an account. Enter a unique account name (for example, your AWS username or a short identifier).
Enter a notification email address for your QuickSight account.
For the SPICE region, choose the same AWS region where your Redshift Serverless workgroup is running.
For authentication method, select the recommended option (Password-based or IAM Identity Center).
Click Create account.
Wait for the account to be created, then click Go to QuickSight.

What is SPICE?

SPICE (Super-fast, Parallel, In-memory Calculation Engine) is QuickSight's in-memory data store. When you import data to SPICE, QuickSight caches it for fast dashboard loading instead of querying Redshift every time someone opens the dashboard.

You should see the QuickSight landing page with navigation options for Analyses, Dashboards, and Data on the left side.

Now connect QuickSight to the customer_analytics_summary table in Redshift.

In the left navigation, click Datasets.
Click New dataset.
Under From new data sources, choose Amazon Redshift Auto-discovered.
For Data source name, enter customer-analytics-redshift.
For Instance ID, select your Redshift Serverless workgroup from the dropdown.
For Database name, enter dev.
For Username, enter admin.
For Password, enter the password you set when creating your Redshift Serverless workgroup.

Click Validate connection. You should see a success message confirming the connection is valid.
Click Create data source.

QuickSight now shows a table selection dialog listing tables in the dev database.

Select the public schema.
Select the customer_analytics_summary table.
Choose Import to SPICE for quicker analytics as the query mode.
Click Visualize.

QuickSight imports the data into SPICE and opens a new analysis with a blank canvas. You should see the field list on the left showing columns like total_spent, purchase_count, email_engagement, and user_state.

Connection validation failed?

If the connection fails, check that your Redshift Serverless workgroup allows QuickSight access. QuickSight and Redshift must be in the same region. Also verify the database name is dev (not sample_data_dev), and your admin password is correct.

Help me troubleshoot my QuickSight connection to Redshift

Build KPI, trend, and category visualizations

With the dataset loaded into SPICE, you can now add visualizations to your analysis. Each visual answers a specific business question.

In the analysis canvas, click the + Add button at the top left to add a new visual.
In the Visual types pane on the left, select the KPI chart type.
Drag total_spent from the field list into the Value field well.
QuickSight aggregates this as a sum by default, showing the total revenue across all customers.

You should see a large KPI number representing the total revenue (sum of total_spent) across your analytics dataset.

Click + Add again to create a second visual.
Select the Vertical bar chart visual type.
Drag preferred_category to the X axis field well.
Drag total_spent to the Value field well.

This bar chart shows total spending grouped by event category, revealing which categories drive the most revenue.

Click + Add to create a third visual.
Select the Horizontal bar chart visual type.
Drag email_engagement to the Y axis field well.
Drag total_spent to the Value field well.
QuickSight defaults to summing total_spent. Click the dropdown arrow on the total_spent field well and change the aggregate to Average.

This horizontal bar chart shows average spending by email engagement level. You should see whether higher email engagement correlates with higher average spend.

Why average instead of sum?

Sum would be misleading here because some engagement levels might have more users than others. Average spend per user gives you a fair comparison between high-engagement and low-engagement groups. This directly visualizes the relationship your t-test analyzed.

Add the segment donut chart and publish the dashboard

Click + Add to create a fourth visual.
Select the Donut chart visual type.
Drag user_state to the Group/Color field well.
QuickSight automatically counts the rows per state, showing you the customer distribution by region.

You now have four visualizations on your canvas. Confirm all four visuals show data: a KPI card, a vertical bar chart, a horizontal bar chart, and a donut chart.

Now publish the analysis as a shareable dashboard.

Click Publish in the top right of the analysis.
Select Publish dashboard.
Choose Publish as a new dashboard.
For the dashboard name, enter Customer Behavior Analytics.
Click Publish.
On the Share dashboard page that appears, click the X icon to close it. You can share later if needed.

Before you open the dashboard, predict: will all four visualizations render with live data from your customer_analytics_summary table?

Click Dashboards in the left navigation and open Customer Behavior Analytics. You should see all four visualizations rendering with data: the KPI card showing total revenue, the bar chart showing spending by category, the horizontal bar chart showing average spend by engagement level, and the donut chart showing customer distribution by state.

Dashboard shows no data?

If your visuals are empty, go back to the dataset and confirm the SPICE import completed successfully. Click Datasets in the left navigation, find your dataset, and check that the row count matches what your pipeline wrote to Redshift.

Help me figure out why my QuickSight visuals are empty

Your analytics pipeline is now a complete product. Data flows from two databases through Python transformations, gets validated with statistical tests, and lands in an interactive dashboard that any stakeholder can explore without writing a single query.

Secret mission

RFM Customer Segmentation

Your pipeline splits customers into high and low engagement, but marketing teams need finer-grained segments. RFM (Recency, Frequency, Monetary) scoring classifies every customer into actionable tiers like Champions, Loyal, At Risk, and Lost. In this mission, you will write a new script from scratch that calculates RFM metrics, applies quintile-based scoring, assigns segment labels, writes results to Redshift, and visualizes the distribution as a heatmap in QuickSight.

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses Amazon Redshift Serverless (covered by a $300 free trial credit for 90 days) and Amazon QuickSight (30-day free trial), both of which incur charges after their trials end.

Cost warning

Redshift Serverless charges $0.375 per RPU-hour when queries are running. QuickSight charges $9-24/user/month after the 30-day trial ends. If you do not plan to continue using these services, delete them before your trials expire to avoid unexpected charges.

Resources you used:

Amazon Redshift Serverless workgroup and namespace (covered by $300 free trial credit for 90 days).
Amazon QuickSight subscription (30-day free trial).
Local project folder (customer-analytics) containing Python scripts and SQLite database.
Python packages (redshift-connector, pandas, numpy, scipy).

Keep everything running

No action needed. Choose this if you are still actively building on this project or want to explore the data further.

Redshift Serverless auto-pauses when idle, so you will not be charged while no queries are running.
Monitor your remaining free trial credit in the Redshift Serverless console under the Free trial section on the Serverless dashboard.
Note your QuickSight trial expiration date so you can unsubscribe before charges begin.
Your local files (customer-analytics folder, interactions.db) remain on your Desktop at no cost.

Pause - I'll come back to this later

Shut down running processes to free up resources while keeping your files intact for later.

Redshift Serverless auto-pauses when idle. No manual action required to pause compute.
The QuickSight trial clock cannot be paused. It continues counting down regardless of usage.
Your local project files and SQLite database persist on your machine at no cost.
If you return after 90 days, your Redshift free trial credit will have expired. Queries will then charge at $0.375 per RPU-hour.

Delete - I don't want to use this again

Remove all project resources completely and start fresh. Follow each step below.

Delete the Redshift Serverless workgroup:

In the AWS Console, open Amazon Redshift Serverless.
Select Workgroup configuration from the left menu.
Click Delete Amazon Redshift Serverless instance.
Type delete in the confirmation field and click Delete.

Delete the Redshift namespace:

Select Namespace configuration from the left menu and choose your namespace.
Click Actions and select Delete namespace.
Type delete in the confirmation field and click Delete to confirm.

Unsubscribe from QuickSight:

Open the QuickSight unsubscribe page in your browser:
https://us-east-1.quicksight.aws.amazon.com/sn/console/unsubscribe
This link works regardless of which AWS region you used. It deletes all QuickSight data, analyses, and users.
Follow the on-screen prompts to confirm the deletion.

Delete local project files:

Delete the project folder by running this command in your terminal:

rm -rf ~/Desktop/customer-analytics

Uninstall Python packages (optional):

Remove the packages you installed for this project by running:

pip uninstall redshift-connector pandas numpy scipy -y

Nice Work!

Nice work! You just built a production-grade customer behavior analytics pipeline from scratch.

You've learned how to:

Build a Python ETL pipeline that extracts data from Amazon Redshift and SQLite, transforms both into per-user aggregates, and merges them into a unified analytics dataset with derived features.
Run statistical hypothesis tests (independent samples t-test and Chi-Squared) with effect sizes (Cohen's d and Cramer's V) to determine whether engagement drives spending and whether geography predicts category preference.
Connect Amazon QuickSight to Redshift and publish an interactive dashboard with KPIs, category breakdowns, engagement segments, and regional distribution.
Secret Mission: Build an RFM segmentation model scoring customers on Recency, Frequency, and Monetary dimensions, and visualize the distribution as a heatmap in QuickSight.

Ready to quiz yourself?