The Pipulate 'TypeError' Debacle: A Lesson in JSON State Management

(nix) 
[mike@nixos:~/repos/pipulate/Notebooks]$ git --no-pager diff --no-textconv
diff --git a/Notebooks/gap_analyzer_sauce.py b/Notebooks/gap_analyzer_sauce.py
index 96988f39..b878905b 100644
--- a/Notebooks/gap_analyzer_sauce.py
+++ b/Notebooks/gap_analyzer_sauce.py
@@ -37,6 +37,11 @@ from sklearn.feature_extraction.text import TfidfVectorizer
 from sklearn.metrics import silhouette_score
 import numpy as np
 
+# --- EXCEL OUTPUT SUPPORT ---
+import platform
+import subprocess
+import ipywidgets as widgets
+from IPython.display import display
 
 import nltk
 
@@ -1770,3 +1775,120 @@ def cluster_and_finalize_dataframe(job: str, df: pd.DataFrame, has_botify: bool)
         print(f"❌ An error occurred during clustering and finalization: {e}")
         pip.set(job, 'final_clustered_df_json', pd.DataFrame().to_json(orient='records'))
         return pd.DataFrame() # Return empty DataFrame
+
+
+# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work
+def _open_folder(path_str: str = "."):
+    """Opens the specified folder in the system's default file explorer."""
+    folder_path = Path(path_str).resolve()
+    
+    if not folder_path.exists() or not folder_path.is_dir():
+        print(f"❌ Error: Path is not a valid directory: {folder_path}")
+        return
+
+    system = platform.system()
+    try:
+        if system == "Windows":
+            os.startfile(folder_path)
+        elif system == "Darwin":  # macOS
+            subprocess.run(["open", folder_path])
+        else:  # Linux (xdg-open covers most desktop environments)
+            subprocess.run(["xdg-open", folder_path])
+    except Exception as e:
+        print(f"❌ Failed to open folder. Error: {e}")
+
+# This utility must be defined for normalize_and_score to work
+def safe_normalize(series):
+    """ Normalize the series safely to avoid divide by zero and handle NaN values. """
+    min_val = series.min()
+    max_val = series.max()
+    range_val = max_val - min_val
+    if range_val == 0:
+        # Avoid division by zero by returning zero array if no range
+        return np.zeros_like(series)
+    else:
+        # Normalize and fill NaN values that might result from empty/NaN series
+        return (series - min_val).div(range_val).fillna(0)
+
+# Surgical port of bf.reorder_columns
+def reorder_columns_surgical(df, priority_column, after_column):
+    if priority_column in df.columns:
+        columns = list(df.columns.drop(priority_column))
+        # Handle cases where the after_column may have been dropped earlier
+        if after_column not in columns:
+            print(f"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.")
+            return df
+            
+        after_column_index = columns.index(after_column)
+        columns.insert(after_column_index + 1, priority_column)
+        df = df[columns]
+    else:
+        print(f"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.")
+    return df
+
+# Surgical port of bf.normalize_and_score - WITH CRITICAL FIX
+def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):
+    
+    # Rename original column fields to match expected names in the dataframe
+    if 'internal_page_rank.raw' in df.columns:
+        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)
+    
+    # --- CRITICAL FIX FOR KEYERROR / TRAILING SLASHES ---
+    # The lookup key (e.g., 'nixos.org') must be matched against the DataFrame column (e.g., 'nixos.org/').
+    # We clean both for comparison to find the unique canonical key, but use the original column name.
+    
+    # Clean the lookup domain (assuming the input `registered_domain` might be missing the slash)
+    clean_lookup_key = registered_domain.rstrip('/')
+    target_col = None
+    
+    for col in df.columns:
+        # Find the column whose stripped name matches the stripped lookup key.
+        if col.rstrip('/') == clean_lookup_key:
+            target_col = col
+            break
+            
+    if target_col is None:
+        raise KeyError(f"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}")
+    # --- END CRITICAL FIX ---
+    
+    # Normalize metrics that are always included
+    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])
+    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- USES THE FOUND, CANONICAL COLUMN NAME
+    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])
+    df['Normalized CPC'] = safe_normalize(df['CPC'])
+
+    # Always include CPC and Keyword Difficulty in the combined score
+    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']
+
+    if has_botify_data:
+        # Normalize additional Botify metrics if available
+        if 'Raw Internal Pagerank' in df.columns:
+            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])
+        else:
+            df['Normalized Raw Internal Pagerank'] = 0
+
+        if "No. of Missed Clicks excluding anonymized queries" in df.columns:
+            df['Normalized Missed Clicks'] = safe_normalize(df["No. of Missed Clicks excluding anonymized queries"])
+            combined_score += df['Normalized Missed Clicks']
+        else:
+            df['Normalized Missed Clicks'] = 0
+
+        # Add Botify metrics to the combined score
+        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +
+                           df['Normalized Search Volume'] +
+                           df['Normalized Search Position'])
+
+    # Apply the combined score to the DataFrame
+    df['Combined Score'] = combined_score
+
+    if reorder:
+        # Reorder columns if required (using the surgically ported reorder function)
+        df = reorder_columns_surgical(df, "CPC", after_col)
+        df = reorder_columns_surgical(df, "Keyword Difficulty", "CPC")
+        if has_botify_data:
+            df = reorder_columns_surgical(df, "Internal Pagerank", "Keyword Difficulty")
+            df = reorder_columns_surgical(df, "No. of Unique Inlinks", "Internal Pagerank")
+            if "No. of Missed Clicks excluding anonymized queries" in df.columns:
+                df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")
+
+    return df
diff --git a/assets/nbs/GAPalyzer.ipynb b/assets/nbs/GAPalyzer.ipynb
index 7318d1d8..a3eaabf5 100644
--- a/assets/nbs/GAPalyzer.ipynb
+++ b/assets/nbs/GAPalyzer.ipynb
@@ -427,7 +427,7 @@
    "id": "20",
    "metadata": {},
    "source": [
-    "## Botify Download"
+    "## Download Botify Data"
    ]
   },
   {
@@ -480,7 +480,7 @@
    "id": "22",
    "metadata": {},
    "source": [
-    "## Botify Join"
+    "## Join Botify Data"
    ]
   },
   {
@@ -572,14 +572,21 @@
     "# print(f\"  Truncated DF rows in state: {len(loaded_truncated_df)}\")"
    ]
   },
