Skip to content

bladata1990/pg-brand-analyst-agent

BranchesTags

Repository files navigation

P&G Brand Analyst Agent

An AI agent that answers brand performance questions in P&G analyst voice. Given a natural-language question like "Why did Gain lose share at Target?", it searches Atlan for business context, queries Databricks for sell-out data, runs root cause diagnostics, and produces an HTML executive report with BCA-classified findings.

Validated result: -3.3pp share decline (29.7% → 26.4%), BCA_Distribution primary (week-20 planogram reset), BCA_Competitive secondary (passive Tide absorption). Independent judge score: 97/100.

Architecture

User question
     │
     ▼
┌─────────────────────────────────────┐
│  brand_analyst_agent.py             │
│  Anthropic SDK tool-use loop        │
│  Model: claude-opus-4-7             │
│  4 tools bound                      │
└──────────────┬──────────────────────┘
               │ tool calls
    ┌──────────┼───────────────────────────┐
    ▼          ▼           ▼               ▼
search_atlan  analyze_    diagnose_       generate_
              market_     root_cause      report
              share
    │          │           │               │
    ▼          ▼           ▼               ▼
atlan_     market_share_ root_cause_    report_
client.py  skill.py      skill.py       skill.py
    │          │           │
    ▼          ▼           ▼
Atlan      Databricks   Databricks
REST API   default.aos  default.ddm
(ces.atlan.com)  (AOS)        (DDM)

Four layers:

  1. Agent (orchestrator) — agent/brand_analyst_agent.py. Anthropic SDK messages.create loop. Runs until stop_reason == "end_turn". System prompt ~1.5K tokens. Binds 4 tools. Passes full message history so the model can reason across multiple tool call rounds.

  2. Skills (thinking layer) — skills/market_share_skill.py, root_cause_skill.py, report_skill.py. Each skill fetches its own Atlan context at runtime, runs inner LLM calls for SQL generation and result interpretation, and returns a structured dict. Skills are stateless Python functions — no classes, no shared state.

  3. Tools (execution layer) — thin dispatch functions in brand_analyst_agent.py::execute_tool. Each tool calls exactly one skill or one REST endpoint. Tools serialize output as JSON strings back into the message history.

  4. Backends — Atlan REST/MCP API (glossary terms), Databricks SQL connector (default.aos, default.ddm), Anthropic API (inner LLM calls inside skills), file system (output/report.html).

Project Structure

PG-Agent/
├── agent/
│   └── brand_analyst_agent.py      # Orchestrator: SDK loop + 4 tool definitions
├── skills/
│   ├── atlan_client.py             # Atlan MCP REST client, glossary fetch helpers
│   ├── databricks_client.py        # Databricks SQL connector + entity resolution
│   ├── llm_client.py               # Inner LLM calls (complete, extract_sql, extract_json)
│   ├── market_share_skill.py       # AOS share analysis
│   ├── root_cause_skill.py         # DDM root cause diagnosis + BCA mapping
│   └── report_skill.py             # HTML report rendering
├── atlan/
│   └── populate_atlan.py           # Push 40 glossary terms to Atlan (one-time setup)
├── data/
│   ├── generate_synthetic_data.py  # Synthetic Fabric Care AOS + DDM data
│   ├── load_to_databricks.py       # Load CSVs into default.aos / default.ddm
│   └── verify_load.py              # Spot-check loaded data
├── output/
│   ├── report.html                 # Generated HTML report (written by agent)
│   └── pg_brand_analyst_agent.excalidraw  # Architecture diagram
└── .env                            # Secrets (not in version control)

How It Works — Step by Step

Step 1: User asks a question

python agent/brand_analyst_agent.py "Why did Gain lose share at Target?"

The agent initializes a messages list with the question and enters the SDK loop.

Step 2: Orchestrator decides on tools

