wholesale_customers_report_specification

LMNT Wholesale Customers Report - Formatting Specification

Report Name: Report - All Wholesale Customers
Source Data: Brainforge x LMNT_ Wholesale Customers x Application Data - Wholesale Customers Data - Daily Updates RAW.csv
Purpose: Create a clean, formatted view of wholesale customer data for reporting and analysis

---

Column Selection & Formatting Plan

Recommended Columns for Report (Priority Order)

1. Customer Identification & Contact

Source Column	Report Column Name	Format	Notes
COMPANY_NAME	Company Name	Text, Left Aligned	Primary identifier
FULL_NAME	Contact Name	Text, Left Aligned	Secondary identifier
EMAIL	Email	Text, Left Aligned	Hyperlink if email
PHONE	Phone	Text, Left Aligned	Format: (XXX) XXX-XXXX if possible
CUSTOMER_ID	Customer ID	Text, Left Aligned	Keep as reference

2. Location Information

Source Column	Report Column Name	Format	Notes
ADDRESS_1	Address Line 1	Text, Left Aligned
ADDRESS_2	Address Line 2	Text, Left Aligned
CITY	City	Text, Left Aligned
STATE	State	Text, Left Aligned

3. Business Classification

Source Column	Report Column Name	Format	Notes
SEGMENT	Segment	Text, Left Aligned	Trusted Health, Bulk Buyer, Specialty Retail
APPLICATION_BUSINESS_TYPE	Business Type	Text, Left Aligned	Fitness/Gym, Health Practitioner, etc.
PARTNER_STATUS	Status	Text, Center Aligned	Active, Churned, etc.

4. Order & Revenue Metrics

Source Column	Report Column Name	Format	Notes
TOTAL_ORDERS	Total Orders	Number, 0 decimals, Right Aligned	Integer count
TOTAL_REVENUE	Total Revenue	Currency ($), 2 decimals, Right Aligned	Format: $1,234.56
AVERAGE_ORDER_VALUE	Avg Order Value	Currency ($), 2 decimals, Right Aligned	Format: $1,234.56
TOTAL_TAX	Total Tax	Currency ($), 2 decimals, Right Aligned	Format: $1,234.56
LAST_ORDER_ID	Last Order ID	Text, Left Aligned	Reference only

5. Date Fields

Source Column	Report Column Name	Format	Notes
FIRST_ORDER_DATE	First Order Date	Date (MM/DD/YYYY), Center Aligned	Format: 01/15/2025
LAST_ORDER_DATE	Last Order Date	Date (MM/DD/YYYY), Center Aligned	Format: 01/15/2025
FIRST_WHOLESALE_ORDER_DATE	First Wholesale Order	Date (MM/DD/YYYY), Center Aligned	Format: 01/15/2025
APPLICATION_SUBMISSION_DATE	Application Submitted	Date (MM/DD/YYYY), Center Aligned	Format: 2025-01-15 → 01/15/2025
APPLICATION_ONBOARD_DATE	Onboarded Date	Date (MM/DD/YYYY), Center Aligned	Format: 2025-01-15 → 01/15/2025
CUSTOMER_CREATED_AT	Customer Created	Date (MM/DD/YYYY), Center Aligned	Format: 5/6/2025 → 05/06/2025

6. Customer Lifetime & Activity

Source Column	Report Column Name	Format	Notes
DAYS_SINCE_LAST_ORDER	Days Since Last Order	Number, 0 decimals, Right Aligned	Integer
CUSTOMER_LIFETIME_DAYS_SINCE_CREATION	Lifetime Days (Creation)	Number, 0 decimals, Right Aligned	Integer
CUSTOMER_LIFETIME_DAYS_SINCE_FIRST_ORDER	Lifetime Days (First Order)	Number, 0 decimals, Right Aligned	Integer

7. Marketing & Engagement

Source Column	Report Column Name	Format	Notes
EMAIL_MARKETING_CONSENT_STATE	Email Consent	Text, Center Aligned	subscribed, not_subscribed, unsubscribed
SMS_MARKETING_CONSENT_STATE	SMS Consent	Text, Center Aligned	subscribed, not_subscribed, unsubscribed
HAS_REFRIGERATOR	Has Refrigerator	Text, Center Aligned	Yes/No (convert TRUE/FALSE)

8. Application & Onboarding Details

