# Analytics Enhancement Report

## Project Overview

**Project Name**: BuyerKiosk Analytics Enhancement
**Date**: December 30, 2024
**Prepared By**: Analytics Discovery Team

### Executive Summary

This report documents the findings from a comprehensive analysis of BuyerKiosk's current reporting capabilities, database assets, and industry best practices. The analysis identified significant opportunities to enhance analytics offerings for resale clothing store operators across four concepts: Plato's Closet (pc), Once Upon a Child (ou), Style Encore (se), and Play It Again Sports (pa).

---

## 1. Current State Analysis

### 1.1 Existing Reports Inventory

The system currently provides 8 major report categories with 30+ endpoints:

| Category | Service/Controller | Status | Gap Assessment |
|----------|-------------------|--------|----------------|
| Sales Stats | SalesStatsService | Strong | Missing velocity metrics |
| Employee Performance | StatsApiController | Strong | Good statistical analysis |
| Event Reports | EventReportController | Good | Could add ROI calculations |
| Backstock Analytics | ReportService | Excellent | Model for new reports |
| Dashboard Stats | DashboardStats | Aging | Needs modernization |
| Task Engine Monitoring | TaskDashboardController | Good | Operational focus |
| Customer Analytics | Limited | Gap | Major opportunity |
| Inventory Velocity | Missing | Gap | Critical need |

### 1.2 Database Assets Available

**Store Database (kiosk_ou00) - 113 tables**
- `buyQueue` - Buy transactions with timestamps, wait times, customer IDs
- `dailySalesData` - POS transactions (limited customer linkage)
- `closeSalesReport` - Daily P&L with returns data
- `statsStoreDaily` / `statsEmployeeDaily` - Pre-aggregated metrics
- `customerSurvey` - NPS and satisfaction (6-step survey)
- `ccCoupons` / `ccRedemptions` - Comeback Cash data

**Central Database (kiosk_sales) - 516,804 item records**
- `sales` - Item-level data with buyDate, salesDate, price, cost
- `departments`, `categories`, `brands`, `sizes` - Product taxonomy
- Coverage: 10 stores, data from 2022-present