The model reads the system prompt (workflow rules, BCA categories, citation rules) and the tool definitions. It typically makes 4–6 tool calls across 2–3 loop iterations:

  1. search_atlan("BCA framework") — optional, to ground itself on category definitions
  2. analyze_market_share(brand="GAIN", market="Target Total Enterprise TA", ...) — always first
  3. diagnose_root_cause(brand="GAIN", ..., hypothesis="distribution declined at Target around week 20") — one per why-hypothesis
  4. generate_report(...) — final call, synthesizes findings

Step 3: market_share_skill runs

skills/market_share_skill.py::analyze_market_share:

  1. Calls describe_table("default.aos") — fetches live schema from Databricks
  2. Calls get_metric_definitions("AOS") — fetches AOS_Metrics category from Atlan glossary
  3. Calls resolve_entity_values(...) — resolves exact canonical strings (brand/market/category) from DB to prevent case-mismatch SQL failures
  4. Calls inner LLM (llm_client.complete) with schema + metric definitions + canonical values → generates Databricks SQL
  5. Executes SQL, validates focal brand appears in results (retries once with correction prompt if not)
  6. Builds data_summary: all 52 focal-brand weekly rows + competitor average shares — no truncation
  7. Calls inner LLM again with structured data_summary → returns interpretation JSON with avg_share, share_change_pp, signal_start_week, why_hypotheses, summary

Step 4: root_cause_skill runs

skills/root_cause_skill.py::diagnose_root_cause:

  1. Calls describe_table("default.ddm") — DDM trade panel schema
  2. Calls get_metric_definitions("DDM") — DDM_Metrics from Atlan
  3. Calls get_bca_framework() — fetches all BCA_* terms from Atlan glossary
  4. Uses signal_start_week from market share context to split data: baseline (before signal week) vs signal period (from signal week onward)
  5. Generates SQL via inner LLM that computes AVG for rate metrics, SUM for volumes, both periods in one query
  6. Calls inner LLM with DDM results + BCA definitions → classifies each driver into a BCA category, assigns severity
  7. Returns result dict including top_drivers, recommendations, summary, and bca_framework (the raw definitions fetched from Atlan)

The bca_framework key is a critical design decision — see the Design Decisions section.

Step 5: generate_report runs

skills/report_skill.py::generate_report:

  1. Fetches BCA framework from Atlan again for the reference table
  2. Renders an HTML report following P&G's SCQ structure (Situation, Complication, Question)
  3. Saves to output/report.html

Step 6: Orchestrator synthesizes

The model reads share findings, root cause diagnosis (including the BCA definitions in bca_framework), and writes the final narrative. It cites specific numbers, maps to BCA categories, and proposes prioritized actions.

Atlan Knowledge Layer

Atlan (ces.atlan.com) is the live knowledge layer, not static documentation. Definitions are fetched at query time and flow directly into SQL generation prompts and interpretation prompts. Changing a glossary term in Atlan changes agent behavior without touching code.

Glossary: Fabric_Care_Analytics

6 categories, 40 terms:

Category Purpose Example Terms
Markets Market hierarchy and retailer scope Target Total Enterprise TA, Walmart Total
Time_Periods Week numbering, rolling window rules Week, Rolling 13 Weeks
AOS_Metrics AOS column definitions and formulas Dollar Share, Dollar IYA, Dollar CYA, %ACV
DDM_Metrics DDM trade panel column definitions TDP, ACV Wtd Distribution, Feature & Display
Product_Hierarchy Brand and category hierarchy GAIN, TIDE, LAUNDRY CARE
Business_Change_Analysis BCA framework: 5 root cause categories BCA_Distribution, BCA_Promotion, BCA_Pricing, BCA_Assortment, BCA_Competitive

What terms contain:

  • Actual column names used in SQL (e.g., sales_lc_amt, sales_lc_iya, acv_pct)
  • Formula definitions (e.g., Dollar Share = SUM(sales_lc_amt) / total category sales * 100)
  • BCA decision rules (e.g., BCA_Competitive requires competitor IYA > 100; otherwise BCA_Distribution)
  • DPSM framework alignment for distribution metrics
  • Concrete diagnostic questions per BCA category

