# Analytics & Reporting Recommendations

## Executive Summary

Analysis of BuyerKiosk's current reporting capabilities, database schemas, and industry best practices reveals significant opportunities to enhance analytics offerings. This document outlines prioritized recommendations for new reports and enhancements.

**Analysis Date**: December 2024

**Store Concepts Covered**:
- `pc` - Plato's Closet (teen/young adult resale)
- `ou` - Once Upon a Child (children's resale)
- `se` - Style Encore (women's resale)
- `pa` - Play It Again Sports (sporting goods resale)

---

## Current State Assessment

### Existing Reports (8 Categories, 30+ Endpoints)

| Category | Status | Notes |
|----------|--------|-------|
| Sales Stats (SalesStatsService) | Strong | Daily/weekly/monthly/YTD with goals |
| Employee Performance (StatsApiController) | Strong | Z-scores, percentiles, efficiency scoring |
| Event Reports (EventReportController) | Good | Sales, marketing, inventory, Comeback Cash |
| Backstock Analytics (ReportService) | Excellent | Aging, health scores, activity metrics |
| Dashboard Stats (DashboardStats) | Aging | Needs modernization |
| Task Engine Monitoring | Good | Operational focused |
| Customer Analytics | Limited | Major opportunity area |
| Inventory Velocity | Missing | Critical gap |

### Key Data Assets

| Database | Key Tables | Analytics Value |
|----------|-----------|-----------------|
| `kiosk_sales` | `sales` (516K+ items) | Days-to-sale, margins, pricing |
| Store DBs | `buyQueue` | Seller behavior, wait times, retention |
| Store DBs | `closeSalesReport` | P&L, returns, goals |
| Store DBs | `customerSurvey` | NPS, satisfaction |
| Store DBs | `ccCoupons`, `ccRedemptions` | Marketing ROI |
| Central | `stores` | Multi-store benchmarking |

### Data Limitations Identified

- **No item-level inventory tracking**: Can't calculate true sell-through rate
- **Category data not populated**: Department/category from POS not flowing through
- **Sales-customer linkage weak**: Employees don't consistently attach customer records to sales
- **No hourly labor data linked to sales**: Can't calculate revenue per labor hour

---

## Priority 1: Inventory Velocity Report Group

### 1.1 Days-to-Sale Analysis Dashboard

**Purpose**: Track how quickly inventory moves and identify trends

**Data Source**: `kiosk_sales.sales` (buyDate, salesDate, price, cost)

**Key Metrics**:
- Days-to-sale distribution (0-7, 8-14, 15-30, 31-60, 61-90, 91-180, 180+)
- Average days-to-sale (current: 85 days, target: 60-90)
- Margin erosion by age bucket (180+ days = 50% margin reduction observed)

**Visualizations**:
- Stacked bar chart: Items by days-to-sale bucket
- Line chart: Average days-to-sale trend over time
- Comparison chart: Store-by-store velocity

**Sample Data Discovered**:
```
Days Bucket    | Items   | Avg Margin
---------------|---------|------------
0-7 days       | 51,059  | $9.41
8-14 days      | 24,275  | $9.23
15-30 days     | 35,116  | $9.22
31-60 days     | 38,065  | $9.05
61-90 days     | 22,186  | $8.88
91-180 days    | 36,884  | $8.28
180+ days      | 38,833  | $4.23 (!)
```

**Key Insight**: Items taking 180+ days to sell have margins cut by more than half. This is actionable intelligence for pricing and markdown decisions.

---

### 1.2 Category Momentum Indicators (MACD-Style)

**Purpose**: Identify trending categories using stock-market-style momentum analysis

**Concept**: Apply Moving Average Convergence Divergence (MACD) methodology to category sales velocity

**Technical Approach**:
```
Fast EMA (12-day) of daily sales count by category
Slow EMA (26-day) of daily sales count by category
MACD Line = Fast EMA - Slow EMA
Signal Line = 9-day EMA of MACD Line
Histogram = MACD Line - Signal Line
```

**Interpretation**:
- **MACD crosses above Signal**: Category momentum increasing (buy more)
- **MACD crosses below Signal**: Category cooling off (reduce buying)
- **Histogram positive/growing**: Strong upward momentum
- **Histogram negative/shrinking**: Weakening momentum

**Visualizations**:
- MACD chart per category with signal line
- Heat map: All categories showing momentum direction
- Alert system: Categories crossing signal lines

**Customizable Parameters**:
- Fast period (default: 12 days)
- Slow period (default: 26 days)
- Signal period (default: 9 days)
- Time range selection

**Additional Momentum Indicators** (future enhancement):
- RSI (Relative Strength Index) for overbought/oversold categories
- Bollinger Bands for volatility analysis
- Volume-weighted moving averages

---

### 1.3 Pricing Optimization Analysis

**Purpose**: Find optimal price points that balance velocity and margin

**Data Source**: `kiosk_sales.sales`

**Analysis**:
- Scatter plot: Price vs days-to-sale
- Sweet spot identification by price band
- Margin optimization recommendations

---

## Priority 2: Customer Behavior Report Group

### 2.1 Seller Retention Analysis

**Purpose**: Track seller (consignor) loyalty and behavior patterns

**Data Source**: `buyQueue` (customerID, isReturning, timeEntered, numContainers)

**Key Metrics**:
- New vs returning seller ratio
- Visit frequency distribution
- Average containers per visit (new vs returning)
- Retention cohorts by signup month

**Current Data**:
- 13,021 unique customers in past year
- 25,823 total buy transactions
- High returning rate indicated by `isReturning` flag

**Visualizations**:
- Cohort retention chart (month-over-month)
- Funnel: First visit → Second visit → Regular seller
- Histogram: Visit frequency distribution

---

### 2.2 Wait Time Optimization Report

**Purpose**: Correlate wait times with customer satisfaction and identify optimal targets

**Data Source**:
- `buyQueue` (lowerBound, upperBound, timeEntered, sortStarted, sortCompleted)
- `customerSurvey` (NPS, step scores)

**Key Metrics**:
- Estimated vs actual wait time accuracy
- Wait time distribution by day/hour
- Correlation: Wait time vs survey scores
- Optimal staffing recommendations

**Visualizations**:
- Heat map: Wait times by day of week / hour
- Scatter plot: Wait time vs NPS score
- Box plot: Wait time distribution by satisfaction tier

---

### 2.3 NPS & Satisfaction Dashboard

**Purpose**: Centralized view of customer satisfaction metrics

**Data Source**:
- `customerSurvey` (6-step survey with NPS)
- `storeStatsAggregate` (90-day rolling averages)

**Key Metrics**:
- NPS score and trend
- Response rate
- Step-by-step satisfaction breakdown
- Detractor/Passive/Promoter distribution

---

## Priority 3: Financial Performance Report Group

### 3.1 Return Rate Tracking

**Purpose**: Monitor returns as a health indicator

**Data Source**: `closeSalesReport` (returnsRetail, grossSalesRetail)

**Key Metrics**:
- Monthly return rate (observed: 0.4-1.8%)
- Return rate trend
- Return dollars impact on margin

**Observed Data**:
```
Month    | Gross Sales | Returns   | Return Rate
---------|-------------|-----------|------------
2025-09  | $99,108     | $1,228    | 1.24%
2025-10  | $118,474    | $2,102    | 1.77%
2025-11  | $28,395     | $114      | 0.40%
```

---

### 3.2 Enhanced P&L Dashboard

**Purpose**: Comprehensive financial view with trending

**Data Source**: `closeSalesReport`

**Enhancements to Current**:
- Year-over-year comparison
- Goal variance analysis with alerts
- Return rate integration
- Buys-to-sales ratio trending
- Cash flow indicators (buys paid vs sales)

---

### 3.3 Seasonal Performance Analysis

**Purpose**: Understand seasonality for planning

**Analysis**:
- Same-store sales by season
- Year-over-year seasonal comparison
- Best/worst performing periods

---

## Priority 4: Cross-Store Benchmarking Report Group

### 4.1 Store Comparison Dashboard

**Purpose**: Compare performance across stores within and between concepts

**Data Source**: Aggregated from all store databases

**Groupings**:
- By concept (pc, ou, se, pa)
- By region (if applicable)
- Overall ranking

**Key Metrics**:
- Days-to-sale average
- Margin performance
- Sales velocity
- Customer satisfaction
- Wait time efficiency

---

### 4.2 Best Practices Identifier

**Purpose**: Learn from top performers

**Analysis**:
- Identify top quartile stores per metric
- Variance from average
- Patterns in high performers
- Recommendations for underperformers

---

## Priority 5: Marketing Effectiveness Report Group

### 5.1 Campaign ROI Dashboard

**Purpose**: Measure marketing investment returns

**Data Source**:
- `seller_marketing_analytics`
- `ccCoupons`, `ccRedemptions`
- Event performance data

**Key Metrics**:
- SMS campaign delivery/click rates
- Comeback Cash redemption rates
- Event-driven sales lift
- Cost per acquisition

---

## Implementation Approach

### Phase 1: New Report Development

Build new reports with consistent design language:
- Modern card-based layouts
- Interactive date range selectors
- Export capabilities (CSV)
- Drill-down navigation

### Phase 2: Existing Report Refactoring

Modernize existing reports to match new design:
- Unified look and feel
- Add YoY comparisons
- Improve interactivity
- Consolidate where logical

### Technical Implementation

**Backend**:
- New service classes in `src/BuyerKiosk/Analytics/`
- API endpoints in `routes/groups/analytics.php`
- TaskEngine jobs for daily aggregation

**Frontend**:
- Templates in `templates/themes/default/admin/analytics/`
- Chart library: Chart.js or ApexCharts
- Responsive design using existing design tokens

**Data Aggregation**:
- Daily TaskEngine jobs for pre-computation
- Store in new aggregation tables
- Real-time option for small date ranges

---

## Database Schema Additions Needed

### New Tables for Analytics

```sql
-- Daily category velocity metrics
CREATE TABLE analytics_category_velocity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    typeNum VARCHAR(10) NOT NULL,
    category VARCHAR(100) NOT NULL,
    date DATE NOT NULL,
    items_sold INT DEFAULT 0,
    avg_days_to_sale DECIMAL(10,2),
    avg_margin DECIMAL(10,2),
    ema_fast DECIMAL(10,4),
    ema_slow DECIMAL(10,4),
    macd_line DECIMAL(10,4),
    signal_line DECIMAL(10,4),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_category_date (typeNum, category, date),
    INDEX idx_date (date),
    INDEX idx_typenum (typeNum)
);

-- Daily store velocity metrics
CREATE TABLE analytics_store_velocity (
    id INT AUTO_INCREMENT PRIMARY KEY,
    typeNum VARCHAR(10) NOT NULL,
    date DATE NOT NULL,
    items_sold INT DEFAULT 0,
    avg_days_to_sale DECIMAL(10,2),
    avg_margin DECIMAL(10,2),
    days_0_7 INT DEFAULT 0,
    days_8_14 INT DEFAULT 0,
    days_15_30 INT DEFAULT 0,
    days_31_60 INT DEFAULT 0,
    days_61_90 INT DEFAULT 0,
    days_91_180 INT DEFAULT 0,
    days_180_plus INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_store_date (typeNum, date),
    INDEX idx_date (date)
);

-- Seller retention cohorts
CREATE TABLE analytics_seller_cohorts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    typeNum VARCHAR(10) NOT NULL,
    cohort_month DATE NOT NULL,
    months_since_first INT NOT NULL,
    original_count INT DEFAULT 0,
    active_count INT DEFAULT 0,
    retention_rate DECIMAL(5,2),
    avg_containers DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_cohort (typeNum, cohort_month, months_since_first),
    INDEX idx_typenum (typeNum)
);
```

---

## Success Metrics

| Report | Target Metric |
|--------|---------------|
| Inventory Velocity | Reduce avg days-to-sale from 85 to 70 days |
| Category Momentum | Identify trending categories 2 weeks earlier |
| Seller Retention | Improve 3-month retention by 10% |
| Wait Time | Maintain <30 min wait with >4.0 satisfaction |
| Cross-Store | Reduce performance variance by 20% |

---

## Next Steps

1. **Review and approve** this recommendations document
2. **Prioritize specific reports** within each group
3. **Create implementation specifications** for Priority 1 reports
4. **Design UI mockups** for new report layouts
5. **Implement and iterate**

---

## Appendix: Industry Benchmarks Reference

| KPI | Industry Target | BuyerKiosk Current |
|-----|-----------------|-------------------|
| Days to Sale | 60-90 days | 85 days |
| Gross Margin | 55-70% | 61% |
| Return Rate | <3% | 0.4-1.8% |
| Sell-Through | 60-70% | N/A (no inventory data) |
| Customer Retention | Track monthly | Tracking available |

*Based on ThredUp 2024/2025 Resale Reports, ConsignR benchmarks, and industry research.*