Ad Sales Report with AI Narratives

Build a SQL analytics pipeline that generates executive ad sales reports with Claude.

Introduction

30 Second Summary

Every week, someone at a company has to look at thousands of data points across channels, regions, and customer segments and answer one question: what happened and why? Translating raw numbers into a clear, structured story is the core of what a Decision Scientist does every day.

In this project, you will build a Python pipeline that creates a realistic ad sales funnel database, analyzes it with SQL, uncovers the root cause of a hidden performance drop, and uses Claude to generate an executive-ready weekly narrative.

What You'll Build

Run your final script and watch it query the database, trace the ad sales funnel from impressions to payments, pinpoint the exact channel and geography behind a performance drop, then hand you a polished weekly narrative from Claude that's ready for a VP of Ad Sales.

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

  • A realistic ad sales database built in SQLite spanning 3 channels, 4 geographies, and 3 customer segments, with a deliberate performance drop baked in for you to discover.
  • SQL analysis scripts that calculate week-over-week conversion rates and surface underperforming segments using GROUP BY, HAVING, and LAG window functions.
  • An AI-generated weekly performance narrative from Claude that explains what happened, traces it to a root cause, and recommends what needs attention next.
  • Secret Mission: Add run-rate forecasting that projects next week's acquisitions by channel, with "with recovery" and "without recovery" scenarios for the affected segment.

Are there any prerequisites?

You should be comfortable with SQL and Python basics. You'll also need a Claude API key from Anthropic.

Before We Start

Before diving into code, let's lock in what you're about to build and why it matters for your interview prep.

Set Up Your Development Environment

Every analysis pipeline starts with the right tools. Before you can query a database or generate AI-powered narratives, you need Python installed on your machine, a code editor to write scripts in, and the Anthropic SDK that connects your code to Claude.

In this step, you'll install Python and VS Code, create a project folder, and set up a virtual environment with the Anthropic SDK ready to go.

In this step, get ready to:
  • Install Python 3.14 and verify it's on your PATH.
  • Install VS Code and create your ad-sales-report project folder.
  • Set up a virtual environment and install the Anthropic SDK.
Install Python

Python is the language you'll use to query your database, perform analysis, and call the Claude API. Let's get it installed.

Check Add Python to PATH

On Windows, the first page of the installer shows a checkbox labelled Add Python to PATH. Make sure this is checked before clicking Install Now. Skipping this means your terminal will not recognise the python command.

On macOS, the python.org installer handles PATH automatically. Run the installer and follow the prompts.

Once the installer finishes, open a terminal to verify the installation.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter.
  • Check your Python version by running this command:
python --version

✔️ I see version 3.14

Python 3.14 is installed and ready. You can close this terminal for now.

ⓧ I see an older version

Your system has an older Python version. You need 3.14 for this project.

  • Go to https://www.python.org/downloads/ and download Python 3.14.
  • Run the installer. On Windows, check Add Python to PATH.
  • Close your terminal completely, open a new one, and run python --version again.

macOS default Python

macOS ships with an older system Python. Even after installing 3.14 from python.org, the python command may still point to the old version. Try python3 --version instead. If that shows 3.14, use python3 wherever this project says python.

ⓧ Command not found

Python is not installed yet, or it is not on your PATH.

  • Go to https://www.python.org/downloads/ and download Python 3.14.
  • Run the installer. On Windows, make sure Add Python to PATH is checked.
  • Close your terminal completely, open a new one, and run python --version again.
  • On macOS, if python is not found, try python3 --version instead.

macOS users: use python3

If python3 works but python does not on your machine, use python3 wherever this project shows python. The same applies to pip3 instead of pip.

Install VS Code and create your project folder

VS Code is the code editor you'll write all your Python scripts in. Let's install it and set up your project workspace.

  • Open your browser and go to https://code.visualstudio.com/.
  • Download VS Code for your operating system.
  • Run the downloaded installer and follow the prompts.

Once installed, open VS Code.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Visual Studio Code and press Enter to open it.

You should see the VS Code welcome screen.

  • Click the Extensions icon in the left sidebar (or press Cmd+Shift+X on macOS, Ctrl+Shift+X on Windows).
  • Type Python in the search bar.
  • Find the Python extension by Microsoft and click Install.

Your editor is ready

The Python extension gives you syntax highlighting, error checking, and autocomplete for Python files. It also auto-installs Pylance for smarter code suggestions.

Now create your project folder. Open VS Code's integrated terminal.

  • Press Ctrl+` (backtick key) to open the terminal panel inside VS Code.
  • Move to your Desktop and create the project folder by running these commands:
cd ~/Desktop
mkdir ad-sales-report
  • Confirm the folder exists by running:
ls

You should see ad-sales-report in the output.

  • In VS Code, click File in the menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the ad-sales-report folder, and click Open (macOS) or Select Folder (Windows).

VS Code reloads with ad-sales-report as your workspace. You should see the folder name in the VS Code title bar.

Don't see the folder in the title bar?

Make sure you selected the ad-sales-report folder itself, not a file inside it. If you accidentally opened the Desktop, click File then Open Folder again and select the correct folder.

Help me open my project folder in VS Code.

Set up the project environment

A virtual environment keeps this project's packages isolated from the rest of your system. You'll create one, then install the Anthropic SDK inside it.

  • Open a new terminal in VS Code by pressing Ctrl+` (backtick key).
  • Create and activate your virtual environment by running these commands:

macOS

python3 -m venv .venv
source .venv/bin/activate

Linux users can follow the same commands.

Windows

python -m venv .venv
.venv\Scripts\activate

Your terminal prompt should now show (.venv) at the beginning. This confirms the virtual environment is active.

Don't see (.venv) in your prompt?

On Windows, if you see a permissions error with the activate script, you may need to allow script execution. Run Set-ExecutionPolicy -Scope CurrentUser RemoteSigned in PowerShell, then try the activate command again.

On macOS, make sure you used python3 to create the venv, not python.

Help me activate my virtual environment.

  • Install the Anthropic SDK by running:
pip install anthropic

You'll see pip download and install the package along with its dependencies. This takes a few seconds.

What is the Anthropic SDK?

The anthropic package is the official Python library for calling the Claude API. It handles authentication, request formatting, and response parsing so you can focus on your analysis logic.

Now set your API key as an environment variable so the SDK can authenticate with Claude.

  • Set your API key by running this command (replace your-api-key-here with your actual key):

macOS

export ANTHROPIC_API_KEY=your-api-key-here

