Markov Chain Attribution in MySQL

Introduction

30 Second Summary

Every company spends money on marketing across multiple channels. The problem is that most businesses give all the credit to whatever channel a customer clicked last before buying. This means discovery channels that introduced a customer in the first place get zero recognition.

In this project, you will build a revenue attribution system in pure MySQL that compares four different models to reveal which marketing channels truly drive conversions. You will progress from naive heuristic models to a probabilistic Markov chain approach that measures what happens when you remove each channel entirely.

What You'll Build

A complete multi-touch attribution case study in MySQL that generates synthetic e-commerce journey data, implements four attribution models, and produces a stakeholder-ready executive summary with channel investment recommendations.

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

• A MySQL database with 2,000 synthetic customer journeys across 5 marketing channels, complete with position-biased touchpoints and conversion events.
• Four attribution models (last-click, first-click, linear, and Markov removal effect) that allocate revenue credit in fundamentally different ways.
• An executive summary view with investment recommendations that tells the data-driven story of which channels are undervalued and deserve more budget.
• Secret Mission: A time-decay attribution model and channel sequence analysis revealing which channel pairs drive the highest conversion rates.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, INSERT, JOIN, GROUP BY). This project teaches intermediate-to-advanced concepts like window functions, CTEs, and stored procedures as you go. You will also need MySQL 8.0 and MySQL Workbench installed on your machine.

Create the Database and Schema

Before you can analyze which marketing channels drive revenue, you need a place to store the data. Every attribution model you build in this project will query from the same underlying tables.

In this step, you will create a dedicated MySQL database and define four tables that represent the e-commerce customer journey: channels, customers, touchpoints, and conversions.

In this step, get ready to:

• Create the ecommerce_attribution database.
• Define four core tables with primary keys and foreign key relationships.
• Verify your schema is set up correctly.

Create the ecommerce_attribution database

Every MySQL project starts by creating a dedicated database. This keeps your attribution tables isolated from any other work on your local server.

• In MySQL Workbench, open a new query tab by pressing Cmd+T (macOS) or Ctrl+T (Windows).
• Create the database and select it by pasting and running this SQL:

-- E-Commerce Revenue Attribution: Markov Chain Analysis
-- Step 1: Create database and schema

CREATE DATABASE IF NOT EXISTS ecommerce_attribution;
USE ecommerce_attribution;

• Execute the statements by pressing Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows).

What does this code do?

CREATE DATABASE IF NOT EXISTS creates the database only if it does not already exist. This makes the script safe to re-run without errors.

USE ecommerce_attribution tells MySQL that all subsequent statements should target this database.

• Check the Output panel at the bottom of MySQL Workbench. You should see two green checkmarks indicating both statements executed successfully.

Seeing an access denied error?

Make sure you are connected with a user that has CREATE privileges on your local instance. The default root user has all privileges.

help me fix a MySQL access denied error when creating a database

Create the four core tables

Your attribution data model has two layers. Reference tables store static entities (channels and customers). Event tables store time-stamped actions (touchpoints and conversions) linked back to those entities through foreign keys.

• Add the two reference tables to your query tab by pasting this code below your existing statements:

-- Marketing channels
CREATE TABLE channels (
    channel_id INT PRIMARY KEY,
    channel_name VARCHAR(50) NOT NULL,
    channel_category VARCHAR(50) NOT NULL
);

-- Customers
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    signup_date DATE NOT NULL,
    device_type VARCHAR(20) NOT NULL
);

What do these tables store?

channels holds the five marketing channels you will attribute revenue to (Organic Search, Paid Search, Email, Social Media, Direct). Each has a category like 'Owned' or 'Paid'.

customers stores 2,000 customers with their signup date and device type. These are the people whose journeys you will trace.

• Add the two event tables below the reference tables:

-- Touchpoints (journey events)
CREATE TABLE touchpoints (
    touchpoint_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    channel_id INT NOT NULL,
    touchpoint_timestamp DATETIME NOT NULL,
    session_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (channel_id) REFERENCES channels(channel_id)
);

-- Conversions (purchases)
CREATE TABLE conversions (
    conversion_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    conversion_timestamp DATETIME NOT NULL,
    revenue DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

What do these tables store?

touchpoints records every interaction a customer has with a marketing channel. Each row is one moment in a customer's journey, timestamped and linked to both the customer and the channel.

conversions records purchases. The revenue column is what you will attribute back to channels. The foreign key to customers lets you connect each purchase to a full journey of touchpoints.

Why do touchpoints use AUTO_INCREMENT?

The channels and customers tables use manually assigned IDs because you will insert specific, known values. Touchpoints and conversions are generated in bulk, so AUTO_INCREMENT lets MySQL assign unique IDs automatically.

• Select all the code in your query tab (Cmd+A on macOS, Ctrl+A on Windows).
• Execute all statements by pressing Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows).
• Check the Output panel. You should see green checkmarks for all six statements (CREATE DATABASE, USE, and four CREATE TABLEs).

Seeing a 'table already exists' error?

If you already ran the CREATE DATABASE and USE statements earlier, MySQL will try to re-execute them. This is fine for those two (IF NOT EXISTS prevents errors). But if you also ran a CREATE TABLE previously, you will need to drop it first.

The easiest fix: highlight only the new CREATE TABLE statements you have not yet executed, then press Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows) to run just the selection.

help me fix a table already exists error in MySQL

Verify the schema

Running a quick check confirms that all four tables were created with the correct columns and data types.

• Verify your tables exist by running this query:

SHOW TABLES;

• You should see four tables listed: channels, conversions, customers, and touchpoints.
• Inspect the structure of each table by running these DESCRIBE commands:

DESCRIBE channels;
DESCRIBE customers;
DESCRIBE touchpoints;
DESCRIBE conversions;

What should I see?

Each DESCRIBE output shows the column names, data types, nullability, and key information for that table. Confirm that:

• channels has 3 columns: channel_id (PRI key), channel_name, channel_category.
• customers has 3 columns: customer_id (PRI key), signup_date, device_type.
• touchpoints has 5 columns with touchpoint_id as PRI and two MUL keys (the foreign keys).
• conversions has 4 columns with conversion_id as PRI and one MUL key.

Don't see all four tables?

Make sure you ran USE ecommerce_attribution; before running SHOW TABLES. Without it, MySQL may be looking at a different database.

Also check that the Output panel does not show any red X icons next to your CREATE TABLE statements. A red X means that statement failed.

help me troubleshoot missing tables in my MySQL schema

• Save your query tab as 01_create_schema.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

✔️ Awesome, I've got everything!

Great. Double check you have saved your file.

ⓧ I'd like to double check the full code

Here is the complete 01_create_schema.sql file:

-- E-Commerce Revenue Attribution: Markov Chain Analysis
-- Step 1: Create database and schema

CREATE DATABASE IF NOT EXISTS ecommerce_attribution;
USE ecommerce_attribution;

-- Marketing channels
CREATE TABLE channels (
    channel_id INT PRIMARY KEY,
    channel_name VARCHAR(50) NOT NULL,
    channel_category VARCHAR(50) NOT NULL
);

-- Customers
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    signup_date DATE NOT NULL,
    device_type VARCHAR(20) NOT NULL
);

-- Touchpoints (journey events)
CREATE TABLE touchpoints (
    touchpoint_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    channel_id INT NOT NULL,
    touchpoint_timestamp DATETIME NOT NULL,
    session_id VARCHAR(36) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (channel_id) REFERENCES channels(channel_id)
);

