Scrape Upwork job listings & generate daily email reports with Apify & Google Sheets

This automated n8n workflow scrapes job listings from Upwork using Apify, processes and cleans the data, and generates daily email reports with job summaries. The system uses Google Sheets for data storage and keyword management, providing a comprehensive solution for tracking relevant job opportunities and market trends. What is Apify? Apify is a web scraping and automation platform that provides reliable APIs for extracting data from websites like Upwork. It handles the complexities of web scraping including rate limiting, proxy management, and data extraction while maintaining compliance with website terms of service. Good to Know Apify API calls may incur costs based on usage; check Apify pricing for details Google Sheets access must be properly authorized to avoid data sync issues The workflow includes data cleaning and deduplication to ensure high-quality results Email reports provide structured summaries for easy review and decision-making Keyword management through Google Sheets allows for flexible job targeting How It Works The workflow is organized into three main phases: Phase 1: Job Scraping & Initial Processing This phase handles the core data collection and initial storage: 1. Trigger Manual Run - Manually starts the workflow for on-demand job scraping 2. Fetch Keywords from Google Sheet - Reads the list of job-related keywords from the All Keywords sheet 3. Loop Through Keywords - Iterates over each keyword to trigger Apify scraping 4. Trigger Apify Scraper - Sends HTTP request to start Apify actor for job scraping 5. Wait for Apify Completion - Waits for the Apify actor to finish execution 6. Delay Before Dataset Read - Waits a few seconds to ensure dataset is ready for processing 7. Fetch Scraped Job Dataset - Fetches the latest dataset from Apify 8. Process Raw Job Data - Filters jobs posted in the last 24 hours and formats the data 9. Save Jobs to Daily Sheet - Appends new job data to the daily Google Sheet 10. Update Keyword Job Count - Updates job count in the All Keywords summary sheet Phase 2: Data Cleaning & Deduplication This phase ensures data quality and removes duplicates: 1. Load Today's Daily Jobs - Loads all jobs added in today's sheet for processing 2. Remove Duplicates by Title/Desc - Removes duplicates based on title and description matching 3. Save Clean Job Data - Saves the cleaned, unique entries back to the sheet 4. Clear Old Daily Sheet Data - Deletes old or duplicate entries from the sheet 5. Reload Clean Job Data - Loads clean data again after deletion for final processing Phase 3: Daily Summary & Email Report This phase generates summaries and delivers the final report: 1. Generate Keyword Summary Stats - Counts job totals per keyword for analysis 2. Update Summary Sheet - Updates the summary sheet with keyword statistics 3. Fetch Final Summary Data - Reads the summary sheet for reporting purposes 4. Build Email Body - Formats email with statistics and sheet link 5. Send Daily Report Email - Sends the structured daily summary email to recipients Data Sources The workflow utilizes Google Sheets for data management: 1. AI Keywords Sheet - Contains keyword management data with columns: - Keyword (text) - Job search terms - Job Count (number) - Number of jobs found for each keyword - Status (text) - Active/Inactive status - Last Updated (timestamp) - When keyword was last processed 2. Daily Jobs Sheet - Contains scraped job data with columns: - Job Title (text) - Title of the job posting - Description (text) - Job description content - Budget (text) - Job budget or hourly rate - Client Rating (number) - Client's rating on Upwork - Posted Date (timestamp) - When job was posted - Job URL (text) - Direct link to the job posting - Keyword (text) - Which keyword found this job - Scraped At (timestamp) - When data was collected 3. Summary Sheet - Contains daily statistics with columns: - Date (date) - Report date - Total Jobs (number) - Total jobs found - Keywords Processed (number) - Number of