Linux users can use the same command.

Windows

set ANTHROPIC_API_KEY=your-api-key-here

Keep your API key private

Never commit your API key to version control or share it publicly. The ANTHROPIC_API_KEY environment variable only lasts for this terminal session. You'll need to set it again if you close and reopen the terminal.

Time to verify everything is wired up. Before you run this next command, what do you think will happen? Will Python successfully find the anthropic package and print a version number?

  • Verify your setup by running:
python -c "import anthropic; print(anthropic.__version__)"

You should see a version number printed, such as 1.0.0. This confirms Python can find the Anthropic SDK and your environment is ready.

Seeing ModuleNotFoundError?

Make sure your virtual environment is active. Look for (.venv) at the start of your terminal prompt. If it's missing, run the activate command again.

If the environment is active but the import still fails, run pip install anthropic again to reinstall the package. On macOS, use pip3 if pip is not found.

Help me fix my Anthropic SDK installation.

Your development environment is ready. You have Python, VS Code, and the Anthropic SDK all set up. Next up, you'll create a realistic ad sales database to practice your SQL analysis on.

Create the Ad Sales Funnel Database

Your development environment is ready. Now you need data to work with before you can practice any of the SQL patterns Fractal tests in interviews.

In this step, you'll build a Python script that creates a SQLite database filled with realistic ad sales funnel data. The data spans multiple weeks, channels, geographies, and customer segments. You'll also bake in a deliberate performance drop so you have something meaningful to investigate later.

In this step, get ready to:
  • Build a Python script that generates realistic ad sales funnel data.
  • Create a SQLite database with 144 rows spanning 4 weeks, 3 channels, 4 geographies, and 3 segments.
  • Verify the database was created and contains the expected data.
Create the database script

You'll build create_database.py in three chunks. First, the table structure. Then the data configuration. Finally, the loop that generates and inserts rows.

  • In VS Code, create a new file called create_database.py inside your ad-sales-report folder.
  • Copy the following code into the file to set up the database connection and table structure:
import sqlite3
import random

random.seed(42)  # Fixed seed so every learner gets the same data

def create_database():
    conn = sqlite3.connect("ad_sales.db")
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS funnel_data")  # Start fresh
    cursor.execute("""
        CREATE TABLE funnel_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            week TEXT NOT NULL,
            channel TEXT NOT NULL,
            geography TEXT NOT NULL,
            segment TEXT NOT NULL,
            impressions INTEGER NOT NULL,
            site_visits INTEGER NOT NULL,
            acquisitions INTEGER NOT NULL,
            payments INTEGER NOT NULL
        )
    """)
    row_count = 0

    conn.commit()
    conn.close()
    print(f"Database created with {row_count} rows in ad_sales.db")


if __name__ == "__main__":
    create_database()

What does this code do?

  • random.seed(42) locks the random number generator so every learner produces identical data. This makes debugging easier.
  • sqlite3.connect("ad_sales.db") creates a local database file. No separate database server needed.
  • DROP TABLE IF EXISTS wipes the old table so you start fresh each time you run the script.
  • The funnel_data table models a classic ad sales funnel: impressions, site visits, acquisitions, and payments.
  • Save create_database.py.
  • Run the script in your terminal by running this command:
python create_database.py

You should see Database created with 0 rows in ad_sales.db printed. Zero rows is correct because you haven't added the data generation code yet.