-- Conversions (purchases)
CREATE TABLE conversions (
    conversion_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    conversion_timestamp DATETIME NOT NULL,
    revenue DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Your database schema is ready. Next up, you will fill these tables with 2,000 realistic customer journeys so you have real data to attribute.

Generate Synthetic Journey Data

Your database has structure but no data. Real attribution analysis needs thousands of customer journeys with realistic patterns to reveal how different models allocate credit.

You'll create a stored procedure that generates 2,000 synthetic customer journeys. Each journey uses position-biased channel selection, meaning discovery channels like Organic Search appear early and closing channels like Direct appear late. This mirrors real e-commerce behavior.

In this step, get ready to:

• Create a stored procedure that generates realistic multi-touch journey data.
• Execute the procedure and verify row counts across all tables.
• Run sanity checks to confirm journey lengths and revenue ranges.

Create the data generation stored procedure

A stored procedure bundles multiple SQL statements into a reusable routine that runs on the server. You need one here because generating 2,000 journeys requires looping logic that plain SQL statements cannot provide.

• Open a new query tab in MySQL Workbench by clicking the New Query Tab button (the "+" icon above the query editor area).
• Paste the following stored procedure code into the new tab:

-- Step 2: Generate synthetic attribution data
USE ecommerce_attribution;

DELIMITER //

CREATE PROCEDURE generate_attribution_data()
BEGIN
    DECLARE v_customer_id INT DEFAULT 1;
    DECLARE v_num_touches INT;
    DECLARE v_touch_num INT;
    DECLARE v_channel_id INT;
    DECLARE v_base_date DATETIME;
    DECLARE v_touch_time DATETIME;
    DECLARE v_will_convert TINYINT;
    DECLARE v_revenue DECIMAL(10,2);
    DECLARE v_rand DOUBLE;

    -- Insert channels
    INSERT INTO channels (channel_id, channel_name, channel_category) VALUES
        (1, 'Organic_Search', 'Owned'),
        (2, 'Paid_Search', 'Paid'),
        (3, 'Email', 'Owned'),
        (4, 'Social_Media', 'Paid'),
        (5, 'Direct', 'Direct');

    -- Generate 2000 customer journeys
    WHILE v_customer_id <= 2000 DO

        -- Create customer
        INSERT INTO customers (customer_id, signup_date, device_type)
        VALUES (
            v_customer_id,
            DATE_SUB('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
            ELT(FLOOR(1 + RAND() * 3), 'desktop', 'mobile', 'tablet')
        );

        -- Journey parameters
        SET v_num_touches = FLOOR(2 + RAND() * 7);
        SET v_base_date = DATE_SUB('2024-03-15', INTERVAL FLOOR(RAND() * 21) DAY);
        SET v_will_convert = IF(RAND() < 0.38, 1, 0);

        -- Generate touchpoints
        SET v_touch_num = 1;
        WHILE v_touch_num <= v_num_touches DO

            SET v_rand = RAND();

            -- Channel selection based on journey position
            IF v_touch_num = 1 THEN
                -- First touch: discovery channels dominate
                SET v_channel_id = CASE
                    WHEN v_rand < 0.35 THEN 1
                    WHEN v_rand < 0.60 THEN 2
                    WHEN v_rand < 0.80 THEN 4
                    WHEN v_rand < 0.90 THEN 3
                    ELSE 5
                END;
            ELSEIF v_touch_num = v_num_touches AND v_will_convert = 1 THEN
                -- Last touch before conversion: closing channels
                SET v_channel_id = CASE
                    WHEN v_rand < 0.30 THEN 5
                    WHEN v_rand < 0.55 THEN 3
                    WHEN v_rand < 0.75 THEN 2
                    WHEN v_rand < 0.90 THEN 1
                    ELSE 4
                END;
            ELSE
                -- Middle touches: even distribution
                SET v_channel_id = CASE
                    WHEN v_rand < 0.25 THEN 1
                    WHEN v_rand < 0.45 THEN 3
                    WHEN v_rand < 0.65 THEN 2
                    WHEN v_rand < 0.85 THEN 4
                    ELSE 5
                END;
            END IF;

            -- Timestamp: spread touches across the journey
            SET v_touch_time = DATE_ADD(v_base_date,
                INTERVAL (v_touch_num - 1) * FLOOR(12 + RAND() * 60) HOUR);

            INSERT INTO touchpoints (customer_id, channel_id, touchpoint_timestamp, session_id)
            VALUES (v_customer_id, v_channel_id, v_touch_time, UUID());

            SET v_touch_num = v_touch_num + 1;
        END WHILE;

        -- Generate conversion if applicable
        IF v_will_convert = 1 THEN
            SET v_revenue = ROUND(25 + RAND() * 475, 2);
            INSERT INTO conversions (customer_id, conversion_timestamp, revenue)
            VALUES (
                v_customer_id,
                DATE_ADD(v_touch_time, INTERVAL FLOOR(1 + RAND() * 30) MINUTE),
                v_revenue
            );
        END IF;

        SET v_customer_id = v_customer_id + 1;
    END WHILE;
END //

DELIMITER ;

What does this procedure do?

This procedure generates a complete multi-touch e-commerce dataset in one execution. Here are the key sections:

• Channel insertion inserts 5 marketing channels categorized as Owned (Organic Search, Email), Paid (Paid Search, Social Media), or Direct.
• Position-biased channel selection uses different probability weights depending on where the touchpoint falls in the journey. First touches favor Organic Search (35%) and Paid Search (25%). Last touches before conversion favor Direct (30%) and Email (25%). This is why last-click attribution over-credits closing channels.
• ELT() picks a random device type by returning the Nth string from a list. UUID() generates unique session IDs for each touchpoint.
• The 38% conversion rate (RAND() < 0.38) determines which customers will convert. Converting customers get a revenue value between £25 and £500.

Why use a stored procedure for data generation?

MySQL 8.0 does not have a GENERATE_SERIES function or easy bulk random data generation. Stored procedures give you WHILE loops and conditional logic that plain SQL cannot provide. They also let you precisely control the channel distribution patterns so the attribution "story" emerges correctly in later steps.

Execute the procedure and verify row counts

With the procedure created, you can now call it to populate all four tables at once. The procedure iterates through 2,000 customers with nested loops, so it may take 10-30 seconds to complete.

• First, select all the code in your query tab and execute it by clicking the lightning bolt icon (or pressing Cmd+Shift+Enter on macOS, Ctrl+Shift+Enter on Windows).
• Wait for the execution to complete. You should see a green checkmark in the Output panel at the bottom.
• Once the procedure is created, run the following to execute it and verify the row counts:

-- Execute the procedure
CALL generate_attribution_data();

-- Verify data generation
SELECT 'Customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'Touchpoints', COUNT(*) FROM touchpoints
UNION ALL
SELECT 'Conversions', COUNT(*) FROM conversions
UNION ALL
SELECT 'Channels', COUNT(*) FROM channels;

What should I see?

The verification query uses UNION ALL to combine row counts from all four tables into a single result set. Your output should look approximately like this:

• Channels: 5
• Customers: 2000
• Touchpoints: approximately 8,000-10,000 (varies due to random journey lengths).
• Conversions: approximately 760 (38% of 2,000 customers).

Procedure not executing?

• If you see a syntax error near DELIMITER, make sure you ran the entire script from the top (including the DELIMITER // and DELIMITER ; lines), not just the CALL statement.
• If Workbench shows a timeout error, the procedure may need more time. Check that your query editor's timeout setting is at least 60 seconds under Edit > Preferences > SQL Editor.
• If you see a "table already has data" or duplicate key error, the procedure may have partially run before. Run TRUNCATE TABLE touchpoints; TRUNCATE TABLE conversions; TRUNCATE TABLE customers; DELETE FROM channels; to clear the tables, then try CALL generate_attribution_data(); again.

Help me troubleshoot my stored procedure execution in MySQL Workbench

Run a sanity check on journey lengths

Row counts confirm the data exists, but you also need to verify the shape of the journeys. If journey lengths are too uniform or too extreme, the attribution models in later steps will not produce meaningful differences.

• Run the following query to check journey length statistics:

-- Verify journey lengths
SELECT
    ROUND(AVG(touch_count), 1) AS avg_touches,
    MIN(touch_count) AS min_touches,
    MAX(touch_count) AS max_touches
FROM (
    SELECT customer_id, COUNT(*) AS touch_count
    FROM touchpoints
    GROUP BY customer_id
) journey_lengths;

What does this query do?

The inner subquery counts touchpoints per customer to get each journey's length. The outer query computes the average, minimum, and maximum across all journeys. You should see an average around 4-5 touches, a minimum of 2, and a maximum of 8.

This range matters because single-touch journeys make all attribution models identical (there is only one channel to credit). Multi-touch journeys are where models disagree and your Markov approach adds value.

• Confirm that min_touches is 2, max_touches is 8, and avg_touches falls between 4 and 6.
• Save this file as 02_generate_data.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Journey lengths look wrong?

• If max_touches is 1, the procedure likely only ran partially. Truncate all tables and re-run CALL generate_attribution_data(); from a clean state.
• If you see NULL or 0 rows, make sure you are connected to the ecommerce_attribution database. Run USE ecommerce_attribution; before the sanity check query.

Help me debug my journey data generation results

✔️ Awesome, I've got everything!

Great. Double check that your file is saved as 02_generate_data.sql and that your verification queries returned the expected row counts.

ⓧ I'd like to double check the full code

Here is the complete 02_generate_data.sql file for comparison:

-- Step 2: Generate synthetic attribution data
USE ecommerce_attribution;

DELIMITER //

CREATE PROCEDURE generate_attribution_data()
BEGIN
    DECLARE v_customer_id INT DEFAULT 1;
    DECLARE v_num_touches INT;
    DECLARE v_touch_num INT;
    DECLARE v_channel_id INT;
    DECLARE v_base_date DATETIME;
    DECLARE v_touch_time DATETIME;
    DECLARE v_will_convert TINYINT;
    DECLARE v_revenue DECIMAL(10,2);
    DECLARE v_rand DOUBLE;

    -- Insert channels
    INSERT INTO channels (channel_id, channel_name, channel_category) VALUES
        (1, 'Organic_Search', 'Owned'),
        (2, 'Paid_Search', 'Paid'),
        (3, 'Email', 'Owned'),
        (4, 'Social_Media', 'Paid'),
        (5, 'Direct', 'Direct');

    -- Generate 2000 customer journeys
    WHILE v_customer_id <= 2000 DO

        -- Create customer
        INSERT INTO customers (customer_id, signup_date, device_type)
        VALUES (
            v_customer_id,
            DATE_SUB('2024-01-01', INTERVAL FLOOR(RAND() * 365) DAY),
            ELT(FLOOR(1 + RAND() * 3), 'desktop', 'mobile', 'tablet')
        );

        -- Journey parameters
        SET v_num_touches = FLOOR(2 + RAND() * 7);
        SET v_base_date = DATE_SUB('2024-03-15', INTERVAL FLOOR(RAND() * 21) DAY);
        SET v_will_convert = IF(RAND() < 0.38, 1, 0);

        -- Generate touchpoints
        SET v_touch_num = 1;
        WHILE v_touch_num <= v_num_touches DO

            SET v_rand = RAND();

            -- Channel selection based on journey position
            IF v_touch_num = 1 THEN
                -- First touch: discovery channels dominate
                SET v_channel_id = CASE
                    WHEN v_rand < 0.35 THEN 1
                    WHEN v_rand < 0.60 THEN 2
                    WHEN v_rand < 0.80 THEN 4
                    WHEN v_rand < 0.90 THEN 3
                    ELSE 5
                END;
            ELSEIF v_touch_num = v_num_touches AND v_will_convert = 1 THEN
                -- Last touch before conversion: closing channels
                SET v_channel_id = CASE
                    WHEN v_rand < 0.30 THEN 5
                    WHEN v_rand < 0.55 THEN 3
                    WHEN v_rand < 0.75 THEN 2
                    WHEN v_rand < 0.90 THEN 1
                    ELSE 4
                END;
            ELSE
                -- Middle touches: even distribution
                SET v_channel_id = CASE
                    WHEN v_rand < 0.25 THEN 1
                    WHEN v_rand < 0.45 THEN 3
                    WHEN v_rand < 0.65 THEN 2
                    WHEN v_rand < 0.85 THEN 4
                    ELSE 5
                END;
            END IF;

            -- Timestamp: spread touches across the journey
            SET v_touch_time = DATE_ADD(v_base_date,
                INTERVAL (v_touch_num - 1) * FLOOR(12 + RAND() * 60) HOUR);

            INSERT INTO touchpoints (customer_id, channel_id, touchpoint_timestamp, session_id)
            VALUES (v_customer_id, v_channel_id, v_touch_time, UUID());

            SET v_touch_num = v_touch_num + 1;
        END WHILE;

        -- Generate conversion if applicable
        IF v_will_convert = 1 THEN
            SET v_revenue = ROUND(25 + RAND() * 475, 2);
            INSERT INTO conversions (customer_id, conversion_timestamp, revenue)
            VALUES (
                v_customer_id,
                DATE_ADD(v_touch_time, INTERVAL FLOOR(1 + RAND() * 30) MINUTE),
                v_revenue
            );
        END IF;

        SET v_customer_id = v_customer_id + 1;
    END WHILE;
END //

DELIMITER ;

-- Execute the procedure
CALL generate_attribution_data();

-- Verify data generation
SELECT 'Customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'Touchpoints', COUNT(*) FROM touchpoints
UNION ALL
SELECT 'Conversions', COUNT(*) FROM conversions
UNION ALL
SELECT 'Channels', COUNT(*) FROM channels;

-- Verify journey lengths
SELECT
    ROUND(AVG(touch_count), 1) AS avg_touches,
    MIN(touch_count) AS min_touches,
    MAX(touch_count) AS max_touches
FROM (
    SELECT customer_id, COUNT(*) AS touch_count
    FROM touchpoints
    GROUP BY customer_id
) journey_lengths;

You now have 2,000 realistic customer journeys with position-biased channel selection sitting in your database. Next up, you'll audit this data for quality issues before running attribution models against it.

Audit Data Quality

You have 2,000 customer journeys sitting in your database. Before you can trust any attribution model built on top of this data, you need to prove the data itself is trustworthy.

In this step, you will run a structured data quality audit. You will check for missing values, orphan records that reference non-existent parents, duplicates, and timestamp inconsistencies. Then you will wrap everything into a reusable view so you can re-run your audit with a single query.

In this step, get ready to:

• Check for NULL values, orphan records, and duplicate touchpoints.
• Validate referential integrity and timestamp ordering within journeys.
• Create a data_quality_summary view for ongoing monitoring.

Check for NULLs, orphan records, and duplicates

Any row with a NULL in a required column or a foreign key pointing at nothing will silently corrupt your attribution calculations. You need to catch these before building models on top of the data.

• In MySQL Workbench, open a new query tab by clicking the New Query Tab button (the + icon above the query editor).
• Start your audit file with the database header and NULL check by pasting this code:

-- Step 3: Data Quality Audit
USE ecommerce_attribution;

-- Check for NULL values across all tables
SELECT
    'touchpoints' AS table_name,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN channel_id IS NULL THEN 1 ELSE 0 END) AS null_channel_id,
    SUM(CASE WHEN touchpoint_timestamp IS NULL THEN 1 ELSE 0 END) AS null_timestamp
FROM touchpoints;

What does this code do?

The CASE WHEN ... IS NULL pattern counts how many rows have a missing value in each column. By summing these conditional 1s, you get a quick NULL inventory for the three most critical columns in touchpoints.

If your data generation worked correctly, all three counts should return 0.

• Select all the code and click the lightning bolt icon (or press Cmd+Shift+Enter on macOS, Ctrl+Shift+Enter on Windows) to execute.
• In the Results grid, you should see a single row with 0 for all three NULL columns.

Seeing non-zero NULL counts?

If any column shows NULLs, your data generation procedure may have been interrupted mid-run. Drop all tables, recreate the schema from 01_create_schema.sql, and re-run 02_generate_data.sql.

Help me debug my NULL value counts.

• Check for orphan records by adding this query below your NULL check:

-- Check for orphan records
SELECT
    'Touchpoints without valid customer' AS check_name,
    COUNT(*) AS issue_count
FROM touchpoints t
LEFT JOIN customers c ON t.customer_id = c.customer_id
WHERE c.customer_id IS NULL

UNION ALL

SELECT
    'Touchpoints with invalid channel',
    COUNT(*)
FROM touchpoints t
LEFT JOIN channels c ON t.channel_id = c.channel_id
WHERE c.channel_id IS NULL

UNION ALL

SELECT
    'Conversions without touchpoints',
    COUNT(*)
FROM conversions conv
LEFT JOIN touchpoints t ON conv.customer_id = t.customer_id
WHERE t.customer_id IS NULL;

What does this code do?

Each LEFT JOIN pairs a child record with its parent. When the WHERE parent IS NULL filter catches rows, those are orphans with no valid parent.

The UNION ALL stacks three separate checks into one result set so you can see all orphan categories at a glance.

• Execute the orphan records query. You should see three rows, each with an issue_count of 0.

• Check for duplicate touchpoints by adding this query:

-- Check for duplicate touchpoints (same customer, channel, timestamp)
SELECT
    customer_id,
    channel_id,
    touchpoint_timestamp,
    COUNT(*) AS duplicate_count
FROM touchpoints
GROUP BY customer_id, channel_id, touchpoint_timestamp
HAVING COUNT(*) > 1;

What does this code do?

This groups touchpoints by the combination of customer, channel, and timestamp. The HAVING COUNT(*) > 1 filter surfaces any rows that share all three values, which would mean the same event was recorded twice.

Duplicates would double-count channel influence in your attribution models, inflating that channel's credit.

• Execute the duplicate check. You should see an empty result set (no rows returned), meaning there are no duplicates.

Validate referential integrity and timestamp ordering

Beyond missing data, your attribution model depends on journeys being logically complete. A conversion without any prior touchpoints would break removal effect calculations. Timestamps out of order would corrupt your Markov transition sequences.

• Check for conversions that have no prior touchpoints by adding this query:

-- Journey completeness: customers with conversion but no prior touchpoints
SELECT
    'Customers with conversion but no touchpoints' AS check_name,
    COUNT(*) AS issue_count
FROM conversions conv
WHERE NOT EXISTS (
    SELECT 1 FROM touchpoints t
    WHERE t.customer_id = conv.customer_id
    AND t.touchpoint_timestamp <= conv.conversion_timestamp
);

What does this code do?

The NOT EXISTS subquery asks: for each conversion, is there at least one touchpoint that happened before the conversion timestamp? If not, that conversion has no marketing journey leading up to it.

This is stricter than the orphan check above. The orphan check only verifies a customer record exists. This check verifies a touchpoint exists before the purchase.

• Execute the query. You should see an issue_count of 0.
• Validate timestamp ordering within journeys by adding this query:

-- Timestamp ordering validation
SELECT
    'Journeys with out-of-order timestamps' AS check_name,
    COUNT(DISTINCT customer_id) AS issue_count
FROM (
    SELECT
        customer_id,
        touchpoint_timestamp,
        LAG(touchpoint_timestamp) OVER (
            PARTITION BY customer_id ORDER BY touchpoint_timestamp
        ) AS prev_timestamp
    FROM touchpoints
) ordered
WHERE prev_timestamp IS NOT NULL
AND touchpoint_timestamp < prev_timestamp;

What does this code do?

The LAG() window function grabs the previous row's timestamp within each customer's partition. If any row's timestamp is earlier than the row before it (when ordered by timestamp), you have a logical inconsistency.

Your Markov chain transition matrix relies on correct ordering to determine which channel leads to which. Out-of-order timestamps would create false transitions.

• Execute the query. You should see 0 journeys with out-of-order timestamps.

Seeing a syntax error on LAG()?

The LAG() window function requires MySQL 8.0.2 or higher. If you see an error, check your version by running SELECT VERSION(); and confirm the result starts with 8.0 or higher.

Help me fix my LAG() window function error.

Create a data quality summary view

Running individual checks is useful during development, but a single queryable view lets you re-audit your data any time with one command. This is the pattern data engineers use in production to monitor pipeline health.

• Create the data_quality_summary view and query it by adding this code at the bottom of your file:

-- Create data quality summary view
CREATE OR REPLACE VIEW data_quality_summary AS
SELECT
    (SELECT COUNT(*) FROM customers) AS total_customers,
    (SELECT COUNT(*) FROM touchpoints) AS total_touchpoints,
    (SELECT COUNT(*) FROM conversions) AS total_conversions,
    (SELECT ROUND(COUNT(*) / (SELECT COUNT(*) FROM customers) * 100, 1)
     FROM conversions) AS conversion_rate_pct,
    (SELECT ROUND(SUM(revenue), 2) FROM conversions) AS total_revenue,
    (SELECT COUNT(*) FROM touchpoints
     WHERE customer_id IS NULL OR channel_id IS NULL
     OR touchpoint_timestamp IS NULL) AS null_value_count,
    (SELECT COUNT(*) FROM conversions conv
     WHERE NOT EXISTS (
         SELECT 1 FROM touchpoints t
         WHERE t.customer_id = conv.customer_id
     )) AS orphan_conversions;

SELECT * FROM data_quality_summary;

What does this code do?

The CREATE OR REPLACE VIEW stores this query definition permanently in the database. Each column uses a scalar subquery to pull one summary metric.

The conversion_rate_pct column divides total conversions by total customers to give you the overall conversion rate as a percentage. The null_value_count and orphan_conversions columns serve as your ongoing data health indicators.

• Execute the code. In the Results grid, you should see a single row showing approximately 2000 total customers, 8000-10000 touchpoints, around 760 conversions, a conversion rate near 38%, and 0 for both null_value_count and orphan_conversions.
• Save this file as 03_data_quality_audit.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows).

View creation failed?

Make sure you have the USE ecommerce_attribution; statement at the top of your file. Without it, MySQL does not know which database to create the view in.

Also check that you selected and executed both statements together (the CREATE OR REPLACE VIEW and the SELECT * FROM data_quality_summary).

Help me debug my view creation in MySQL Workbench.

Why audit generated data?

Even though you generated this data yourself, auditing it builds a transferable skill. In production, data arrives from external systems with real integrity failures. The exact same queries you just wrote (NULL checks, orphan detection, duplicate surfacing, timestamp validation) form the standard data quality toolkit.

More practically, your stored procedure uses RAND() extensively. Confirming the output is clean proves the procedure logic is correct.

✔️ Awesome, I've got everything!

Great. Double check you have saved 03_data_quality_audit.sql and that all your audit checks return 0 issues.

ⓧ I'd like to double check the full code

Here is the complete 03_data_quality_audit.sql file. Compare yours against it:

-- Step 3: Data Quality Audit
USE ecommerce_attribution;

SELECT
    'touchpoints' AS table_name,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN channel_id IS NULL THEN 1 ELSE 0 END) AS null_channel_id,
    SUM(CASE WHEN touchpoint_timestamp IS NULL THEN 1 ELSE 0 END) AS null_timestamp
