Weekly Magento 2 Stuck Order Reporting with Gmail & Google Sheets

This n8n workflow automates the process of identifying and reporting stuck Magento 2 orders, delivering a weekly summary via email and logging the details in a Google Sheet. It helps e-commerce teams proactively address order issues, ensuring smooth operations and preventing customer service bottlenecks.

What it does

This workflow runs on a weekly schedule and performs the following actions:

1. Triggers Weekly: Initiates the workflow once a week at a specified time.
2. Fetches Stuck Orders: Makes an HTTP request to a Magento 2 API endpoint to retrieve a list of orders that are currently in a "stuck" state.
3. Processes Order Data: Uses a Code node to transform and format the retrieved order data into a more readable and usable format.
4. Logs to Google Sheets: Appends the details of the stuck orders (after processing) to a designated Google Sheet, creating a historical record.
5. Sends Email Report: Compiles the processed stuck order data into an email and sends it via Gmail to a specified recipient (e.g., the operations team or customer support).

Prerequisites/Requirements

To use this workflow, you will need:

• n8n Instance: A running n8n instance.
• Magento 2 API Access: An accessible Magento 2 API endpoint for retrieving order data, along with appropriate authentication credentials (e.g., API key or token).
• Google Sheets Account: A Google account with access to Google Sheets. You'll need to create a new spreadsheet or identify an existing one where the stuck order data will be appended.
• Gmail Account: A Google account with access to Gmail for sending the email reports.
• n8n Credentials: Configured n8n credentials for:
  • Google Sheets (OAuth2 or Service Account)
  • Gmail (OAuth2)
  • HTTP Request (if your Magento API requires authentication, e.g., Bearer Token, Basic Auth)

Setup/Usage

1. Import the Workflow:
   • Download the provided JSON file for this workflow.
   • In your n8n instance, go to "Workflows" and click "New".
   • Click the "Import from JSON" button and paste the workflow JSON or upload the file.
2. Configure Credentials:
   • Click on the "Google Sheets" node and set up your Google Sheets credential.
   • Click on the "Gmail" node and set up your Gmail credential.
   • Click on the "HTTP Request" node. If your Magento API requires authentication, configure the appropriate credential (e.g., "Generic Credential" for Bearer Token or Basic Auth).
3. Customize Nodes:
   • Schedule Trigger (Node 839): Adjust the "Mode" and "Time" to your desired weekly schedule for running the report.
   • HTTP Request (Node 19):
     • Update the URL field to your Magento 2 API endpoint for fetching orders.
     • Configure Headers or Query Parameters as needed for filtering orders by status (e.g., status=stuck or similar).
     • Ensure Authentication is correctly set up if required by your Magento API.
   • Code (Node 834): Review and modify the JavaScript code if you need to change how the Magento order data is processed or formatted before being sent to Google Sheets and Gmail.
   • Google Sheets (Node 18):
     • Select the Spreadsheet ID of your target Google Sheet.
     • Specify the Sheet Name where the data should be appended.
     • Ensure the Operation is set to Append Row.
   • Gmail (Node 356):
     • Set the To email address(es) for the recipients of the report.
     • Customize the Subject and Body of the email to include relevant information from the processed order data. Use expressions like {{ $json.propertyName }} to dynamically insert data.
4. Activate the Workflow: Once all nodes are configured, save the workflow and activate it by toggling the "Active" switch in the top right corner.

The workflow will now run automatically on your defined schedule, providing timely reports on stuck Magento orders.