How ayandebnath88/product-growth-intelligence-copilot works
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: schema.sql Schema · tableau_exports.sql Exports · revenue_analysis.sql Revenue · customer_analysis.sql Customers
Components 18
Data preparation
Load raw business data into a local analytical SQLite schema.
Analytics SQL
Domain-specific queries, marts, and validation for business analysis.
- Revenue · Revenue and performance analysis queries.
sql/revenue_analysis.sql - Customers · Customer segmentation and behavior analysis queries.
sql/customer_analysis.sql - Funnel · Conversion and funnel metric queries.
sql/funnel_analysis.sql - Cohorts · Cohort retention and lifecycle analysis queries.
sql/cohort_analysis.sql - Sellers · Seller performance and contribution analysis queries.
sql/seller_analysis.sql - Profiling · Data profiling and quality exploration queries.
sql/data_profiling.sql - AI insights · Narrative insight generation queries for assisted analysis.
sql/agentic_insights.sql - Tests · Analytical sanity checks and validation queries.
sql/test.sql
Reporting
Tableau workbooks and export assets that surface the analysis.
- Exports · SQL handoff structures and extract-ready outputs for Tableau.
exports/tableau_exports.sql - Dashboards · Main Tableau workbook for executive and payment dashboards.
exports/Dashboards.twb - Funnel book · Dedicated Tableau workbook for funnel visualization.
exports/Dashboard_Funnel.twb
Context
Documentation and narrative artifacts that define metrics and insights.
- Data dict · Metric and schema definitions for the analytical model.
docs/data_dictionary.md - Findings · Initial analytical findings and business observations.
docs/initial_findings.md - Scope · Project framing, goals, and analytical boundaries.
insights/project_scope.md - Insight prompt · Prompt template for narrative insight generation.
ai/insight_generator_prompt.md
Connections 21
- 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.