FROM touchpoints;

SELECT 'Touchpoints without valid customer' AS check_name, COUNT(*) AS issue_count
FROM touchpoints t LEFT JOIN customers c ON t.customer_id = c.customer_id WHERE c.customer_id IS NULL
UNION ALL
SELECT 'Touchpoints with invalid channel', COUNT(*)
FROM touchpoints t LEFT JOIN channels c ON t.channel_id = c.channel_id WHERE c.channel_id IS NULL
UNION ALL
SELECT 'Conversions without touchpoints', COUNT(*)
FROM conversions conv LEFT JOIN touchpoints t ON conv.customer_id = t.customer_id WHERE t.customer_id IS NULL;

SELECT customer_id, channel_id, touchpoint_timestamp, COUNT(*) AS duplicate_count
FROM touchpoints
GROUP BY customer_id, channel_id, touchpoint_timestamp
HAVING COUNT(*) > 1;

SELECT 'Customers with conversion but no touchpoints' AS check_name, COUNT(*) AS issue_count
FROM conversions conv
WHERE NOT EXISTS (
    SELECT 1 FROM touchpoints t
    WHERE t.customer_id = conv.customer_id
    AND t.touchpoint_timestamp <= conv.conversion_timestamp
);

SELECT 'Journeys with out-of-order timestamps' AS check_name, COUNT(DISTINCT customer_id) AS issue_count
FROM (
    SELECT customer_id, touchpoint_timestamp,
        LAG(touchpoint_timestamp) OVER (PARTITION BY customer_id ORDER BY touchpoint_timestamp) AS prev_timestamp
    FROM touchpoints
) ordered
WHERE prev_timestamp IS NOT NULL AND touchpoint_timestamp < prev_timestamp;

CREATE OR REPLACE VIEW data_quality_summary AS
SELECT
    (SELECT COUNT(*) FROM customers) AS total_customers,
    (SELECT COUNT(*) FROM touchpoints) AS total_touchpoints,
    (SELECT COUNT(*) FROM conversions) AS total_conversions,
    (SELECT ROUND(COUNT(*) / (SELECT COUNT(*) FROM customers) * 100, 1) FROM conversions) AS conversion_rate_pct,
    (SELECT ROUND(SUM(revenue), 2) FROM conversions) AS total_revenue,
    (SELECT COUNT(*) FROM touchpoints WHERE customer_id IS NULL OR channel_id IS NULL OR touchpoint_timestamp IS NULL) AS null_value_count,
    (SELECT COUNT(*) FROM conversions conv WHERE NOT EXISTS (SELECT 1 FROM touchpoints t WHERE t.customer_id = conv.customer_id)) AS orphan_conversions;

SELECT * FROM data_quality_summary;

Your data is verified clean. Every touchpoint has a valid customer and channel, no duplicates exist, timestamps are in order, and every conversion has a complete journey leading up to it. Next up, you will explore this data to understand channel patterns before building attribution models.

Explore the Data

Your data is clean and validated. Now it's time to understand what story it tells before building attribution models.

Exploratory analysis reveals the patterns that make naive attribution models fail. You'll see exactly how channels cluster at different positions in the customer journey, which is the insight that motivates the Markov approach later.

In this step, get ready to:

• Analyze how marketing channels distribute across journey positions.
• Compare journey patterns between customers who convert and those who don't.
• Build a channel co-occurrence matrix to see which channels appear together.

Analyze channel frequency and position

Before attributing revenue, you need to understand which channels appear where. A channel that dominates as the first touchpoint plays a different role than one that shows up right before purchase.

• Create a new query tab in MySQL Workbench and save it as 04_exploratory_analysis.sql.
• Add the database selector and run the channel frequency distribution query:

-- Step 4: Exploratory Data Analysis
USE ecommerce_attribution;

-- Channel frequency distribution
SELECT
    c.channel_name,
    COUNT(*) AS total_appearances,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM touchpoints), 1) AS pct_of_touches
FROM touchpoints t
JOIN channels c ON t.channel_id = c.channel_id
GROUP BY c.channel_name
ORDER BY total_appearances DESC;

What does this query do?

This counts how many times each channel appears across all touchpoints in the dataset. The subquery (SELECT COUNT(*) FROM touchpoints) computes the total touchpoint count, which lets you express each channel's appearances as a percentage.

This tells you the overall "market share" of each channel in your journey data. It doesn't tell you WHERE in the journey they appear.

