Automated restaurant menu engineering from sales data and supplier costs

Restaurant menus are living documents, yet most are updated on hunches, seasonal assumptions, or spreadsheets that haven't been touched in months. Meanwhile, your sales data knows exactly which dishes move, your suppliers send invoices with fluctuating costs, and your margins quietly erode whilst you're focused on service.

The core problem is friction. Your point-of-sale system tracks what sells. Your accounting software records what ingredients cost. But connecting those two sources to engineer your menu in real time requires manually pulling reports, calculating margins, summarising findings, and emailing changes to your kitchen team. By the time that's done, the market's moved on.

This Alchemy workflow removes every manual step. You'll automatically pull sales data from your POS, cross-reference it with supplier invoices, analyse profitability, generate a summary of which dishes deserve menu space, and trigger downstream actions like kitchen briefings or supplier negotiations. The entire process runs on a schedule you set, feeding fresh insights directly into your decision-making without anyone touching a spreadsheet.

The Automated Workflow

We'll use four tools working in concert. Deepnote runs the analysis on your actual sales and cost data; Smmry summarises the findings into prose; Terrakotta-ai generates actionable recommendations; and your orchestration tool (we'll show Zapier, n8n, and Make examples) ties it all together.

Architecture Overview

The workflow follows this pattern:

1. Trigger: Weekly at Monday 9 AM.
2. Extract: Pull last week's sales from your POS API; pull supplier invoices from your accounting system.
3. Analyse: Deepnote notebook calculates margin, velocity, and profit contribution for each dish.
4. Summarise: Smmry condenses the analysis into a readable summary.
5. Recommend: Terrakotta-ai generates specific actions (promote this dish, review pricing, consider removal).
6. Deliver: Send results to your team via email, Slack, or your internal system....... For more on this, see Competitor pricing analysis and dynamic pricing recommend....

Let's build this step by step.

Step 1:

Extract Sales Data and Supplier Costs

Most modern restaurants use a cloud POS like Square, Toast, or Lightspeed. We'll assume Square, but the pattern works with any REST API.

From Square, fetch last week's orders:

Copy
GET https://connect.squareup.com/v2/orders/search

{
  "query": {
    "filter": {
      "date_time_filter": {
        "created_at": {
          "start_at": "2024-01-08T00:00:00Z",
          "end_at": "2024-01-15T00:00:00Z"
        }
      }
    }
  }
}

From your accounting system (e.g., Xero), pull supplier invoices:

Copy
GET https://api.xero.com/api.xro/2.0/Invoices?where=Type=="ACCPAY"&order=InvoiceNumber DESC

Headers:
Authorization: Bearer YOUR_XERO_ACCESS_TOKEN

Both APIs return JSON. Store these in temporary variables within your orchestration tool.

Step 2:

Set Up Deepnote for Analysis

Deepnote is a collaborative notebook environment. Create a new notebook and write Python to combine the data sources, calculate margins, and rank dishes by profitability.

Create a Deepnote integration in your orchestration tool. Most tools (Zapier, n8n, Make) support webhooks or API calls to trigger notebook execution and retrieve results.

Here's a simplified Deepnote notebook structure. You would store this code in your actual Deepnote environment:

Copyimport pandas as pd
import json
from datetime import datetime, timedelta

pos_data = json.loads(trigger_data['pos_orders'])
supplier_invoices = json.loads(trigger_data['supplier_invoices'])

# Transform POS data into sales by menu item
sales_df = pd.DataFrame([
    {
        'item_name': order['line_items'][0]['name'],
        'quantity': order['line_items'][0]['quantity'],
        'revenue': float(order['line_items'][0]['gross_sales_money']['amount']) / 100,
        'order_id': order['id']
    }
    for order in pos_data['orders'] 
    if 'line_items' in order
])

# Aggregate by item
sales_summary = sales_df.groupby('item_name').agg({
    'quantity': 'sum',
    'revenue': 'sum'
}).reset_index()
sales_summary['avg_price'] = sales_summary['revenue'] / sales_summary['quantity']

# Transform supplier invoices into ingredient costs
costs_df = pd.DataFrame([
    {
        'ingredient': line['description'],
        'cost': float(line['unit_amount']['amount']) / 100,
        'quantity': line['quantity']
    }
    for invoice in supplier_invoices.get('Invoices', [])
    for line in invoice.get('LineItems', [])
])

# Map ingredients to dishes (simplified; you'd maintain a recipe mapping table)
recipe_mapping = {
    'Grilled Salmon': ['salmon_fillet', 'lemon', 'olive_oil'],
    'Caesar Salad': ['romaine', 'parmesan', 'croutons', 'caesar_dressing'],
}

