# ayandebnath88/product-growth-intelligence-copilot architecture

> Architecture diagram of the GitHub repository ayandebnath88/product-growth-intelligence-copilot, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-17). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/ayandebnath88/product-growth-intelligence-copilot
- Repository: https://github.com/ayandebnath88/product-growth-intelligence-copilot

## Overview

1. **Repository purpose**
   - This is a **business intelligence / product analytics pipeline**, not an application backend.
   - The architecture centers on turning raw relational business data into **SQLite-backed analytical datasets**, then surfacing results in **Tableau dashboards**.

2. **Primary data flow**
   - The likely flow is: **raw source data → SQLite schema → analytical SQL queries → dashboard-ready outputs → Tableau visualizations**.
   - The repo is organized around that pipeline rather than around runtime services or APIs.

3. **Data storage boundary**
   - The core persistence layer is **SQLite**, defined by `sql/schema.sql`.
   - The SQL files suggest a modeled local analytics database with separate business domains rather than a single monolithic query.

4. **Ingestion and preparation**
   - `scripts/load_data.py` is the ETL-style entry point for **loading or transforming data** into the database or intermediate files.
   - This is the main Python boundary in the repo and likely handles preprocessing with **Pandas** before data reaches SQL/Tableau.

5. **Database verification and QA**
   - `scripts/verifydb.py` indicates a **validation step** to check database integrity, row counts, or expected structures after load.
   - `sql/test.sql` reinforces that the project includes **sanity checks** for analytical correctness.

6. **Analytical SQL layer**
   - The `sql/` directory is the core analytics engine:
     - `revenue_analysis.sql`
     - `customer_analysis.sql`
     - `funnel_analysis.sql`
     - `cohort_analysis.sql`
     - `seller_analysis.sql`
     - `data_profiling.sql`
     - `agentic_insights.sql`
   - These scripts represent **domain-specific analytical marts / query sets** for KPIs, segmentation, funnel metrics, cohort behavior, and revenue breakdowns.

7. **Business domain separation**
   - The repository cleanly separates analysis into **revenue, customer, funnel, cohort, seller, and profiling** concerns.
   - That suggests independent query surfaces feeding different dashboard pages or stakeholder questions, rather than one generalized reporting model.

8. **Presentation layer**
   - `exports/Dashboard_Funnel.twb` and `exports/Dashboards.twb` are **Tableau workbook artifacts**.
   - Tableau is the final presentation boundary: it consumes the prepared datasets and renders the executive, funnel, and payment dashboards described in the README.

9. **Exported/intermediate assets**
   - `exports/tableau_exports.sql` suggests a bridge between SQL analytics and Tableau, likely providing **table-friendly extract queries or exported structures**.
   - This sits between the database and the Tableau workbooks as a handoff layer.

10. **Documentation and analytical framing**
   - `docs/` and `insights/` contain supporting analysis artifacts such as `data_dictionary.md`, `initial_findings.md`, and `project_scope.md`.
   - These files define the **semantic layer**: what each metric means, what questions are being answered, and what the business context is.

11. **AI-assisted insight layer**
   - `ai/insight_generator_prompt.md` and `sql/agentic_insights.sql` indicate an optional **LLM-assisted insight generation** path.
   - This is adjacent to the analytics pipeline, not the core storage or dashboarding path, and appears aimed at converting query results into narrative insights.

12. **External/runtime dependencies that matter**
   - The repo depends on **SQLite** for the analytical database, **Python/Pandas** for preprocessing, and **Tableau Public** for dashboard delivery.
   - Git/GitHub function as versioning and publishing infrastructure, but the runtime architecture is primarily a **local analytics workflow**.

13. **How to draw the architecture**
   - Model it as three main layers:
     - **Data preparation**: Python scripts + schema SQL
     - **Analytics computation**: domain SQL scripts and validation/tests
     - **Visualization/reporting**: Tableau workbooks and exported dashboard assets
   - Surround those with documentation/insight artifacts and an optional AI narrative layer.

14. **Key architectural takeaway**
   - This repo is a **repeatable analytics pipeline** with explicit boundaries between loading, modeling, querying, and dashboarding.
   - It is optimized for **executive reporting and business analysis**, not for transaction processing or user-facing application logic.