• Execute the query. You should see all 5 channels with their total appearances and percentage of total touches.

Frequency alone doesn't reveal channel roles. A channel could appear 20% of the time but always as the first touch (discovery) or always as the last touch (closing). Let's separate those positions.

• Add this query below the first one to break channels down by journey position:

-- Channel by position: first touch vs last touch vs middle
WITH positioned_touches AS (
    SELECT
        t.customer_id,
        c.channel_name,
        ROW_NUMBER() OVER (
            PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp ASC
        ) AS touch_position,
        COUNT(*) OVER (PARTITION BY t.customer_id) AS total_touches
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
)
SELECT
    channel_name,
    SUM(CASE WHEN touch_position = 1 THEN 1 ELSE 0 END) AS first_touch_count,
    SUM(CASE WHEN touch_position = total_touches THEN 1 ELSE 0 END) AS last_touch_count,
    SUM(CASE WHEN touch_position > 1 AND touch_position < total_touches
        THEN 1 ELSE 0 END) AS middle_touch_count
FROM positioned_touches
GROUP BY channel_name
ORDER BY first_touch_count DESC;

What does this query do?

The CTE (Common Table Expression) positioned_touches uses two window functions to label each touchpoint:

• ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY touchpoint_timestamp ASC) assigns each touchpoint its position number (1st, 2nd, 3rd) within that customer's journey.
• COUNT(*) OVER (PARTITION BY customer_id) counts the total touches for each customer, so you can identify the last one.

The outer SELECT then counts how often each channel appears as first touch, last touch, or in the middle. You should see Organic_Search dominating first touches and Direct dominating last touches.

• Execute this query. You should see a table with each channel's first_touch_count, last_touch_count, and middle_touch_count.

Why does this pattern matter for attribution?

If Organic_Search dominates as the first touch and Direct dominates as the last touch, then last-click attribution will massively over-credit Direct and under-credit Organic_Search. This is the core bias your Markov model will fix later.

Seeing zero middle touches for some channels?

This is expected for customers with only 2 touchpoints. Those journeys have a first touch and a last touch but nothing in between. Check that your total_touches column counts correctly by verifying against your data quality summary.

If the query returns an error, make sure your CTE uses WITH (not a subquery) and that the outer SELECT references positioned_touches exactly as named.

Help me debug my channel position query.

Analyze journey patterns for converters vs. non-converters

Knowing whether converting customers behave differently from non-converters gives you a baseline for evaluating your attribution models. If converters have longer journeys, channels in the middle matter more than a last-click model would suggest.

• Add this query to compare journey length between converters and non-converters:

-- Journey length: converters vs non-converters
SELECT
    CASE WHEN conv.customer_id IS NOT NULL THEN 'Converted' ELSE 'Did Not Convert' END AS outcome,
    COUNT(DISTINCT t.customer_id) AS customer_count,
    ROUND(AVG(touch_count), 1) AS avg_journey_length,
    ROUND(AVG(journey_hours), 1) AS avg_journey_hours
FROM (
    SELECT
        customer_id,
        COUNT(*) AS touch_count,
        TIMESTAMPDIFF(HOUR, MIN(touchpoint_timestamp), MAX(touchpoint_timestamp)) AS journey_hours
    FROM touchpoints
    GROUP BY customer_id
) t
LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
    ON t.customer_id = conv.customer_id
GROUP BY CASE WHEN conv.customer_id IS NOT NULL THEN 'Converted' ELSE 'Did Not Convert' END;

What does this query do?

The inner subquery computes per-customer stats: total touchpoint count and time span (in hours) from first to last touch. TIMESTAMPDIFF(HOUR, MIN(...), MAX(...)) measures the duration of each customer's entire journey.

The outer query splits customers into two groups (converted or not) and averages their journey metrics. You should see that converters tend to have slightly more touchpoints and longer journeys than non-converters.

• Execute the query. You should see two rows: one for 'Converted' and one for 'Did Not Convert', each with customer_count, avg_journey_length, and avg_journey_hours.

Now let's check whether the device a customer uses affects their likelihood of converting.

• Add this query to analyze device type conversion rates:

-- Device type conversion rates
SELECT
    cust.device_type,
    COUNT(DISTINCT cust.customer_id) AS total_customers,
    COUNT(DISTINCT conv.customer_id) AS converted_customers,
    ROUND(COUNT(DISTINCT conv.customer_id) * 100.0 / COUNT(DISTINCT cust.customer_id), 1) AS conversion_rate_pct,
    ROUND(AVG(conv.revenue), 2) AS avg_revenue
FROM customers cust
LEFT JOIN conversions conv ON cust.customer_id = conv.customer_id
GROUP BY cust.device_type
ORDER BY conversion_rate_pct DESC;

What does this query do?

This groups all customers by their device_type (desktop, mobile, tablet) and computes the conversion rate and average revenue for each group. The LEFT JOIN ensures non-converting customers are still counted in total_customers.

Since the data is randomly generated, conversion rates should be roughly similar across devices (around 38%). In real data, this query often reveals meaningful device-based differences.

• Execute the query. You should see three rows (desktop, mobile, tablet) with their respective conversion rates and average revenue.

Seeing NULL in the avg_revenue column?

This is expected behavior. AVG(conv.revenue) only averages non-NULL values, so if a device type has zero conversions it would show NULL. In your generated data every device type should have conversions, so if you see NULL, check that your LEFT JOIN condition matches on customer_id.

Help me debug my device conversion query.

Build a channel co-occurrence matrix

Co-occurrence tells you which channels tend to appear together in the same journey. This is a precursor to the transition matrix you'll build in the next step. If two channels rarely co-occur, removing one won't affect paths that go through the other.

• Add this self-join query to build the co-occurrence matrix:

-- Channel co-occurrence matrix (which channels appear together)
SELECT
    c1.channel_name AS channel_a,
    c2.channel_name AS channel_b,
    COUNT(DISTINCT t1.customer_id) AS journeys_together
FROM touchpoints t1
JOIN touchpoints t2
    ON t1.customer_id = t2.customer_id
    AND t1.channel_id < t2.channel_id
JOIN channels c1 ON t1.channel_id = c1.channel_id
JOIN channels c2 ON t2.channel_id = c2.channel_id
GROUP BY c1.channel_name, c2.channel_name
ORDER BY journeys_together DESC;

What does this query do?

This is a self-join on the touchpoints table. It pairs every touchpoint with every other touchpoint from the same customer (t1.customer_id = t2.customer_id).

The condition t1.channel_id < t2.channel_id ensures you only count each pair once (Organic_Search + Email, not also Email + Organic_Search). COUNT(DISTINCT t1.customer_id) then counts how many unique customers experienced both channels in their journey.

• Execute the query. You should see 10 rows (one for each unique pair of 5 channels) sorted by how often they co-occur.
• Save 04_exploratory_analysis.sql.

Query taking a long time?

The self-join creates a large intermediate result set. With ~9000 touchpoints, this can produce millions of row comparisons. On most machines it should still finish within a few seconds. If it's taking longer than 10 seconds, check that your JOIN condition includes both t1.customer_id = t2.customer_id and t1.channel_id < t2.channel_id.

Help me debug my co-occurrence self-join.

✔️ Awesome, I've got everything!

Great. Double check you've saved 04_exploratory_analysis.sql and all five queries executed without errors.

ⓧ I'd like to double check the full code

-- Step 4: Exploratory Data Analysis
USE ecommerce_attribution;

SELECT c.channel_name, COUNT(*) AS total_appearances, ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM touchpoints), 1) AS pct_of_touches FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id GROUP BY c.channel_name ORDER BY total_appearances DESC;

WITH positioned_touches AS (
    SELECT t.customer_id, c.channel_name,
        ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp ASC) AS touch_position,
        COUNT(*) OVER (PARTITION BY t.customer_id) AS total_touches
    FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id
)
SELECT channel_name,
    SUM(CASE WHEN touch_position = 1 THEN 1 ELSE 0 END) AS first_touch_count,
    SUM(CASE WHEN touch_position = total_touches THEN 1 ELSE 0 END) AS last_touch_count,
    SUM(CASE WHEN touch_position > 1 AND touch_position < total_touches THEN 1 ELSE 0 END) AS middle_touch_count
FROM positioned_touches GROUP BY channel_name ORDER BY first_touch_count DESC;

SELECT CASE WHEN conv.customer_id IS NOT NULL THEN 'Converted' ELSE 'Did Not Convert' END AS outcome,
    COUNT(DISTINCT t.customer_id) AS customer_count,
    ROUND(AVG(touch_count), 1) AS avg_journey_length,
    ROUND(AVG(journey_hours), 1) AS avg_journey_hours
FROM (SELECT customer_id, COUNT(*) AS touch_count, TIMESTAMPDIFF(HOUR, MIN(touchpoint_timestamp), MAX(touchpoint_timestamp)) AS journey_hours FROM touchpoints GROUP BY customer_id) t
LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv ON t.customer_id = conv.customer_id
GROUP BY CASE WHEN conv.customer_id IS NOT NULL THEN 'Converted' ELSE 'Did Not Convert' END;

SELECT cust.device_type, COUNT(DISTINCT cust.customer_id) AS total_customers, COUNT(DISTINCT conv.customer_id) AS converted_customers, ROUND(COUNT(DISTINCT conv.customer_id) * 100.0 / COUNT(DISTINCT cust.customer_id), 1) AS conversion_rate_pct, ROUND(AVG(conv.revenue), 2) AS avg_revenue FROM customers cust LEFT JOIN conversions conv ON cust.customer_id = conv.customer_id GROUP BY cust.device_type ORDER BY conversion_rate_pct DESC;

SELECT c1.channel_name AS channel_a, c2.channel_name AS channel_b, COUNT(DISTINCT t1.customer_id) AS journeys_together FROM touchpoints t1 JOIN touchpoints t2 ON t1.customer_id = t2.customer_id AND t1.channel_id < t2.channel_id JOIN channels c1 ON t1.channel_id = c1.channel_id JOIN channels c2 ON t2.channel_id = c2.channel_id GROUP BY c1.channel_name, c2.channel_name ORDER BY journeys_together DESC;

You now have a clear picture of your data's shape. Organic_Search dominates discovery, Direct dominates closing, and converters take longer paths. Next, you'll build the heuristic attribution models that expose exactly how these position biases distort revenue credit.

Implement Heuristic Attribution Models

Your exploratory analysis revealed that Organic_Search dominates first touches while Direct and Email cluster at the end of journeys. This position bias is exactly why attribution models exist.

In this step, you'll implement three heuristic models that each answer the same question differently: "which channel deserves credit for this conversion?" Each model has a built-in bias you'll later compare against the Markov approach.

In this step, get ready to:

• Implement last-click attribution using ROW_NUMBER() to credit the final touchpoint.
• Implement first-click attribution to credit the discovery touchpoint.
• Implement linear attribution to distribute credit equally across all touchpoints.

Implement last-click attribution

Last-click attribution gives 100% of the conversion credit to the final touchpoint before the purchase. It's the default model in most analytics platforms because it's simple to implement.

The problem? It systematically over-credits "closing" channels like Direct and Email while ignoring the discovery channels that brought the customer in the first place.

• Open a new query tab in MySQL Workbench by clicking the **New Query Tab** button (or pressing Ctrl+T).
• Start your heuristic models file by pasting this last-click attribution query:

-- Step 5: Heuristic Attribution Models
USE ecommerce_attribution;