Seeing a syntax error or ModuleNotFoundError?

  • Make sure your virtual environment is active. You should see (.venv) at the start of your terminal prompt.
  • Check that the triple-quoted SQL string (""") has matching opening and closing quotes. A missing quote causes a syntax error.

Still stuck? Ask for help.

Now you need to define the data dimensions and multipliers that make the generated data feel realistic.

  • In create_database.py, find the line row_count = 0.
  • Add the following configuration code above that line:
    # Define the funnel dimensions
    weeks = ["2026-W30", "2026-W31", "2026-W32", "2026-W33"]
    channels = ["Organic Search", "Paid Social", "Display Ads"]
    geographies = ["US-East", "US-West", "EMEA", "APAC"]
    segments = ["Enterprise", "Mid-Market", "SMB"]

    base_impressions = {  # Base traffic volume by channel
        "Organic Search": 50000,
        "Paid Social": 40000,
        "Display Ads": 60000,
    }

    geo_multipliers = {  # Scale by geography market size
        "US-East": 1.0,
        "US-West": 0.85,
        "EMEA": 0.70,
        "APAC": 0.55,
    }

    segment_multipliers = {  # Scale by customer segment volume
        "Enterprise": 0.6,
        "Mid-Market": 1.0,
        "SMB": 1.4,
    }

    # Funnel conversion rates at each stage
    visit_rate = 0.12
    acq_rate = 0.08
    pay_rate = 0.65

What does this configuration model?

This is the blueprint for realistic ad sales data. You have 4 weeks of data across 3 advertising channels, 4 geographies, and 3 customer segments. That gives you 4 x 3 x 4 x 3 = 144 unique rows.

The multipliers make data feel real. US-East gets full traffic (1.0) while APAC gets roughly half (0.55). Enterprise customers generate fewer but higher-value impressions than SMB. The conversion rates model how people move through the funnel: 12% of impressions become site visits, 8% of visits become acquisitions, and 65% of acquisitions convert to payments.

  • Save create_database.py.

The configuration is ready. Next, you'll add the loop that uses these dimensions to generate and insert 144 rows of funnel data.

  • In create_database.py, find the line row_count = 0.
  • Add the following data generation loop below that line (above conn.commit()):
    for week in weeks:
        for channel in channels:
            for geo in geographies:
                for seg in segments:
                    base = base_impressions[channel]
                    geo_mult = geo_multipliers[geo]
                    seg_mult = segment_multipliers[seg]
                    noise = random.uniform(0.90, 1.10)
                    impressions = int(base * geo_mult * seg_mult * noise)
                    # Apply conversion rates down the funnel
                    visits = int(impressions * visit_rate * random.uniform(0.90, 1.10))
                    acqs = int(visits * acq_rate * random.uniform(0.85, 1.15))
                    # Deliberate 40% drop for W33 Paid Social EMEA
                    if (
                        week == "2026-W33"
                        and channel == "Paid Social"
                        and geo == "EMEA"
                    ):
                        acqs = int(acqs * 0.60)
                    pays = int(acqs * pay_rate * random.uniform(0.90, 1.10))
                    cursor.execute(
                        """
                        INSERT INTO funnel_data
                        (week, channel, geography, segment, impressions,
                         site_visits, acquisitions, payments)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                        """,
                        (week, channel, geo, seg, impressions, visits, acqs, pays),
                    )
                    row_count += 1

What does this loop do?

The nested loop generates one row for every combination of week, channel, geography, and segment. Each row calculates impressions from the base value scaled by geography and segment multipliers, plus random noise so no two rows are identical.

The critical lines are the if block that checks for 2026-W33 + Paid Social + EMEA. When all three match, acquisitions are reduced by 40% (acqs * 0.60). This is the deliberate performance drop you'll investigate in later steps.

  • Save create_database.py.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Scroll up through your file and confirm each section is present, in this order:

  • Chunk 1: Imports (sqlite3, random), random.seed(42), the create_database() function definition, database connection, and the CREATE TABLE statement with 9 columns.
  • Chunk 2: Four dimension lists (weeks, channels, geographies, segments), three multiplier dictionaries, and three conversion rate variables.
  • Chunk 3: The four-level nested for loop that generates impressions, applies conversion rates, inserts the 40% drop for W33 Paid Social EMEA, and inserts each row with cursor.execute().
  • Bottom: conn.commit(), conn.close(), the print statement, and the if __name__ == "__main__" guard.
Run the script and verify the database

Before you run the script, think about how many rows you expect to see. You have 4 weeks, 3 channels, 4 geographies, and 3 segments. What is 4 x 3 x 4 x 3?

  • Run the database creation script by running this command:
python create_database.py

You should see Database created with 144 rows in ad_sales.db printed in the terminal. That confirms every combination of week, channel, geography, and segment produced a row.

Still seeing 0 rows or getting an error?

  • If you see 0 rows, the data generation loop may be missing or placed after conn.commit(). Make sure the for loop sits between row_count = 0 and conn.commit().
  • If you see a NameError, check that the configuration dictionaries (base_impressions, geo_multipliers, etc.) appear before the for loop, not after it.
  • Check your indentation. Every line inside create_database() must be indented by at least 4 spaces.

Help me debug my create_database.py script.

Now verify the data is actually in the database. Before you run the next command, do you expect the row count to match the 144 you just saw?

  • Confirm the row count directly from SQLite by running this command:
python -c "import sqlite3; conn = sqlite3.connect('ad_sales.db'); print(conn.execute('SELECT COUNT(*) FROM funnel_data').fetchone()[0]); conn.close()"

You should see 144 printed. This confirms all 144 rows were committed to the database.

Why query the database directly?

The script's print statement counts rows as they're generated in Python. This verification query reads directly from the SQLite file on disk. If both numbers match, you know the data was actually written, not just processed in memory.

Your database is loaded with 144 rows of realistic funnel data, and there's a hidden problem buried in week 33. Next up, you'll write SQL queries to surface that problem and practice the exact query patterns Fractal tests in interviews.

Analyze the Funnel with SQL

Your database is loaded with 144 rows of ad sales funnel data. Now comes the core skill Fractal tests in every Decision Scientist interview: writing SQL queries that answer business questions.

In this step, you'll query ad_sales.db to calculate conversion rates at every funnel stage, break down performance by channel, and compare weeks using window functions. By the end, you'll see a clear signal that something went wrong in W33.

In this step, get ready to:
  • Write SQL queries to calculate weekly funnel conversion rates.
  • Break down acquisitions by channel using GROUP BY and HAVING.
  • Compare week-over-week performance using the LAG window function.
Write the weekly funnel summary query

The first question any stakeholder asks is "how did we do this week?" Your weekly summary query answers that by aggregating impressions, site visits, acquisitions, and payments across every row for each week, then calculating conversion rates between each stage.

  • Create a new file called analyze_funnel.py in your ad-sales-report folder.
  • Add the database connection helper and the weekly summary query by copying this code:
import sqlite3


def connect():
    # Open a connection to the ad sales database
    return sqlite3.connect("ad_sales.db")


def weekly_summary(conn):
    # Aggregate funnel metrics by week with conversion rates
    query = """
        SELECT
            week,
            SUM(impressions) AS total_impressions,
            SUM(site_visits) AS total_visits,
            SUM(acquisitions) AS total_acquisitions,
            SUM(payments) AS total_payments,
            ROUND(SUM(site_visits) * 100.0 / SUM(impressions), 2) AS visit_rate_pct,
            ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2) AS acq_rate_pct,
            ROUND(SUM(payments) * 100.0 / SUM(acquisitions), 2) AS pay_rate_pct
        FROM funnel_data
        GROUP BY week
        ORDER BY week
    """
    rows = conn.execute(query).fetchall()

What does this query do?

GROUP BY week collapses all 36 rows per week (3 channels x 4 geographies x 3 segments) into a single summary row. Each SUM() totals a funnel metric across all segments.

The ROUND() expressions calculate conversion rates between funnel stages. For example, visit_rate_pct is the percentage of impressions that became site visits.

  • Still inside weekly_summary, add the print formatting below the rows = conn.execute(query).fetchall() line. Then add the __main__ block at the bottom of the file:
    # Print a formatted table of weekly results
    print("=" * 90)
    print("WEEKLY FUNNEL SUMMARY")
    print("=" * 90)
    print(
        f"{'Week':<12} {'Impressions':>12} {'Visits':>10} {'Acqs':>8} "
        f"{'Payments':>10} {'Visit%':>8} {'Acq%':>8} {'Pay%':>8}"
    )
    print("-" * 90)
    for row in rows:
        print(
            f"{row[0]:<12} {row[1]:>12,} {row[2]:>10,} {row[3]:>8,} "
            f"{row[4]:>10,} {row[5]:>8}% {row[6]:>7}% {row[7]:>7}%"
        )
    print()


if __name__ == "__main__":
    conn = connect()
    weekly_summary(conn)
    conn.close()

What does this code do?

The f-strings format each row into aligned columns using Python's string alignment syntax. :<12 left-aligns in a 12-character-wide field. :>12, right-aligns with comma separators for large numbers.

The if __name__ == "__main__" block runs all your analysis functions when you execute the script directly.

  • Save analyze_funnel.py.
  • Run the script to see your first funnel summary by running:
python analyze_funnel.py

You should see a table with four rows (W30 through W33), showing impressions, visits, acquisitions, payments, and conversion rates for each week.

Seeing an error?

  • If you see no such table: funnel_data, make sure ad_sales.db is in the same folder as analyze_funnel.py. Rerun python create_database.py if needed.
  • If you see IndentationError, check that the print statements inside weekly_summary are indented with exactly 4 spaces.

Still stuck? Help me debug my analyze_funnel.py script. I'm getting an error when running python analyze_funnel.py after creating the weekly_summary function.

Add the channel breakdown query

The weekly totals show the big picture, but a stakeholder's next question is always "which channel drove the change?" This query breaks acquisitions down by channel and week so you can spot which channel is underperforming.

  • In analyze_funnel.py, add the channel_breakdown function below weekly_summary:
def channel_breakdown(conn):
    # Break down acquisitions by channel and week
    query = """
        SELECT
            week,
            channel,
            SUM(acquisitions) AS total_acquisitions,
            ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2) AS acq_rate_pct
        FROM funnel_data
        GROUP BY week, channel
        ORDER BY week, channel
    """
    rows = conn.execute(query).fetchall()
    print("=" * 60)
    print("ACQUISITIONS BY CHANNEL AND WEEK")
    print("=" * 60)
    print(f"{'Week':<12} {'Channel':<18} {'Acquisitions':>14} {'Acq Rate':>10}")
    print("-" * 60)
    for row in rows:
        print(f"{row[0]:<12} {row[1]:<18} {row[2]:>14,} {row[3]:>9}%")
    print()

What does this query do?

GROUP BY week, channel creates one row for each channel in each week. This lets you compare how Organic Search, Paid Social, and Display Ads performed side by side, week over week.

The acquisition rate (acq_rate_pct) measures what percentage of site visits converted into acquisitions. A drop in this rate for a specific channel is a red flag.

  • In the if __name__ block at the bottom of the file, find this line:
    weekly_summary(conn)
    conn.close()
  • Add channel_breakdown(conn) between those two lines, so the block now looks like this:
    weekly_summary(conn)
    channel_breakdown(conn)
    conn.close()
  • Save analyze_funnel.py and run it again:
python analyze_funnel.py

Below the weekly summary, you should now see a second table breaking out acquisitions by channel per week. Scan the Paid Social rows. Do the W33 numbers look different from the other weeks?

Not seeing the channel breakdown?

  • Make sure channel_breakdown(conn) appears before conn.close() in the __main__ block. Calling it after the connection is closed causes an error.
  • Check that the channel_breakdown function is defined at the module level (no extra indentation), not nested inside weekly_summary.

Still stuck? Help me debug why my channel_breakdown function output is not appearing when I run analyze_funnel.py.

The channel breakdown shows the numbers, but eyeballing a table to find outliers is slow. In Fractal interviews, they test whether you can use HAVING to filter aggregated results. You'll add a query that automatically flags channel-weeks performing more than 10% below the overall average.

  • Still inside the channel_breakdown function, add this HAVING query below the last print() call:
    # Use HAVING to filter for underperforming channel-weeks
    having_query = """
        SELECT
            week,
            channel,
            ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2) AS acq_rate_pct
        FROM funnel_data
        GROUP BY week, channel
        HAVING acq_rate_pct < (
            SELECT ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2)
            FROM funnel_data
        ) * 0.9
        ORDER BY acq_rate_pct
    """
    low_performers = conn.execute(having_query).fetchall()
    if low_performers:
        print("UNDERPERFORMING CHANNEL-WEEKS (acq rate > 10% below overall average):")
        for row in low_performers:
            print(f"  {row[0]} | {row[1]:<18} | Acq Rate: {row[2]}%")
        print()

What is HAVING and how is it different from WHERE?

WHERE filters individual rows before any grouping happens. HAVING filters after GROUP BY runs, so it can reference aggregated values like SUM() or ROUND().

This query uses a subquery to calculate the overall average acquisition rate, then keeps only channel-weeks where the rate fell below 90% of that average. This is a common Fractal interview question.

  • Save analyze_funnel.py and run it again:
python analyze_funnel.py

Below the channel table, you should see a new section listing any channel-week combinations that underperformed. Look for Paid Social in W33.

Add a week-over-week comparison using window functions

You can see the drop now, but you need to quantify it. The LAG() window function lets you compare each week's acquisitions to the previous week in a single query, without a self-join. This is the exact window function pattern Fractal asks about in interviews.

  • In analyze_funnel.py, add the start of the wow_comparison function below channel_breakdown. This first part builds two CTEs that set up the week-over-week comparison:
def wow_comparison(conn):
    # CTE query using LAG to compare acquisitions week-over-week
    query = """
        WITH weekly_channel AS (
            SELECT
                week,
                channel,
                geography,
                SUM(acquisitions) AS total_acqs
            FROM funnel_data
            GROUP BY week, channel, geography
        ),
        with_lag AS (
            SELECT
                week,
                channel,
                geography,
                total_acqs,
                LAG(total_acqs) OVER (
                    PARTITION BY channel, geography ORDER BY week
                ) AS prev_week_acqs
            FROM weekly_channel
        )