Main entry points: [`sql/schema.sql`](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/schema.sql) (Schema), [`exports/tableau_exports.sql`](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/tableau_exports.sql) (Exports), [`sql/revenue_analysis.sql`](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/revenue_analysis.sql) (Revenue), [`sql/customer_analysis.sql`](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/customer_analysis.sql) (Customers)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_prep["Data preparation"]
  node_load_data["Load data<br/>python etl<br/>[load_data.py]"]
  node_verifydb["Verify DB<br/>python qa<br/>[verifydb.py]"]
  node_schema_sql[("Schema<br/>[schema.sql]")]
end

subgraph group_analytics["Analytics SQL"]
  node_revenue_sql["Revenue<br/>analysis sql"]
  node_customer_sql["Customers<br/>analysis sql"]
  node_funnel_sql["Funnel<br/>analysis sql"]
  node_cohort_sql["Cohorts<br/>analysis sql"]
  node_seller_sql["Sellers<br/>analysis sql"]
  node_profiling_sql["Profiling<br/>analysis sql<br/>[data_profiling.sql]"]
  node_agentic_sql["AI insights<br/>analysis sql"]
  node_test_sql["Tests<br/>sql checks<br/>[test.sql]"]
end

subgraph group_reporting["Reporting"]
  node_tableau_exports["Exports<br/>tableau bridge"]
  node_dashboards_twb["Dashboards<br/>tableau workbook<br/>[Dashboards.twb]"]
  node_funnel_twb["Funnel book<br/>tableau workbook"]
end

subgraph group_context["Context"]
  node_data_dict["Data dict<br/>docs<br/>[data_dictionary.md]"]
  node_findings["Findings<br/>docs"]
  node_scope["Scope<br/>docs<br/>[project_scope.md]"]
  node_prompt["Insight prompt<br/>ai prompt"]
end

node_load_data -->|"populate"| node_schema_sql
node_verifydb -->|"validate"| node_schema_sql
node_schema_sql -->|"query base"| node_revenue_sql
node_schema_sql -->|"query base"| node_customer_sql
node_schema_sql -->|"query base"| node_funnel_sql
node_schema_sql -->|"query base"| node_cohort_sql
node_schema_sql -->|"query base"| node_seller_sql
node_schema_sql -->|"inspect"| node_profiling_sql
node_schema_sql -->|"summarize"| node_agentic_sql
node_revenue_sql -->|"shape output"| node_tableau_exports
node_customer_sql -->|"shape output"| node_tableau_exports
node_funnel_sql -->|"shape output"| node_tableau_exports
node_cohort_sql -->|"shape output"| node_tableau_exports
node_seller_sql -->|"shape output"| node_tableau_exports
node_tableau_exports -->|"feed"| node_dashboards_twb
node_tableau_exports -->|"feed"| node_funnel_twb
node_test_sql -->|"reinforce"| node_verifydb
node_data_dict -.->|"define"| node_revenue_sql
node_data_dict -.->|"define"| node_customer_sql
node_scope -.->|"frame"| node_findings
node_prompt -.->|"guide"| node_agentic_sql

