# Sales playbook for BIC

Plain-English definitions of every term BIC needs to answer sales questions
correctly. Each section is one self-contained chunk; BIC retrieves them
individually at chat time.


## Net sales

**Net sales** is gross sale amount minus all discounts, excluding returns
and exchanges. The column is `SALE_NET_AMOUNT` on `ERS_SALES_TRANSACTIONS`.

Net = Gross − Manual discount − Effective promo discount.
Equivalent: `SALE_GROSS_AMOUNT − SALE_MANUAL_DISCOUNT − SALE_EFF_PROMO_DISCOUNT`.

When someone says "sales" with no qualifier, they mean net sales, not gross.

Always filter `lower(trType) = 'sale'` to exclude returns, exchanges, and
adjustments — the table also stores those rows.

Ask BIC about this:
- "What was net sales last month?"
- "Show net sales by format for the week"
- "Why is gross higher than net?"


## Gross sales vs net sales

**Gross sales** (`SALE_GROSS_AMOUNT`) is the MRP-times-quantity total before
any discount. **Net sales** (`SALE_NET_AMOUNT`) is what the customer paid.

Discount erosion = Gross − Net. As a percentage:
`(SALE_GROSS_AMOUNT − SALE_NET_AMOUNT) / SALE_GROSS_AMOUNT × 100`.

Use gross when the question is about ticket size or list-price exposure;
use net for revenue, achievement, and growth conversations.

Ask BIC about this:
- "What is the discount percentage by store?"
- "Show gross vs net sales by section"


## SALE_QTY — units sold

**`SALE_QTY`** is the line-item quantity of each sale row. Sum it for total
units. It is signed: returns appear with negative quantities.

To count just the sales (excluding returns) use the `trType` filter:
`sum(SALE_QTY) WHERE lower(trType) = 'sale'`.

Average selling price (ASP) is `sum(SALE_NET_AMOUNT) / sum(SALE_QTY)` over
the same filter window.

Ask BIC about this:
- "How many units sold last month?"
- "What is the ASP for menswear?"


## Bills, ABS, ABV

A **bill** is one customer transaction — one `saleId` value, multiple line
items rolled together. Count bills with `count(distinct saleId)`.

**ABS — Average Basket Size** = `sum(SALE_QTY) / count(distinct saleId)`.
Units per bill.

**ABV — Average Basket Value** = `sum(SALE_NET_AMOUNT) / count(distinct saleId)`.
Net rupees per bill.

These are reported per store, per format, or per zone — not company-wide.

Ask BIC about this:
- "What is the average basket size for VALLI?"
- "Show ABV trend for the last 8 weeks"
- "Which stores have ABS below 1.8?"


## SSPD — sales per square foot per day

**SSPD** measures store productivity. SSPD = `sum(SALE_NET_AMOUNT) / store_area_sqft / number_of_days_in_window`.

Store area in square feet comes from `ERS_LOCATION_CODES.AREA`. Use only
`isActiveSaleLocation = 1` stores when comparing.

SSPD is reported in **rupees**, not lakhs — typical values fall in the
₹500–₹3000 range per sqft per day.

Ask BIC about this:
- "Show SSPD by format"
- "Which stores have the highest SSPD?"


## WTD, MTD, YTD, FYTD

**WTD — week-to-date.** Monday of the current ISO week through the most
recent completed sales day. If today is Wednesday, WTD covers Mon and Tue.

**MTD — month-to-date.** First day of the current calendar month through
the most recent completed sales day.

**YTD — year-to-date.** January 1 of the current calendar year through
today.

**FYTD — fiscal-year-to-date.** April 1 of the current fiscal year through
today. *This is what brand owners usually mean by "this year".*

When the team says "this year" without context, they mean **FYTD** (fiscal),
not calendar YTD. Confirm with the user only if the difference matters.

Ask BIC about this:
- "What does FYTD mean?"
- "What is FYTD net sales?"
- "Show MTD vs same-period-last-year"


## LY vs CY comparisons

**CY** = current year. **LY** = last year — the **same window of dates**, not
calendar year boundaries.

LY-WTD compares Mon–today of this week to Mon–same-day of the same ISO week
last year. LY-MTD compares 1st–today of this month to 1st–same-day of the
same month last year.

Growth = `(CY − LY) / LY × 100`. Negative values are degrowth, not "minus
growth".

Use the `targetdate_prevYear_achivedSales_in_rs` column on
`abp_target_vs_achieved_sales_new` for pre-aggregated LY values when
available — it already aligns the date windows correctly.

Ask BIC about this:
- "What is the LY vs CY sales comparison?"
- "Compare this month sales vs same month last year"
- "Which formats have positive YoY growth?"


## ABP — annual business plan

**ABP** is the monthly sales target set at the start of the fiscal year.
The fiscal year runs **April 1 to March 31**.

Daily ABP lives in `abp_daywise_targets` — one row per `(store, day, abp_section)`.
Monthly achievement vs target is pre-computed in `abp_target_vs_achieved_sales_new`.