-- Last-Click Attribution
-- 100% credit to the final touchpoint before conversion
SELECT
    'Last-Click' AS model,
    channel_name,
    COUNT(*) AS conversions_attributed,
    ROUND(SUM(revenue), 2) AS revenue_attributed,
    ROUND(SUM(revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT
        t.customer_id,
        c.channel_name,
        conv.revenue,
        ROW_NUMBER() OVER (
            PARTITION BY t.customer_id
            ORDER BY t.touchpoint_timestamp DESC
        ) AS rn
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
    JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) ranked
WHERE rn = 1
GROUP BY channel_name
ORDER BY revenue_attributed DESC;

What does this query do?

The subquery joins touchpoints with conversions and assigns a row number to each touchpoint per customer, ordered by timestamp descending. The DESC ordering means rn = 1 is always the most recent touchpoint before conversion.

The WHERE t.touchpoint_timestamp <= conv.conversion_timestamp filter ensures you only count touches that happened before the purchase. The outer query groups by channel and sums up all the revenue attributed to each one.

• Select all the code and click the **Execute** button (lightning bolt icon) to run it.
• You should see a results grid with 5 rows (one per channel), showing channel_name, conversions_attributed, revenue_attributed, and pct_of_revenue. Notice that Direct and Email likely dominate the revenue share.

Seeing zero rows or an error?

Make sure you included the USE ecommerce_attribution; line at the top. Without it, MySQL doesn't know which database to query.

If you see a syntax error near OVER, confirm your MySQL version is 8.0 or higher. Window functions are not available in MySQL 5.7.

Help me debug my last-click attribution query

Implement first-click attribution

First-click is the mirror image of last-click. It gives 100% credit to the touchpoint that first introduced the customer to your brand. This model over-credits discovery channels like Organic_Search and Social_Media.

• Add the first-click attribution query below your last-click query in the same file:

-- First-Click Attribution
-- 100% credit to the first touchpoint in the journey
SELECT
    'First-Click' AS model,
    channel_name,
    COUNT(*) AS conversions_attributed,
    ROUND(SUM(revenue), 2) AS revenue_attributed,
    ROUND(SUM(revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT
        t.customer_id,
        c.channel_name,
        conv.revenue,
        ROW_NUMBER() OVER (
            PARTITION BY t.customer_id
            ORDER BY t.touchpoint_timestamp ASC
        ) AS rn
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
    JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) ranked
WHERE rn = 1
GROUP BY channel_name
ORDER BY revenue_attributed DESC;

What changed from last-click?

Only one thing changed: ORDER BY t.touchpoint_timestamp ASC instead of DESC. Now rn = 1 picks the earliest touchpoint in the journey. Same structure, opposite direction, completely different business conclusion.

• Select and execute the first-click query.
• Compare the results to last-click. You should see Organic_Search and Paid_Search gaining revenue share while Direct drops significantly.

Results look identical to last-click?

Double-check that the ORDER BY clause says ASC, not DESC. If you accidentally left DESC in, you're running last-click a second time.

Help me fix my first-click attribution query

Implement linear attribution

Linear attribution takes a different approach entirely. Instead of picking a single winner, it splits revenue equally across every touchpoint in the journey. A 5-touch journey gives each channel 20% of the credit.

• Add the linear attribution query below your first-click query:

-- Linear Attribution
-- Revenue split equally across all touchpoints in the journey
SELECT
    'Linear' AS model,
    channel_name,
    ROUND(SUM(fractional_conversions), 2) AS conversions_attributed,
    ROUND(SUM(fractional_revenue), 2) AS revenue_attributed,
    ROUND(SUM(fractional_revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT
        t.customer_id,
        c.channel_name,
        conv.revenue,
        1.0 / COUNT(*) OVER (PARTITION BY t.customer_id) AS fractional_conversions,
        conv.revenue / COUNT(*) OVER (PARTITION BY t.customer_id) AS fractional_revenue
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
    JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) attributed
GROUP BY channel_name
ORDER BY revenue_attributed DESC;

What does this query do?

Instead of ROW_NUMBER(), this query uses COUNT(*) OVER (PARTITION BY t.customer_id) to count how many touchpoints each customer has. Then it divides revenue by that count to produce fractional_revenue per touchpoint.

The outer query sums those fractions by channel. Channels that appear more frequently across journeys accumulate more fractional revenue, reflecting their overall participation.

• Select and execute the linear attribution query.
• You should see all five channels with more balanced revenue shares than either last-click or first-click. The pct_of_revenue values should sum close to 100%.

Why do all three models matter?

Each model represents a different marketing philosophy. Last-click rewards closers. First-click rewards discoverers. Linear assumes every interaction is equally valuable.

None of them are "wrong," but none use actual behavioural data to determine importance. That's what the Markov chain approach in the next steps will do.

Revenue percentages don't sum to 100%?

Small rounding differences (99.9% or 100.1%) are normal due to ROUND() on individual rows. If you see a large gap (more than 1%), check that your WHERE clause matches t.touchpoint_timestamp <= conv.conversion_timestamp exactly.

Help me debug my linear attribution query

• Save your query tab as 05_heuristic_models.sql by clicking **File > Save Script As**.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file with all three attribution queries.

ⓧ I'd like to double check the full code

Here is the complete 05_heuristic_models.sql file:

-- Step 5: Heuristic Attribution Models
USE ecommerce_attribution;

SELECT 'Last-Click' AS model, channel_name, COUNT(*) AS conversions_attributed, ROUND(SUM(revenue), 2) AS revenue_attributed, ROUND(SUM(revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT t.customer_id, c.channel_name, conv.revenue,
        ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp DESC) AS rn
    FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) ranked WHERE rn = 1 GROUP BY channel_name ORDER BY revenue_attributed DESC;

SELECT 'First-Click' AS model, channel_name, COUNT(*) AS conversions_attributed, ROUND(SUM(revenue), 2) AS revenue_attributed, ROUND(SUM(revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT t.customer_id, c.channel_name, conv.revenue,
        ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp ASC) AS rn
    FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) ranked WHERE rn = 1 GROUP BY channel_name ORDER BY revenue_attributed DESC;

SELECT 'Linear' AS model, channel_name, ROUND(SUM(fractional_conversions), 2) AS conversions_attributed, ROUND(SUM(fractional_revenue), 2) AS revenue_attributed, ROUND(SUM(fractional_revenue) * 100.0 / (SELECT SUM(revenue) FROM conversions), 1) AS pct_of_revenue
FROM (
    SELECT t.customer_id, c.channel_name, conv.revenue,
        1.0 / COUNT(*) OVER (PARTITION BY t.customer_id) AS fractional_conversions,
        conv.revenue / COUNT(*) OVER (PARTITION BY t.customer_id) AS fractional_revenue
    FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id
    WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
) attributed GROUP BY channel_name ORDER BY revenue_attributed DESC;

You now have three different answers to "who deserves credit." Next, you'll build a Markov transition matrix that shows how customers actually flow between channels, and use that to compute a data-driven attribution model.

Build the Markov Transition Matrix

Your heuristic models assign credit based on position. Last-click rewards the final channel. First-click rewards the first. Linear splits evenly. None of them answer a deeper question: how do customers actually flow between channels on their way to a purchase?

A Markov chain models this flow as a network of states and transitions. Each channel is a state. Each time a customer moves from one channel to the next, that's a transition. By counting all transitions across all journeys, you build a probability matrix that reveals the true structure of your marketing funnel.

In this step, get ready to:

• Build ordered journey sequences with START and terminal states.
• Compute transition probabilities using window function normalization.
• Identify gateway, closer, and pass-through channels from the matrix.

Build ordered journey sequences and extract transitions

To build a transition matrix, you need every pair of consecutive states in every customer journey. That means numbering each touchpoint in order, tagging the journey outcome, and then extracting every (from_state, to_state) pair.

• Create a new query tab in MySQL Workbench.
• Paste the following query, which uses three chained CTEs to build the complete transition dataset:

-- Step 6: Build the Markov Transition Matrix
USE ecommerce_attribution;

-- Build ordered journey sequences with outcomes
WITH ordered_touchpoints AS (
    SELECT
        t.customer_id,
        c.channel_name,
        t.touchpoint_timestamp,
        ROW_NUMBER() OVER (
            PARTITION BY t.customer_id
            ORDER BY t.touchpoint_timestamp
        ) AS touch_position,
        COUNT(*) OVER (PARTITION BY t.customer_id) AS total_touches
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
),
journey_with_outcome AS (
    SELECT
        ot.*,
        CASE
            WHEN conv.customer_id IS NOT NULL THEN 'CONVERSION'
            ELSE 'NULL_STATE'
        END AS outcome
    FROM ordered_touchpoints ot
    LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
        ON ot.customer_id = conv.customer_id
),
-- Extract all state transitions
all_transitions AS (
    -- START -> first channel in journey
    SELECT 'START' AS from_state, channel_name AS to_state
    FROM journey_with_outcome
    WHERE touch_position = 1

    UNION ALL

    -- Channel -> next channel (consecutive touchpoints)
    SELECT a.channel_name AS from_state, b.channel_name AS to_state
    FROM journey_with_outcome a
    JOIN journey_with_outcome b
        ON a.customer_id = b.customer_id
        AND b.touch_position = a.touch_position + 1

    UNION ALL

    -- Last channel -> CONVERSION or NULL_STATE
    SELECT channel_name AS from_state, outcome AS to_state
    FROM journey_with_outcome
    WHERE touch_position = total_touches
)
-- Compute transition probability matrix
SELECT
    from_state,
    to_state,
    COUNT(*) AS transition_count,
    ROUND(
        COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY from_state),
        4
    ) AS transition_probability
FROM all_transitions
GROUP BY from_state, to_state
ORDER BY from_state, transition_probability DESC;

What does this query do?

This query chains three CTEs together to transform raw touchpoint data into a complete state-transition dataset:

• ordered_touchpoints uses ROW_NUMBER() to assign each touchpoint its position in the customer's journey, and COUNT(*) OVER to record the total journey length.
• journey_with_outcome adds a terminal state label. If the customer converted, their journey ends in CONVERSION. If they didn't, it ends in NULL_STATE.
• all_transitions uses three UNION ALL branches to capture every transition type: START to first channel, channel to next channel (via self-join on consecutive positions), and last channel to CONVERSION or NULL_STATE.

Why include START and terminal states?

A Markov chain needs defined entry and exit points. START represents the moment before a customer's first interaction. CONVERSION and NULL_STATE represent the two possible journey endings. Without these boundary states, you can't measure which channels are the best entry points or which ones close the deal.

Compute transition probabilities and run the query

The final SELECT in your query does the mathematical heavy lifting. It takes all the raw transitions and normalizes them into probabilities.

How does the normalization work?

The expression COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY from_state) divides each transition count by the total transitions leaving that state. This produces a probability between 0 and 1.

For example, if Organic_Search transitions to Email 200 times and Organic_Search has 1000 total outgoing transitions, the probability is 0.2000. All probabilities from a single from_state always sum to 1.0.

• Click the lightning bolt icon (or press Cmd+Shift+Enter on macOS or Ctrl+Shift+Enter on Windows) to execute the full query.
• You should see a results grid with four columns: from_state, to_state, transition_count, and transition_probability.
• Save the file as 06_markov_transition_matrix.sql.

Query taking a long time?

The self-join in all_transitions pairs every customer's consecutive touchpoints. With ~8000-10000 touchpoints this should complete in a few seconds. If it times out, check that you're running against the local MySQL instance and not a remote server.

help me debug my Markov transition matrix query in MySQL Workbench

Interpret the matrix to find gateways, closers, and pass-throughs

Your transition matrix tells a story about how customers navigate your marketing channels. Three patterns matter most for attribution.

• Scroll through the results and look for rows where from_state is START. The channels with the highest probabilities here are your gateway channels. These are where customers first enter the funnel.
• Look for rows where to_state is CONVERSION. The channels with the highest probabilities of transitioning to CONVERSION are your closer channels.
• Look for channels whose highest-probability transitions go to other channels rather than to CONVERSION or NULL_STATE. These are pass-through channels. They keep customers in the funnel without closing the deal themselves.

What patterns should I see?

Because you built your data with position bias, Organic_Search and Paid_Search should appear as gateway channels (high probability from START). Direct and Email should appear as closer channels (high probability to CONVERSION).

This is exactly why last-click attribution misleads. It gives all credit to closers like Direct. But without the gateway channels that brought customers in, there would be no one left to close. The removal effect in the next step measures this structural importance directly.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file as 06_markov_transition_matrix.sql.

ⓧ I'd like to double check the full code

Here's the complete file for comparison:

-- Step 6: Build the Markov Transition Matrix
USE ecommerce_attribution;

WITH ordered_touchpoints AS (
    SELECT t.customer_id, c.channel_name, t.touchpoint_timestamp,
        ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp) AS touch_position,
        COUNT(*) OVER (PARTITION BY t.customer_id) AS total_touches
    FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id
),
journey_with_outcome AS (
    SELECT ot.*,
        CASE WHEN conv.customer_id IS NOT NULL THEN 'CONVERSION' ELSE 'NULL_STATE' END AS outcome
    FROM ordered_touchpoints ot
    LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv ON ot.customer_id = conv.customer_id
),
all_transitions AS (
    SELECT 'START' AS from_state, channel_name AS to_state FROM journey_with_outcome WHERE touch_position = 1
    UNION ALL
    SELECT a.channel_name AS from_state, b.channel_name AS to_state
    FROM journey_with_outcome a JOIN journey_with_outcome b ON a.customer_id = b.customer_id AND b.touch_position = a.touch_position + 1
    UNION ALL
    SELECT channel_name AS from_state, outcome AS to_state FROM journey_with_outcome WHERE touch_position = total_touches
)
SELECT from_state, to_state, COUNT(*) AS transition_count,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY from_state), 4) AS transition_probability
FROM all_transitions
GROUP BY from_state, to_state
ORDER BY from_state, transition_probability DESC;

