WhatsApp Automation Platform for Affiliate Management

Project Overview

Developed a WhatsApp-based affiliate management control panel for Youvit that streamlines communication workflows through template-based messaging, automated scheduling, and Google Sheets integration for affiliate data management.

The system enables the team to manage affiliate onboarding, send personalized broadcast messages, and track communication history through a centralized dashboard.

The Challenge

Youvit's affiliate team needed to manage communications with their growing affiliate network more efficiently:

Manual Communication Process

• Time-consuming individual message composition and sending
• Repetitive welcome messages for new affiliate onboarding
• Lack of standardized message templates across team members

Data Management Issues

• Affiliate registration data scattered across Google Sheets
• No centralized system to track communication status
• Manual status updates between "new" and "contacted" affiliates

Scheduling Limitations

• No system for automated recurring communications
• Difficulty coordinating message timing across the team
• Limited ability to plan communication campaigns in advance

The Solution

Built a Next.js 15 web application that integrates WhatsApp messaging capabilities with Google Sheets data management, providing a unified control panel for affiliate communications.

Core Features

Template Management System

• Create and edit message templates with dynamic parameter support
• Template categorization (general, marketing, onboarding, education, recognition, promotion)
• Real-time preview with parameter substitution
• Support for bold text formatting and clickable links

Google Sheets Integration

• Real-time sync with affiliate registration forms
• Automatic detection of new affiliates (blank status column)
• Status management workflow (new → contacted)
• Contact information extraction and formatting

WhatsApp Messaging via WAHA API

• Send individual and bulk messages through WAHA (WhatsApp HTTP API)
• Automatic phone number formatting for Indonesian numbers
• Rate-limited sending with configurable delays (3-5 seconds between messages)
• Session status monitoring and connection health checks

Scheduling System

• One-time message scheduling with date/time selection
• Recurring schedules using cron expressions (daily, weekly, monthly patterns)
• Schedule management dashboard with status tracking
• Execution history with success/failure rates

Affiliate Management Interface

• Onboarding Page: Process new affiliate registrations with welcome messages
• Active Affiliates Page: View and manage contacted affiliates
• Contact selection interface for targeted messaging
• Manual recipient input with phone number validation

Dashboard Analytics

• Real-time metrics: new affiliates count, active affiliates count, active schedules
• Quick action buttons for common tasks
• WhatsApp connection status indicator
• Recent activity summary

Technical Architecture

Frontend

• Next.js 15 with App Router and Server Components
• React 19 with TypeScript in strict mode
• Tailwind CSS v4 with Lightning CSS for styling
• shadcn/ui components for consistent UI elements

Backend API

• RESTful API routes for affiliates, messages, templates, and schedules
• Prisma ORM with PostgreSQL database
• Node-cron for schedule execution
• Middleware-based authentication with cookie sessions

External Integrations

• Google Sheets API using service account authentication
• WAHA API for WhatsApp message delivery
• Phone number formatting for Indonesian WhatsApp format

Database Schema

• Templates table with parameters relationship
• Schedules table with recipients and execution history
• Schedule parameters for dynamic content

Key Capabilities

Automated Affiliate Onboarding

• Detect new affiliate registrations from Google Sheets
• Send standardized welcome messages with sample request links
• Update affiliate status from "new" to "contacted"
• Track onboarding completion rates

Template-Based Messaging

• Dynamic parameter insertion: {name} for personalization
• Static parameters for campaign-specific content (promo codes, links)
• Template preview with actual affiliate data
• Message formatting with WhatsApp-compatible bold text

Flexible Scheduling

• Cron-based recurring schedules (e.g., "0 9 * * 1" for weekly Monday 9 AM)
• One-time scheduling with date/time picker
• Timezone handling for Jakarta time (Asia/Jakarta)
• Schedule pause/resume functionality

Contact Management

• Import contacts from Google Sheets affiliate data
• Manual phone number input with validation
• Contact deduplication based on phone numbers
• Search and filter capabilities

Results & Impact

Process Automation

• Automated welcome message workflow for new affiliate onboarding
• Standardized message templates ensuring consistent communication
• Reduced manual data entry through Google Sheets integration

Operational Efficiency

• Centralized dashboard for all affiliate communication tasks
• Bulk messaging capabilities with personalization
• Scheduled messaging reduces manual intervention

Data Organization

• Unified view of affiliate data from Google Sheets
• Clear status tracking (new/contacted) for onboarding pipeline
• Communication history and delivery tracking

System Reliability

• WAHA API integration provides stable WhatsApp connectivity
• Error handling and retry mechanisms for failed messages
• Session monitoring with automatic reconnection

Technical Implementation Details

Authentication System

• Environment-based admin credentials
• HTTP-only cookie sessions
• Middleware protection for all authenticated routes

Message Processing

• Parameter replacement engine for dynamic content
• Phone number normalization for Indonesian format
• Rate limiting to prevent WhatsApp API blocks

Google Sheets Workflow

• Service account authentication for API access
• Real-time data synchronization
• Column mapping for affiliate registration forms

Scheduling Engine

• Cron expression validation and parsing
• Job queue management with execution tracking
• Error handling and retry logic for failed deliveries

This control panel serves as the central hub for Youvit's affiliate communication operations, providing the tools needed to manage their affiliate network efficiently while maintaining personalized communication quality.