A store is "below ABP" when MTD achieved sales fall short of the pro-rated
monthly target: `target_amount × (working_days_so_far / total_working_days_in_month)`.

ABP is the baseline plan, not the stretch target. Promotional pop-ups and
event days are excluded from the original ABP build.

Ask BIC about this:
- "Which stores are below ABP target this month?"
- "What is the ABP achievement percentage?"
- "Show ABP vs achieved by zone"


## Discounts — manual, promo, effective

Three discount columns on `ERS_SALES_TRANSACTIONS`:

`SALE_MANUAL_DISCOUNT` — discretionary discount granted at the till by the
cashier. Tracked for governance.

`SALE_EFF_PROMO_DISCOUNT` — effective promotional discount from a campaign
or scheme. This is the campaign-attributable discount.

`SALE_DISCOUNT_TYPE` — the campaign or scheme name. Use this to break down
discount cost by program.

Total discount = manual + effective promo. Discount % of gross =
`(manual + promo) / SALE_GROSS_AMOUNT`.

Ask BIC about this:
- "What is the discount percentage by store?"
- "Show discount type breakdown for the last quarter"
- "Which stores give the highest manual discounts?"


## Sections, sub-sections, divisions

The merchandise hierarchy on `ERS_SALES_TRANSACTIONS` is:

`SALE_DIVISION` — broadest (e.g. Men, Women, Kids, Home).
`SALE_SECTION` — within division (e.g. Menswear, Western Womenswear).
`SALE_SUBSECTION` — within section (e.g. Shirts, Trousers).
`SALE_SUBSECTION_GROUP` — finest (e.g. Formal Shirts).

The `ABP_*` columns mirror this hierarchy but are normalized for ABP-target
matching: use `ABP_Division`, `ABP_Section`, `ABP_SubSection`,
`ABP_SubSectionGroup` when joining or comparing against ABP tables.

Ask BIC about this:
- "Show section-wise sales breakdown"
- "What is the menswear contribution to total sales?"
- "Which sub-sections grew fastest this quarter?"


## Stores — names, formats, zones

A store is identified by `shortname` (the human-readable code like `VALK01`)
or `ERS_COMPANY_CODE` (the numeric POS id).

`name` is the long display name. `FORMAT` is the format brand (VALLI, KLM,
VML). `zone` and `AREA` are geographic groupings; `ShowroomCity` and
`ShowroomState` are the literal address.

Always filter `isActiveSaleLocation = 1` on `ERS_LOCATION_CODES` when
comparing stores — there are inactive and warehouse codes in the table.

Ask BIC about this:
- "Show me top 10 stores by net sales"
- "Which stores are in Hyderabad?"
- "Show store-wise sales for VALLI format only"


## Vendors and brands

`SALE_VENDORNAME` is the specific vendor on the sale line. `SALE_VENDORGROUP_NAME`
is the parent group (multiple vendor names can roll into one group).

`SALE_VENDORCITY` and `SALE_VENDORAGENT_NAME` add the supplier-side context
when needed.

When someone says "brand", they almost always mean `SALE_VENDORGROUP_NAME`,
not the format. The store-format brands (VALLI, KLM) are different from
the merchandise vendor brands.

Ask BIC about this:
- "Which vendor has the highest sales this year?"
- "Show top 20 vendor groups by net sales"
- "What is vendor-wise contribution to menswear?"


## Lakhs and crores — when to use which

Indian notation: **1 lakh = 100,000**, **1 crore = 10,000,000 = 100 lakhs**.

Store-level numbers (single store, single section, single day) → **lakhs**,
two decimals (`12.45 L`).

Brand-level, format-level, or company-level rollups → **crores**, two
decimals (`1.24 Cr`).

Never quote raw rupees in chat — it's hard to read at scale. Convert to
lakhs or crores depending on the audience.

In SQL, divide by `1e5` for lakhs and `1e7` for crores:
`round(sum(SALE_NET_AMOUNT) / 1e7, 2) AS net_sales_cr`.

Ask BIC about this:
- "Convert this to crores"
- "Why is this in lakhs and that in crores?"


## Time conventions — IST, fiscal year, working days

All dates and times in BIC are **IST (Asia/Kolkata)**. Sales are tagged to
the IST date the transaction completed. The `SALE_DATE` column is the IST
date; `SALE_DATETIME` includes the IST time.

The **fiscal year** runs **April 1 to March 31**. "FY26" means April 2025
through March 2026.

The **week** starts on **Monday**. Working days exclude Sundays for stores
that close on Sundays — but most VALLI stores are open 7 days, so check
the store's operating-day flag before excluding.

When computing month-to-date or year-to-date, **exclude today** if today's
sales are still incomplete. The standard cutoff is yesterday's end-of-day.

Ask BIC about this:
- "Are these times IST or UTC?"
- "What date does this week cover?"
- "Why does today's number look low?"