What are CTEs and LAG doing here?

A CTE (Common Table Expression) is a named temporary result set you define with WITH. The first CTE weekly_channel aggregates acquisitions by week, channel, and geography.

The second CTE with_lag uses LAG(total_acqs) OVER (PARTITION BY channel, geography ORDER BY week) to peek at the previous week's acquisitions for the same channel-geography pair. This creates a side-by-side comparison without needing a self-join.

  • Still inside the query string in wow_comparison, add the final SELECT clause below the closing ) of the with_lag CTE. Then add the print formatting and close the function:
        SELECT
            week,
            channel,
            geography,
            total_acqs,
            prev_week_acqs,
            total_acqs - prev_week_acqs AS abs_change,
            ROUND(
                (total_acqs - prev_week_acqs) * 100.0 / prev_week_acqs, 1
            ) AS pct_change
        FROM with_lag
        WHERE prev_week_acqs IS NOT NULL
        ORDER BY pct_change ASC
        LIMIT 5
    """
    rows = conn.execute(query).fetchall()
    print("=" * 85)
    print("TOP 5 LARGEST WEEK-OVER-WEEK DECLINES (by channel + geography)")
    print("=" * 85)
    print(
        f"{'Week':<12} {'Channel':<18} {'Geo':<10} "
        f"{'Acqs':>6} {'Prev':>6} {'Change':>8} {'%':>8}"
    )
    print("-" * 85)
    for row in rows:
        print(
            f"{row[0]:<12} {row[1]:<18} {row[2]:<10} "
            f"{row[3]:>6,} {row[4]:>6,} {row[5]:>8,} {row[6]:>7}%"
        )
    print()

What does the final SELECT do?

The final SELECT reads from the with_lag CTE and calculates two things: the absolute change (total_acqs - prev_week_acqs) and the percentage change. ORDER BY pct_change ASC LIMIT 5 surfaces the five biggest declines.

WHERE prev_week_acqs IS NOT NULL filters out W30 rows, since the first week has no prior week to compare against.

  • In the if __name__ block, find this line:
    channel_breakdown(conn)
    conn.close()
  • Add wow_comparison(conn) between those two lines, so the block now looks like this:
    channel_breakdown(conn)
    wow_comparison(conn)
    conn.close()
  • Save analyze_funnel.py.

Before you run the script, predict: which channel and geography combination do you think will show the biggest week-over-week decline in W33?

  • Run the full analysis by running:
python analyze_funnel.py

The final table shows the top 5 largest week-over-week declines. Paid Social in EMEA should appear near the top with the steepest percentage drop in W33.

Seeing a syntax error in the query?

  • Make sure the SELECT clause you added follows directly after the closing ) of the with_lag CTE, with no extra blank lines or missing commas between column names.
  • Verify the query string starts with """ and ends with """ on its own line. A missing closing triple-quote causes Python to treat the next lines as part of the string.
  • Check that wow_comparison is defined at the module level (not indented inside another function).

Still stuck? Help me fix a syntax error in my wow_comparison function that uses CTEs and LAG in analyze_funnel.py.

✔️ Awesome, I've got everything!

Make sure you've saved analyze_funnel.py and that the output shows all three sections: the weekly summary, the channel breakdown with HAVING filter, and the top 5 week-over-week declines.

ⓧ I'd like to double check the full code

Scroll through analyze_funnel.py and confirm each of these is present, in order:

  • import sqlite3 at the top of the file.
  • connect() function returning sqlite3.connect("ad_sales.db").
  • weekly_summary(conn) function with a GROUP BY week query and print formatting.
  • channel_breakdown(conn) function with two queries: the basic GROUP BY week, channel query and the HAVING filter query.
  • wow_comparison(conn) function with two CTEs (weekly_channel and with_lag) and the final SELECT with print formatting.
  • if __name__ == "__main__" block calling weekly_summary(conn), channel_breakdown(conn), and wow_comparison(conn) in that order.

Your analysis shows something went wrong in W33. The numbers dropped, and the window function pinpointed where. But "numbers went down" is not good enough for an executive. In the next step, you'll dig into the root cause and structure your findings into something a VP can act on.

Root Cause Deep Dive

Your analyze_funnel.py script revealed that W33 acquisitions dropped. The channel breakdown flagged Paid Social, and the LAG() window function showed where the biggest declines hit. But a stakeholder reading that output would ask the obvious next question: why did it drop?

In this step, you'll do what the Fractal JD calls "root cause analysis and deep dives." You'll drill into the specific channel and geography combination driving the W33 decline, compare it against a trailing average baseline, and package the findings into a structured summary that Claude can turn into a narrative in the next step.

In this step, get ready to:
  • Write a CTE-based SQL query comparing W33 vs W32 acquisitions by channel and geography.
  • Build a structured analysis summary that pinpoints the primary driver of the drop.
  • Output the findings as JSON for AI narrative generation.
Write the week-over-week comparison query

The funnel analysis in the previous step showed the overall drop, but it didn't isolate which channel-geography pair caused it. To find the root cause, you need to compare every combination of channel and geography between W33 and W32.

  • Create a new file called root_cause.py in your ad-sales-report folder and paste this scaffold:
import sqlite3
import json

# Reusable database connection
def connect():
    return sqlite3.connect("ad_sales.db")

# Main investigation function
def investigate(conn):
    pass

if __name__ == "__main__":
    conn = connect()
    summary = investigate(conn)
    conn.close()

What does this code do?

This sets up the same pattern as analyze_funnel.py: a connect() helper, an investigate() function where all the analysis will live, and a main block that runs it. The pass placeholder keeps the function valid until you add the real query next.

  • Save root_cause.py.
  • Confirm the file exists by running:
ls

You should see root_cause.py alongside create_database.py, analyze_funnel.py, and ad_sales.db in the output.

Now you'll replace the pass placeholder with a CTE query that compares W33 and W32 acquisitions across every channel-geography combination.

  • In root_cause.py, find the line that says pass inside the investigate function. Replace pass with this CTE query:
    wow_query = """
        WITH current_week AS (
            SELECT channel, geography,
                   SUM(acquisitions) AS acqs_w33
            FROM funnel_data
            WHERE week = '2026-W33'
            GROUP BY channel, geography
        ),
        previous_week AS (
            SELECT channel, geography,
                   SUM(acquisitions) AS acqs_w32
            FROM funnel_data
            WHERE week = '2026-W32'
            GROUP BY channel, geography
        )
        SELECT
            c.channel,
            c.geography,
            c.acqs_w33,
            p.acqs_w32,
            c.acqs_w33 - p.acqs_w32 AS abs_change,
            ROUND(
                (c.acqs_w33 - p.acqs_w32) * 100.0 / p.acqs_w32, 1
            ) AS pct_change
        FROM current_week c
        JOIN previous_week p
            ON c.channel = p.channel AND c.geography = p.geography
        ORDER BY abs_change ASC
    """
    rows = conn.execute(wow_query).fetchall()

What is a CTE and why use one here?

A CTE (Common Table Expression) creates a temporary named result set you can reference later in the same query. Here you define two CTEs: current_week aggregates W33 acquisitions by channel and geography, and previous_week does the same for W32.

The main SELECT then joins them on channel and geography to calculate the absolute and percentage change. Sorting by abs_change ASC puts the biggest decline first. CTEs are a core interview pattern at Fractal because they make complex multi-step queries readable.

  • Still in root_cause.py, add this print formatting directly below the rows = conn.execute(wow_query).fetchall() line:
    # Print the comparison table
    print("=" * 80)
    print("ROOT CAUSE ANALYSIS: W33 vs W32 Acquisitions")
    print("=" * 80)
    print(
        f"{'Channel':<18} {'Geography':<10} {'W33':>8} {'W32':>8} "
        f"{'Change':>8} {'%':>8}"
    )
    print("-" * 80)
    for row in rows:
        print(
            f"{row[0]:<18} {row[1]:<10} {row[2]:>8,} {row[3]:>8,} "
            f"{row[4]:>8,} {row[5]:>7}%"
        )

What does this code do?

This prints a formatted table showing every channel-geography combination with its W33 acquisitions, W32 acquisitions, the absolute change, and the percentage change. The combination with the largest decline appears first because of the ORDER BY abs_change ASC in the query.

Before you run this, think about what you expect to see. You know the database has a deliberate 40% drop in Paid Social EMEA acquisitions for W33. Will that show up as the top decline in the table?

  • Save root_cause.py and run the script:
python root_cause.py

You should see a comparison table with Paid Social + EMEA at the top showing the largest percentage decline. The other channel-geography combinations show smaller fluctuations.

Seeing a syntax error or empty table?

  • Make sure you deleted the pass line entirely. Leaving it above the query causes an indentation issue.
  • Check that all the code inside investigate() is indented with 4 spaces. Python is sensitive to indentation levels.
  • Confirm ad_sales.db exists in the same folder. If you deleted it, re-run python create_database.py to recreate it.

Help me debug my root_cause.py script

Build the analysis summary

The comparison table tells you WHAT happened. Now you need to package the findings into a structured format that answers WHY and feeds directly into Claude for narrative generation.

  • In root_cause.py, add this code below the print table (still inside the investigate function, after the for row in rows: loop):
    # Calculate 3-week trailing average for each channel-geography pair
    avg_query = """
        SELECT
            channel,
            geography,
            ROUND(AVG(acquisitions_total), 0) AS avg_acqs
        FROM (
            SELECT channel, geography, week,
                   SUM(acquisitions) AS acquisitions_total
            FROM funnel_data
            WHERE week IN ('2026-W30', '2026-W31', '2026-W32')
            GROUP BY channel, geography, week
        )
        GROUP BY channel, geography
    """
    avg_rows = conn.execute(avg_query).fetchall()
    avg_lookup = {(r[0], r[1]): r[2] for r in avg_rows}

    # Get overall totals and identify the primary driver
    total_w33 = conn.execute(
        "SELECT SUM(acquisitions) FROM funnel_data WHERE week = '2026-W33'"
    ).fetchone()[0]
    total_w32 = conn.execute(
        "SELECT SUM(acquisitions) FROM funnel_data WHERE week = '2026-W32'"
    ).fetchone()[0]
    primary_driver = rows[0]
    three_week_avg = avg_lookup.get(
        (primary_driver[0], primary_driver[1]), 0
    )

What does this code do?

The avg_query calculates the average acquisitions across W30, W31, and W32 for each channel-geography pair. This gives you a baseline to compare W33 against. A single bad week could be noise, but dropping below a 3-week average signals a real problem.

The primary_driver = rows[0] grabs the first row from the comparison table. Because the query sorts by abs_change ASC, the first row is the channel-geography pair with the biggest decline.

  • Save root_cause.py and run the script to confirm no errors:
python root_cause.py

You should see the same comparison table as before. No errors means the new queries ran successfully behind the scenes.

  • Now add the summary dictionary below the three_week_avg line. This structures all your findings into a single Python dictionary:
    # Structure findings for AI narrative generation
    summary = {
        "overall_change": {
            "w33_total": total_w33,
            "w32_total": total_w32,
            "abs_change": total_w33 - total_w32,
            "pct_change": round(
                (total_w33 - total_w32) * 100.0 / total_w32, 1
            ),
        },
        "primary_driver": {
            "channel": primary_driver[0],
            "geography": primary_driver[1],
            "w33_acqs": primary_driver[2],
            "w32_acqs": primary_driver[3],
            "pct_change": primary_driver[5],
            "three_week_avg": three_week_avg,
        },
        "top_declines": [
            {
                "channel": r[0],
                "geography": r[1],
                "pct_change": r[5],
            }
            for r in rows[:3]
        ],
    }

What does this dictionary capture?

  • overall_change: the total W33 vs W32 acquisition change across ALL channels and geographies.
  • primary_driver: the single channel-geography pair with the largest decline, plus its 3-week average for context.
  • top_declines: the 3 worst-performing channel-geography pairs. This gives Claude enough context to write a nuanced narrative, not just a single headline.
  • Add this final section directly below the summary dictionary to print the JSON output and return it:
    # Print the structured summary as JSON
    print()
    print("=" * 80)
    print("STRUCTURED SUMMARY (for AI narrative generation)")
    print("=" * 80)
    print(json.dumps(summary, indent=2))
    print()

    return summary

Why return the summary?

The return summary line makes this function reusable. In the next step, generate_narrative.py will import and call investigate() to get the summary dictionary, then feed it directly to Claude as part of the prompt.

Before you run this, predict: will the primary_driver in the JSON output point to a specific channel and geography? What do you expect it to be?

  • Save root_cause.py and run the script:
python root_cause.py

You should see the comparison table followed by a structured JSON summary. The primary_driver section identifies Paid Social in EMEA as the largest decline, with a specific percentage drop and its 3-week average baseline for comparison.

Not seeing the JSON summary?

  • Check that the summary dictionary and the print(json.dumps(summary, indent=2)) line are both indented at the same level as the rest of the code inside investigate(). They should be 4 spaces in.
  • Make sure the return summary line is at the bottom of investigate(), not inside the for loop or the dictionary.
  • If you see a NameError for json, confirm that import json is at the top of the file.

Help me debug my root cause summary output

✔️ Awesome, I've got everything!

Great. Double check you've saved root_cause.py and the JSON summary is printing correctly.

ⓧ I'd like to double check the full code

Confirm each of these sections appears in your root_cause.py, in this order:

  • Imports: import sqlite3 and import json at the top.
  • connect() function: returns a connection to ad_sales.db.
  • investigate() function: CTE wow_query comparing W33 vs W32, then print formatting for the comparison table.
  • Trailing average: avg_query calculating 3-week averages, total_w33 and total_w32 totals, primary_driver, and three_week_avg.
  • Summary dictionary: summary with overall_change, primary_driver, and top_declines.
  • Print + return: print(json.dumps(summary, indent=2)) followed by return summary.
  • Main block: if __name__ == "__main__": calling investigate(conn).

You now have the full picture: you know that acquisitions dropped in W33, you know exactly which channel and geography caused it, and you have the numbers to prove it. That structured summary is exactly the kind of input an executive would need. In the next step, you'll send it to Claude and let AI turn those findings into a polished weekly performance narrative.

Generate the AI-Powered Weekly Narrative

Your root cause analysis pinpointed exactly which channel and geography drove the W33 drop. That is the hard part of the analytical work. Now you need to communicate it.

In this step, you will feed your SQL findings into Claude and have it generate the kind of weekly executive narrative the Fractal JD describes: what drove results and what needs attention. This is the generative AI differentiator the role highlights.

In this step, get ready to:
  • Build a data gathering function that re-runs your SQL queries and imports your root cause analysis.
  • Craft a structured prompt that tells Claude exactly what kind of narrative to write.
  • Generate and save a polished weekly performance report ready for an executive audience.
Create the data gathering function

Before Claude can write a narrative, it needs structured data to work with. You will build a function that queries the database for weekly summaries and channel breakdowns, then imports the root cause findings from root_cause.py.

  • Create a new file called generate_narrative.py in your ad-sales-report folder and paste the following code to set up imports and the weekly funnel query:
import sqlite3
import json
import anthropic

def get_analysis_data():
    # Connect to the ad sales database
    conn = sqlite3.connect("ad_sales.db")

    # Query weekly totals and conversion rates across the full funnel
    weekly_query = """
        SELECT
            week,
            SUM(impressions) AS impressions,
            SUM(site_visits) AS visits,
            SUM(acquisitions) AS acquisitions,
            SUM(payments) AS payments,
            ROUND(SUM(site_visits) * 100.0 / SUM(impressions), 2) AS visit_rate,
            ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2) AS acq_rate,
            ROUND(SUM(payments) * 100.0 / SUM(acquisitions), 2) AS pay_rate
        FROM funnel_data
        GROUP BY week
        ORDER BY week
    """
  • The function continues. Paste this code directly below in the same file to execute the query and structure the results:
    # Execute the query and build a list of weekly records
    weekly_rows = conn.execute(weekly_query).fetchall()
    weekly_data = []
    for row in weekly_rows:
        weekly_data.append(
            {
                "week": row[0],
                "impressions": row[1],
                "visits": row[2],
                "acquisitions": row[3],
                "payments": row[4],
                "visit_rate_pct": row[5],
                "acq_rate_pct": row[6],
                "pay_rate_pct": row[7],
            }
        )

What does this code do?

This is the first half of your data gathering function. It runs the same weekly summary query you wrote in analyze_funnel.py, but instead of printing a table, it stores each week's numbers in a Python dictionary.

Claude needs structured data, not formatted terminal output. That is why you are building dictionaries instead of printing rows.

  • Now complete the get_analysis_data() function by pasting this code directly below the previous section. This adds the channel breakdown query and imports your root cause findings:
    # Query acquisition rates broken down by channel
    channel_query = """
        SELECT
            week, channel,
            SUM(acquisitions) AS acquisitions,
            ROUND(SUM(acquisitions) * 100.0 / SUM(site_visits), 2) AS acq_rate
        FROM funnel_data
        GROUP BY week, channel
        ORDER BY week, channel
    """
    channel_rows = conn.execute(channel_query).fetchall()
    channel_data = []
    for row in channel_rows:
        channel_data.append(
            {
                "week": row[0],
                "channel": row[1],
                "acquisitions": row[2],
                "acq_rate_pct": row[3],
            }
        )
    # Import and run the root cause investigation from the previous step
    from root_cause import investigate
    root_cause_summary = investigate(conn)
    conn.close()

    return {
        "weekly_summary": weekly_data,
        "channel_breakdown": channel_data,
        "root_cause": root_cause_summary,
    }

What does this section do?

This completes the data gathering function. The channel query breaks down acquisitions by channel per week, which is where the Paid Social drop becomes visible.

The from root_cause import investigate line reuses the function you built in Step 4. It runs the root cause analysis and returns the structured summary dictionary. All three data sources (weekly totals, channel breakdown, root cause) are bundled into one dictionary for Claude.

  • Save generate_narrative.py.
  • Test that your data gathering function works by running this command:
python -c "from generate_narrative import get_analysis_data; data = get_analysis_data(); print(list(data.keys()))"

You should see the root cause analysis tables print (from the investigate() function), followed by ['weekly_summary', 'channel_breakdown', 'root_cause'] on the last line.

Seeing a ModuleNotFoundError?

  • Make sure root_cause.py is in the same folder as generate_narrative.py. Both files must be inside ad-sales-report.
  • Check that your virtual environment is still activated. You should see (.venv) at the start of your terminal prompt.
  • If you see ModuleNotFoundError: No module named 'anthropic', re-run pip install anthropic in your activated virtual environment.

Why can't Python find my module?

Build the prompt and call Claude

The data is ready. Now you need to tell Claude what to do with it. A good prompt for executive narratives is specific about the audience, the structure, and the tone.

  • Add the generate_narrative() function below get_analysis_data() by pasting this code. It builds the structured prompt:
def generate_narrative(analysis_data):
    # Create the Anthropic client (reads ANTHROPIC_API_KEY from environment)
    client = anthropic.Anthropic()

    # Build a structured prompt with all analysis findings
    prompt = f"""You are writing a weekly Ad Sales performance update for the