+  {
+   "cell_type": "markdown",
+   "id": "26",
+   "metadata": {},
+   "source": [
+    "## Cluster Keywords"
+   ]
+  },
   {
    "cell_type": "code",
    "execution_count": null,
-   "id": "26",
+   "id": "27",
    "metadata": {},
    "outputs": [],
    "source": [
-    "# %% editable=true slideshow={\"slide_type\": \"\"}\n",
     "import pandas as pd # Keep for display\n",
     "from IPython.display import display\n",
     "import gap_analyzer_sauce # Ensure module is imported\n",
@@ -612,146 +619,6 @@
     "# print(f\"  Clustered DF rows in state: {len(loaded_clustered_df)}\")"
    ]
   },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "id": "27",
-   "metadata": {
-    "editable": true,
-    "slideshow": {
-     "slide_type": ""
-    },
-    "tags": []
-   },
-   "outputs": [],
-   "source": [
-    "# --- CORE SUPPORT FUNCTIONS ---\n",
-    "import pandas as pd\n",
-    "import numpy as np\n",
-    "from pathlib import Path\n",
-    "import os\n",
-    "import platform\n",
-    "import subprocess\n",
-    "import ipywidgets as widgets\n",
-    "from IPython.display import display\n",
-    "\n",
-    "# Surgical port of _open_folder from FAQuilizer, necessary for the widget to work\n",
-    "def _open_folder(path_str: str = \".\"):\n",
-    "    \"\"\"Opens the specified folder in the system's default file explorer.\"\"\"\n",
-    "    folder_path = Path(path_str).resolve()\n",
-    "    \n",
-    "    if not folder_path.exists() or not folder_path.is_dir():\n",
-    "        print(f\"❌ Error: Path is not a valid directory: {folder_path}\")\n",
-    "        return\n",
-    "\n",
-    "    system = platform.system()\n",
-    "    try:\n",
-    "        if system == \"Windows\":\n",
-    "            os.startfile(folder_path)\n",
-    "        elif system == \"Darwin\":  # macOS\n",
-    "            subprocess.run([\"open\", folder_path])\n",
-    "        else:  # Linux (xdg-open covers most desktop environments)\n",
-    "            subprocess.run([\"xdg-open\", folder_path])\n",
-    "    except Exception as e:\n",
-    "        print(f\"❌ Failed to open folder. Error: {e}\")\n",
-    "\n",
-    "# This utility must be defined for normalize_and_score to work\n",
-    "def safe_normalize(series):\n",
-    "    \"\"\" Normalize the series safely to avoid divide by zero and handle NaN values. \"\"\"\n",
-    "    min_val = series.min()\n",
-    "    max_val = series.max()\n",
-    "    range_val = max_val - min_val\n",
-    "    if range_val == 0:\n",
-    "        # Avoid division by zero by returning zero array if no range\n",
-    "        return np.zeros_like(series)\n",
-    "    else:\n",
-    "        # Normalize and fill NaN values that might result from empty/NaN series\n",
-    "        return (series - min_val).div(range_val).fillna(0)\n",
-    "\n",
-    "# Surgical port of bf.reorder_columns\n",
-    "def reorder_columns_surgical(df, priority_column, after_column):\n",
-    "    if priority_column in df.columns:\n",
-    "        columns = list(df.columns.drop(priority_column))\n",
-    "        # Handle cases where the after_column may have been dropped earlier\n",
-    "        if after_column not in columns:\n",
-    "            print(f\"⚠️ Reorder Error: Target column '{after_column}' not found. Skipping reorder of '{priority_column}'.\")\n",
-    "            return df\n",
-    "            \n",
-    "        after_column_index = columns.index(after_column)\n",
-    "        columns.insert(after_column_index + 1, priority_column)\n",
-    "        df = df[columns]\n",
-    "    else:\n",
-    "        print(f\"⚠️ Reorder Error: Column '{priority_column}' not found in DataFrame.\")\n",
-    "    return df\n",
-    "\n",
-    "# Surgical port of bf.normalize_and_score - WITH CRITICAL FIX\n",
-    "def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_col, reorder):\n",
-    "    \n",
-    "    # Rename original column fields to match expected names in the dataframe\n",
-    "    if 'internal_page_rank.raw' in df.columns:\n",
-    "        df = df.rename(columns={'internal_page_rank.raw': 'Raw Internal Pagerank'}, inplace=False)\n",
-    "    \n",
-    "    # --- CRITICAL FIX FOR KEYERROR / TRAILING SLASHES ---\n",
-    "    # The lookup key (e.g., 'nixos.org') must be matched against the DataFrame column (e.g., 'nixos.org/').\n",
-    "    # We clean both for comparison to find the unique canonical key, but use the original column name.\n",
-    "    \n",
-    "    # Clean the lookup domain (assuming the input `registered_domain` might be missing the slash)\n",
-    "    clean_lookup_key = registered_domain.rstrip('/')\n",
-    "    target_col = None\n",
-    "    \n",
-    "    for col in df.columns:\n",
-    "        # Find the column whose stripped name matches the stripped lookup key.\n",
-    "        if col.rstrip('/') == clean_lookup_key:\n",
-    "            target_col = col\n",
-    "            break\n",
-    "            \n",
-    "    if target_col is None:\n",
-    "        raise KeyError(f\"Could not find client domain column for '{registered_domain}' in DataFrame. Available columns: {df.columns.tolist()}\")\n",
-    "    # --- END CRITICAL FIX ---\n",
-    "    \n",
-    "    # Normalize metrics that are always included\n",
-    "    df['Normalized Search Volume'] = safe_normalize(df['Search Volume'])\n",
-    "    df['Normalized Search Position'] = safe_normalize(df[target_col]) # <-- USES THE FOUND, CANONICAL COLUMN NAME\n",
-    "    df['Normalized Keyword Difficulty'] = safe_normalize(df['Keyword Difficulty'])\n",
-    "    df['Normalized CPC'] = safe_normalize(df['CPC'])\n",
-    "\n",
-    "    # Always include CPC and Keyword Difficulty in the combined score\n",
-    "    combined_score = df['Normalized CPC'] - df['Normalized Keyword Difficulty']\n",
-    "\n",
-    "    if has_botify_data:\n",
-    "        # Normalize additional Botify metrics if available\n",
-    "        if 'Raw Internal Pagerank' in df.columns:\n",
-    "            df['Normalized Raw Internal Pagerank'] = safe_normalize(df['Raw Internal Pagerank'])\n",
-    "        else:\n",
-    "            df['Normalized Raw Internal Pagerank'] = 0\n",
-    "\n",
-    "        if \"No. of Missed Clicks excluding anonymized queries\" in df.columns:\n",
-    "            df['Normalized Missed Clicks'] = safe_normalize(df[\"No. of Missed Clicks excluding anonymized queries\"])\n",
-    "            combined_score += df['Normalized Missed Clicks']\n",
-    "        else:\n",
-    "            df['Normalized Missed Clicks'] = 0\n",
-    "\n",
-    "        # Add Botify metrics to the combined score\n",
-    "        combined_score += (-1 * df['Normalized Raw Internal Pagerank'] +\n",
-    "                           df['Normalized Search Volume'] +\n",
-    "                           df['Normalized Search Position'])\n",
-    "\n",
-    "    # Apply the combined score to the DataFrame\n",
-    "    df['Combined Score'] = combined_score\n",
-    "\n",
-    "    if reorder:\n",
-    "        # Reorder columns if required (using the surgically ported reorder function)\n",
-    "        df = reorder_columns_surgical(df, \"CPC\", after_col)\n",
-    "        df = reorder_columns_surgical(df, \"Keyword Difficulty\", \"CPC\")\n",
-    "        if has_botify_data:\n",
-    "            df = reorder_columns_surgical(df, \"Internal Pagerank\", \"Keyword Difficulty\")\n",
-    "            df = reorder_columns_surgical(df, \"No. of Unique Inlinks\", \"Internal Pagerank\")\n",
-    "            if \"No. of Missed Clicks excluding anonymized queries\" in df.columns:\n",
-    "                df = reorder_columns_surgical(df, \"No. of Missed Clicks excluding anonymized queries\", \"No. of Unique Inlinks\")\n",
-    "\n",
-    "    return df"
-   ]
-  },
   {
    "cell_type": "code",
    "execution_count": null,
(nix) 
[mike@nixos:~/repos/pipulate/Notebooks]$

(nix) 
[mike@nixos:~/repos/pipulate/Notebooks]$ git --no-pager diff --no-textconv
diff --git a/Notebooks/gap_analyzer_sauce.py b/Notebooks/gap_analyzer_sauce.py
index b878905b..810a877e 100644
--- a/Notebooks/gap_analyzer_sauce.py
+++ b/Notebooks/gap_analyzer_sauce.py
@@ -42,6 +42,12 @@ import platform
 import subprocess
 import ipywidgets as widgets
 from IPython.display import display
+import xlsxwriter
+
+# (Keep all previously added functions, including _open_folder, 
+#  safe_normalize, reorder_columns_surgical, normalize_and_score_surgical)
+# ...
+
 
 import nltk
 
@@ -1892,3 +1898,119 @@ def normalize_and_score_surgical(df, registered_domain, has_botify_data, after_c
                 df = reorder_columns_surgical(df, "No. of Missed Clicks excluding anonymized queries", "No. of Unique Inlinks")
 
     return df
+
+
+def create_deliverables_excel_and_button(job: str, df: pd.DataFrame, client_domain_from_keys: str, has_botify: bool):
+    """
+    Creates the deliverables directory, writes the first "Gap Analysis" tab
+    to the Excel file, creates the "Open Folder" button, and stores
+    key file paths in pip state.
+
+    Args:
+        job (str): The current Pipulate job ID.
+        df (pd.DataFrame): The final clustered/arranged DataFrame.
+        client_domain_from_keys (str): The client's domain from the keys module.
+        has_botify (bool): Flag indicating if Botify data is present.
+
+    Returns:
+        tuple: (button, xl_file, loop_list)
+               The ipywidget Button, the Path object for the Excel file,
+               and the list of sheet names (loop_list).
+    """
+    print("Writing first pass of Excel file...")
+
+    # --- CORE LOGIC (Moved from Notebook) ---
+    try:
+        # --- FIX: Re-derive missing variables ---
+        # 1. Get semrush_lookup
+        semrush_lookup = _extract_registered_domain(client_domain_from_keys)
+
+        # 2. Get competitors list from pip state
+        competitors_dict_json = pip.get(job, 'competitors_dict_json', '{}')
+        competitors_dict = json.loads(competitors_dict_json)
+        # We need the *column names* from the df, which are the *values* in cdict
+        competitors = list(competitors_dict.values())
+        if not competitors:
+             # Fallback: try to infer from df columns (less reliable)
+             print("  ⚠️ Warning: competitors_dict_json was empty. Inferring competitors from DataFrame columns.")
+             # This is a bit fragile, but a necessary fallback
+             non_metric_cols = set(df.select_dtypes(exclude=[np.number]).columns)
+             competitors = [col for col in df.columns if '/' in col or col == semrush_lookup or '.com' in col]
+
+
+        # 3. Find the canonical client column name (TARGET_COMPETITOR_COL)
+        clean_lookup_key = semrush_lookup.rstrip('/')
+        TARGET_COMPETITOR_COL = None
+        for col in df.columns: # Use the passed 'df'
+            if col.rstrip('/') == clean_lookup_key:
+                TARGET_COMPETITOR_COL = col
+                break
+
+        if TARGET_COMPETITOR_COL is None:
+             # This will cause normalize_and_score_surgical to fail, but we must try.
+             print(f"❌ CRITICAL ERROR: Could not find canonical column for '{semrush_lookup}' in DataFrame.")
+             # We'll let it fail in normalize_and_score_surgical for a clearer traceback.
+             TARGET_COMPETITOR_COL = semrush_lookup # Use the base name as a last resort
+        # --- END FIX ---
+
+
+        # --- 1. DEFINE SECURE OUTPUT PATHS ---
+        deliverables_dir = Path("deliverables") / job
+        deliverables_dir.mkdir(parents=True, exist_ok=True)
+
+        xl_filename = f"{semrush_lookup.replace('.', '_').rstrip('_')}_GAPalyzer_{job}_V1.xlsx"
+        xl_file = deliverables_dir / xl_filename
+
+        # --- 2. EXECUTE CORE LOGIC ---
+        print(f"  - Writing 'Gap Analysis' tab to {xl_file.name}...")
+        loop_list = ["Gap Analysis"] # This is needed by the next cell
+        last_competitor = competitors[-1] if competitors else None # Handle empty list
+
+        # Apply the normalization/scoring logic
+        # Pass the re-derived semrush_lookup and the canonical TARGET_COMPETITOR_COL
+        # NOTE: normalize_and_score_surgical finds its *own* target_col, so we just need semrush_lookup
+        df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)
+
+        # Save Initial Excel Sheet
+        arg_dict = {'options': {'strings_to_urls': False}}
+        try:
+            with pd.ExcelWriter(xl_file, engine="xlsxwriter", engine_kwargs=arg_dict, mode='w') as writer:
+                df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)
+            print("  ✅ 'Gap Analysis' tab written (Unformatted Pass 1).")
+        except Exception as e:
+            print(f"  ❌ Error writing Excel file: {e}")
+
+        # --- 3. CREATE SECURE EGRESS BUTTON ---
+        button = widgets.Button(
+            description=f"📂 Open Deliverables Folder ({job})",
+            tooltip=f"Open {deliverables_dir.resolve()}",
+            button_style='success'
+        )
+        
+        # Define the on_click handler that calls our private helper
+        def on_open_folder_click(b):
+            _open_folder(str(deliverables_dir))
+            
+        button.on_click(on_open_folder_click)
+
+        # --- OUTPUT (to pip state) ---
+        pip.set(job, 'final_xl_file', str(xl_file))
+        pip.set(job, 'deliverables_folder', str(deliverables_dir))
+        # --- FIX: Serialize lists to JSON strings before storing ---
+        pip.set(job, 'loop_list', json.dumps(loop_list)) # Store loop_list for the next step
+        # Store competitors and target col for the *next* cell (the formatting one)
+        pip.set(job, 'competitors_list', json.dumps(competitors)) 
+        # --- END FIX ---
+        pip.set(job, 'semrush_lookup', semrush_lookup) # The clean domain
+        pip.set(job, 'target_competitor_col', TARGET_COMPETITOR_COL) # The canonical column name
+        print(f"💾 Stored final Excel path, folder, and competitor info in pip state.")
+        # ---------------------------
+
+        # --- RETURN VALUE ---
+        # Return values needed for notebook display AND next cell
+        return button, xl_file, loop_list, competitors, semrush_lookup, TARGET_COMPETITOR_COL, has_botify
+
+    except Exception as e:
+        print(f"❌ An error occurred during Excel creation: {e}")
+        # Return dummy values to avoid breaking the notebook flow
+        return widgets.Button(description=f"Error: {e}", disabled=True), None, [], [], None, None, False
diff --git a/assets/nbs/GAPalyzer.ipynb b/assets/nbs/GAPalyzer.ipynb
index a3eaabf5..93a83444 100644
--- a/assets/nbs/GAPalyzer.ipynb
+++ b/assets/nbs/GAPalyzer.ipynb
@@ -261,11 +261,6 @@
    },
    "outputs": [],
    "source": [
-    "# %% editable=true slideshow={\"slide_type\": \"\"}\n",
-    "from IPython.display import display\n",
-    "import gap_analyzer_sauce # Ensure module is imported\n",
-    "import keys # Ensure keys is imported if not already\n",
-    "\n",
     "# This function now handles:\n",
     "# 1. Pivoting df2 by Keyword/Domain.\n",
     "# 2. Calculating Competitors Positioning.\n",
@@ -307,12 +302,6 @@
    },
    "outputs": [],
    "source": [
-    "# %% editable=true slideshow={\"slide_type\": \"\"}\n",
-    "import gap_analyzer_sauce # Ensure module is imported\n",
-    "import nest_asyncio # Keep nest_asyncio import if running directly in notebook (though sauce handles it too)\n",
-    "nest_asyncio.apply() # Apply it just in case for notebook context\n",
-    "\n",
-    "\n",
     "# This function now handles:\n",
     "# 1. Loading competitors_df from pip state.\n",
     "# 2. Checking for and fetching missing homepage titles asynchronously.\n",
@@ -398,9 +387,6 @@
    },
    "outputs": [],
    "source": [
-    "from IPython.display import display\n",
-    "import gap_analyzer_sauce # Ensure module is imported\n",
-    "\n",
     "# This function now handles:\n",
     "# 1. Merging pivot_df and agg_df.\n",
     "# 2. Reading the filter keyword list from the CSV file.\n",
@@ -546,12 +532,6 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "# %% editable=true slideshow={\"slide_type\": \"\"}\n",
-    "import pandas as pd\n",
-    "from IPython.display import display\n",
-    "import gap_analyzer_sauce # Ensure module is imported\n",
-    "from io import StringIO # Keep for optional verification\n",
-    "\n",
     "# This function now handles:\n",
     "# 1. Iterating through volume cutoffs to find the best fit under ROW_LIMIT.\n",
     "# 2. Handling edge cases (e.g., if filtering removes all rows).\n",
@@ -587,15 +567,6 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "import pandas as pd # Keep for display\n",
-    "from IPython.display import display\n",
-    "import gap_analyzer_sauce # Ensure module is imported\n",
-    "import itertools # Keep for any potential future use in cell\n",
-    "import json # Keep for any potential future use in cell\n",
-    "from pathlib import Path # Keep for any potential future use in cell\n",
-    "import re # Keep for any potential future use in cell\n",
-    "from io import StringIO # Keep for optional verification\n",
-    "\n",
     "# This one function now handles the entire clustering and finalization process:\n",
     "# 1. Loads/tests clustering parameters from a JSON cache file.\n",
     "# 2. Runs iterative ML clustering (TF-IDF, SVD, k-means) to find the best fit.\n",
@@ -632,65 +603,31 @@
    },
    "outputs": [],
    "source": [
-    "import pandas as pd\n",
-    "import xlsxwriter\n",
-    "import itertools\n",
-    "from pathlib import Path\n",
-    "from IPython.display import display\n",
-    "import os\n",
-    "import platform\n",
-    "import subprocess\n",
-    "import ipywidgets as widgets\n",
-    "# NOTE: This cell assumes 'job', 'df', 'competitors', 'semrush_lookup', 'has_botify' are defined.\n",
-    "\n",
-    "# --- 1. DEFINE SECURE OUTPUT PATHS ---\n",
-    "# Create the secure, client-specific deliverables folder: Notebooks/deliverables/{job}/\n",
-    "deliverables_dir = Path(\"deliverables\") / job\n",
-    "deliverables_dir.mkdir(parents=True, exist_ok=True)\n",
-    "\n",
-    "# Define the final Excel file path (using a standardized, clean name for the file)\n",
-    "# We ensure the filename is clean by stripping the trailing slash/underscore that might be left by SEMRush input.\n",
-    "xl_filename = f\"{semrush_lookup.replace('.', '_').rstrip('_')}_GAPalyzer_{job}_V1.xlsx\"\n",
-    "xl_file = deliverables_dir / xl_filename\n",
-    "\n",
-    "# --- 2. EXECUTE CORE LOGIC ---\n",
-    "print(f\"- Writing Gap Analysis tab to {xl_file.name} (first pass)...\")\n",
-    "\n",
-    "# Initialize Loop List (verbatim)\n",
-    "loop_list = [\"Gap Analysis\"]\n",
-    "last_competitor = competitors[-1]\n",
-    "\n",
-    "# Apply the normalization and scoring logic\n",
-    "# The target_col lookup inside this function will now succeed.\n",
-    "df_tab = normalize_and_score_surgical(df.copy(), semrush_lookup, has_botify, last_competitor, False)\n",
-    "\n",
-    "# Save Initial Excel Sheet with xlsxwriter (Engine Kwargs included verbatim)\n",
-    "arg_dict = {'options': {'strings_to_urls': False}}\n",
-    "try:\n",
-    "    with pd.ExcelWriter(xl_file, engine=\"xlsxwriter\", engine_kwargs=arg_dict, mode='w') as writer:\n",
-    "        # NOTE: Using df_tab here, which has the Normalized columns\n",
-    "        df_tab.to_excel(writer, sheet_name='Gap Analysis', index=False)\n",
-    "    \n",
-    "    print(\"✅ Gap Analysis tab written (Unformatted Pass 1).\")\n",
-    "    \n",
-    "except Exception as e:\n",
-    "    print(f\"❌ Error writing Excel file: {e}\")\n",
-    "    \n",
-    "# --- 3. DISPLAY SECURE EGRESS BUTTON ---\n",
-    "\n",
-    "# The button description clearly indicates the file and the security of the folder.\n",
-    "button = widgets.Button(\n",
-    "    description=f\"📂 Open Deliverables Folder ({job})\",\n",
-    "    tooltip=f\"Open {deliverables_dir.resolve()}\",\n",
-    "    button_style='success'\n",
+    "# This function now handles:\n",
+    "# 1. Creating the 'deliverables' directory.\n",
+    "# 2. Defining the final Excel file path.\n",
+    "# 3. Normalizing and scoring the data for the first \"Gap Analysis\" tab.\n",
+    "# 4. Writing this first tab to the Excel file.\n",
+    "# 5. Creating the \"Open Deliverables Folder\" button and its click handler.\n",
+    "# 6. Storing 'final_xl_file', 'deliverables_folder', and 'loop_list' in pip state.\n",
+    "# 7. Returning the button, xl_file path, and loop_list.\n",
+    "\n",
+    "# It receives 'df', 'competitors', 'semrush_lookup', and 'has_botify' from previous cells.\n",
+    "button, xl_file, loop_list = gap_analyzer_sauce.create_deliverables_excel_and_button(\n",
+    "    job,\n",
+    "    df,\n",
+    "    competitors,\n",
+    "    keys.client_domain, # Use keys.client_domain for semrush_lookup\n",
+    "    has_botify\n",
     ")\n",
-    "# Use a lambda function to call the portable _open_folder function on click\n",
-    "button.on_click(lambda b: _open_folder(str(deliverables_dir)))\n",
+    "\n",
+    "# Display the button\n",
     "display(button)\n",
     "\n",
-    "# Store final file path in pipeline\n",
-    "pip.set(job, 'final_xl_file', str(xl_file))\n",
-    "pip.set(job, 'deliverables_folder', str(deliverables_dir))"
+    "# Optional verification\n",
+    "# print(\"\\nVerification:\")\n",
+    "# print(f\"  Final XL File stored: {pip.get(job, 'final_xl_file')}\")\n",
+    "# print(f\"  Loop List stored: {pip.get(job, 'loop_list')}\")"
    ]
   },
   {
(nix) 
[mike@nixos:~/repos/pipulate/Notebooks]$