Brainforge Knowledge

wholesale_customers_formatting_guide

7 min read

LMNT Wholesale Customers Data - Formatting Guide

Based on: Zero-Defect Analysis & Formatting Standards
Source Sheet: Wholesale Customers Data - Daily Updates RAW
Target Sheet: Wholesale Customers Data - Formatted (copy of RAW with formatting applied)

Step 1: Create a Live Copy (Formula-Based) of the RAW Sheet

Create Dynamic Live Copy

  1. Create a new sheet adjacent to the RAW sheet

    • Click the ”+” button to add a new sheet
    • Or right-click a sheet tab → Insert sheet
    • Rename it to: Wholesale Customers Data - Formatted

  2. In cell A1 of the new sheet, paste this formula:

={'Wholesale Customers Data - Daily Updates RAW'!A:AP}

This formula:

  • Creates a live link to all data from the RAW sheet (columns A through AP)
  • Automatically updates when the RAW sheet data changes
  • Preserves all data including headers
  • Allows you to apply formatting without affecting the source
  1. Add filters:
    • Select row 1 (the header row)
    • Data → Create a filter
    • This enables filtering on all columns

Note: This creates a live reference, so any changes to the RAW sheet will automatically appear in the formatted sheet. You can then apply all formatting to this sheet without affecting the source data.

Step 2: Apply Formatting Based on Client Principles

A. Units & Scale (Non-Negotiable)

Revenue & Currency Fields

Columns: TOTAL_REVENUE, TOTAL_TAX, AVERAGE_ORDER_VALUE

Formatting:

  • Custom number format: $#,##0 (whole dollars, no decimals per client standard)
  • Column headers should include unit: e.g., “Total Revenue ($)” or “Total Revenue (USD)”

Formula to apply in header (if needed):

="Total Revenue ($)"

Count Fields

Columns: TOTAL_ORDERS, DAYS_SINCE_LAST_ORDER, CUSTOMER_LIFETIME_DAYS_SINCE_CREATION, CUSTOMER_LIFETIME_DAYS_SINCE_FIRST_ORDER

Formatting:

  • Custom number format: #,##0 (whole numbers with thousands separator)
  • Column headers: “Total Orders (count)” or “Days Since Last Order (days)“

Percentage Fields

Note: No percentage columns in current data, but if added:

  • Format: 0% or 0.0% (0-1 decimal max per client standard)
  • Header: “Conversion Rate (%)“

B. Precision & Rounding

Dollars: Whole Numbers Only

Apply to: All currency columns

  • Format: $#,##0 (removes decimals)
  • This aligns with client standard: “Dollars: whole numbers rounded”

Implementation:

  1. Select currency columns (TOTAL_REVENUE, TOTAL_TAX, AVERAGE_ORDER_VALUE)
  2. Format → Number → Custom number format
  3. Enter: $#,##0

Percentages: 0-1 Decimal Max

If percentages are calculated:

  • Format: 0% or 0.0% (depending on precision needed)
  • Round using: ROUND(value, 1) for 1 decimal, ROUND(value, 0) for whole numbers

C. Date & Time Logic

Date Columns:

  • FIRST_ORDER_DATE
  • LAST_ORDER_DATE
  • FIRST_WHOLESALE_ORDER_DATE
  • CUSTOMER_CREATED_AT
  • APPLICATION_SUBMISSION_DATE
  • APPLICATION_ONBOARD_DATE
  • APPLICATION_AGREEMENT_SENT
  • APPLICATION_AGREEMENT_SIGNED

Formatting:

  • Format: MM/DD/YYYY (consistent date format)
  • Custom number format: MM/DD/YYYY

Time Logic (if calculating time windows):

  • Use formulas for date ranges (e.g., TODAY(), EDATE(), EOMONTH())
  • Label time logic explicitly (e.g., “Last 30 Days”, “YTD”, “Last 13 Weeks”)

Example formulas for time windows:

// Days since last order (already in data, but verify formula-based)
=IF(LAST_ORDER_DATE="", "", TODAY() - LAST_ORDER_DATE)
 
// Last 30 days filter
=AND(LAST_ORDER_DATE >= TODAY() - 30, LAST_ORDER_DATE <= TODAY())

D. Usability & Readability

Freeze Headers and Key Columns

  1. Freeze header row:

    • Select row 2
    • View → Freeze → 1 row

  2. Freeze key identifier columns (left-to-right priority):

    • Freeze first 3-5 columns: COMPANY_NAME, FULL_NAME, EMAIL, SEGMENT
    • View → Freeze → 3-5 columns
    • Note: Don’t freeze too many (per client standard: “avoid excessive horizontal scrolling”)

Organize Columns (Left-to-Right Priority)

Recommended order:

  1. Identifiers (Company Name, Contact Name, Email, Customer ID)
  2. Location (Address, City, State)
  3. Classification (Segment, Business Type, Status)
  4. Key Metrics (Total Orders, Total Revenue, Avg Order Value)
  5. Dates (First Order, Last Order, Days Since Last Order)
  6. Lifetime Metrics (Lifetime Days)
  7. Marketing (Email Consent, SMS Consent, Has Refrigerator)
  8. Application Details (Status, Dates, Discovery Source)

Column Width Optimization

  • Auto-fit columns: Select all → Format → Column width → Auto-fit
  • Or set specific widths:
    • Identifiers: 150-200px
    • Numbers: 100-120px
    • Dates: 120px
    • Text: 150-250px

Add Filters

  • Select row 1 (headers)
  • Data → Create a filter
  • This enables filtering on all columns

E. Formula Transparency

Distinguish Calculated vs Input Cells

Input cells (from RAW data): Keep as-is, no background color