What terms do NOT contain: fabricated quantitative thresholds, implementation metadata, instruction-style language ("do NOT infer..."), or benchmarks not grounded in reference sources.

To populate or update the glossary:

python atlan/populate_atlan.py

Skills Reference

skills/market_share_skill.py

Function: analyze_market_share(brand, market, question, category, period_weeks) -> dict

Fetches AOS schema and metric definitions from Atlan, generates SQL via inner LLM, executes against default.aos, builds a structured data summary (no truncation — all focal-brand weekly rows), interprets via inner LLM.

Returns:

{
  "avg_share": 27.1,
  "avg_share_ya": 29.7,
  "share_change_pp": -2.6,
  "total_sales": 1234567.0,
  "sales_index_ya": 89,
  "signal_start_week": 20,
  "top_competitors": [{"brand": "TIDE", "share_change_pp": 2.1}],
  "why_hypotheses": ["distribution declined at Target around week 20"],
  "summary": "Gain lost 2.6pp share vs YA...",
  "brand": "GAIN",
  "market": "Target Total Enterprise TA"
}

SQL retry logic: If generated SQL returns rows but the focal brand is missing, retries once with an explicit correction prompt that states the brand filter bug and the bad SQL.

skills/root_cause_skill.py

Function: diagnose_root_cause(brand, market, hypothesis, market_share_context, category) -> dict

Fetches DDM schema, DDM metrics, and BCA framework from Atlan. Uses signal_start_week from market_share_context to split the time range into baseline vs signal. Generates SQL that returns both periods in a single query using CASE/AVG. Maps results to BCA categories via inner LLM.

Returns:

{
  "hypothesis_tested": "distribution declined at Target around week 20",
  "signal_period": "weeks 20-52",
  "top_drivers": [
    {
      "bca_category": "BCA_Distribution",
      "metric": "acv_pct",
      "finding": "ACV distribution dropped from 94.2% to 81.7%",
      "severity": "high",
      "pct_change": -13.3
    }
  ],
  "recommendations": [...],
  "summary": "...",
  "brand": "GAIN",
  "market": "Target Total Enterprise TA",
  "bca_framework": {
    "BCA_Distribution": "...",
    "BCA_Competitive": "competitor IYA > 100 required; without it, share transfer is BCA_Distribution",
    ...
  }
}

Note: bca_framework is included in the return dict. This is intentional — see Design Decisions.

skills/report_skill.py

Function: generate_report(question, brand, market, share_findings, root_causes, recommendations) -> str

Fetches BCA framework from Atlan for the reference table. Renders HTML following P&G's SCQ structure. The orchestrator writes the prose for share_findings, root_causes, and recommendations based on the structured skill outputs.

skills/atlan_client.py

Atlan REST/MCP client. Key functions:

  • search_any(query, size) — full-text search across glossary terms
  • get_metric_definitions(table) — returns all terms in AOS_Metrics or DDM_Metrics category
  • get_bca_framework() — returns all BCA_* terms as {name: description} dict
  • _get_glossary_qn() — resolves glossary qualifiedName (cached)
  • _get_category_qns() — resolves all category qualifiedNames (cached)

All Atlan calls go through _call_mcp(tool, args) which calls the Atlan MCP endpoint at https://ces.atlan.com/mcp and parses the SSE response.

skills/databricks_client.py

Databricks SQL connector. Key functions:

  • query(sql_text) -> list[dict] — executes SQL, returns list of row dicts
  • describe_table(table) -> str — returns human-readable schema string for LLM context
  • resolve_entity_values(table, brand, market, category) -> dict — looks up canonical case-exact string values from the DB using UPPER() LIKE '%...%' to prevent SQL failures from wrong casing

skills/llm_client.py

Shared inner LLM client for all skill reasoning calls (SQL generation, result interpretation, BCA mapping). Uses claude-opus-4-7.

  • complete(system, user, max_tokens) — single-turn completion
  • extract_sql(text) — strips markdown code fences, returns raw SQL
  • extract_json(text) — tries fenced JSON first, falls back to regex {...} extraction
  • validate_brand_in_rows(rows, brand, brand_col) — checks focal brand appears in query results