# Calculate cost of goods for each dish
def calculate_cogs(dish_name, ingredients):
    return sum(costs_df[costs_df['ingredient'].isin(ingredients)]['cost'].sum())

sales_summary['cogs'] = sales_summary['item_name'].apply(
    lambda x: calculate_cogs(x, recipe_mapping.get(x, []))
)
sales_summary['gross_margin'] = (sales_summary['revenue'] - sales_summary['cogs']) / sales_summary['revenue']
sales_summary['total_profit'] = sales_summary['revenue'] - sales_summary['cogs']

# Rank by profitability
sales_summary = sales_summary.sort_values('total_profit', ascending=False)

# Output for next step
output = sales_summary.to_json(orient='records')

This notebook will output JSON that your orchestration tool can pass to the next step. The output looks like:

Copy[
  {
    "item_name": "Grilled Salmon",
    "quantity": 47,
    "revenue": 1175.00,
    "avg_price": 25.00,
    "cogs": 235.00,
    "gross_margin": 0.80,
    "total_profit": 940.00
  },
  {
    "item_name": "Caesar Salad",
    "quantity": 89,
    "revenue": 712.00,
    "avg_price": 8.00,
    "cogs": 178.00,
    "gross_margin": 0.75,
    "total_profit": 534.00
  }
]

Step 3:

Summarise with Smmry

Smmry is a text summarisation API. Feed it your analysis results as narrative prose, and it condenses them.

First, format your Deepnote output as readable text:

Copy
text_to_summarise = f"""
Weekly menu performance analysis for {week_ending}.

Top performers by profit:
1. {results[0]['item_name']}: £{results[0]['total_profit']:.2f} profit, {results[0]['quantity']} sold
2. {results[1]['item_name']}: £{results[1]['total_profit']:.2f} profit, {results[1]['quantity']} sold
3. {results[2]['item_name']}: £{results[2]['total_profit']:.2f} profit, {results[2]['quantity']} sold

Low performers:
{results[-1]['item_name']}: £{results[-1]['total_profit']:.2f} profit, {results[-1]['quantity']} sold (margin: {results[-1]['gross_margin']:.1%})

Margin analysis:
Average margin across menu: {results['gross_margin'].mean():.1%}
Items below average margin: {(results['gross_margin'] < results['gross_margin'].mean()).sum()}
"""

Then call Smmry's API:

Copy
[POST](/tools/post) https://api.smmry.com/sm

{
  "sm_api_input": text_to_summarise,
  "sm_api_key": "YOUR_SMMRY_KEY"
}

Smmry returns a condensed version, which you'll pass to Terrakotta-ai.

Step 4:

Generate Recommendations with Terrakotta-ai

Terrakotta-ai is designed for structured business recommendations. Send it your analysis summary and ask for specific actions.

Copy
POST https://api.terrakotta-ai.com/v1/analysis

{
  "context": "Restaurant menu engineering",
  "data_summary": smmry_output,
  "analysis_results": deepnote_json,
  "prompt": "Based on this sales and cost data, recommend which dishes to promote, which to review for pricing, and which to consider removing. Prioritise recommendations that improve overall margin without sacrificing volume.",
  "output_format": "json"
}

Terrakotta-ai returns structured recommendations:

Copy{
  "recommendations": 
    {
      "action": "promote",
      "dish": "Grilled Salmon",
      "reason": "Highest margin (80%) and strong volume (47 units). Feature on social media.",
      "expected_impact": "10% volume increase = +£94 profit/week"
    },
    {
      "action": "review_pricing",
      "dish": "Caesar Salad",
      "reason": "High volume (89 units) but margin at average (75%). Test £1 price increase.",
      "expected_impact": "+£89 profit/week at same volume"
    },
    {
      "action": "consider_removal",
      "dish": "Vegetarian Lasagne",
      "reason": "2 units sold, margin 55%, occupies oven space.",
      "expected_impact": "Free [[capacity](/tools/capacity) for higher-margin item"
    }
  ]
}

Step 5:

Wire Everything in Your Orchestration Tool

We'll show examples for all three popular platforms.

Option A:

Zapier

Zapier's visual interface makes this straightforward.

Step 1: Create a Schedule trigger (Monday 9 AM UTC).

Step 2: Webhook by Zapier calls your POS API:

Copy
POST https://hooks.zapier.com/hooks/catch/YOUR_ZAPIER_ID/YOUR_WEBHOOK_ID

Method: GET
URL: https://connect.squareup.com/v2/orders/search
Headers:
  Authorization: Bearer YOUR_SQUARE_TOKEN
Body (JSON):
{
  "query": {
    "filter": {
      "date_time_filter": {
        "created_at": {
          "start_at": "{{7 days ago}}",
          "end_at": "{{today}}"
        }
      }
    }
  }
}

Step 3: Code by Zapier transforms the response:

Copyconst orders = inputData.orders;
return {
  pos_orders: JSON.stringify(orders),
  week_ending: new Date().toISOString().split('T')[0]
};

Step 4: Webhook to Deepnote:

Copy
POST https://deepnote.com/api/v1/notebooks/YOUR_NOTEBOOK_ID/run

{
  "cells": {
    "cell_1": {
      "trigger_data": {
        "pos_orders": "{{Step 3 output}}",
        "supplier_invoices": "{{Step 2b output from Xero call}}"
      }
    }
  }
}

Step 5: Wait for Deepnote to complete (typically 30 seconds).

Step 6: Webhook to Smmry:

Copy
POST https://api.smmry.com/sm

{
  "sm_api_input": "{{Deepnote results formatted as text}}",
  "sm_api_key": "{{YOUR_SMMRY_KEY}}"
}

Step 7: Webhook to Terrakotta-ai:

Copy
POST https://api.terrakotta-ai.com/v1/analysis

{
  "context": "Restaurant menu engineering",
  "data_summary": "{{Smmry output}}",
  "analysis_results": "{{Deepnote JSON}}",
  "prompt": "Recommend which dishes to promote, review for pricing, or remove.",
  "output_format": "json"
}

Step 8: Gmail action sends the final recommendation:

Copy
To: kitchen.manager@restaurant.local
Subject: Weekly Menu Engineering Report
Body: Please find this week's recommendations attached.
Attachment: Terrakotta-ai output as PDF

Total Zapier cost: £20 per month (Professional plan) if staying within request limits; add £49 per month (Advanced) if you need 100k+ tasks.

Option B: n8n (Self-Hosted or Cloud)

n8n is more developer-friendly and offers granular control.

Create a workflow with these nodes:

Copy{
  "nodes": [
    {
      "name": "Weekly Trigger",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [250, 300],
      "parameters": {
        "mode": "everyWeek",
        "dayOfWeek": "Monday",
        "hour": 9,
        "minute": 0
      }
    },
    {
      "name": "Fetch Square Orders",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [450, 300],
      "parameters": {
        "url": "https://connect.squareup.com/v2/orders/search",
        "method": "POST",
        "headers": {
          "Authorization": "Bearer {{env.SQUARE_TOKEN}}",
          "Content-Type": "application/json"
        },
        "body": {
          "query": {
            "filter": {
              "date_time_filter": {
                "created_at": {
                  "start_at": "{{$now.subtract(7, 'days').format('YYYY-MM-DDTHH:mm:ssZ')}}",
                  "end_at": "{{$now.format('YYYY-MM-DDTHH:mm:ssZ')}}"
                }
              }
            }
          }
        }
      }
    },
    {
      "name": "Fetch Xero Invoices",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [450, 400],
      "parameters": {
        "url": "https://api.xero.com/api.xro/2.0/Invoices?where=Type==\"ACCPAY\"",
        "method": "GET",
        "headers": {
          "Authorization": "Bearer {{env.XERO_TOKEN}}",
          "Accept": "application/json"
        }
      }
    },
    {
      "name": "Call Deepnote",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [650, 350],
      "parameters": {
        "url": "https://deepnote.com/api/v1/notebooks/{{env.DEEPNOTE_NOTEBOOK_ID}}/run",
        "method": "POST",
        "headers": {
          "Authorization": "Bearer {{env.DEEPNOTE_TOKEN}}",
          "Content-Type": "application/json"
        },
        "body": {
          "trigger_data": {
            "pos_orders": "{{JSON.stringify($('Fetch Square Orders').all()[0].body.orders)}}",
            "supplier_invoices": "{{JSON.stringify($('Fetch Xero Invoices').all()[0].body)}}"
          }
        }
      }
    },
    {
      "name": "Wait for Deepnote",
      "type": "n8n-nodes-base.wait",
      "typeVersion": 1,
      "position": [850, 350],
      "parameters": {
        "amount": 40,
        "unit": "seconds"
      }
    },
    {
      "name": "Call Smmry",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [1050, 350],
      "parameters": {
        "url": "https://api.smmry.com/sm",
        "method": "POST",
        "body": {
          "sm_api_input": "{{$('Call Deepnote').first().body.analysis_text}}",
          "sm_api_key": "{{env.SMMRY_KEY}}"
        }
      }
    },
    {
      "name": "Call Terrakotta-ai",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [1250, 350],
      "parameters": {
        "url": "https://api.terrakotta-ai.com/v1/analysis",
        "method": "POST",
        "headers": {
          "Authorization": "Bearer {{env.TERRAKOTTA_TOKEN}}",
          "Content-Type": "application/json"
        },
        "body": {
          "context": "Restaurant menu engineering",
          "data_summary": "{{$('Call Smmry').first().body.sm_api_summary}}",
          "analysis_results": "{{$('Call Deepnote').first().body.results}}",
          "prompt": "Recommend which dishes to promote, review for pricing, or remove.",
          "output_format": "json"
        }
      }
    },
    {
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [1450, 350],
      "parameters": {
        "fromEmail": "reports@restaurant.local",
        "toEmail": "kitchen.manager@restaurant.local",
        "subject": "Weekly Menu Engineering Report",
        "text": "Please see recommendations below.\n\n{{JSON.stringify($('Call Terrakotta-ai').first().body.recommendations, null, 2)}}"
      }
    }
  ],
  "connections": {
    "Weekly Trigger": {
      "main": [
        [
          { "node": "Fetch Square Orders", "type": "main", "index": 0 },
          { "node": "Fetch Xero Invoices", "type": "main", "index": 0 }
        ]
      ]
    },
    "Fetch Square Orders": {
      "main": [
        [{ "node": "Call Deepnote", "type": "main", "index": 0 }]
      ]
    },
    "Fetch Xero Invoices": {
      "main": [
        [{ "node": "Call Deepnote", "type": "main", "index": 0 }]
      ]
    },
    "Call Deepnote": {
      "main": [
        [{ "node": "Wait for Deepnote", "type": "main", "index": 0 }]
      ]
    },
    "Wait for Deepnote": {
      "main": [
        [{ "node": "Call Smmry", "type": "main", "index": 0 }]
      ]
    },
    "Call Smmry": {
      "main": [
        [{ "node": "Call Terrakotta-ai", "type": "main", "index": 0 }]
      ]
    },
    "Call Terrakotta-ai": {
      "main": [
        [{ "node": "Send Email", "type": "main", "index": 0 }]
      ]
    }
  }
}