Calculated cells (if adding):

  • Light blue background: #E3F2FD (RGB: 227, 242, 253)
  • Or light gray: #F5F5F5

Example: If adding a calculated column like “Revenue per Order”:

// In new column header
="Revenue per Order ($)" // Label with unit
 
// In data cells
=IF(TOTAL_ORDERS > 0, ROUND(TOTAL_REVENUE / TOTAL_ORDERS, 0), 0)
// Format as currency: $#,##0
// Apply light blue background to indicate calculated

Label Assumptions

If adding assumptions or constants:

  • Create a separate section at top or bottom of sheet
  • Label clearly: “Assumptions” or “Constants”
  • Use distinct formatting (e.g., yellow background)

Step 3: Column-Specific Formatting Recommendations

Text Columns (Standard Formatting)

Columns: COMPANY_NAME, FULL_NAME, EMAIL, PHONE, ADDRESS_1, ADDRESS_2, CITY, STATE, SEGMENT, APPLICATION_BUSINESS_TYPE, PARTNER_STATUS, etc.

Formatting:

  • Text format (default)
  • Left-aligned
  • Wrap text if needed for long values

Boolean Columns

Columns: HAS_REFRIGERATOR, APPLICATION_PRICING_ACTIVATED, APPLICATION_HAS_PHYSICAL_STORE

Formatting:

  • Convert to readable text: TRUE → Yes, FALSE → No
  • Or use conditional formatting:
    • TRUE → Green background
    • FALSE → Light gray background

Formula to convert (if needed):

=IF(HAS_REFRIGERATOR = TRUE, "Yes", IF(HAS_REFRIGERATOR = FALSE, "No", ""))

Columns: EMAIL_MARKETING_CONSENT_STATE, SMS_MARKETING_CONSENT_STATE

Formatting:

  • Text format
  • Consider conditional formatting:
    • subscribed → Green
    • not_subscribed → Yellow
    • unsubscribed → Red

Status Column

Column: PARTNER_STATUS

Formatting:

  • Text format
  • Conditional formatting:
    • Active → Green background (#D9EAD3)
    • Churned → Red background (#F4CCCC)
    • REMOVE WHOLESALE STATUS → Gray background (#D9D9D9)

Days Since Last Order (Activity Indicator)

Column: DAYS_SINCE_LAST_ORDER

Formatting:

  • Number format: #,##0 (whole numbers)
  • Conditional formatting (traffic light):
    • < 90 days → Green (#D9EAD3) - Recently active
    • 90-180 days → Yellow (#FFF2CC) - At risk
    • > 180 days → Red (#F4CCCC) - Churned risk

Step 4: Conditional Formatting Rules

Revenue Tiers (Highlight High-Value Customers)

Column: TOTAL_REVENUE

Rules:

  • > $10,000 → Bold text, Blue color (#1155CC)
  • $1,000 - $10,000 → Default formatting
  • < $1,000 → Light gray text (#999999)

Order Frequency

Column: TOTAL_ORDERS

Rules:

  • > 10 orders → Green background
  • 5-10 orders → Yellow background
  • < 5 orders → Default

Step 5: Data Validation & Sense-Checking

Plausibility Checks

Add helper columns or notes for validation:

  1. Order-of-magnitude check:

    • Flag if TOTAL_REVENUE > $100,000 (verify large customers)
    • Flag if AVERAGE_ORDER_VALUE > $5,000 (verify unusual orders)

  2. Percentage bounds:

    • If calculating percentages, ensure 0-100% range

  3. Directionality:

    • LAST_ORDER_DATE should be >= FIRST_ORDER_DATE
    • DAYS_SINCE_LAST_ORDER should be reasonable (not negative, not > 10 years)

Consistency Checks

Cross-reference validations:

  • TOTAL_REVENUE should equal sum of individual orders (if available)
  • AVERAGE_ORDER_VALUE should equal TOTAL_REVENUE / TOTAL_ORDERS (verify)
  • Lifetime days should be consistent with date differences

Step 6: Final Formatting Checklist

Before sharing the formatted sheet:

  • All currency columns formatted as $#,##0 (whole dollars)
  • All date columns formatted as MM/DD/YYYY
  • All number columns formatted with thousands separator (#,##0)
  • Headers include units where applicable (e.g., “Total Revenue ($)”)
  • Header row frozen
  • Key identifier columns frozen (3-5 columns max)
  • Filters applied to header row
  • Conditional formatting applied (status, days since last order, revenue tiers)
  • Column widths optimized (no excessive horizontal scrolling)
  • Calculated cells (if any) have distinct background color
  • Boolean values converted to Yes/No for readability
  • Sense-checking completed (plausibility, consistency, coherence)

Quick Reference: Number Formats

Data TypeFormat CodeExample
Currency (whole dollars)$#,##0$1,234
Currency (with cents)$#,##0.00$1,234.56
Whole numbers#,##01,234
Percentages (0 decimals)0%45%
Percentages (1 decimal)0.0%45.2%
DatesMM/DD/YYYY01/15/2025

Implementation Order

  1. Duplicate the RAW sheet → Wholesale Customers Data - Formatted
  2. Add filters to row 1
  3. Freeze header row and key columns
  4. Apply number formatting (currency, dates, integers)
  5. Apply conditional formatting (status, days, revenue)
  6. Optimize column widths and organization
  7. Convert boolean values to Yes/No (if desired)
  8. Add unit labels to headers (if needed)
  9. Sense-check the data
  10. Final review against formatting principles

Notes

  • Keep the RAW sheet untouched as source of truth
  • The formatted sheet should be clearly labeled as formatted/derived
  • If adding calculated columns, clearly distinguish them from source data
  • Document any assumptions or known data quality issues
  • Ensure formulas work when data refreshes (use relative references)

Graph View

Backlinks