click node_load_data "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/scripts/load_data.py"
click node_verifydb "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/scripts/verifydb.py"
click node_schema_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/schema.sql"
click node_revenue_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/revenue_analysis.sql"
click node_customer_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/customer_analysis.sql"
click node_funnel_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/funnel_analysis.sql"
click node_cohort_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/cohort_analysis.sql"
click node_seller_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/seller_analysis.sql"
click node_profiling_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/data_profiling.sql"
click node_agentic_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/agentic_insights.sql"
click node_test_sql "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/test.sql"
click node_tableau_exports "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/tableau_exports.sql"
click node_dashboards_twb "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/Dashboards.twb"
click node_funnel_twb "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/Dashboard_Funnel.twb"
click node_data_dict "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/docs/data_dictionary.md"
click node_findings "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/docs/initial_findings.md"
click node_scope "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/insights/project_scope.md"
click node_prompt "https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/ai/insight_generator_prompt.md"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_load_data,node_verifydb,node_schema_sql toneBlue
class node_revenue_sql,node_customer_sql,node_funnel_sql,node_cohort_sql,node_seller_sql,node_profiling_sql,node_agentic_sql,node_test_sql toneAmber
class node_tableau_exports,node_dashboards_twb,node_funnel_twb toneMint
class node_data_dict,node_findings,node_scope,node_prompt toneRose
```

## Components

### Data preparation

Load raw business data into a local analytical SQLite schema.

- [Load data](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/scripts/load_data.py): python etl · Main Python entrypoint for ingesting and transforming source data. · `scripts/load_data.py`
- [Verify DB](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/scripts/verifydb.py): python qa · Database validation and integrity checks after loading. · `scripts/verifydb.py`
- [Schema](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/schema.sql): sqlite schema · SQLite database structure and modeled analytics tables. · `sql/schema.sql`

### Analytics SQL

Domain-specific queries, marts, and validation for business analysis.

- [Revenue](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/revenue_analysis.sql): analysis sql · Revenue and performance analysis queries. · `sql/revenue_analysis.sql`
- [Customers](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/customer_analysis.sql): analysis sql · Customer segmentation and behavior analysis queries. · `sql/customer_analysis.sql`
- [Funnel](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/funnel_analysis.sql): analysis sql · Conversion and funnel metric queries. · `sql/funnel_analysis.sql`
- [Cohorts](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/cohort_analysis.sql): analysis sql · Cohort retention and lifecycle analysis queries. · `sql/cohort_analysis.sql`
- [Sellers](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/seller_analysis.sql): analysis sql · Seller performance and contribution analysis queries. · `sql/seller_analysis.sql`
- [Profiling](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/data_profiling.sql): analysis sql · Data profiling and quality exploration queries. · `sql/data_profiling.sql`
- [AI insights](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/agentic_insights.sql): analysis sql · Narrative insight generation queries for assisted analysis. · `sql/agentic_insights.sql`
- [Tests](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/sql/test.sql): sql checks · Analytical sanity checks and validation queries. · `sql/test.sql`

### Reporting

Tableau workbooks and export assets that surface the analysis.

- [Exports](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/tableau_exports.sql): tableau bridge · SQL handoff structures and extract-ready outputs for Tableau. · `exports/tableau_exports.sql`
- [Dashboards](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/Dashboards.twb): tableau workbook · Main Tableau workbook for executive and payment dashboards. · `exports/Dashboards.twb`
- [Funnel book](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/exports/Dashboard_Funnel.twb): tableau workbook · Dedicated Tableau workbook for funnel visualization. · `exports/Dashboard_Funnel.twb`

### Context

Documentation and narrative artifacts that define metrics and insights.

- [Data dict](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/docs/data_dictionary.md): docs · Metric and schema definitions for the analytical model. · `docs/data_dictionary.md`
- [Findings](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/docs/initial_findings.md): docs · Initial analytical findings and business observations. · `docs/initial_findings.md`
- [Scope](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/insights/project_scope.md): docs · Project framing, goals, and analytical boundaries. · `insights/project_scope.md`
- [Insight prompt](https://github.com/ayandebnath88/product-growth-intelligence-copilot/blob/main/ai/insight_generator_prompt.md): ai prompt · Prompt template for narrative insight generation. · `ai/insight_generator_prompt.md`

## Connections

- Load data → Schema: populate · Loads transformed data into the SQLite schema.
- Verify DB → Schema: validate · Checks the database structure and loaded contents.
- Schema → Revenue: query base · Revenue analysis reads from the modeled SQLite tables.
- Schema → Customers: query base · Customer analysis reads from the modeled SQLite tables.
- Schema → Funnel: query base · Funnel analysis reads from the modeled SQLite tables.
- Schema → Cohorts: query base · Cohort analysis reads from the modeled SQLite tables.
- Schema → Sellers: query base · Seller analysis reads from the modeled SQLite tables.
- Schema → Profiling: inspect · Profiling queries inspect data quality and distributions.
- Schema → AI insights: summarize · AI-oriented queries extract structured signals for narratives.
- Revenue → Exports: shape output · Revenue results are prepared for Tableau consumption.
- Customers → Exports: shape output · Customer results are prepared for Tableau consumption.
- Funnel → Exports: shape output · Funnel results are prepared for Tableau consumption.
- Cohorts → Exports: shape output · Cohort results are prepared for Tableau consumption.
- Sellers → Exports: shape output · Seller results are prepared for Tableau consumption.
- Exports → Dashboards: feed · Export queries support the main Tableau workbook.
- Exports → Funnel book: feed · Export queries support the funnel workbook.
- Tests → Verify DB: reinforce · SQL checks complement database verification.
- Data dict → Revenue: define · Documentation clarifies the meaning of metrics and fields.
- Data dict → Customers: define · Documentation clarifies the meaning of metrics and fields.
- Scope → Findings: frame · Scope informs the analytical conclusions and observations.
- Insight prompt → AI insights: guide · The prompt shapes the narrative insight generation path.