Source Column	Report Column Name	Format	Notes
APPLICATION_STATUS	Application Status	Text, Center Aligned	Completed, Purchased, In Progress, Declined
APPLICATION_HAS_PHYSICAL_STORE	Has Physical Store	Text, Center Aligned	Yes/No (convert Yes/No)
APPLICATION_NUMBER_OF_LOCATIONS	Number of Locations	Text, Left Aligned	1, 2-5, 11-50, 51+, etc.
APPLICATION_WEBSITE	Website	Text, Left Aligned	Hyperlink if URL
APPLICATION_SOCIAL_HANDLES	Social Handles	Text, Left Aligned
APPLICATION_AGREEMENT_SENT	Agreement Sent	Date (MM/DD/YYYY), Center Aligned	If date exists
APPLICATION_AGREEMENT_SIGNED	Agreement Signed	Date (MM/DD/YYYY), Center Aligned	If date exists
APPLICATION_PRICING_ACTIVATED	Pricing Activated	Text, Center Aligned	Yes/No (convert TRUE/FALSE)
APPLICATION_PARTNER_DISCOVERY	Discovery Source	Text, Left Aligned	How they found LMNT

---

Columns to EXCLUDE from Report

These columns are less useful for a general report view:

• APPLICATION_MATCH_TYPE - Technical field
• APPLICATION_SUBMISSION_ID - Technical reference
• APPLICATION_OPT_IN_MARKETING - Redundant with consent states
• APPLICATION_OPT_IN_STORE_LOCATOR - Less critical for general report

---

Formatting Rules by Data Type

Currency Fields

• Format: $#,##0.00
• Examples: $342.00, $1,234.56, $27,000.00
• Columns: TOTAL_REVENUE, AVERAGE_ORDER_VALUE, TOTAL_TAX

Date Fields

• Format: MM/DD/YYYY
• Input formats to handle:
  • 7/6/2025 → 07/06/2025
  • 2025-05-01 → 05/01/2025
  • 5/6/2025 → 05/06/2025
• Columns: All date columns listed above

Number Fields (Integers)

• Format: #,##0 (no decimals, with thousands separator)
• Examples: 1, 1,234, 13,000
• Columns: TOTAL_ORDERS, DAYS_SINCE_LAST_ORDER, lifetime days fields

Boolean Fields

• Convert: TRUE → Yes, FALSE → No
• Columns: HAS_REFRIGERATOR, APPLICATION_PRICING_ACTIVATED

Text Fields

• Standard text formatting
• Consider: Truncate very long text fields if needed
• Hyperlink: Email addresses and URLs should be clickable links

---

Column Order Recommendation

Suggested order for readability:

1. Identification (Company Name, Contact Name, Email, Phone, Customer ID)
2. Location (Address 1, Address 2, City, State)
3. Business Classification (Segment, Business Type, Status)
4. Key Metrics (Total Orders, Total Revenue, Avg Order Value)
5. Dates (First Order, Last Order, First Wholesale Order)
6. Activity (Days Since Last Order, Lifetime Days)
7. Marketing (Email Consent, SMS Consent, Has Refrigerator)
8. Application Details (Application Status, Onboarded Date, Discovery Source)
9. Additional Details (Website, Social Handles, Agreement dates)

---

Implementation Steps

Option 1: Google Sheets Formula Approach

1. Create new sheet: “Report - All Wholesale Customers”
2. Use QUERY() or ARRAYFORMULA() to pull and transform data from RAW sheet
3. Apply formatting rules using custom number formats
4. Use conditional formatting for status indicators

Option 2: Google Apps Script Approach

1. Create script to read RAW data
2. Transform and format data programmatically
3. Write formatted data to report sheet
4. Set up trigger for daily updates

Option 3: Manual Setup with Formulas

1. Copy selected columns to report sheet
2. Add helper columns for transformations (e.g., TRUE/FALSE → Yes/No)
3. Apply formatting manually
4. Use formulas to maintain sync with RAW data

---

Conditional Formatting Suggestions

Status Indicators

• Active: Green background
• Churned: Red background
• REMOVE WHOLESALE STATUS: Gray background

Revenue Tiers

• High revenue (>$10,000): Bold, blue text
• Medium revenue ($1,000-$10,000): Standard
• Low revenue (<$1,000): Light gray text

Days Since Last Order

• < 90 days: Green (recently active)
• 90-180 days: Yellow (at risk)

• 180 days: Red (churned risk)

---

Next Steps

1. Review column selection - Confirm which columns to include/exclude
2. Confirm formatting preferences - Adjust date formats, currency symbols, etc.
3. Choose implementation approach - Formula-based vs. Script-based
4. Test with sample data - Validate transformations work correctly
5. Set up automation - Ensure report updates when RAW data refreshes

---

Questions to Resolve

1. Should we include all application fields or only key ones?
2. Do we need separate sheets for different segments (Trusted Health, Bulk Buyer, Specialty Retail)?
3. Should we add calculated fields (e.g., Revenue per Day, Order Frequency)?
4. Do we need filtering/sorting capabilities in the report?
5. Should we create summary statistics at the top of the report?