Save this as a JSON file and import into n8n. Update environment variables for all API tokens.

n8n cost: Free (self-hosted) or £20 per month (n8n Cloud).

Option C:

Make (Integromat)

Make's visual builder is somewhere between Zapier and n8n.

Build a scenario with these modules in order:

Make cost: £9 per month (Core plan with sufficient operations).

Step 6:

Handle Errors and Edge Cases

All three orchestration tools support error handling. Add a fallback action:

Copy
If any API call fails:
  1. Log the error (timestamp, endpoint, response).
  2. Send Slack notification to #operations channel.
  3. Email fallback report with cached results from last week.

In n8n, add an "On Error" edge from HTTP Request nodes:

Copy{
  "name": "Handle Error",
  "type": "n8n-nodes-base.slack",
  "parameters": {
    "channel": "#operations",
    "message": "Menu engineering workflow failed at step {{node_name}}: {{error_message}}"
  }
}

The Manual Alternative

If you prefer more control or don't want to commit to a full automation, you can run this workflow semi-manually:

1. Export last week's sales from your POS manually into CSV.
2. Download supplier invoices from your accounting system.
3. Upload both to Deepnote and run the notebook on demand.
4. Copy-paste the JSON results into a Smmry request using their web interface.
5. Paste Smmry output into Terrakotta-ai's interface and review recommendations.
6. Manually send the summary to your team.

This approach takes about 90 minutes per week but gives you the chance to validate results before acting. For a restaurant just starting to track menu profitability, this is a reasonable first step.

Pro Tips

1. Cache Deepnote results to avoid recomputing if something fails downstream. Store the JSON output in a cloud storage integration (Google Drive, S3) so you can retrieve it without re-running analysis.

2. Maintain a recipe mapping table outside your code. Create a simple Google Sheet mapping each menu item to its ingredients and quantities, then query it as a lookup table in Deepnote. This makes it easy to update without changing code.

3. Build in manual overrides for seasonal items. Add a checkbox column to your recipe table: if "temporary item" is ticked, exclude it from removal recommendations even if volume is low.

4. Watch API rate limits. Square allows 1000 requests per day for search; Xero allows 60 requests per minute. Since you're running weekly, you're safe, but log API call counts in your orchestration tool to monitor.

5. Test with dummy data first. Before connecting to live POS data, run the workflow with test JSON payloads. This catches formatting errors before they disrupt your team.

6. Consider time zones in your cron schedule. Deepnote logs timestamps in UTC; if your restaurant is in GMT, schedule the workflow for 9 AM UTC (which is 9 AM GMT in winter, 10 AM BST in summer). Or hard-code UTC offsets in your Deepnote notebook.

7. Version your Deepnote notebook. Deepnote auto-saves, but maintain a README cell documenting what changed in each week's release. If a recommendation goes wrong, you can quickly identify the cause.

Cost Breakdown

For a restaurant generating £20,000+ in weekly sales, this investment pays for itself if it improves margin by even 2 percentage points across the menu.