**Key Data Characteristics**:
- Item-level sales data tracks days from buy to sale
- Returns data available in closeSalesReport
- Customer linkage to sales is weak (employees don't consistently attach)
- Category/department data not populated from POS
- Strong buy-side customer tracking via buyQueue

### 1.3 Data Quality Findings

| Data Point | Quality | Notes |
|------------|---------|-------|
| Days-to-sale | Excellent | buyDate + salesDate available |
| Item margins | Excellent | price - cost tracked |
| Returns | Good | Available in daily close reports |
| Customer-sale linkage | Poor | Not consistently captured |
| Category taxonomy | Poor | Not flowing from POS |
| Seller retention | Good | buyQueue tracks returning sellers |
| Wait times | Good | lowerBound/upperBound captured |
| Survey data | Good | NPS and 6-step satisfaction |

---

## 2. Industry Benchmarks

### 2.1 Key Performance Indicators (KPIs)

Based on industry research (ThredUp 2024/2025 Resale Reports, ConsignR benchmarks):

| KPI | Industry Target | BuyerKiosk Current | Gap |
|-----|-----------------|-------------------|-----|
| Days to Sale | 60-90 days | 85 days | Slight |
| Gross Margin | 55-70% | 61% | On target |
| Return Rate | <3% | 0.4-1.8% | Exceeding |
| Inventory Turnover | 3-5x annually | Not tracked | Gap |
| Sell-Through Rate | 60-70% | Cannot calculate | Data gap |

### 2.2 Market Context

- U.S. secondhand apparel market grew 14% in 2024
- Online resale accelerating at 23% growth
- 94% of retail executives say customers participate in resale
- Resale outpacing broader retail by 5X

---

## 3. Key Data Discovery

### 3.1 Days-to-Sale Distribution (Last 12 Months)

Analysis of 246,418 sold items revealed significant margin erosion over time:

| Days Bucket | Items Sold | Avg Margin | Margin vs Baseline |
|-------------|------------|------------|-------------------|
| 0-7 days | 51,059 | $9.41 | Baseline |
| 8-14 days | 24,275 | $9.23 | -2% |
| 15-30 days | 35,116 | $9.22 | -2% |
| 31-60 days | 38,065 | $9.05 | -4% |
| 61-90 days | 22,186 | $8.88 | -6% |
| 91-180 days | 36,884 | $8.28 | -12% |
| 180+ days | 38,833 | $4.23 | **-55%** |

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

### 3.2 Store Coverage

| Store | Items | Concept | Data Range |
|-------|-------|---------|------------|
| pc80586 | 347,164 | Plato's Closet | Active |
| pc80926 | 83,217 | Plato's Closet | Active |
| pc80951 | 49,562 | Plato's Closet | Active |
| pc80721 | 9,401 | Plato's Closet | Active |
| pc80774 | 9,130 | Plato's Closet | Active |
| pa11871 | 6,386 | Play It Again | Active |
| pc80554 | 5,171 | Plato's Closet | Active |
| pc80626 | 4,615 | Plato's Closet | Historical |
| pc00 | 2,046 | Plato's Closet | Historical |
| pa11680 | 112 | Play It Again | Limited |

### 3.3 Financial Data Sample (kiosk_ou00)

Recent closeSalesReport data shows:

| Month | Gross Sales | Returns | Return Rate | Net GM |
|-------|-------------|---------|-------------|--------|
| Sep 2025 | $99,108 | $1,228 | 1.24% | $607 |
| Oct 2025 | $118,474 | $2,102 | 1.77% | $616 |
| Nov 2025 | $28,395 | $114 | 0.40% | $204 |
| Dec 2025 | $265,325 | $331 | 0.12% | $158,997 |

### 3.4 Seller Behavior (kiosk_ou00, Last 12 Months)

| Metric | Value |
|--------|-------|
| Unique sellers | 13,021 |
| Total buy transactions | 25,823 |
| Returning seller visits | 24,985 |
| Avg visits per seller | 1.98 |

---

## 4. Proposed Analytics Enhancements

### 4.1 Priority 1: Inventory Velocity Reports

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

**Reports**:

1. **Days-to-Sale Analysis Dashboard**
   - Distribution chart by velocity buckets (0-7, 8-14, 15-30, 31-60, 61-90, 91-180, 180+)
   - Trend analysis over time
   - Store-by-store comparison
   - Margin erosion visualization

2. **Category Momentum Indicators (MACD-Style)**
   - Apply stock-market momentum analysis to category sales
   - Moving Average Convergence Divergence (MACD) adapted for retail
   - Fast EMA (12-day) vs Slow EMA (26-day) with Signal line (9-day)
   - Crossover alerts: "Category X momentum increasing - buy more"
   - Heat map showing all categories at a glance
   - Customizable time periods

3. **Pricing Optimization Analysis**
   - Price point vs days-to-sale correlation
   - Sweet spot identification by category
   - Markdown timing recommendations

**Data Sources**: `kiosk_sales.sales`

**Technical Approach**:
- Daily aggregation via TaskEngine
- Store results in new analytics tables
- MACD calculations in PHP service class
- Chart.js for visualizations

### 4.2 Priority 2: Customer Behavior Reports

**Purpose**: Understand seller behavior and satisfaction drivers

**Reports**:

4. **Seller Retention Analysis**
   - New vs returning seller ratios
   - Cohort analysis by signup month
   - Visit frequency distribution
   - Container volume trends

5. **Wait Time Optimization Report**
   - Estimated vs actual wait time accuracy
   - Heat map by day/hour
   - Correlation with survey satisfaction scores
   - Staffing recommendations

6. **NPS & Satisfaction Dashboard**
   - NPS score trends
   - Response rate tracking
   - 6-step satisfaction breakdown
   - Detractor/Passive/Promoter distribution

**Data Sources**: `buyQueue`, `customerSurvey`, `storeStatsAggregate`

### 4.3 Priority 3: Financial Performance Reports

**Purpose**: Enhanced financial visibility and trend analysis

**Reports**:

7. **Return Rate Tracking**
   - Monthly return rate trends
   - Return dollars impact on margin
   - Anomaly detection

8. **Enhanced P&L Dashboard**
   - Year-over-year comparison
   - Goal variance analysis with alerts
   - Buys-to-sales ratio trending
   - Cash flow indicators

9. **Seasonal Performance Analysis**
   - Same-store sales by season
   - YoY seasonal comparison
   - Best/worst period identification

**Data Sources**: `closeSalesReport`

### 4.4 Priority 4: Cross-Store Benchmarking

**Purpose**: Compare performance and identify best practices

**Reports**:

10. **Store Comparison Dashboard**
    - Days-to-sale by store
    - Margin comparison
    - Sales velocity ranking
    - Group by concept (pc, ou, se, pa)

11. **Best Practices Identifier**
    - Top quartile identification
    - Variance from average analysis
    - Success pattern recognition

**Data Sources**: Aggregated across all store databases

### 4.5 Priority 5: Marketing Effectiveness

**Purpose**: Measure marketing ROI

**Reports**:

12. **Campaign ROI Dashboard**
    - SMS delivery and click rates
    - Comeback Cash redemption analysis
    - Event-driven sales lift measurement
    - Cost per acquisition

**Data Sources**: `seller_marketing_analytics`, `ccCoupons`, `ccRedemptions`

---

## 5. Technical Requirements

### 5.1 New Database Tables

```sql
-- Daily velocity metrics per store
analytics_store_velocity (
    typeNum, date, items_sold, avg_days_to_sale, avg_margin,
    days_0_7, days_8_14, days_15_30, days_31_60,
    days_61_90, days_91_180, days_180_plus
)

-- Category momentum with MACD values
analytics_category_velocity (
    typeNum, category, date, items_sold,
    avg_days_to_sale, avg_margin,
    ema_fast, ema_slow, macd_line, signal_line
)

-- Seller retention cohorts
analytics_seller_cohorts (
    typeNum, cohort_month, months_since_first,
    original_count, active_count, retention_rate
)
```

### 5.2 New Service Classes

- `BuyerKiosk\Analytics\Services\VelocityService` - Velocity data retrieval
- `BuyerKiosk\Analytics\Services\MACDCalculator` - Momentum calculations
- `BuyerKiosk\Analytics\Services\RetentionService` - Cohort analysis
- `BuyerKiosk\Analytics\Services\BenchmarkService` - Cross-store comparison

### 5.3 TaskEngine Jobs

- `VelocityDailyAggregationJob` - Daily at 2am
- `CategoryMACDCalculationJob` - Daily at 3am
- `MomentumAlertJob` - Daily at 4am
- `SellerCohortUpdateJob` - Daily at 5am

### 5.4 API Endpoints

```
GET /api/analytics/:typeNum/velocity
GET /api/analytics/:typeNum/velocity/distribution
GET /api/analytics/:typeNum/momentum/macd
GET /api/analytics/:typeNum/momentum/overview
GET /api/analytics/:typeNum/retention/cohorts
GET /api/analytics/:typeNum/benchmarks
```

### 5.5 Frontend Components

- Chart.js or ApexCharts for visualizations
- Responsive card-based layouts
- Date range selectors
- CSV export capabilities
- Consistent with existing design tokens

---

## 6. Implementation Approach

### Phase 1: Inventory Velocity (Priority 1)
- Create database migrations
- Implement MACD calculator service
- Build TaskEngine aggregation jobs
- Create velocity dashboard UI
- Add MACD momentum heat map

### Phase 2: Customer Behavior (Priority 2)
- Implement retention cohort analysis
- Build wait time correlation analysis
- Create NPS dashboard
- Add seller retention visualizations

### Phase 3: Financial & Benchmarking (Priority 3-4)
- Enhance P&L dashboard with YoY
- Add return rate tracking
- Implement cross-store benchmarks
- Create comparison views by concept

### Phase 4: Marketing & Refinement (Priority 5)
- Build campaign ROI dashboard
- Refactor existing reports for consistency
- Add enhancements to legacy reports

---

## 7. 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% |

---

## 8. User Personas

### Primary Users

**Store Manager**
- Needs: Daily operational metrics, staffing guidance, inventory alerts
- Reports: Velocity dashboard, wait time optimization, NPS trends

**Regional Manager**
- Needs: Multi-store comparison, performance variance, best practices
- Reports: Cross-store benchmarks, concept comparisons

**Store Owner**
- Needs: Financial performance, ROI, strategic insights
- Reports: Enhanced P&L, marketing ROI, seasonal analysis

---

## 9. Constraints and Considerations

### Data Limitations
- Category taxonomy not populated from POS (work with available data)
- Sales-customer linkage weak (focus on buy-side customer analysis)
- No item-level current inventory (can only analyze sold items)

### Technical Considerations
- Use TaskEngine for daily aggregations to avoid query performance issues
- Store pre-calculated MACD values to enable fast dashboard loading
- Central database (kiosk_sales) spans all stores - single source for velocity

### Design Considerations
- New reports should establish design patterns for future refactoring
- Existing dashboard page remains the primary summary view
- Reports grouped by logical category with drill-down navigation

---

## 10. Appendix

### A. Store Concepts Reference

| Prefix | Concept | Target Market |
|--------|---------|---------------|
| pc | Plato's Closet | Teen/young adult clothing |
| ou | Once Upon a Child | Children's items |
| se | Style Encore | Women's clothing |
| pa | Play It Again Sports | Sporting goods |

### B. MACD Calculation Reference

```
Fast EMA (12-day): k = 2/13 = 0.1538
Slow EMA (26-day): k = 2/27 = 0.0741
Signal (9-day): k = 2/10 = 0.2000

EMA_today = (Value × k) + (EMA_yesterday × (1 - k))
MACD Line = Fast EMA - Slow EMA
Signal Line = 9-day EMA of MACD Line
Histogram = MACD Line - Signal Line
```

### C. Industry Sources

- ThredUp 2024/2025 Resale Reports
- ConsignR Consignment Store Analytics
- FashionUnited Industry Benchmarks
- Shopify Retail Analytics Best Practices

---

*End of Report*