Current CRM and Data Challenges
- Wholesale application tracking spreadsheet covers 2023-2025 applications only
- Missing thousands of pre-2023 partners and anyone who changed emails
- No connection between application data and actual Shopify partner accounts
- Manual process requiring 3+ separate report pulls to create partner summaries
- Shopify contains actual partner data but missing key application details
- Company names mostly absent (only recent accounts have metafields)
- Onboard dates not tracked (only account creation dates available)
- Tag addition dates not recorded by Shopify
Partner Identification and Segmentation
- All wholesale partners tagged with “wholesale270” in Shopify
- Three segment categories (rule-based but manually assigned):
- Bulk buyer - purchasing for own use, not reselling
- Trusted health - reselling in health/medical settings
- Specialty retail - reselling in retail environments
- Segments can change over time (bulk buyer → trusted health most common)
- Customer ID will be added to CRM sheets this month for better matching
- Tag creation dates available in Shopify to identify first wholesale order vs onboard date
Reporting Requirements and Calculations
- Revenue calculation: gross minus taxes, no discounts or promotions offered
- All partners same pricing within segments, specialty retail has different SKU lines
- Key metrics needed:
- Lifetime spend and year-to-date spend
- Spend by product category (sparkling vs drink mix)
- Last order date for inactive partner identification
- Growth year-over-year calculations
- Geographic analysis (frequent question: “how many partners in San Diego?”)
- Refrigerator costs excluded from revenue (technically gifts, only pay shipping)
- Monthly reporting includes new partner onboarding numbers
Next Steps
- Laura/Madison to share wholesale data pull and segmentation spreadsheet via Slack
- Brainforge to replicate reporting logic in SQL and Snowflake
- Create master table joining CRM application data with Shopify partner data
- Build live dashboard by end of next month for real-time reporting
- Import historical company names into Shopify metafields where possible