wholesale_customers_report_implementation

LMNT Wholesale Customers Report - Implementation Guide

This guide provides step-by-step instructions and formulas to create the formatted report sheet.

---

Step 1: Create the Report Sheet

1. In your Google Sheet, create a new sheet named: “Report - All Wholesale Customers”
2. Keep the RAW data sheet as-is (don’t modify it)

---

Step 2: Set Up Column Headers

In row 1 of the report sheet, add these headers (in this order):

Company Name | Contact Name | Email | Phone | Customer ID | Address Line 1 | Address Line 2 | City | State | Segment | Business Type | Status | Total Orders | Total Revenue | Avg Order Value | Total Tax | Last Order ID | First Order Date | Last Order Date | First Wholesale Order | Days Since Last Order | Lifetime Days (Creation) | Lifetime Days (First Order) | Customer Created | Email Consent | SMS Consent | Has Refrigerator | Application Status | Application Submitted | Onboarded Date | Has Physical Store | Number of Locations | Website | Social Handles | Agreement Sent | Agreement Signed | Pricing Activated | Discovery Source

---

Step 3: Formula-Based Approach (Recommended for Start)

Option A: Using QUERY() Function

This pulls data from the RAW sheet and applies basic transformations:

=QUERY('RAW Sheet Name'!A:AP, 
  "SELECT 
    E, D, B, C, A, G, H, I, J, F, AC, W, O, P, R, Q, N, S, T, U, V, X, Y, Z, K, L, M, AD, AH, AI, AE, AF, AG, AJ, AK, AL, AM, AN
  WHERE E IS NOT NULL
  ORDER BY P DESC", 1)

Note: Adjust column letters based on your RAW sheet structure. This formula:

• Selects columns in the order specified
• Filters out rows where COMPANY_NAME (E) is empty
• Orders by TOTAL_REVENUE (P) descending
• Includes header row (the 1 at the end)

Option B: Using ARRAYFORMULA() with Individual Column References

More control over transformations:

In A2 (Company Name):

=ARRAYFORMULA(IF('RAW Sheet Name'!E2:E="", "", 'RAW Sheet Name'!E2:E))

In B2 (Contact Name):

=ARRAYFORMULA(IF('RAW Sheet Name'!D2:D="", "", 'RAW Sheet Name'!D2:D))

In C2 (Email - with hyperlink):

=ARRAYFORMULA(IF('RAW Sheet Name'!B2:B="", "", HYPERLINK("mailto:" & 'RAW Sheet Name'!B2:B, 'RAW Sheet Name'!B2:B)))

In D2 (Phone):

=ARRAYFORMULA(IF('RAW Sheet Name'!C2:C="", "", 'RAW Sheet Name'!C2:C))

In M2 (Total Orders - formatted as number):

=ARRAYFORMULA(IF('RAW Sheet Name'!O2:O="", "", VALUE('RAW Sheet Name'!O2:O)))

In N2 (Total Revenue - formatted as currency):

=ARRAYFORMULA(IF('RAW Sheet Name'!P2:P="", "", VALUE('RAW Sheet Name'!P2:P)))

In O2 (Avg Order Value - formatted as currency):

=ARRAYFORMULA(IF('RAW Sheet Name'!R2:R="", "", VALUE('RAW Sheet Name'!R2:R)))

In Q2 (Total Tax - formatted as currency):

=ARRAYFORMULA(IF('RAW Sheet Name'!Q2:Q="", "", VALUE('RAW Sheet Name'!Q2:Q)))

In S2 (First Order Date - convert to MM/DD/YYYY):

=ARRAYFORMULA(IF('RAW Sheet Name'!S2:S="", "", 
  IF(ISNUMBER('RAW Sheet Name'!S2:S), 
    TEXT('RAW Sheet Name'!S2:S, "MM/DD/YYYY"),
    IF(ISDATE('RAW Sheet Name'!S2:S),
      TEXT('RAW Sheet Name'!S2:S, "MM/DD/YYYY"),
      'RAW Sheet Name'!S2:S))))

In T2 (Last Order Date - convert to MM/DD/YYYY):

=ARRAYFORMULA(IF('RAW Sheet Name'!T2:T="", "", 
  IF(ISNUMBER('RAW Sheet Name'!T2:T), 
    TEXT('RAW Sheet Name'!T2:T, "MM/DD/YYYY"),
    IF(ISDATE('RAW Sheet Name'!T2:T),
      TEXT('RAW Sheet Name'!T2:T, "MM/DD/YYYY"),
      'RAW Sheet Name'!T2:T))))

In Y2 (Has Refrigerator - convert TRUE/FALSE to Yes/No):

=ARRAYFORMULA(IF('RAW Sheet Name'!M2:M="", "", 
  IF('RAW Sheet Name'!M2:M=TRUE, "Yes", 
  IF('RAW Sheet Name'!M2:M=FALSE, "No", 'RAW Sheet Name'!M2:M))))

In AM2 (Pricing Activated - convert TRUE/FALSE to Yes/No):

=ARRAYFORMULA(IF('RAW Sheet Name'!AL2:AL="", "", 
  IF('RAW Sheet Name'!AL2:AL=TRUE, "Yes", 
  IF('RAW Sheet Name'!AL2:AL=FALSE, "No", 'RAW Sheet Name'!AL2:AL))))

---

Step 4: Apply Number Formatting

After data is populated, apply custom number formats:

Currency Columns (N, O, Q - Total Revenue, Avg Order Value, Total Tax)

1. Select the column
2. Format → Number → Custom number format
3. Enter: $#,##0.00

Date Columns (S, T, U, Z, AH, AI, AK, AL)

1. Select the column
2. Format → Number → Date
3. Choose: MM/DD/YYYY or custom format MM/DD/YYYY

Integer Columns (M, V, X, Y - Orders, Days)

1. Select the column
2. Format → Number → Custom number format
3. Enter: #,##0 (no decimals, with thousands separator)

---

Step 5: Apply Conditional Formatting

Status Column (Column K)

1. Select column K (Status)
2. Format → Conditional formatting
3. Add rules:
   • Text contains “Active” → Background: Green (#d9ead3)
   • Text contains “Churned” → Background: Red (#f4cccc)
   • Text contains “REMOVE” → Background: Gray (#d9d9d9)

Days Since Last Order (Column V)

1. Select column V
2. Format → Conditional formatting
3. Add rules:
   • Less than 90 → Background: Green (#d9ead3)
   • Between 90 and 180 → Background: Yellow (#fff2cc)
   • Greater than 180 → Background: Red (#f4cccc)

Revenue Tiers (Column N - Total Revenue)

1. Select column N
2. Format → Conditional formatting
3. Add rules:
   • Greater than 10000 → Text: Bold, Blue (#1155cc)
   • Between 1000 and 10000 → Default
   • Less than 1000 → Text: Light gray (#999999)

---

Step 6: Freeze Header Row

1. Select row 2
2. View → Freeze → 1 row
3. Optionally freeze first few columns (Company Name, Contact Name, Email)

---

Step 7: Add Filters

1. Select row 1 (headers)
2. Data → Create a filter
3. This allows filtering by any column

---

Alternative: Google Apps Script Approach

If you prefer automation, here’s a script that can transform the data:

function formatWholesaleReport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheet = ss.getSheetByName('RAW Sheet Name'); // Update with actual sheet name
  const reportSheet = ss.getSheetByName('Report - All Wholesale Customers');
  
  // Clear existing data (keep headers)
  if (reportSheet.getLastRow() > 1) {
    reportSheet.getRange(2, 1, reportSheet.getLastRow() - 1, reportSheet.getLastColumn()).clear();
  }
  
  // Get raw data
  const rawData = rawSheet.getDataRange().getValues();
  const headers = rawData[0];
  
  // Find column indices
  const colMap = {
    companyName: headers.indexOf('COMPANY_NAME'),
    fullName: headers.indexOf('FULL_NAME'),
    email: headers.indexOf('EMAIL'),
    phone: headers.indexOf('PHONE'),
    customerId: headers.indexOf('CUSTOMER_ID'),
    address1: headers.indexOf('ADDRESS_1'),
    address2: headers.indexOf('ADDRESS_2'),
    city: headers.indexOf('CITY'),
    state: headers.indexOf('STATE'),
    segment: headers.indexOf('SEGMENT'),
    businessType: headers.indexOf('APPLICATION_BUSINESS_TYPE'),
    status: headers.indexOf('PARTNER_STATUS'),
    totalOrders: headers.indexOf('TOTAL_ORDERS'),
    totalRevenue: headers.indexOf('TOTAL_REVENUE'),
    avgOrderValue: headers.indexOf('AVERAGE_ORDER_VALUE'),
    totalTax: headers.indexOf('TOTAL_TAX'),
    lastOrderId: headers.indexOf('LAST_ORDER_ID'),
    firstOrderDate: headers.indexOf('FIRST_ORDER_DATE'),
    lastOrderDate: headers.indexOf('LAST_ORDER_DATE'),
    firstWholesaleOrder: headers.indexOf('FIRST_WHOLESALE_ORDER_DATE'),
    daysSinceLastOrder: headers.indexOf('DAYS_SINCE_LAST_ORDER'),
    lifetimeDaysCreation: headers.indexOf('CUSTOMER_LIFETIME_DAYS_SINCE_CREATION'),
    lifetimeDaysFirstOrder: headers.indexOf('CUSTOMER_LIFETIME_DAYS_SINCE_FIRST_ORDER'),
    customerCreated: headers.indexOf('CUSTOMER_CREATED_AT'),
    emailConsent: headers.indexOf('EMAIL_MARKETING_CONSENT_STATE'),
    smsConsent: headers.indexOf('SMS_MARKETING_CONSENT_STATE'),
    hasRefrigerator: headers.indexOf('HAS_REFRIGERATOR'),
    appStatus: headers.indexOf('APPLICATION_STATUS'),
    appSubmitted: headers.indexOf('APPLICATION_SUBMISSION_DATE'),
    onboarded: headers.indexOf('APPLICATION_ONBOARD_DATE'),
    hasPhysicalStore: headers.indexOf('APPLICATION_HAS_PHYSICAL_STORE'),
    numLocations: headers.indexOf('APPLICATION_NUMBER_OF_LOCATIONS'),
    website: headers.indexOf('APPLICATION_WEBSITE'),
    socialHandles: headers.indexOf('APPLICATION_SOCIAL_HANDLES'),
    agreementSent: headers.indexOf('APPLICATION_AGREEMENT_SENT'),
    agreementSigned: headers.indexOf('APPLICATION_AGREEMENT_SIGNED'),
    pricingActivated: headers.indexOf('APPLICATION_PRICING_ACTIVATED'),
    discovery: headers.indexOf('APPLICATION_PARTNER_DISCOVERY')
  };
  
  // Transform data
  const formattedData = [];
  for (let i = 1; i < rawData.length; i++) {
    const row = rawData[i];
    if (!row[colMap.companyName]) continue; // Skip empty rows
    
    formattedData.push([
      row[colMap.companyName] || '',
      row[colMap.fullName] || '',
      row[colMap.email] || '',
      row[colMap.phone] || '',
      row[colMap.customerId] || '',
      row[colMap.address1] || '',
      row[colMap.address2] || '',
      row[colMap.city] || '',
      row[colMap.state] || '',
      row[colMap.segment] || '',
      row[colMap.businessType] || '',
      row[colMap.status] || '',
      row[colMap.totalOrders] || 0,
      row[colMap.totalRevenue] || 0,
      row[colMap.avgOrderValue] || 0,
      row[colMap.totalTax] || 0,
      row[colMap.lastOrderId] || '',
      formatDate(row[colMap.firstOrderDate]),
      formatDate(row[colMap.lastOrderDate]),
      formatDate(row[colMap.firstWholesaleOrder]),
      row[colMap.daysSinceLastOrder] || 0,
      row[colMap.lifetimeDaysCreation] || 0,
      row[colMap.lifetimeDaysFirstOrder] || 0,
      formatDate(row[colMap.customerCreated]),
      row[colMap.emailConsent] || '',
      row[colMap.smsConsent] || '',
      row[colMap.hasRefrigerator] === true ? 'Yes' : (row[colMap.hasRefrigerator] === false ? 'No' : ''),
      row[colMap.appStatus] || '',
      formatDate(row[colMap.appSubmitted]),
      formatDate(row[colMap.onboarded]),
      row[colMap.hasPhysicalStore] || '',
      row[colMap.numLocations] || '',
      row[colMap.website] || '',
      row[colMap.socialHandles] || '',
      formatDate(row[colMap.agreementSent]),
      formatDate(row[colMap.agreementSigned]),
      row[colMap.pricingActivated] === true ? 'Yes' : (row[colMap.pricingActivated] === false ? 'No' : ''),
      row[colMap.discovery] || ''
    ]);
  }
  
  // Write data to report sheet
  if (formattedData.length > 0) {
    reportSheet.getRange(2, 1, formattedData.length, formattedData[0].length).setValues(formattedData);
    
    // Apply formatting
    applyFormatting(reportSheet, formattedData[0].length);
  }
}
 
function formatDate(dateValue) {
  if (!dateValue) return '';
  if (dateValue instanceof Date) {
    return Utilities.formatDate(dateValue, Session.getScriptTimeZone(), 'MM/dd/yyyy');
  }
  // Try to parse string dates
  if (typeof dateValue === 'string') {
    const date = new Date(dateValue);
    if (!isNaN(date.getTime())) {
      return Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd/yyyy');
    }
  }
  return dateValue;
}
 
function applyFormatting(sheet, numCols) {
  // Format currency columns (14, 15, 16 - Total Revenue, Avg Order Value, Total Tax)
  const currencyCols = [14, 15, 16];
  currencyCols.forEach(col => {
    sheet.getRange(2, col, sheet.getLastRow() - 1, 1).setNumberFormat('$#,##0.00');
  });
  
  // Format date columns
  const dateCols = [18, 19, 20, 24, 29, 30, 33, 34]; // Adjust based on your column order
  dateCols.forEach(col => {
    sheet.getRange(2, col, sheet.getLastRow() - 1, 1).setNumberFormat('MM/dd/yyyy');
  });
  
  // Format integer columns
  const intCols = [13, 21, 22, 23]; // Total Orders, Days columns
  intCols.forEach(col => {
    sheet.getRange(2, col, sheet.getLastRow() - 1, 1).setNumberFormat('#,##0');
  });
}

To use this script:

1. Extensions → Apps Script
2. Paste the code
3. Update 'RAW Sheet Name' with your actual RAW sheet name
4. Run formatWholesaleReport() function
5. Optionally set up a time-driven trigger to run daily

---

Quick Start Checklist

• Create “Report - All Wholesale Customers” sheet
• Add column headers
• Set up formulas (QUERY or ARRAYFORMULA approach)
• Apply number formatting (currency, dates, integers)
• Apply conditional formatting (status, days, revenue)
• Freeze header row
• Add filters
• Test with sample data
• Verify all transformations work correctly

---

Troubleshooting

Dates not formatting correctly

• Check if dates are stored as text vs. date values
• Use DATEVALUE() function to convert text dates
• Ensure timezone settings are correct

Currency showing as numbers

• Apply custom number format: $#,##0.00
• Ensure values are numeric (not text)

TRUE/FALSE not converting

• Check if values are actually boolean vs. text strings
• Use IF statements to handle both cases

Performance issues with large datasets

• Consider using Apps Script for >10,000 rows
• Use QUERY() with LIMIT for testing
• Optimize formulas (avoid volatile functions)