Setup

Prerequisites

  • Python 3.11+
  • Access to ces.atlan.com with API key
  • Databricks workspace with default.aos and default.ddm tables
  • Anthropic API key with access to claude-opus-4-7

Install dependencies

pip install anthropic databricks-sql-connector python-dotenv requests

Configure environment

Create .env in the project root:

ANTHROPIC_API_KEY=sk-ant-...
ATLAN_BASE_URL=https://ces.atlan.com
ATLAN_API_KEY=...
DATABRICKS_HOST=<workspace>.azuredatabricks.net
DATABRICKS_HTTP_PATH=/sql/1.0/warehouses/<warehouse-id>
DATABRICKS_TOKEN=dapi...

Load synthetic data (optional — for development/testing)

python data/generate_synthetic_data.py     # generates synthetic_aos.csv, synthetic_ddm.csv
python data/load_to_databricks.py          # loads into default.aos, default.ddm
python data/verify_load.py                 # spot-checks row counts and sample rows

The synthetic data covers 52 weeks, 5 brands (GAIN, TIDE, ALL, DOWNY, BOUNCE), 3 markets. Gain has a step-change distribution decline at week 20 embedded in the data.

Populate Atlan glossary (one-time)

python atlan/populate_atlan.py

This pushes 40 glossary terms across 6 categories into the Fabric_Care_Analytics glossary in Atlan. Re-running is safe — terms are upserted.

Running

python agent/brand_analyst_agent.py "Why did Gain lose share at Target?"

Or with a custom question:

python agent/brand_analyst_agent.py "What's driving Tide's share growth at Walmart over the last 13 weeks?"

The agent prints each tool call as it executes, including the generated SQL (first 300 characters). The final HTML report is written to output/report.html.

Expected tool call sequence for the Gain/Target question:

[Tool] analyze_market_share({brand: GAIN, market: Target...})
  [market_share_skill] Canonical values — brand='GAIN' market='Target Total Enterprise TA'
  [market_share_skill] Generated SQL (attempt 1): SELECT ...
[Tool] diagnose_root_cause({brand: GAIN, market: Target..., hypothesis: distribution declined...})
  [root_cause_skill] Canonical values — brand='GAIN' market='Target Total Enterprise TA'
  [root_cause_skill] Generated SQL (attempt 1): SELECT ...
[Tool] generate_report({question: ..., brand: GAIN, ...})
  [Report saved] /path/to/output/report.html

Key Design Decisions

1. Atlan as runtime knowledge, not config

The agent fetches glossary terms from Atlan on every question. This means:

  • BCA definitions, metric formulas, and market context flow into SQL prompts dynamically
  • Updating a term in Atlan changes agent behavior immediately, with no code changes
  • The glossary is the authoritative source for what metrics mean and how to compute them

This contrasts with embedding definitions in the system prompt or in Python constants — those require code changes to update and diverge from the source of truth over time.

2. bca_framework must reach the orchestrator

root_cause_skill fetches the BCA framework from Atlan and uses it for the internal diagnosis. Early versions returned only the diagnosis labels ("bca_category": "BCA_Competitive") to the orchestrator. The orchestrator had the label but not the definition, and hallucinated to fill the gap — inventing Tide promotional activity that did not exist in the data.

The fix is result["bca_framework"] = bca at the end of root_cause_skill. Now the orchestrator receives both the diagnosis and the actual definition of each BCA category. When writing the report narrative, it reads the real BCA_Competitive definition ("competitor IYA > 100 required; without it, share transfer is BCA_Distribution") and correctly classifies Tide's share gain as passive absorption rather than active competitive action.

This is a general principle: if the orchestrator needs to reason about a domain concept, it needs the concept definition — not just the conclusion derived from it.

Judge score before this fix: 87/100. After: 97/100.

3. data_summary, not raw rows

Early versions of market_share_skill passed raw query rows to the interpretation LLM, with a rows[:80] truncation. With 52 weeks × 5 brands = 260 rows, the truncation dropped most of the focal brand's data. The week-20 step-change signal was hidden in the truncated rows.