You now have a complete first-order Markov transition matrix showing how customers flow between channels. Next, you'll use this structure to compute removal effects and finally answer the question: what happens to conversions if you remove each channel entirely?

Compute Removal Effects and Attribution

Your transition matrix shows how customers flow between channels. But the real question is: what happens to conversions if a channel disappears entirely?

This is the core insight behind removal effects. Instead of crediting the channel that happened to be first or last, you measure each channel's structural importance to the conversion network. A channel with a high removal effect is a bridge that customers need to reach conversion.

In this step, get ready to:

• Compute the baseline conversion rate using a CTE that flags each customer as converted or not.
• Calculate the empirical removal effect for each channel by filtering out journeys that contain it.
• Normalize removal effects into attribution shares and apply them to total revenue.

Compute the baseline conversion rate

Before you can measure what happens when a channel is removed, you need a reference point. The baseline conversion rate is the overall probability of any customer converting across all journeys.

The query you are about to write uses two CTEs to establish this baseline. Here is the logic for the first one:

    SELECT
        cust.customer_id,
        CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
    FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
    LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
        ON cust.customer_id = conv.customer_id

What does this CTE do?

The customer_outcomes CTE creates a simple flag for every customer who has at least one touchpoint. If they appear in the conversions table, they get converted = 1. Otherwise, converted = 0.

The second CTE, baseline, then uses AVG(converted) to compute the overall conversion rate. Since converted is 0 or 1, the average equals the proportion of customers who converted.

Compute the empirical removal effect for each channel

The removal effect asks: "If we completely remove Channel X from every journey, how much does the conversion rate drop?" A large drop means the channel is structurally important.

In production, this requires matrix inversion. In pure SQL, you use an empirical approximation: filter to journeys that never touched Channel X and measure their conversion rate directly.

Why empirical instead of true matrix math?

Full Markov chain removal effects require matrix operations (the fundamental matrix N = (I-Q)^-1). SQL cannot perform matrix inversion natively.

The practical production approach is SQL for path extraction plus Python for linear algebra. Your pure-SQL version uses empirical path filtering, which gives directionally correct attribution for datasets with sufficient path diversity.

Here is the key pattern that powers the removal calculation:

    SELECT
        ch.channel_name,
        COUNT(*) AS journeys_without_channel,
        SUM(co.converted) AS conversions_without,
        AVG(co.converted) AS conv_rate_without
    FROM channels ch
    CROSS JOIN customer_outcomes co
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer_channel_presence ccp
        WHERE ccp.customer_id = co.customer_id
        AND ccp.channel_name = ch.channel_name
    )
    GROUP BY ch.channel_name

What does this pattern do?

The CROSS JOIN pairs every channel with every customer (5 channels x 2000 customers = 10,000 rows). Then NOT EXISTS filters out any customer who actually touched that channel.

What remains for each channel is the set of customers who never interacted with it. Computing AVG(co.converted) on this filtered set gives the conversion rate "without" that channel. If it drops significantly below baseline, the channel is structurally important.

Normalize removal effects into attribution shares

Raw removal effects do not sum to 100%. To turn them into revenue allocation, you normalize each channel's removal effect by dividing it by the sum of all removal effects. This gives each channel a proportional share that adds up to 1.

• Create a new query tab in MySQL Workbench and create a file called 07_removal_effect.sql.
• Paste the full removal effect query below, which combines all the CTEs into one statement:

-- Step 7: Compute Empirical Removal Effects & Attribution
USE ecommerce_attribution;

