CRM Project — System Requirements (Simplified)

Introduction

We have access to three core datasets: Sales Transactions, Customers Master, and Products (Items) Master. The business teams—primarily Marketing and Sales, supported by the Data team (and relevant suppliers/partners where applicable)—want to generate deeper customer insights and apply established marketing practices (segmentation, lifecycle monitoring, and targeted customer lists).

To enable this, the system must:

• Transform and aggregate the raw inputs into consistent, validated, and analytics-ready datasets
• Store the curated results in a backend database for reliable access
• Expose the information via self-service reporting so business teams can filter and extract relevant customer data

Expected Results

1. Clean curated data in a database available for:

2. Ad-hoc analysis

3. Targeted extraction requests using filters (even if some filters are not exposed in the UI)

4. Standard Excel output(s):

5. A summary report that can be exported/submitted as needed

6. Automatic transformation and calculation:

7. Data is transformed and calculated automatically as soon as the most updated input data is received

8. Self-service Streamlit dashboard:

9. A Streamlit + Plotly application is updated and available so Marketing and Sales can:

   • Filter customers by key attributes (e.g., month, value tier / activity status / lifecycle event, opt-in flags)
   • Extract/export lists for actioning
   • Review aggregated summaries (e.g., customer counts by value tier or lifecycle event)

High-Level System Requirements

Backend (Data & Storage)

• The system must ingest the agreed input files (Sales Transactions yearly files, Customers Master, Products Master).
• The system must store a monthly CRM snapshot for every customer (historical months remain available for analysis).

• The system must support filtered extraction for business and analytical use cases, including:

• Month selection

• Activity status, value tier, and lifecycle event (as defined in the CRM logic)
• Opt-in flags (email/SMS/phone)
• Customer group / city / store (where applicable)
• The system must support exporting the extracted results in common formats (CSV/Excel).

Automation

• The system must refresh calculations automatically upon receipt of updated data (batch refresh workflow).
• The system must provide basic data quality controls (e.g., missing customer_id/product_id, invalid dates) and logging.

Frontend / Reporting (Streamlit + Plotly)

• The reporting layer must provide:

• Customer list view with filters and CSV export

• Aggregated summaries (customer counts by value tier / lifecycle event) with time context
• The latest report month should be the default view, with historical navigation available via a month selector.