Workflow: 0830_Filter_Summarize_Send_Scheduled
Integrations: extractFromFile, filter, form, formTrigger, gmail, googleSheets, googleSheetsTool, scheduleTrigger, set, stickyNote, summarize

{"meta": {"instanceId": "408f9fb9940c3cb18ffdef0e0150fe342d6e655c3a9fac21f0f644e8bedabcd9", "templateCredsSetupCompleted": true}, "nodes": [{"id": "8884df86-b7cd-4cf7-8b71-fd21113bfc0f", "name": "Client Usage Log", "type": "n8n-nodes-base.googleSheetsTool", "position": [420, 500], "parameters": {"columns": {"value": {}, "schema": [{"id": "date", "type": "string", "display": true, "required": false, "displayName": "date", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "workflow_id", "type": "string", "display": true, "required": false, "displayName": "workflow_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "execution_id", "type": "string", "display": true, "required": false, "displayName": "execution_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "client_id", "type": "string", "display": true, "required": false, "displayName": "client_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "client_name", "type": "string", "display": true, "required": false, "displayName": "client_name", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "input_tokens", "type": "string", "display": true, "required": false, "displayName": "input_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "output_tokens", "type": "string", "display": true, "required": false, "displayName": "output_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "total_tokens", "type": "string", "display": true, "required": false, "displayName": "total_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "input_cost", "type": "string", "display": true, "required": false, "displayName": "input_cost", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "output_cost", "type": "string", "display": true, "required": false, "displayName": "output_cost", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "total_cost", "type": "string", "display": true, "required": false, "displayName": "total_cost", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "autoMapInputData", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false}, "options": {}, "operation": "append", "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit#gid=0", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "list", "value": "1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit?usp=drivesdk", "cachedResultName": "89. Langchain Code Node - Client Usage Log"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "XHvC7jIRR8A2TlUl", "name": "Google Sheets account"}}, "typeVersion": 4.5}, {"id": "1e4aca76-8b79-4780-b0c5-2cd92a41aa0e", "name": "Logging Attributes", "type": "n8n-nodes-base.set", "position": [-360, -120], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "22164635-7a23-47e2-9868-96899cd9d317", "name": "workflow_id", "type": "string", "value": "={{ $workflow.id }}"}, {"id": "ed1cb653-b3fd-40bf-b00b-10d777f098af", "name": "execution_id", "type": "string", "value": "={{ $execution.id }}"}, {"id": "3de228a1-79b9-4805-8d92-917f691411be", "name": "client_id", "type": "string", "value": "=12345"}]}, "includeOtherFields": true}, "typeVersion": 3.4}, {"id": "d7f37c54-5d96-47ba-b82e-0926a08137df", "name": "On form submission", "type": "n8n-nodes-base.formTrigger", "position": [-920, -120], "webhookId": "9da21424-e23b-43b8-a6ec-a6daa129c326", "parameters": {"options": {}, "formTitle": "CV Parsing Service", "formFields": {"values": [{"fieldType": "file", "fieldLabel": "Upload a file", "requiredField": true, "acceptFileTypes": ".pdf"}, {"fieldType": "dropdown", "fieldLabel": "Acknowledgement", "multiselect": true, "fieldOptions": {"values": [{"option": "I acknowledge the use of this service will be added to my bill."}]}, "requiredField": true}]}, "responseMode": "lastNode", "formDescription": "Use this form to upload CVs and we'll extract the data from them. This workflow tracks usage metrics so we can calculate the bill later on."}, "typeVersion": 2.2}, {"id": "06da0c8e-2035-45ae-a301-50021650a5f8", "name": "Custom LLM Subnode", "type": "@n8n/n8n-nodes-langchain.code", "position": [260, 340], "parameters": {"code": {"supplyData": {"code": "const { ChatOpenAI } = require(\"@langchain/openai\");\n\n// 1. Configure as required.\n// - costs are per million tokens and depends on the model.\nconst openAIApiKey = \"sk-...\";\nconst model = \"gpt-4o-mini\";\nconst input_token_cost = 0.150;\nconst output_token_cost = 0.600;\n\n// 2. do not edit below this line --\nconst tools = await this.getInputConnectionData('ai_tool', 0);\nconst googleSheetTool = tools[0];\n\nconst {\n  workflow_id,\n  execution_id,\n  client_id } = $input.first().json;\n\nconst llm = new ChatOpenAI({\n  apiKey: openAIApiKey,\n  model,\n  callbacks: [\n    {\n      handleLLMEnd: async function(output,runId,parentId) {\n        const generation = output.generations[0][0];\n        const message = generation.message;\n        const row = {\n          date: (new Date()).toGMTString(),\n          workflow_id,\n          execution_id,\n          client_id,\n          input_tokens: message.usage_metadata.input_tokens,\n          output_tokens: message.usage_metadata.output_tokens,\n          total_tokens: message.usage_metadata.total_tokens,\n          input_cost: (message.usage_metadata.input_tokens / 1_000_000) * input_token_cost,\n          output_cost: (message.usage_metadata.output_tokens / 1_000_000) * output_token_cost,\n        };\n        row.total_cost = row.input_cost + row.output_cost;\n        await googleSheetTool.func(row);\n      }\n    }\n  ]\n});\n\nreturn llm;"}}, "inputs": {"input": [{"type": "ai_tool", "required": true}]}, "outputs": {"output": [{"type": "ai_languageModel"}]}}, "typeVersion": 1}, {"id": "35993bd5-b521-4239-bf23-aed47d339f54", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [360, 480], "parameters": {"width": 200, "height": 280, "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n### Update Workbook\nThis is the workbook which will track the token usage and costs."}, "typeVersion": 1}, {"id": "623ca94d-a215-416b-a9fe-62a1f96317e3", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [-1040, -280], "parameters": {"color": 7, "width": 560, "height": 380, "content": "## 1. Offer AI Service to Clients\nHere, we'll using an n8n form to offer a document extraction service for Resume/CV PDFs. The user simply uploads a PDF and we return it in JSON format. This is just a simple example for demonstration purposes. "}, "typeVersion": 1}, {"id": "ba9eb149-e77f-4bf6-8ec5-7d8d4619485d", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [-460, -280], "parameters": {"color": 7, "width": 320, "height": 380, "content": "## 2. Gather External Variables to Send to Log\nThere are some variables we can't define in the subnode but we can add them here."}, "typeVersion": 1}, {"id": "63bfe329-17dd-4321-94c6-17d306ed7412", "name": "Sticky Note3", "type": "n8n-nodes-base.stickyNote", "position": [-120, -280], "parameters": {"color": 7, "width": 720, "height": 380, "content": "## 3. Provide AI Service\nOur service uses an LLM (OpenAI GPT4o-mini in this example) to organise the uploaded PDF's data into a structured JSON format. This conversion is useful for following integrations or data storage. In this example however, we'll use display it back to the user."}, "typeVersion": 1}, {"id": "f45862e9-7f8e-46bb-900a-807fee981ed5", "name": "Sticky Note4", "type": "n8n-nodes-base.stickyNote", "position": [-120, 120], "parameters": {"color": 7, "width": 720, "height": 440, "content": "## 4. Use Custom LLM Subnode to Track Usage & Cost\n[Learn more about the Langchain Code Node](https://docs.n8n.io/integrations/builtin/cluster-nodes/root-nodes/n8n-nodes-langchain.code/)\n\nBy creating our custom LLM subnode using the Langchain Code node, we are able to tap into the chat completion's response which contains the token usage metadata for the information extractor request.\n\nWith this, we can calculate exactly how much the client's request is costing per use!\n\nThe only remaining step then is to store\nthis data somewhere. Rather than importing\nmore dependencies or hardcoding more\ncredentials, a novel solution can be to attach\na tool to our subnode.\n\nHere, we've decided to use the Google Sheets\ntool and append the client's usage metrics to\nthe sheet. Check it out here - [Client Usage Log](\nhttps://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit?usp=sharing)"}, "typeVersion": 1}, {"id": "9f5014a5-0e9a-4af0-b076-03cdc0a14ab9", "name": "Display JSON Document", "type": "n8n-nodes-base.form", "position": [360, -120], "webhookId": "1b9d0195-1662-43c2-94a0-f9c867d75578", "parameters": {"options": {"customCss": ".header p {\n  font-family: monospace;\n  background-color: #efefef;\n  padding: 1rem;\n  font-size: 0.8rem;\n  text-align: left;\n  max-height: 480px;\n  overflow: auto;\n  white-space: pre;\n}"}, "operation": "completion", "completionTitle": "=Results for {{ $('On form submission').item.json['Upload a file'][0].filename }}", "completionMessage": "={{ JSON.stringify($json.output, null, 2) }}"}, "typeVersion": 1}, {"id": "b977f89c-1118-455f-986e-735a17eecd9b", "name": "Filter Last Month", "type": "n8n-nodes-base.filter", "position": [1120, -120], "parameters": {"options": {}, "conditions": {"options": {"version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict"}, "combinator": "and", "conditions": [{"id": "2a86f83e-b103-46fe-a8b8-15811d4138fa", "operator": {"type": "dateTime", "operation": "afterOrEquals"}, "leftValue": "={{new Date($json.date) }}", "rightValue": "={{ $now.startOf('month') }}"}, {"id": "7b4c03a3-4df9-4b5d-9f1f-660e54a1c2d1", "operator": {"type": "dateTime", "operation": "beforeOrEquals"}, "leftValue": "={{new Date($json.date) }}", "rightValue": "={{ $now.endOf('month') }}"}]}}, "typeVersion": 2.2}, {"id": "10d95dcb-d975-4b20-961e-d1fe63417878", "name": "Get Client Logs", "type": "n8n-nodes-base.googleSheets", "position": [920, -120], "parameters": {"options": {}, "filtersUI": {"values": [{"lookupValue": "12345", "lookupColumn": "client_id"}]}, "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit#gid=0", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "list", "value": "1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit?usp=drivesdk", "cachedResultName": "89. Langchain Code Node - Client Usage Log"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "XHvC7jIRR8A2TlUl", "name": "Google Sheets account"}}, "typeVersion": 4.5}, {"id": "f6505545-d57c-443a-9883-2d536f3a973a", "name": "Calculate Totals", "type": "n8n-nodes-base.summarize", "position": [1320, -120], "parameters": {"options": {}, "fieldsToSummarize": {"values": [{"field": "total_cost", "aggregation": "sum"}, {"field": "total_tokens", "aggregation": "sum"}]}}, "typeVersion": 1.1}, {"id": "1c4ae8ff-ec2b-4fd3-974f-cc766385b16b", "name": "Every End of Month", "type": "n8n-nodes-base.scheduleTrigger", "position": [720, -120], "parameters": {"rule": {"interval": [{"field": "months", "triggerAtHour": 18, "triggerAtDayOfMonth": 31}]}}, "typeVersion": 1.2}, {"id": "f321fbe6-36b1-4bd8-899b-832a8fc6217a", "name": "Send Invoice", "type": "n8n-nodes-base.gmail", "position": [1520, -120], "webhookId": "68315f84-d7e0-4525-a625-bb3ff431931c", "parameters": {"sendTo": "jim@example.com", "message": "=Hello,\nThis is an invoice for {{ $now.monthLong }} {{ $now.year }}.\n\nTotal usage: {{ $json.sum_total_tokens }} tokens\nTotal token cost: ${{ $json.sum_total_cost.toFixed(5) }}\nTax @ 20%: ${{ ($json.sum_total_cost * 0.2).toFixed(5) }}\nTotal payable: ${{ ($json.sum_total_cost * 1.2).toFixed(5) }}\n\nPayable within 14 days.\nThank you for your custom.", "options": {}, "subject": "=Invoice for {{ $now.monthLong }} {{ $now.year }}", "emailType": "text"}, "credentials": {"gmailOAuth2": {"id": "Sf5Gfl9NiFTNXFWb", "name": "Gmail account"}}, "typeVersion": 2.1}, {"id": "a7d8de78-c3b7-4687-8994-fe28387d7572", "name": "Sticky Note5", "type": "n8n-nodes-base.stickyNote", "position": [620, -280], "parameters": {"color": 7, "width": 1100, "height": 380, "content": "## 5. Automatically Send Invoice at End of Month (Optional)\nWith our client usage log, it's fairly simple to aggregate the log records for the client within a certain timeframe and calculate the totals.\nThis can satisfy accurate billing requirements from clients or at least, allows your team to understand how much each client is costing and budget accordingly."}, "typeVersion": 1}, {"id": "169fa40d-c6e8-4315-be35-d2c73f626edf", "name": "Sticky Note6", "type": "n8n-nodes-base.stickyNote", "position": [-1500, -920], "parameters": {"width": 440, "height": 1020, "content": "## Try It Out!\n### This n8n template demonstrates how to use the Langchain code node to track token usage and cost for every LLM call.\n\nThis is useful if your templates handle multiple clients or customers and you need a cheap and easy way to capture how much of your AI credits they are using.\n\n### How it works\n* In our mock AI service, we're offering a data conversion API to convert Resume PDFs into JSON documents.\n* A form trigger is used to allow for PDF upload and the file is parsed using the Extract from File node.\n* An Edit Fields node is used to capture additional variables to send to our log.\n* Next, we use the Information Extractor node to organise the Resume data into the given JSON schema.\n* The LLM subnode attached to the Information Extractor is a custom one we've built using the Langchain Code node.\n* With our custom LLM subnode, we're able to capture the usage metadata using lifecycle hooks.\n* We've also attached a Google Sheet tool to our LLM subnode, allowing us to send our usage metadata to a google sheet.\n* Finally, we demonstrate how you can aggregate from the google sheet to understand how much AI tokens/costs your clients are liable for.\n\n\n**Check out the example Client Usage Log** - https://docs.google.com/spreadsheets/d/1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw/edit?usp=sharing\n\n### How to use\n* **SELF-HOSTED N8N ONLY** - the Langchain Code node is only available in the self-hosted version of n8n. It is not available in n8n cloud.\n* The LLM subnode can only be attached to non-\"AI agent\" nodes; Basic LLM node, Information Extractor, Question & Answer Chain, Sentiment Analysis, Summarization Chain and Text Classifier.\n\n### Need Help?\nJoin the [Discord](https://discord.com/invite/XPKeKXeB7d) or ask in the [Forum](https://community.n8n.io/)!"}, "typeVersion": 1}, {"id": "922710e3-f92b-4a7f-9ff2-c3d7d55f04d5", "name": "Sticky Note7", "type": "n8n-nodes-base.stickyNote", "position": [-1040, -420], "parameters": {"color": 3, "width": 280, "height": 120, "content": "### SELF-HOSTED N8N ONLY\nPlease note, this template only works in the self-hosted version of n8n only. It will not work in the cloud version."}, "typeVersion": 1}, {"id": "56c23cb5-818f-434d-96a7-0029f6607299", "name": "Parse PDF Upload", "type": "n8n-nodes-base.extractFromFile", "position": [-700, -120], "parameters": {"options": {}, "operation": "pdf", "binaryPropertyName": "Upload_a_file"}, "typeVersion": 1}, {"id": "f4cc9870-a73e-487c-a131-aca2735b2e60", "name": "Extract Resume Data", "type": "@n8n/n8n-nodes-langchain.informationExtractor", "position": [0, -120], "parameters": {"text": "={{ $json.text }}", "options": {}, "schemaType": "manual", "inputSchema": "{\n  \"type\": \"object\",\n  \"properties\": {\n    \"name\": { \"type\": \"string\" },\n    \"label\": { \"type\": \"string\" },\n    \"email\": { \"type\": \"string\" },\n    \"phone\": { \"type\": \"string\" },\n    \"url\": { \"type\": \"string\" },\n    \"summary\": { \"type\": \"string\" },\n    \"location\": {\n      \"type\": \"object\",\n      \"properties\": {\n        \"address\": { \"type\": \"string\" },\n        \"postalCode\": { \"type\": \"string\" },\n        \"city\": { \"type\": \"string\" },\n        \"countryCode\": { \"type\": \"string\" },\n        \"region\": { \"type\": \"string\" }\n      }\n    },\n    \"work\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"name\": { \"type\": \"string\" },\n          \"location\": { \"type\": \"string\" },\n          \"description\": { \"type\": \"string\" },\n          \"position\": { \"type\": \"string\" },\n          \"url\": { \"type\": \"string\" },\n          \"startDate\": { \"type\": \"string\" },\n          \"endDate\": { \"type\": \"string\" },\n          \"summary\": { \"type\": \"string\" },\n          \"highlights\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    },\n    \"education\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"institution\": { \"type\": \"string\" },\n          \"url\": { \"type\": \"string\" },\n          \"area\": { \"type\": \"string\" },\n          \"studyType\": { \"type\": \"string\" },\n          \"startDate\": { \"type\": \"string\" },\n          \"endDate\": { \"type\": \"string\" },\n          \"score\": { \"type\": \"string\" },\n          \"courses\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    },\n    \"skills\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"name\": { \"type\": \"string\" },\n          \"level\": { \"type\": \"string\" },\n          \"keywords\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    }\n  }\n}"}, "typeVersion": 1}], "pinData": {}, "connections": {"Get Client Logs": {"main": [[{"node": "Filter Last Month", "type": "main", "index": 0}]]}, "Calculate Totals": {"main": [[{"node": "Send Invoice", "type": "main", "index": 0}]]}, "Client Usage Log": {"ai_tool": [[{"node": "Custom LLM Subnode", "type": "ai_tool", "index": 0}]]}, "Parse PDF Upload": {"main": [[{"node": "Logging Attributes", "type": "main", "index": 0}]]}, "Filter Last Month": {"main": [[{"node": "Calculate Totals", "type": "main", "index": 0}]]}, "Custom LLM Subnode": {"ai_languageModel": [[{"node": "Extract Resume Data", "type": "ai_languageModel", "index": 0}]]}, "Every End of Month": {"main": [[{"node": "Get Client Logs", "type": "main", "index": 0}]]}, "Logging Attributes": {"main": [[{"node": "Extract Resume Data", "type": "main", "index": 0}]]}, "On form submission": {"main": [[{"node": "Parse PDF Upload", "type": "main", "index": 0}]]}, "Extract Resume Data": {"main": [[{"node": "Display JSON Document", "type": "main", "index": 0}]]}}}