-- Identify which channels each customer touched
-- Then compute conversion rate when each channel is removed
WITH customer_outcomes AS (
    SELECT
        cust.customer_id,
        CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
    FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
    LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
        ON cust.customer_id = conv.customer_id
),
customer_channel_presence AS (
    SELECT DISTINCT t.customer_id, c.channel_name
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
),
baseline AS (
    SELECT
        COUNT(*) AS total_journeys,
        SUM(converted) AS total_conversions,
        AVG(converted) AS baseline_conv_rate
    FROM customer_outcomes
),
removal_stats AS (
    SELECT
        ch.channel_name,
        COUNT(*) AS journeys_without_channel,
        SUM(co.converted) AS conversions_without,
        AVG(co.converted) AS conv_rate_without
    FROM channels ch
    CROSS JOIN customer_outcomes co
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer_channel_presence ccp
        WHERE ccp.customer_id = co.customer_id
        AND ccp.channel_name = ch.channel_name
    )
    GROUP BY ch.channel_name
)
SELECT
    rs.channel_name,
    ROUND(b.baseline_conv_rate, 4) AS baseline_conv_rate,
    ROUND(rs.conv_rate_without, 4) AS conv_rate_without_channel,
    ROUND(1 - (rs.conv_rate_without / b.baseline_conv_rate), 4) AS removal_effect,
    ROUND(
        (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
        SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER (),
        4
    ) AS attribution_share,
    rs.journeys_without_channel,
    ROUND(
        (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
        SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER ()
        * (SELECT SUM(revenue) FROM conversions),
        2
    ) AS revenue_attributed
FROM removal_stats rs
CROSS JOIN baseline b
ORDER BY removal_effect DESC;

What does the normalization do?

The final SELECT computes three key values per channel:

• removal_effect = 1 - (conv_rate_without / baseline_conv_rate). A value of 0.15 means removing the channel drops conversion rate by 15% relative to baseline.
• attribution_share divides each removal effect by the sum of all removal effects using SUM(...) OVER (). This is the window function that normalizes across all rows without collapsing them.
• revenue_attributed multiplies the attribution share by total revenue. This is the final dollar figure each channel "deserves."

• Save 07_removal_effect.sql and execute the query by clicking the lightning bolt icon or pressing Cmd+Shift+Enter (macOS) or Ctrl+Shift+Enter (Windows).
• You should see 5 rows in the result grid, one per channel. Each row shows the baseline rate, the rate without that channel, the removal effect, attribution share, and revenue attributed.

How do I interpret these results?

Look at which channel has the highest removal_effect. This is the channel whose absence causes the biggest drop in conversions. Due to the position-biased data generation, you should see discovery channels (like Organic_Search) score higher than they did under last-click attribution.

The attribution_share column should sum to approximately 1.0000 across all 5 channels. The revenue_attributed column should sum to your total revenue. These are your sanity checks.

Query takes a few seconds

The CROSS JOIN in removal_stats creates ~10,000 rows before filtering (5 channels x 2000 customers). This is manageable but may take 2-5 seconds depending on your hardware.

Query returning NULL values?

If removal_effect or attribution_share shows NULL, check that you ran CALL generate_attribution_data() in Step 2 and that the conversions table has data. Run SELECT COUNT(*) FROM conversions; to verify.

If you see a division by zero error, your baseline_conv_rate may be 0. Confirm your touchpoints table is populated with SELECT COUNT(*) FROM touchpoints; and that at least some customers appear in both tables.

Help me debug my removal effect query

✔️ Awesome, I've got everything!

Great. Double check you have saved 07_removal_effect.sql and that your attribution shares sum to approximately 1.0.

ⓧ I'd like to double check the full code

-- Step 7: Compute Empirical Removal Effects & Attribution
USE ecommerce_attribution;

-- Identify which channels each customer touched
-- Then compute conversion rate when each channel is removed
WITH customer_outcomes AS (
    SELECT
        cust.customer_id,
        CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
    FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
    LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
        ON cust.customer_id = conv.customer_id
),
customer_channel_presence AS (
    SELECT DISTINCT t.customer_id, c.channel_name
    FROM touchpoints t
    JOIN channels c ON t.channel_id = c.channel_id
),
baseline AS (
    SELECT
        COUNT(*) AS total_journeys,
        SUM(converted) AS total_conversions,
        AVG(converted) AS baseline_conv_rate
    FROM customer_outcomes
),
removal_stats AS (
    SELECT
        ch.channel_name,
        COUNT(*) AS journeys_without_channel,
        SUM(co.converted) AS conversions_without,
        AVG(co.converted) AS conv_rate_without
    FROM channels ch
    CROSS JOIN customer_outcomes co
    WHERE NOT EXISTS (
        SELECT 1
        FROM customer_channel_presence ccp
        WHERE ccp.customer_id = co.customer_id
        AND ccp.channel_name = ch.channel_name
    )
    GROUP BY ch.channel_name
)
SELECT
    rs.channel_name,
    ROUND(b.baseline_conv_rate, 4) AS baseline_conv_rate,
    ROUND(rs.conv_rate_without, 4) AS conv_rate_without_channel,
    ROUND(1 - (rs.conv_rate_without / b.baseline_conv_rate), 4) AS removal_effect,
    ROUND(
        (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
        SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER (),
        4
    ) AS attribution_share,
    rs.journeys_without_channel,
    ROUND(
        (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
        SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER ()
        * (SELECT SUM(revenue) FROM conversions),
        2
    ) AS revenue_attributed
FROM removal_stats rs
CROSS JOIN baseline b
ORDER BY removal_effect DESC;

You now have a data-driven answer to which channels truly matter. Next, you will compare all four attribution models side-by-side and validate whether the Markov approach tells a different story than last-click.

Compare Attribution Models

You've computed the Markov removal effects and seen how each channel's structural importance translates into attribution shares. Now you need to see all four models next to each other so you can spot where they agree and where they diverge.

A side-by-side comparison is how you build a compelling case for stakeholders. If every model tells the same story, no one needs convincing. When models disagree, the gap between them IS the insight.

In this step, get ready to:

• Create a unified comparison query showing revenue from all 4 models per channel.
• Validate the Markov model with directional checks.
• Identify the most under-valued channel as your investment recommendation.

Build the unified model comparison query

Each attribution model lives in its own query file right now. To compare them, you need a single query that re-computes all four models as CTEs (Common Table Expressions) and joins the results into one output table.

• Create a new query tab in MySQL Workbench.
• Paste the following unified comparison query:

-- Step 8: Model Comparison & Validation
USE ecommerce_attribution;

-- Unified comparison of all attribution models
WITH last_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS revenue
    FROM (
        SELECT c.channel_name, conv.revenue,
            ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp DESC) AS rn
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
        JOIN conversions conv ON t.customer_id = conv.customer_id
        WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
    ) r WHERE rn = 1
    GROUP BY channel_name
),
first_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS revenue
    FROM (
        SELECT c.channel_name, conv.revenue,
            ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp ASC) AS rn
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
        JOIN conversions conv ON t.customer_id = conv.customer_id
        WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
    ) r WHERE rn = 1
    GROUP BY channel_name
),
linear_attr AS (
    SELECT channel_name, ROUND(SUM(rev_share), 2) AS revenue
    FROM (
        SELECT c.channel_name,
            conv.revenue / COUNT(*) OVER (PARTITION BY t.customer_id) AS rev_share
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
        JOIN conversions conv ON t.customer_id = conv.customer_id
        WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
    ) r
    GROUP BY channel_name
),
markov_attr AS (
    WITH customer_outcomes AS (
        SELECT cust.customer_id,
            CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
        FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
        LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
            ON cust.customer_id = conv.customer_id
    ),
    customer_channel_presence AS (
        SELECT DISTINCT t.customer_id, c.channel_name
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
    ),
    baseline AS (
        SELECT AVG(converted) AS baseline_conv_rate
        FROM customer_outcomes
    ),
    removal_stats AS (
        SELECT ch.channel_name,
            AVG(co.converted) AS conv_rate_without
        FROM channels ch
        CROSS JOIN customer_outcomes co
        WHERE NOT EXISTS (
            SELECT 1 FROM customer_channel_presence ccp
            WHERE ccp.customer_id = co.customer_id
            AND ccp.channel_name = ch.channel_name
        )
        GROUP BY ch.channel_name
    )
    SELECT
        rs.channel_name,
        ROUND(
            (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
            SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER ()
            * (SELECT SUM(revenue) FROM conversions),
            2
        ) AS revenue
    FROM removal_stats rs
    CROSS JOIN baseline b
)
-- Final comparison
SELECT
    ch.channel_name,
    COALESCE(lc.revenue, 0) AS last_click_revenue,
    COALESCE(fc.revenue, 0) AS first_click_revenue,
    COALESCE(li.revenue, 0) AS linear_revenue,
    COALESCE(mk.revenue, 0) AS markov_revenue,
    ROUND(COALESCE(mk.revenue, 0) - COALESCE(lc.revenue, 0), 2) AS markov_vs_lastclick_delta
FROM channels ch
LEFT JOIN last_click lc ON ch.channel_name = lc.channel_name
LEFT JOIN first_click fc ON ch.channel_name = fc.channel_name
LEFT JOIN linear_attr li ON ch.channel_name = li.channel_name
LEFT JOIN markov_attr mk ON ch.channel_name = mk.channel_name
ORDER BY markov_vs_lastclick_delta DESC;

What does this query do?

This query uses four CTEs to re-compute each attribution model, then joins the results on channel_name so you see one row per channel with columns for every model.

• last_click and first_click CTEs use ROW_NUMBER() with opposite sort orders to isolate the final or initial touchpoint.
• linear_attr splits revenue evenly across all touchpoints using COUNT(*) OVER (PARTITION BY t.customer_id).
• markov_attr nests the removal effect logic (the same CTEs from Step 7) and normalizes shares into revenue.
• The final SELECT LEFT JOINs all four CTEs and computes markov_vs_lastclick_delta to quantify where the models disagree.

• Click the lightning bolt icon to execute the query.
• You should see a results grid with 5 rows (one per channel) and 6 columns: channel_name, last_click_revenue, first_click_revenue, linear_revenue, markov_revenue, and markov_vs_lastclick_delta.
• Save this query as 08_model_comparison.sql.

Query taking too long or timing out?

The markov_attr CTE uses a CROSS JOIN across 2000 customers and 5 channels. This is the heaviest operation in the query. On most machines it completes within 5-10 seconds.

• If you see a timeout error, check that no other long-running queries are active in other tabs.
• Make sure the USE ecommerce_attribution; line is included at the top of your file.

Why is my unified comparison query running slowly or erroring?

Validate the Markov model with directional checks

Numbers alone are not enough. You need to verify that the Markov model behaves the way theory predicts. Three directional checks confirm the model is capturing discovery value correctly.

• Look at the markov_revenue column. Add up all 5 values mentally or with a calculator. They should sum to approximately the same total as the last_click_revenue column (your total revenue from conversions).
• Find the Organic_Search row. Confirm that its markov_revenue is HIGHER than its last_click_revenue.
• Find the Direct row. Confirm that its markov_revenue is LOWER than its last_click_revenue.

Why these specific checks?

Your synthetic data was built with position bias: discovery channels like Organic_Search appear early in journeys, while closing channels like Direct appear last. Last-click over-credits closers and under-credits discoverers.

The Markov removal effect measures structural importance. When you remove a discovery channel, many journeys lose their entry point entirely. The model captures this by giving discovery channels more credit than last-click would.

Your exact numbers will differ

Because the data generation uses randomness, your specific revenue values will be different from someone else running the same project. The directional patterns (Organic up, Direct down) should hold consistently. This is actually a feature for portfolio work. It demonstrates understanding, not memorized outputs.

Identify the most under-valued channel

The markov_vs_lastclick_delta column is your investment signal. A positive delta means the Markov model credits that channel MORE than last-click does. That channel is being under-funded relative to its true impact.

• Look at the first row in your results (the query orders by markov_vs_lastclick_delta DESC).
• The channel in that first row has the largest positive delta. This is your recommended channel for increased investment.
• Look at the last row. The channel with the largest negative delta is being over-credited by last-click. Budget could be safely reallocated away from it.

What is the delta telling stakeholders?

The delta translates a statistical concept (removal effect) into a business action. A positive delta of £5,000 on Organic Search means: under last-click, your company is under-investing in Organic Search by roughly £5,000 worth of credit.

This is the kind of data-driven recommendation that separates a report from a decision tool. You are not just measuring. You are prescribing.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file as 08_model_comparison.sql.

ⓧ I'd like to double check the full code

Here is the complete 08_model_comparison.sql file:

-- Step 8: Model Comparison & Validation
USE ecommerce_attribution;

WITH last_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS revenue
    FROM (SELECT c.channel_name, conv.revenue, ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp DESC) AS rn FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id WHERE t.touchpoint_timestamp <= conv.conversion_timestamp) r WHERE rn = 1 GROUP BY channel_name
),
first_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS revenue
    FROM (SELECT c.channel_name, conv.revenue, ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp ASC) AS rn FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id WHERE t.touchpoint_timestamp <= conv.conversion_timestamp) r WHERE rn = 1 GROUP BY channel_name
),
linear_attr AS (
    SELECT channel_name, ROUND(SUM(rev_share), 2) AS revenue
    FROM (SELECT c.channel_name, conv.revenue / COUNT(*) OVER (PARTITION BY t.customer_id) AS rev_share FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id JOIN conversions conv ON t.customer_id = conv.customer_id WHERE t.touchpoint_timestamp <= conv.conversion_timestamp) r GROUP BY channel_name
),
markov_attr AS (
    WITH customer_outcomes AS (SELECT cust.customer_id, CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted FROM (SELECT DISTINCT customer_id FROM touchpoints) cust LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv ON cust.customer_id = conv.customer_id),
    customer_channel_presence AS (SELECT DISTINCT t.customer_id, c.channel_name FROM touchpoints t JOIN channels c ON t.channel_id = c.channel_id),
    baseline AS (SELECT AVG(converted) AS baseline_conv_rate FROM customer_outcomes),
    removal_stats AS (SELECT ch.channel_name, AVG(co.converted) AS conv_rate_without FROM channels ch CROSS JOIN customer_outcomes co WHERE NOT EXISTS (SELECT 1 FROM customer_channel_presence ccp WHERE ccp.customer_id = co.customer_id AND ccp.channel_name = ch.channel_name) GROUP BY ch.channel_name)
    SELECT rs.channel_name, ROUND((1 - (rs.conv_rate_without / b.baseline_conv_rate)) / SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER () * (SELECT SUM(revenue) FROM conversions), 2) AS revenue FROM removal_stats rs CROSS JOIN baseline b
)
SELECT ch.channel_name,
    COALESCE(lc.revenue, 0) AS last_click_revenue,
    COALESCE(fc.revenue, 0) AS first_click_revenue,
    COALESCE(li.revenue, 0) AS linear_revenue,
    COALESCE(mk.revenue, 0) AS markov_revenue,
    ROUND(COALESCE(mk.revenue, 0) - COALESCE(lc.revenue, 0), 2) AS markov_vs_lastclick_delta
FROM channels ch
LEFT JOIN last_click lc ON ch.channel_name = lc.channel_name
LEFT JOIN first_click fc ON ch.channel_name = fc.channel_name
LEFT JOIN linear_attr li ON ch.channel_name = li.channel_name
LEFT JOIN markov_attr mk ON ch.channel_name = mk.channel_name
ORDER BY markov_vs_lastclick_delta DESC;

You now have hard numbers showing where each model agrees and disagrees. Next up, you'll turn these numbers into a stakeholder-ready executive summary with clear investment recommendations.

Build the Executive Summary

You now have four attribution models producing different revenue allocations per channel. The next question is: so what? A marketing director does not want a spreadsheet of model outputs.

In this step, you will turn your Markov analysis into a decision-ready executive summary. The view you create will compare Markov attribution against last-click, flag channels that deserve more or less investment, and output a stakeholder-ready narrative string.

In this step, get ready to:

• Create an executive_summary view with per-channel investment recommendations.
• Write a narrative query that produces stakeholder-ready insight strings.

Create the executive summary view

The model comparison from Step 8 showed raw revenue numbers for all four models. Your executive summary distills that into a single actionable output: for each channel, should you increase investment, decrease it, or maintain?

The logic uses a 10% threshold. If Markov attributes more than 10% above what last-click gives a channel, it recommends increasing investment. If Markov attributes more than 10% below, it recommends decreasing.

• Create a new query tab in MySQL Workbench.
• Create the executive_summary view and query it by running this code:

-- Step 9: Executive Summary & Storytelling
USE ecommerce_attribution;