VP of Ad Sales at a Fortune 10 tech company.

Here is this week's analysis data:

WEEKLY FUNNEL SUMMARY:
{json.dumps(analysis_data['weekly_summary'], indent=2)}

CHANNEL BREAKDOWN:
{json.dumps(analysis_data['channel_breakdown'], indent=2)}

ROOT CAUSE ANALYSIS:
{json.dumps(analysis_data['root_cause'], indent=2)}

Write a concise weekly performance narrative (under 300 words) that covers:
1. Overall performance this week vs last week (use specific numbers)
2. The 3 most important findings from the funnel analysis
3. Root cause of any significant changes
4. 2-3 recommended next steps

Use a professional, executive-ready tone. Lead with the headline finding.
Do not use bullet points for the main narrative. Use them only for the
recommended actions at the end."""

Why is the prompt so specific?

Vague prompts produce vague output. This prompt gives Claude four things: the audience (VP of Ad Sales), the raw data (injected via json.dumps()), the structure (4 numbered requirements), and the tone (professional, executive-ready).

Each constraint narrows Claude's output toward something an executive can act on. Without the "under 300 words" limit, for example, you would get a multi-page essay instead of a tight weekly update.

  • Complete the function by pasting the API call directly below the prompt:
    # Send the prompt to Claude with an executive analyst persona
    message = client.messages.create(
        model="claude-sonnet-5",
        max_tokens=1024,
        system="You are a Senior Ad Sales Analyst writing a weekly performance "
        "update for executive leadership. Be concise, data-driven, and "
        "actionable. Always include specific numbers.",
        messages=[{"role": "user", "content": prompt}],
    )

    # Extract the narrative text from Claude's response
    narrative = message.content[0].text
    return narrative

What does the API call do?

  • model="claude-sonnet-5" selects Claude Sonnet 5, which is fast and cost-effective for text generation tasks like this.
  • The system parameter sets Claude's persona separately from the user prompt. This keeps the role instruction stable while the data in the user prompt changes each week.
  • message.content[0].text extracts the text from Claude's response object.
  • Finally, add the main() function and entry point at the bottom of the file. This orchestrates everything and saves the report:
def main():
    # Gather all analysis data from the database
    print("Gathering analysis data...")
    analysis_data = get_analysis_data()

    # Generate the narrative using Claude
    print("Generating narrative with Claude...")
    print()

    narrative = generate_narrative(analysis_data)

    # Print the formatted report
    print("=" * 70)
    print("WEEKLY AD SALES PERFORMANCE REPORT - W33")
    print("=" * 70)
    print()
    print(narrative)
    print()

    # Save the report to a text file
    with open("weekly_report_W33.txt", "w", encoding="utf-8") as f:
        f.write("WEEKLY AD SALES PERFORMANCE REPORT - W33\n")
        f.write("=" * 50 + "\n\n")
        f.write(narrative)

    print("Report saved to weekly_report_W33.txt")

if __name__ == "__main__":
    main()

What does main() do?

The main() function is the orchestrator. It calls get_analysis_data() to gather your SQL findings, passes them to generate_narrative() to get Claude's narrative, prints the result, and saves it to weekly_report_W33.txt.

  • Save generate_narrative.py.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the file before running it.

ⓧ I'd like to double check the full code

Scroll up and confirm your generate_narrative.py has these sections in order:

  • Imports: sqlite3, json, anthropic.
  • get_analysis_data() function: weekly query, weekly data loop, channel query, channel data loop, root cause import, return statement with all three data keys.
  • generate_narrative() function: Anthropic client, f-string prompt with json.dumps() for all three data sections, messages.create() call, narrative extraction and return.
  • main() function: gathers data, generates narrative, prints report, saves to weekly_report_W33.txt.
  • Entry point: if __name__ == "__main__": calls main().

Before you run the script, think about this: what do you expect Claude's narrative to focus on? Will it pick up on the Paid Social EMEA drop, or will it treat the week as business-as-usual?

  • Run the complete pipeline by running this command:
python generate_narrative.py

You should see the root cause analysis tables print first, then a polished multi-paragraph weekly performance narrative under the WEEKLY AD SALES PERFORMANCE REPORT - W33 header. Claude's narrative will reference specific numbers from your funnel data, name the Paid Social EMEA drop as the root cause, and recommend next steps.

The report is also saved to weekly_report_W33.txt in your project folder.

Why does the root cause table print before the narrative?

Your get_analysis_data() function calls investigate(conn) from root_cause.py, which prints its analysis tables as a side effect. The narrative follows because Claude needs a few seconds to generate the response.

Seeing an AuthenticationError?

  • Make sure your ANTHROPIC_API_KEY environment variable is set in the same terminal session where you are running the script. If you opened a new terminal, you need to set it again.
  • Check that you copied your full API key with no extra spaces or missing characters.
  • Confirm your virtual environment is activated. You should see (.venv) at the start of your terminal prompt.

Why is my Anthropic API key not being recognized?

Secret mission

Add Run-Rate Forecasting

Your narrative tells the VP what happened and why. But a Senior Decision Scientist also projects what is coming next. Add a forecasting component that calculates trailing-average run rates by channel, models recovery scenarios for the EMEA Paid Social drop, and generates an Outlook section in your weekly narrative.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Your Claude API usage was pay-per-call (approximately $0.02 total) and stops when you stop calling it.

Decide whether to keep your project files for interview prep, or delete them entirely.

Resources you used:

  • ad-sales-report project folder (contains all Python scripts, ad_sales.db, the .venv virtual environment, and weekly_report_W33.txt).
  • Claude API usage (approximately $0.02 total across all calls, pay-per-call with no ongoing charges).

Keep everything running

No action needed. Choose this if you want to keep your scripts and database for Fractal interview prep or further experimentation.

  • All files are local on your machine. There are no servers, cloud resources, or subscriptions to worry about.
  • Your ANTHROPIC_API_KEY environment variable was set for the current terminal session only. It disappears when you close that terminal window.
  • You can re-run any of the scripts at any time by opening the ad-sales-report folder in VS Code, activating the virtual environment, and setting your API key again.

Pause - I'll come back to this later

There are no running processes or paid resources to pause. Your project is already idle.

  • Close VS Code if you want to free up memory.
  • Your files stay on disk until you delete them. Nothing is running in the background.
  • When you come back, reopen the ad-sales-report folder in VS Code, activate your virtual environment, and set ANTHROPIC_API_KEY again.

Delete - I don't want to use this again

Remove the entire project folder to clean up all files, the database, and the virtual environment.

  • Close VS Code.
  • Delete the ad-sales-report folder on your Desktop by running this command in a new terminal.

macOS

rm -rf ~/Desktop/ad-sales-report

Windows

Remove-Item -Recurse -Force ~\Desktop\ad-sales-report
  • This removes all Python scripts, ad_sales.db, weekly_report_W33.txt, and the .venv virtual environment.
  • Your Claude API usage is already consumed. There is no subscription or running service to cancel.
  • Python and VS Code remain installed on your machine. These are general-purpose tools you will likely use for other projects.

Nice Work!

Nice Work!

You just built a complete ad sales analytics pipeline that mirrors the Senior Decision Scientist's daily workflow at Fractal. You can query a funnel, find what's broken, explain why, and deliver a narrative an executive can act on.

You've learned how to:

  • Write SQL queries using GROUP BY, HAVING, window functions (LAG), CTEs, and self-joins against a SQLite ad sales funnel database to calculate conversion rates across channels, geographies, and segments.
  • Perform a structured root cause analysis that pinpointed the exact channel and geography driving a week-over-week performance drop, turning a vague "numbers went down" into a specific, actionable finding.
  • Use the Claude API to auto-generate polished, executive-ready weekly performance narratives from your analysis data, covering what happened, why, and what needs attention.
  • Secret Mission: Add run-rate forecasting that projects next week's acquisitions by channel using trailing averages, with "with recovery" and "without recovery" scenarios for the affected segment.

Ready to quiz yourself?