The fix: compute a structured data_summary before calling the interpretation LLM. This contains:

  • All 52 focal-brand weekly rows (share, YA share, sales)
  • Competitor average shares (aggregated, not weekly)

The interpretation LLM gets everything it needs for the focal brand analysis without token waste on competitor weekly detail.

4. resolve_entity_values prevents SQL failures

SQL generation LLMs frequently guess wrong casing or format for string values (GAIN vs Gain, TARGET TOTAL ENTERPRISE TA vs Target Total Enterprise TA). databricks_client.resolve_entity_values runs a UPPER() LIKE '%...%' lookup before SQL generation to retrieve the canonical database-exact string. The exact strings are then injected into the SQL generation prompt with an explicit instruction to use them verbatim.

5. SQL retry with correction prompt

If generated SQL executes without error but the focal brand does not appear in results, the skill retries once. The retry prompt includes the specific failure reason and the bad SQL. One retry is enough — if the model generated a brand filter accidentally on the first attempt, seeing the bad SQL and the explicit failure mode is sufficient to correct it.

6. Domain knowledge over prompt instructions

When the agent was hallucinating Tide promotional activity, the intuitive fix was to add "do not infer competitor promotions without data" to the system prompt. The better fix was to write BCA_Competitive correctly in Atlan with the decision rule that distinguishes passive from active competitive dynamics.

Prompt instructions are brittle — they can be overridden by confident model inferences, they accumulate, and they are invisible to the data team maintaining the glossary. Domain concepts in Atlan are auditable, versioned, and reusable across any agent that reads the glossary.

7. Glossary term hygiene

During development, early glossary terms contained:

  • Fabricated quantitative benchmarks ("significant change threshold: 2pp", "TDP 200-400 for major brands")
  • Implementation metadata inside business definitions (channel_type, hierarchy_rank, rolling=true)
  • Instruction-style language inside definitions ("INTERPRETATION RULE: do NOT infer...")

All of these were audited against three reference sources (the P&G pipeline document, the DDM JSON context file, and the pipeline system prompts) and removed. A fabricated benchmark flowing into an interpretation prompt produces a confidently wrong analysis. Zero fabricated claims remain in the current glossary.

Evaluation

The agent was evaluated using an independent LLM judge. The judge received:

  • The ground truth synthetic data generation rules (what the data was designed to show)
  • The agent's HTML report output
  • No access to the agent's code or prompts

Evaluation dimensions:

  1. Quantitative accuracy — share numbers, IYA, signal week
  2. Root cause classification — correct BCA category assignment
  3. Hallucination check — claims not supported by data
  4. Reasoning quality — logical chain from data to conclusion
  5. Actionability — are recommendations specific and implementable

Final score: 97/100

The 3-point deduction was for a minor framing issue in the recommendations section (slightly generic language for one action item). No hallucinated competitor claims, correct BCA_Distribution primary / BCA_Competitive secondary classification, accurate numbers throughout.

Next Steps

  • Multi-brand, multi-market queries — extend the orchestrator to decompose compound questions into parallel skill calls
  • Feedback loop to Atlan — when the agent produces a diagnosis, write a validated example back to the glossary as a case study term; future agents can use these as few-shot examples
  • Slack-native output — add a generate_slack_summary skill that produces Block Kit JSON alongside the HTML report
  • Broader BCA coverage — add BCA_Pricing and BCA_Assortment test cases to the synthetic data; current synthetic data primarily tests BCA_Distribution and BCA_Competitive
  • Confidence scoring — have the diagnosis skill return a confidence level based on the magnitude and consistency of the data signal; surface low-confidence diagnoses differently in the report
  • Multi-retailer comparison — "Is the Gain decline Target-specific or across all channels?" requires running analyze_market_share across multiple markets and comparing results

About

P&G Brand Analyst Agent — Anthropic SDK tool-use loop with Atlan knowledge layer and Databricks AOS/DDM data

Resources

Readme
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages