CrazeAnalytix/.agents/skills/crazeanalytix-standards/references/data-processing.md

Data Processing Patterns

Table of Contents

• Column Mapping
• Currency Parsing
• Month Normalization
• CSV Processing
• Excel Processing
• Filtering
• Aggregation
• Key Constants

Column Mapping

Use getColumnValue(row, aliases[]) to flexibly extract values from rows with varying header names:

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

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

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

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.