mirror of
https://github.com/christianvidalwolf-prog/CrazeAnalytix.git
synced 2026-08-03 17:55:24 +02:00
115 lines
4.3 KiB
Markdown
115 lines
4.3 KiB
Markdown
# Data Processing Patterns
|
|
|
|
## Table of Contents
|
|
- [Column Mapping](#column-mapping)
|
|
- [Currency Parsing](#currency-parsing)
|
|
- [Month Normalization](#month-normalization)
|
|
- [CSV Processing](#csv-processing)
|
|
- [Excel Processing](#excel-processing)
|
|
- [Filtering](#filtering)
|
|
- [Aggregation](#aggregation)
|
|
- [Key Constants](#key-constants)
|
|
|
|
## Column Mapping
|
|
|
|
Use `getColumnValue(row, aliases[])` to flexibly extract values from rows with varying header names:
|
|
|
|
```typescript
|
|
const customer = getColumnValue(row, ['NEW CUSTOMER', 'Customer', 'Client', 'Account', 'Partner', 'COUNTRY']);
|
|
const asin = getColumnValue(row, ['CUSTOMER REFERENCE', 'AMAZON ASIN', 'ASIN', 'PRODUCT ID']);
|
|
const sku = getColumnValue(row, ['RAW ARTICLE NO.', 'SKU', 'Item No']);
|
|
```
|
|
|
|
The function normalizes keys to lowercase and returns the first matching alias value.
|
|
|
|
## Currency Parsing
|
|
|
|
`parseCurrency()` handles EU and US formats:
|
|
- EU: `1.234,56` → removes dots, replaces comma with period
|
|
- US: `1,234.56` → removes commas
|
|
- Strips currency symbols (`€`, `$`, `£`)
|
|
- Returns `0` on parse failure
|
|
|
|
`parseUnits()` for integers — removes all dots and commas, parses as integer.
|
|
|
|
## Month Normalization
|
|
|
|
`normalizeMonth()` handles:
|
|
- English short/long: `Jan`, `January`
|
|
- Spanish: `Enero` → `Jan`, `Febrero` → `Feb`, `Marzo` → `Mar`, `Abril` → `Apr`, `Agosto` → `Aug`, `Diciembre` → `Dec`
|
|
- Numeric: `01` → `Jan`, `1` → `Jan`
|
|
- Combined: `Apr-23` → `Apr` (extracts month part)
|
|
- Excel serial dates: `45544` → converts to month name
|
|
|
|
Mapping via `MONTH_MAP` constant (lowercase keys to 3-letter English months).
|
|
|
|
## CSV Processing
|
|
|
|
```typescript
|
|
Papa.parse(fileOrContent, {
|
|
header: true,
|
|
skipEmptyLines: true,
|
|
complete: (results) => {
|
|
const data = results.data
|
|
.map((row, index) => mapRowToRecord(row, index))
|
|
.filter(r => r.year > 2023 && isAllowedCustomer(r.customer));
|
|
resolve(data);
|
|
}
|
|
});
|
|
```
|
|
|
|
## Excel Processing
|
|
|
|
```typescript
|
|
const arrayBuffer = await file.arrayBuffer();
|
|
const workbook = XLSX.read(arrayBuffer, { type: 'array' });
|
|
const sheetName = workbook.SheetNames[0];
|
|
const worksheet = workbook.Sheets[sheetName];
|
|
const rawData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
|
|
|
|
// Header row is index 0, data starts at index 1
|
|
const headers = rawData[0];
|
|
const data = rawData.slice(1).map((row, index) => {
|
|
const obj = Object.fromEntries(headers.map((h, i) => [h, row[i]]));
|
|
return mapRowToRecord(obj, index);
|
|
});
|
|
```
|
|
|
|
For multi-sheet workbooks (like Ads Weekly), iterate `workbook.SheetNames` and process each sheet with the year derived from the sheet name.
|
|
|
|
## Filtering
|
|
|
|
### Sales Filtering (`filterData`)
|
|
Multi-dimensional filtering across: customer, year, month, week, line, asin, sku, title, stock, vendorStock, woc, bulkSearch, and columnFilters.
|
|
|
|
### Ads Filtering (`filterAdsData`)
|
|
When no customer filter is selected, defaults to `PAN_EU_COUNTRIES` only. This means unfiltered ads data shows DE + IT + FR + ES combined.
|
|
|
|
### Column Filters
|
|
Support Excel-style operators: `equals`, `notEquals`, `contains`, `notContains`, `startsWith`, `endsWith`, `gt`, `lt`, `gte`, `lte`. Plus `selectedValues` for checkbox filtering and `sort` for column sorting.
|
|
|
|
### Numeric Conditions (`checkNumericConditions`)
|
|
Supports range strings (`"10-20"`), comparison operators (`">5"`, `"<=100"`), and special labels (`"Out of Stock"`, `"Low Stock"`, `"In Stock"`).
|
|
|
|
## Aggregation
|
|
|
|
`aggregateData()` produces:
|
|
- `totalSellOut`, `totalUnits` — grand totals
|
|
- `totalsByYear` — `Record<string, { sellOut, units }>`
|
|
- `byLine` — revenue and units per product line
|
|
- `byCustomer` — revenue per marketplace
|
|
- `seasonality` — monthly trends across years
|
|
- `topMovers`, `bottomMovers` — YoY growth/decline rankings
|
|
- `comparisonPeriods` — which periods are being compared
|
|
- `topLinesSplit`, `byCustomerSplit`, `byLineOverviewSplit` — yearly breakdown data
|
|
|
|
## Key Constants
|
|
|
|
```typescript
|
|
const MONTH_ORDER = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
|
|
const PAN_EU_COUNTRIES = ['Amazon DE', 'Amazon IT', 'Amazon FR', 'Amazon ES'];
|
|
const ALLOWED_CUSTOMERS = ['Amazon DE', 'Amazon IT', 'Amazon FR', 'Amazon ES', 'Amazon UK', 'Amazon SC'];
|
|
```
|
|
|
|
Country name normalization maps: "Germany"/"Deutschland"/"DE"/"Alemania" → "Amazon DE", and similar for other countries.
|