Auto-generate a weekly business health report with n8n and Claude

Google Sheets data → n8n scheduled fetch → Claude analysis → Gmail delivery → Sheets logging

Who

Small-business owners or operators who need a weekly digest of business metrics, trends, and action items emailed automatically

You get

Every Monday at 7AM, receive a plain-text email comparing this week vs last week, with trends, concerns, and top 3 actions

You need

n8n · gmail · google-sheets

Steps

1. Prepare the data sheet and connect Google account

   Create a Google Sheet with one row per day and columns for your key business metrics (e.g., Revenue, Leads, Calls, Conversions). In the n8n workflow, open both Fetch This Week Data and Fetch Last Week Data nodes, connect your Google account, and select the sheet. Both nodes use the same documentId.

2. Configure report settings

   Open the Configure Report Settings node (a Code node) and edit the first three constants: BUSINESS_NAME, REPORT_EMAIL, and METRICS_DESCRIPTION (a short plain-English description of what each column means, e.g. 'Revenue (USD), New Leads, Sales Calls Booked, Deals Closed, Refunds'). Save the node; it will auto-calculate the date ranges for this week and last week.

   Setup guidance · Source text。JS Code field of the Configure Report Settings node; replace constants then save

   Copy prompt

   // ================================================
   //  EDIT THIS NODE -- all your settings live here
   // ================================================
   
   const BUSINESS_NAME    = 'Your Business Name';
   const REPORT_EMAIL     = '[email protected]';
   const METRICS_DESCRIPTION = 'Revenue (USD), New Leads, Sales Calls Booked, Deals Closed, Refunds';
   
   // ================================================
   //  DO NOT EDIT BELOW THIS LINE
   // ================================================
   
   const now   = new Date();
   const today = now.toISOString().split('T')[0];
   
   // This week: last 7 days
   const thisWeekEnd   = new Date(now);
   const thisWeekStart = new Date(now);
   thisWeekStart.setDate(thisWeekStart.getDate() - 7);
   
   // Last week: 8-14 days ago
   const lastWeekEnd   = new Date(thisWeekStart);
   const lastWeekStart = new Date(thisWeekStart);
   lastWeekStart.setDate(lastWeekStart.getDate() - 7);
   
   function fmt(d) { return d.toISOString().split('T')[0]; }
   
   return [{
     json: {
       business_name:        BUSINESS_NAME,
       report_email:         REPORT_EMAIL,
       metrics_description:  METRICS_DESCRIPTION,
       run_date:             today,
       this_week_start:      fmt(thisWeekStart),
       this_week_end:        fmt(thisWeekEnd),
       last_week_start:      fmt(lastWeekStart),
       last_week_end:        fmt(lastWeekEnd)
     }
   }];

3. Set up Claude AI and the analysis prompt

   Under the Analyse with Claude node, open the Claude Sonnet sub-node, add an Anthropic credential, and paste your API key from console.anthropic.com. The model is set to claude-sonnet-4-5. The Analyse with Claude node already contains the full system prompt; you may edit it to describe your business and what good performance looks like, as noted in Sticky Note3.

   Execution prompt · Source text。Text field of the Analyse with Claude node (promptType=define)

   Copy prompt

   =You are a sharp business analyst reviewing weekly performance data for {{ $json.business_name }}.
   
   Your job is to write a clear, honest weekly business health report that a busy owner can read in 2 minutes.
   
   METRICS IN THIS DATASET: {{ $json.metrics_description }}
   
   DATA:
   {{ $json.formatted_data }}
   
   Write the report using EXACTLY this structure:
   
   WEEKLY BUSINESS HEALTH REPORT -- {{ $json.run_date }}
   
   OVERALL HEALTH: [One of: Strong / Steady / Needs Attention / Critical]
   
   WEEK-ON-WEEK SUMMARY:
   Write 2-3 sentences comparing this week to last week in plain English. Be specific with numbers.
   
   WHAT IS WORKING:
   - [specific win with numbers]
   - [specific win with numbers]
   
   WHAT NEEDS ATTENTION:
   - [specific concern with numbers and why it matters]
   - [specific concern with numbers and why it matters]
   
   TOP 3 ACTIONS THIS WEEK:
   1. [Concrete action -- one sentence]
   2. [Concrete action -- one sentence]
   3. [Concrete action -- one sentence]
   
   TREND TO WATCH:
   One sentence on the most important trend developing across both weeks.
   
   Rules: Be direct. Use actual numbers from the data. Do not hedge or add disclaimers. If data is missing or incomplete, say so and explain what you can still see.

4. Connect Gmail and create the log sheet

   Connect your Gmail account in the Send Weekly Report node. Then create a second tab in the same Google Sheet named Report Log with columns in this order: Date, Week Covered, Business, Status, Summary. Open the Log Report Run node, connect the same Google account, and select the Report Log tab. [First-test safety] Before the first test, set Send Weekly Report to your own test inbox, or temporarily disconnect that Gmail node, and use a test archive sheet for Report Log; reconnect production recipients/sheet only after the dry-run looks correct.

5. Activate and verify

   Save the workflow and click Activate. The Weekly Schedule Trigger is already set to Mondays at 7:00. Before the first scheduled run, execute manually once to verify the email arrives and Report Log appends correctly. If data is missing or date ranges look wrong, check the constants in Configure Report Settings and the date column format in your sheet.

You are setting up an n8n automation: every Monday at 7AM it pulls the last two weeks of business data from Google Sheets, analyses it with Claude AI, emails a health report via Gmail, and logs the run to a second sheet tab. First confirm you have: an n8n instance, Google OAuth credentials (Sheets + Gmail), and an Anthropic API key. Then replace YOUR_BUSINESS_NAME, REPORT_EMAIL, METRICS_DESCRIPTION, and YOUR_GOOGLE_SHEET_ID. Run a manual test before the first scheduled execution and verify both inbox and Report Log.