Business Insights Reporting System

The challenges

1. Performance Bottlenecks

• Issue: Queries on large datasets in PostgreSQL slowed down the system.
• Solution: Used incremental data extraction and caching mechanisms.

2. Data Consistency Issues

• Issue: Discrepancies between PostgreSQL and BigQuery data.
• Solution: Implemented data validation and reconciliation checks.

3. API Latency

• Issue: High response time when fetching insights.
• Solution: Optimized API responses using pagination and caching.

Architecture

1. Data Collection (PostgreSQL & API Integration)

• Customer transactions, demographic details, and return data are stored in a PostgreSQL database.
• A batch job extracts transactional and customer data in and pushes it into a Google BigQuery.

2. ETL Process Using Apache Spark

• BigQuery integrated Spark procedure processes the data by aggregating and transforming it into meaningful insights.
• Key metrics include customer segmentation, purchase frequency, average basket size, and return reasons.
• The processed data is loaded into PostgreSQL for accessing in the dashboard.
• The ETL jobs are scheduled to run every night to ensure daily updates.

3. REST API for Insights Retrieval

• A REST API is built to fetch the latest insights from PostgreSQL.
• The API returns JSON responses, which are then used by the front-end application.

4. Web Dashboard for Business Insights Visualization

• The frontend (built with Angular) fetches the insights via the REST API.
• The dashboard displays daily insights with visualization components (charts, graphs, tables) for key metrics like: Customer Demographics, Purchase Patterns, Return Patterns.

Results

• 90% Reduction in Report Generation Time: BigQuery and Spark optimized data processing.
• Enhanced Business Decision-Making: Key insights on customer behavior improved marketing strategies.
• Scalability Achieved: The architecture scaled with increasing data volume.