-- Executive summary view with investment recommendations
CREATE OR REPLACE VIEW executive_summary AS
WITH last_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS lc_revenue
    FROM (
        SELECT c.channel_name, conv.revenue,
            ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp DESC) AS rn
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
        JOIN conversions conv ON t.customer_id = conv.customer_id
        WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
    ) r WHERE rn = 1
    GROUP BY channel_name
),
markov_attr AS (
    WITH customer_outcomes AS (
        SELECT cust.customer_id,
            CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
        FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
        LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
            ON cust.customer_id = conv.customer_id
    ),
    customer_channel_presence AS (
        SELECT DISTINCT t.customer_id, c.channel_name
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
    ),
    baseline AS (
        SELECT AVG(converted) AS baseline_conv_rate
        FROM customer_outcomes
    ),
    removal_stats AS (
        SELECT ch.channel_name,
            AVG(co.converted) AS conv_rate_without
        FROM channels ch
        CROSS JOIN customer_outcomes co
        WHERE NOT EXISTS (
            SELECT 1 FROM customer_channel_presence ccp
            WHERE ccp.customer_id = co.customer_id
            AND ccp.channel_name = ch.channel_name
        )
        GROUP BY ch.channel_name
    )
    SELECT
        rs.channel_name,
        ROUND(1 - (rs.conv_rate_without / b.baseline_conv_rate), 4) AS removal_effect,
        ROUND(
            (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
            SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER ()
            * (SELECT SUM(revenue) FROM conversions),
            2
        ) AS mk_revenue
    FROM removal_stats rs
    CROSS JOIN baseline b
)
SELECT
    mk.channel_name,
    lc.lc_revenue AS lastclick_revenue,
    mk.mk_revenue AS markov_revenue,
    mk.removal_effect,
    ROUND(mk.mk_revenue - lc.lc_revenue, 2) AS revenue_delta,
    ROUND((mk.mk_revenue - lc.lc_revenue) / lc.lc_revenue * 100, 1) AS delta_pct,
    CASE
        WHEN mk.mk_revenue > lc.lc_revenue * 1.1 THEN 'INCREASE INVESTMENT'
        WHEN mk.mk_revenue < lc.lc_revenue * 0.9 THEN 'DECREASE INVESTMENT'
        ELSE 'MAINTAIN'
    END AS recommendation
FROM markov_attr mk
JOIN last_click lc ON mk.channel_name = lc.channel_name
ORDER BY revenue_delta DESC;

-- View the executive summary
SELECT * FROM executive_summary;

What does this view do?

The view combines last-click revenue and Markov revenue for each channel, then computes three decision columns:

• revenue_delta is the raw pound difference between what Markov says and what last-click says.
• delta_pct expresses that difference as a percentage of last-click revenue.
• recommendation uses a 10% threshold via a CASE expression. If Markov credits a channel more than 10% above last-click, the channel is under-invested.

• Click the lightning bolt icon to execute the full script.
• In the results grid, you should see one row per channel with columns: channel_name, lastclick_revenue, markov_revenue, removal_effect, revenue_delta, delta_pct, and recommendation.
• Confirm that the recommendation column shows a mix of INCREASE INVESTMENT, DECREASE INVESTMENT, and MAINTAIN values.

Query takes a long time or times out?

The view contains a CROSS JOIN that produces 2000 customers x 5 channels = 10,000 rows before filtering. On most machines this completes in a few seconds, but if MySQL Workbench shows a timeout, try running just the SELECT * FROM executive_summary statement on its own after the CREATE VIEW succeeds.

help me debug why my executive_summary view is timing out in MySQL Workbench

Why compare against last-click specifically?

Last-click is the industry default. Most analytics platforms (Google Analytics included) default to last-click attribution. Your executive summary shows stakeholders exactly how much revenue their current default model is mis-allocating per channel.

Write the narrative query for stakeholder presentation

Numbers in a table are useful for analysts. But when presenting to executives, a plain-English sentence is far more persuasive. You will use CONCAT() to assemble a structured insight string from each row of the executive summary.

• Add the following narrative query below your existing code in the same tab, then execute it:

-- Narrative output for stakeholder presentation
SELECT CONCAT(
    'ATTRIBUTION INSIGHT: Under last-click, ',
    channel_name,
    ' receives £', FORMAT(lastclick_revenue, 0),
    ' in credit. Markov analysis reveals it actually drives £',
    FORMAT(markov_revenue, 0),
    ' (', delta_pct, '% ',
    CASE WHEN delta_pct > 0 THEN 'more' ELSE 'less' END,
    '). Recommendation: ', recommendation, '.'
) AS insight
FROM executive_summary
ORDER BY ABS(revenue_delta) DESC;

What does this query do?

CONCAT() stitches column values into a human-readable sentence. FORMAT(lastclick_revenue, 0) adds thousand-separators for readability (e.g. £12,345 instead of 12345.00).

The ORDER BY ABS(revenue_delta) DESC puts the channel with the biggest gap between Markov and last-click at the top. This is your strongest recommendation.

• In the results grid, you should see one insight string per channel. Each string reads like a complete sentence with pound values, a percentage comparison, and an actionable recommendation.
• The first row (largest absolute delta) is your headline finding. This is what you would lead with in a stakeholder meeting.

Seeing NULL in the insight column?

If any column used in CONCAT() is NULL, the entire result becomes NULL. Run SELECT * FROM executive_summary first and check for NULL values in any column. This typically means the view creation had an issue with the JOIN between markov_attr and last_click.

help me debug NULL values in my CONCAT narrative query

Extra for Experts: Why ABS() in the ORDER BY?

A channel that is over-credited by last-click (negative delta) is just as important to flag as one that is under-credited (positive delta). Using ABS(revenue_delta) sorts by magnitude of the gap regardless of direction, so your most impactful insight always surfaces first.

✔️ Awesome, I've got everything!

Great. Double check you have saved your 09_executive_summary.sql file.

ⓧ I'd like to double check the full code

Here is the complete 09_executive_summary.sql file for comparison:

-- Step 9: Executive Summary & Storytelling
USE ecommerce_attribution;

-- Executive summary view with investment recommendations
CREATE OR REPLACE VIEW executive_summary AS
WITH last_click AS (
    SELECT channel_name, ROUND(SUM(revenue), 2) AS lc_revenue
    FROM (
        SELECT c.channel_name, conv.revenue,
            ROW_NUMBER() OVER (PARTITION BY t.customer_id ORDER BY t.touchpoint_timestamp DESC) AS rn
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
        JOIN conversions conv ON t.customer_id = conv.customer_id
        WHERE t.touchpoint_timestamp <= conv.conversion_timestamp
    ) r WHERE rn = 1
    GROUP BY channel_name
),
markov_attr AS (
    WITH customer_outcomes AS (
        SELECT cust.customer_id,
            CASE WHEN conv.customer_id IS NOT NULL THEN 1 ELSE 0 END AS converted
        FROM (SELECT DISTINCT customer_id FROM touchpoints) cust
        LEFT JOIN (SELECT DISTINCT customer_id FROM conversions) conv
            ON cust.customer_id = conv.customer_id
    ),
    customer_channel_presence AS (
        SELECT DISTINCT t.customer_id, c.channel_name
        FROM touchpoints t
        JOIN channels c ON t.channel_id = c.channel_id
    ),
    baseline AS (
        SELECT AVG(converted) AS baseline_conv_rate
        FROM customer_outcomes
    ),
    removal_stats AS (
        SELECT ch.channel_name,
            AVG(co.converted) AS conv_rate_without
        FROM channels ch
        CROSS JOIN customer_outcomes co
        WHERE NOT EXISTS (
            SELECT 1 FROM customer_channel_presence ccp
            WHERE ccp.customer_id = co.customer_id
            AND ccp.channel_name = ch.channel_name
        )
        GROUP BY ch.channel_name
    )
    SELECT
        rs.channel_name,
        ROUND(1 - (rs.conv_rate_without / b.baseline_conv_rate), 4) AS removal_effect,
        ROUND(
            (1 - (rs.conv_rate_without / b.baseline_conv_rate)) /
            SUM(1 - (rs.conv_rate_without / b.baseline_conv_rate)) OVER ()
            * (SELECT SUM(revenue) FROM conversions),
            2
        ) AS mk_revenue
    FROM removal_stats rs
    CROSS JOIN baseline b
)
SELECT
    mk.channel_name,
    lc.lc_revenue AS lastclick_revenue,
    mk.mk_revenue AS markov_revenue,
    mk.removal_effect,
    ROUND(mk.mk_revenue - lc.lc_revenue, 2) AS revenue_delta,
    ROUND((mk.mk_revenue - lc.lc_revenue) / lc.lc_revenue * 100, 1) AS delta_pct,
    CASE
        WHEN mk.mk_revenue > lc.lc_revenue * 1.1 THEN 'INCREASE INVESTMENT'
        WHEN mk.mk_revenue < lc.lc_revenue * 0.9 THEN 'DECREASE INVESTMENT'
        ELSE 'MAINTAIN'
    END AS recommendation
FROM markov_attr mk
JOIN last_click lc ON mk.channel_name = lc.channel_name
ORDER BY revenue_delta DESC;

-- View the executive summary
SELECT * FROM executive_summary;

-- Narrative output for stakeholder presentation
SELECT CONCAT(
    'ATTRIBUTION INSIGHT: Under last-click, ',
    channel_name,
    ' receives £', FORMAT(lastclick_revenue, 0),
    ' in credit. Markov analysis reveals it actually drives £',
    FORMAT(markov_revenue, 0),
    ' (', delta_pct, '% ',
    CASE WHEN delta_pct > 0 THEN 'more' ELSE 'less' END,
    '). Recommendation: ', recommendation, '.'
) AS insight
FROM executive_summary
ORDER BY ABS(revenue_delta) DESC;

Secret mission

Add Time-Decay Attribution and Channel Sequence Analysis

Your four attribution models already tell a powerful story. But marketers want to know two more things: does recency matter, and which channel combinations are the "power combos" that drive conversions above baseline? Build a time-decay model and a channel sequence analyzer to find out.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally on your MySQL instance. There are no cloud services or ongoing costs to worry about.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

• The ecommerce_attribution MySQL database (tables, views, stored procedure, and all generated data).
• SQL script files (01_create_schema.sql through 10_secret_mission.sql).
• Your local MySQL 8.0 server instance.

Keep everything running

No action needed. Choose this if you're still actively exploring the attribution models or want to experiment with new queries.

• Your ecommerce_attribution database stays intact in MySQL with all tables, views, and data.
• Your SQL script files remain wherever you saved them for future reference.
• The MySQL server continues running in the background. It uses minimal resources when idle.

Pause - I'll come back to this later

Stop the MySQL server to free up system memory while keeping all your data intact for later.

• On macOS, open **System Preferences** (or **System Settings**), then select **MySQL**. Click **Stop MySQL Server**.
• On Windows, press the **Windows** key and search for services. Find **MySQL80** in the list, right-click it, and select **Stop**.
• Your database and all SQL files stay on disk. When you want to resume, start the MySQL service again and reconnect in MySQL Workbench.

Delete - I don't want to use this again

Remove the project database and script files completely. Your MySQL server and MySQL Workbench remain installed.

• In MySQL Workbench, open a new query tab and run the following command to drop the entire database:

DROP DATABASE ecommerce_attribution;

• This removes all tables, views, stored procedures, and data in one command.
• Delete the SQL script files you saved during the project (01_create_schema.sql through 10_secret_mission.sql). Navigate to the folder where you saved them and delete them.
• On macOS or Linux, you can delete the files from the terminal:

rm 01_create_schema.sql 02_generate_data.sql 03_data_quality_audit.sql 04_exploratory_analysis.sql 05_heuristic_models.sql 06_markov_transition_matrix.sql 07_removal_effect.sql 08_model_comparison.sql 09_executive_summary.sql 10_secret_mission.sql

• On Windows, select all the SQL files in File Explorer, right-click, and choose **Delete**.

Nice Work!

Nice Work!

You just built a probabilistic marketing attribution system in pure SQL. Starting from synthetic e-commerce data, you progressed through increasingly sophisticated models until you could quantify exactly how much revenue each marketing channel truly drives.

You've learned how to:

• Build a Markov transition probability matrix and compute empirical removal effects to measure each channel's true structural importance in driving conversions.
• Implement and compare 4 attribution models (last-click, first-click, linear, Markov) to reveal how different approaches allocate revenue credit across marketing channels.
• Produce a stakeholder-ready executive summary with data-driven investment recommendations backed by removal effect analysis.
• Added time-decay attribution and channel sequence analysis to identify high-converting channel pairs and weight touchpoints by recency.

Ready to quiz yourself?