""" Struggle-Only JTBD Analysis (Semantic SSPS Version) --------------------------------------------------- Ce face scriptul: 1. Încarcă: - fișierul de keywords Google (competitor) - fișierul JTBD (cu coloana `struggle`) 2. Construiește: - SSPS (Struggle Problem Score) = semantic friction + failure/effort terms - CPCS_St (Competitor Problem Coverage Score - Struggle only) - struggle_gap = SSPS × (1 - CPCS_St) - intent (unaware / pain-aware / problem-aware / solution-aware) 3. Salvează: - struggle_semantic_output.csv (toate JTBD-urile cu scoruri pe struggle) - top_struggle_triggers_semantic.csv (top 20 struggles cu gap cel mai mare) """ import pandas as pd import numpy as np from io import StringIO from sklearn.feature_extraction.text import TfidfVectorizer from sklearn.metrics.pairwise import cosine_similarity # ========================================================= # 1. Universal Anchors for Struggle (Domain-Agnostic) # ========================================================= STRUGGLE_FRICTION_ANCHORS = [ "trying to do something but failing", "cannot complete the task", "hard to correct errors", "unclear why the method fails", "requires too much manual work", "difficult to achieve consistency", "takes too much effort to fix things", "steps break when they are repeated", "cannot maintain the same quality over time", "it keeps breaking in different ways" ] STRUGGLE_NEUTRAL_ANCHORS = [ "simple task with no issues", "routine work that just runs", "clear steps that are easy to follow", "things work as expected", "consistent and predictable process" ] # ========================================================= # 2. Explicit Lexicons (Complementary) # ========================================================= FAILURE_TERMS = { "fails", "breaking", "breaks", "crashes", "timeouts", "loops", "inconsistent", "unreliable", "error", "errors", "bug", "bugs" } EFFORT_TERMS = { "try", "trying", "tried", "fix", "fixing", "fixed", "redo", "redoing", "rework", "reworking", "manual", "manually", "overhead", "time-consuming", "frustrating", "frustration" } TECH_TERMS = { "framework", "platform", "llm", "agent", "tool", "workflow", "pipeline", "api", "sdk", "model", "orchestration" } # ========================================================= # 3. Data Loaders # ========================================================= def load_google_keywords(path: str) -> pd.DataFrame: """ Încarcă fișierul exportat din Google Keyword Planner (UTF-16). Ne interesează în principal coloana 'Keyword' -> 'keyword'. """ raw = pd.read_csv(path, encoding="utf-16") text = "\n".join(raw.iloc[1:, 0].astype(str)) df = pd.read_csv(StringIO(text), sep="\t") if "Keyword" not in df.columns: raise ValueError("Nu am găsit coloana 'Keyword' în fișierul Google Ads.") df = df.rename(columns={"Keyword": "keyword"}) df["keyword"] = df["keyword"].astype(str).str.lower().fillna("") return df def load_jtbd(path: str) -> pd.DataFrame: """ Încarcă fișierul JTBD. Se așteaptă o coloană 'struggle' (descrierea efortului/durerii). """ df = pd.read_csv(path) df["jtbd_id"] = df.index if "struggle" not in df.columns: df["struggle"] = "" return df # ========================================================= # 4. Struggle Text # ========================================================= def normalize_text(x): return x.lower().strip() if isinstance(x, str) else "" def extract_struggle(df: pd.DataFrame) -> pd.DataFrame: df["struggle_text"] = df["struggle"].astype(str).apply(normalize_text) return df # ========================================================= # 5. TF-IDF + Centroids (Struggle friction vs neutral) # ========================================================= def prepare_vectorizer_and_centroids_struggle(jtbd_df: pd.DataFrame): """ Construiește un vectorizator TF-IDF pe: - toate struggle_text - STRUGGLE_FRICTION_ANCHORS - STRUGGLE_NEUTRAL_ANCHORS Calculează centroidul semantic al fricțiunii de struggle și al unui struggle "normal" (fără probleme). """ corpus = ( jtbd_df["struggle_text"].tolist() + STRUGGLE_FRICTION_ANCHORS + STRUGGLE_NEUTRAL_ANCHORS ) vectorizer = TfidfVectorizer( ngram_range=(1, 2), min_df=1, max_df=0.95, ) X = vectorizer.fit_transform(corpus) N = len(jtbd_df) F = len(STRUGGLE_FRICTION_ANCHORS) friction_vecs = X[N: N + F] neutral_vecs = X[N + F:] # transformăm în ndarrays 2D compatibile cu cosine_similarity friction_centroid = np.asarray(friction_vecs.mean(axis=0)).reshape(1, -1) neutral_centroid = np.asarray(neutral_vecs.mean(axis=0)).reshape(1, -1) return vectorizer, friction_centroid, neutral_centroid # ========================================================= # 6. Semantic SSPS + Explicit Terms # ========================================================= def compute_semantic_struggle(text: str, vectorizer: TfidfVectorizer, friction_centroid: np.ndarray, neutral_centroid: np.ndarray) -> float: """ semantic_struggle = sim(text, friction_centroid) - sim(text, neutral_centroid) """ if not isinstance(text, str) or not text.strip(): return 0.0 vec = vectorizer.transform([text]) friction_sim = cosine_similarity(vec, friction_centroid)[0][0] neutral_sim = cosine_similarity(vec, neutral_centroid)[0][0] return float(friction_sim - neutral_sim) def compute_ssps(row, vectorizer: TfidfVectorizer, friction_centroid: np.ndarray, neutral_centroid: np.ndarray) -> float: """ SSPS (Struggle Problem Score) final: 1.75 × semantic_struggle + 3 × (# failure_terms) + 2 × (# effort_terms) - 1 × (# tech_terms) + 0.03 × lungime_text_cuvinte """ text = row.get("struggle_text", "") if not isinstance(text, str): text = "" # componentă semantică semantic = compute_semantic_struggle(text, vectorizer, friction_centroid, neutral_centroid) # componentă explicită lexicală explicit = ( 3 * sum(t in text for t in FAILURE_TERMS) + 2 * sum(t in text for t in EFFORT_TERMS) - 1 * sum(t in text for t in TECH_TERMS) + 0.03 * len(text.split()) ) return 1.75 * semantic + explicit # ========================================================= # 7. Competitor Coverage (Struggle Only) # ========================================================= def compute_cpcs_struggle(X_struggle, X_kw) -> np.ndarray: """ CPCS_St = media similarităților cosine între fiecare struggle_text și TOATE keyword-urile competitorului. """ if X_kw.shape[0] == 0: return np.zeros(X_struggle.shape[0], dtype=float) sim = cosine_similarity(X_struggle, X_kw) return np.array([float(sim[i].mean()) for i in range(sim.shape[0])]) # ========================================================= # 8. Intent Prediction from Struggle # ========================================================= def predict_intent_from_struggle(text: str) -> str: if not isinstance(text, str): text = "" tech_hits = sum(t in text for t in TECH_TERMS) fail_hits = sum(t in text for t in FAILURE_TERMS) eff_hits = sum(t in text for t in EFFORT_TERMS) # similar logic, dar în ordinea relevanței if tech_hits >= 2: return "solution-aware" if fail_hits >= 1: return "problem-aware" if eff_hits >= 1: return "pain-aware" return "unaware" # ========================================================= # 9. Main Pipeline # ========================================================= def main(): KW_PATH = "Keyword Stats 2025-12-10 at 19_41_15.csv" JTBD_PATH = "all_code_agent_posts_comments_jtbd_comments__jtbd_with_job_clusters.csv" print("Loading data...") kw_df = load_google_keywords(KW_PATH) jtbd_df = load_jtbd(JTBD_PATH) print("Extracting struggle text...") jtbd_df = extract_struggle(jtbd_df) print("Preparing vectorizer and semantic centroids for struggle...") vectorizer, friction_centroid, neutral_centroid = prepare_vectorizer_and_centroids_struggle(jtbd_df) print("Vectorizing struggles and keywords...") X_struggle = vectorizer.transform(jtbd_df["struggle_text"].tolist()) X_kw = vectorizer.transform(kw_df["keyword"].tolist()) print("Computing SSPS (Struggle Problem Score)...") jtbd_df["SSPS"] = jtbd_df.apply( lambda row: compute_ssps(row, vectorizer, friction_centroid, neutral_centroid), axis=1, ) print("Computing CPCS_St (Competitor Problem Coverage - Struggle)...") jtbd_df["CPCS_St"] = compute_cpcs_struggle(X_struggle, X_kw) print("Computing struggle_gap = SSPS × (1 - CPCS_St)...") jtbd_df["struggle_gap"] = jtbd_df["SSPS"] * (1.0 - jtbd_df["CPCS_St"]) print("Predicting intent from struggle text...") jtbd_df["struggle_intent"] = jtbd_df["struggle_text"].apply(predict_intent_from_struggle) print("Saving full struggle semantic output...") jtbd_df.to_csv("struggle_semantic_output.csv", index=False) print("Saving top 20 struggle triggers by struggle_gap...") top_triggers = jtbd_df.sort_values(by="struggle_gap", ascending=False).head(20) top_triggers.to_csv("top_struggle_triggers_semantic.csv", index=False) print("DONE — semantic SSPS pipeline executed.") if __name__ == "__main__": main()