Weekly Report Sunday 7PM Cron

This workflow automatically generates and sends weekly business performance reports to active youth entrepreneurs every Sunday at 7:15 PM EAT. It analyzes their daily profit tracking data, calculates key metrics, and delivers personalized reports via WhatsApp or SMS based on their preferred communication channel.

Purpose

No business context provided yet — add a context.md to enrich this documentation.

How It Works

1. Daily Schedule Check: Triggers every day at 7:15 PM EAT but only proceeds on Sundays
2. User Selection: Fetches all active youth entrepreneurs who aren't already in a weekly report stage
3. Data Analysis: For each user, calculates weekly metrics including total sales, costs, profit, best/worst performing days
4. Credit Tracking: Retrieves any active credit module data for comprehensive reporting
5. Trend Analysis: Compares current week's performance against the previous week
6. Report Generation: Creates a personalized weekly report with key insights and performance metrics
7. Data Storage: Saves the complete report to the database for historical tracking
8. Stage Management: Updates the user's current stage to indicate they're in the weekly report flow
9. Message Delivery: Sends the report opener message via WhatsApp or SMS based on user preference
10. Chat History: Logs the interaction for conversation continuity

Workflow Diagram

graph TD
    A[Every Day 7:30PM] --> B[Check Sunday]
    B --> C[Fetch Active Users]
    C --> D[Loop Users]
    D --> E[Compute Weekly Data]
    E --> F[Fetch Credit]
    F --> G[Fetch Previous Week]
    G --> H[Build Report]
    H --> I[Store Weekly Report]
    I --> J[Set Report Stage]
    J --> K[Is WhatsApp?]
    K -->|Yes| L[Send WhatsApp]
    K -->|No| M[Send SMS]
    L --> N[Save to Chat History]
    M --> N
    N --> D

Trigger

Schedule Trigger: Runs daily at 7:15 PM East Africa Time (EAT), but only executes the workflow on Sundays through a JavaScript filter.

Nodes Used

Node Type	Node Name	Purpose
Schedule Trigger	Every Day 7:30PM	Daily trigger at 7:15 PM EAT
Code	Check Sunday	Filters execution to Sundays only
Postgres	Fetch Active Users	Gets active entrepreneurs not in report stage
Split in Batches	Loop Users	Processes users one by one
Postgres	Compute Weekly Data	Calculates weekly business metrics
Postgres	Fetch Credit	Retrieves credit tracking information
Postgres	Fetch Previous Week	Gets last week's data for comparison
Code	Build Report	Generates personalized report content
Postgres	Store Weekly Report	Saves report to database
Postgres	Set Report Stage	Updates user's current stage
If	Is WhatsApp?	Routes message based on communication channel
Twilio	Send WhatsApp	Delivers report via WhatsApp
HTTP Request	Send SMS	Delivers report via SMS (Africa's Talking)
Postgres	Save to Chat History	Logs interaction for chat continuity

External Services & Credentials Required

• PostgreSQL Database: Stores user data, daily tracking, and weekly reports
• Twilio: WhatsApp message delivery
• Africa's Talking: SMS delivery for non-WhatsApp users

Required Credentials

• postgres: Database connection for user and report data
• twilioApi: Twilio account for WhatsApp messaging
• Africa's Talking API key (hardcoded in HTTP request node)

Environment Variables

No explicit environment variables are used. The workflow relies on: - Database connection settings via Postgres credentials - Twilio configuration via credential store - Africa's Talking API key embedded in the HTTP request node

Data Flow

Input

• Trigger: Time-based (Sunday 7:15 PM EAT)
• Database Queries: Active user records, daily profit tracking, credit data, previous reports

Processing

• Weekly metrics calculation (sales, costs, profit, trends)
• Report personalization with user's name and business data
• Message formatting for different channels (WhatsApp vs SMS)

Output

• Database Records: New weekly report entries, updated user stages
• Messages: Personalized report opener sent via WhatsApp or SMS
• Chat History: Logged interactions for conversation continuity

Error Handling

• Always Output Data: Several Postgres nodes are configured to continue even with empty results
• Continue on Error: The "Save to Chat History" node continues execution even if logging fails
• Graceful Filtering: Sunday check returns empty array for non-Sunday executions

Known Limitations

• Africa's Talking API key is hardcoded in the HTTP request node (should be moved to credentials)
• Emoji filtering for SMS removes all Unicode emoji characters
• Fixed business days assumption (26 days per month)
• No retry mechanism for failed message deliveries

Related Workflows

This workflow likely connects to: - Daily profit tracking workflows that populate the source data - User onboarding workflows that set initial stages - Interactive report response handlers for the numbered menu options

Setup Instructions

1. Import Workflow: Import the JSON into your n8n instance

2. Configure Database Connection:

   • Set up PostgreSQL credentials named "Postgres account"
   • Ensure database contains required tables: youthEntrepreneursReal, dailyProfitTracking, weeklyReports, creditTracking, n8n_chat_histories

3. Set Up Twilio:

   • Create Twilio credentials named "Twilio account"
   • Configure WhatsApp sandbox or production number (+254203892316)

4. Configure Africa's Talking:

   • Replace the hardcoded API key in the "Send SMS" node with your credentials
   • Update the sender ID (24436) if needed

5. Timezone Settings:

   • Workflow is set to Africa/Nairobi timezone
   • Adjust if your users are in different timezones

6. Test Setup:

   • Create test users in the database with onboardingStatus = 'active'
   • Add sample daily profit tracking data
   • Run workflow manually to verify message delivery

7. Activate Workflow: Enable the workflow to start automatic Sunday executions