How to Build a Monthly Pharmacy Store Retail Performance Digest to Email

Build a Spojit workflow that runs on the first of every month, pulls front-of-store and OTC retail sales from your point-of-sale REST API and a MySQL reporting table, totals revenue and top categories with the math connector, and emails store managers a clean non-clinical performance digest.

What This Integration Does

Pharmacy retail teams often have their numbers scattered across two places: live till and OTC sales sit in a point-of-sale system, while loyalty sign-ups and prescription-ready pickup-reminder volume get logged into a reporting database. Each month someone exports both, pastes them into a spreadsheet, and writes up a summary for each store manager. This workflow does all of that on a schedule. It reads both sources, adds up retail revenue, ranks the top-selling categories, and sends managers a tidy email digest covering sales, loyalty sign-ups, and pickup-reminder counts. Everything stays on the retail, admin, and notification side of the business: no clinical data, no diagnoses, no medical records.

The workflow is driven by a Schedule trigger set to fire once a month. When it runs, it calls your point-of-sale system over the http connector for the previous month's sales lines, queries a MySQL table for loyalty and reminder counts, totals and ranks the figures with the math connector, formats a readable summary with a Transform node, and ends with a Send Email node to the store managers. It is read-only on your source systems and leaves no state behind: each run is independent, so re-running it for a given month simply produces the same digest again from the same underlying data.

Prerequisites

An API key or token for your point-of-sale system's REST API, supplied in the request Authorization header. The http connector is built in and needs no separate connection.
A MySQL connection added under Connections, with read access to the table that holds your monthly loyalty sign-ups and pickup-reminder counts (for example a store_metrics table keyed by store_id and period).
The store managers' email addresses added to your org allowlist under Settings -> General -> Email recipients, so the Send Email node can reach them.
The exact field names your point-of-sale API returns for each sales line (category, line revenue) and the category labels you want ranked.

Step 1: Add the Schedule trigger

Start a new workflow and add a Trigger node, then set Trigger Type to Schedule. Add a 5-field Unix cron expression and an IANA timezone. To run at 7am on the first day of each month in Sydney, use:

0 7 1 * *
Australia/Sydney

The trigger output is { scheduledAt }, an ISO timestamp you can reference later as {{ scheduledAt }}. A single Schedule trigger can hold more than one schedule if you later want, say, a mid-month preview run as well.

Step 2: Read last month's retail sales from the point-of-sale API

Add a Connector node on the http connector in Direct mode and pick the http-get tool. Point it at your point-of-sale system's reporting endpoint and pass the previous month as a query range. Pharmacy point-of-sale platforms have no native tile in Spojit, so this is the intended way to reach them: their REST API via the http connector. Set the url and headers fields like this:

url: https://api.your-pos.com/v1/sales/lines?period=2026-05&type=retail
headers:
  Authorization: Bearer YOUR_POS_API_KEY
  Accept: application/json

Map the response to an output variable such as posSales. Assume the API returns a list of sales lines under posSales.data.lines, each with a category and a numeric revenue. If the API paginates, repeat the call inside a Loop node until you have every line for the month. For a deeper walkthrough of calling external APIs this way, see How to Connect to Any REST API Using HTTP Requests.

Step 3: Query loyalty and pickup-reminder counts from MySQL

Add a second Connector node, this time on the MySQL connector in Direct mode, and pick the execute-query tool. Read the prior month's loyalty sign-ups and pickup-reminder volume for the store from your reporting table:

SELECT loyalty_signups, pickup_reminders
FROM store_metrics
WHERE store_id = 'AURORA-01'
  AND period = '2026-05'

Map the result to an output variable such as storeMetrics. You will reference the first row later as {{ storeMetrics.rows.0.loyalty_signups }} and {{ storeMetrics.rows.0.pickup_reminders }}. Keep this query parameterised by store and period so the same workflow can be reused across locations. Pickup reminders here are a simple admin count, not any patient or clinical detail.

Step 4: Total revenue and rank top categories with the math connector

First, collect the per-line revenue figures into an array. Add a Transform node that pulls every revenue value out of {{ posSales.data.lines }} into a flat list, and also groups the lines by category with a subtotal per category. Output two variables: revenueValues (a list of numbers) and categoryTotals (category plus subtotal).

Now add a Connector node on the math connector in Direct mode using the sum tool, passing the list as the numbers input:

numbers: {{ revenueValues }}

The tool returns { result }, your total retail revenue for the month. Map it to totalRevenue. To present each category's share, add another math connector node using the percentage tool, passing the category subtotal as value and {{ totalRevenue.result }} as total; it returns a result and a formatted string. For a manager-friendly currency figure, use the currency tool to format {{ totalRevenue.result }}. If you want the average basket value too, the average tool takes the same numbers list.

Step 5: Assemble the digest text

Add a Transform node to build the email body from the numbers gathered so far. Sort categoryTotals by subtotal descending and keep the top three, then compose a plain-text summary that pulls in the totals and the MySQL counts. Output a single variable such as digestBody containing text along these lines:

Monthly Retail Performance - Aurora Pharmacy 01
Period: May 2026

Total retail revenue: {{ totalRevenue.formatted }}
Average basket: {{ avgBasket.result }}

Top categories:
1. {{ topCategories.0.category }} - {{ topCategories.0.share }}
2. {{ topCategories.1.category }} - {{ topCategories.1.share }}
3. {{ topCategories.2.category }} - {{ topCategories.2.share }}

Loyalty sign-ups: {{ storeMetrics.rows.0.loyalty_signups }}
Pickup reminders sent: {{ storeMetrics.rows.0.pickup_reminders }}

Keep the wording retail and admin only. This digest reports store performance, not customer health information.

Step 6: Email the digest to store managers

Add a Send Email node as the final step. It sends from Spojit's built-in mail service, so no connection is required. Set the fields:

Recipients: a comma-separated list of manager addresses, all on the org allowlist, for example aurora01.manager@example.com, regional.retail@example.com.
Subject: a templated line such as Retail digest - Aurora 01 - May 2026.
Body: {{ digestBody }} from the previous step.
If sending fails: choose Fail the workflow so a delivery problem surfaces in your execution history rather than passing silently.

If you would rather send from your own pharmacy domain, swap this for a Connector node on the resend or smtp connector using send-email. Remember the built-in Send Email node counts toward your monthly email allowance. For the node's full option set, see Using Send Email Nodes.

Tips

Compute the previous month dynamically so you never edit the cron job: use a Connector node on the date connector with subtract and format to derive the period string, then feed it into both the http-get query and the MySQL WHERE clause.
The math connector's sum and average tools expect a flat array of numbers under numbers. Use the Transform node to strip out any strings or null line revenues first, or the math step will reject the input.
To digest several stores in one run, wrap Steps 2 through 6 in a Loop node over your list of store IDs, or move that block into a child workflow and call it once per store with a Subworkflow node.
Ask Miraxa, the intelligent layer across your automation, to scaffold the skeleton: "Build a monthly Schedule workflow that calls an http-get on my POS API, runs a MySQL execute-query, sums revenue with the math connector, and ends with a Send Email node." Then fine-tune each node in the properties panel.

Common Pitfalls

Recipient not on the allowlist. The Send Email node only delivers to addresses on your org allowlist. Add every manager under Settings -> General -> Email recipients before going live, or the send will be blocked.
Timezone drift on the first of the month. A cron of 0 7 1 * * fires in the IANA timezone you set, not UTC. If the timezone is wrong, the run can land on the last day of the prior month and pull the wrong period. Confirm the timezone and the derived period string together.
Division by zero in percentage. The math connector's percentage tool returns an error field instead of a number when total is 0 (a month with no recorded revenue). Add a Condition node to skip category ranking when {{ totalRevenue.result }} is 0.
Source schema drift. If the point-of-sale API renames the revenue or category field, or the store_metrics columns change, the digest silently shows zeros. Validate the shape in the Transform node and let the workflow fail loudly rather than email a blank report.

Testing

Before enabling the schedule, hard-code a known closed month (for example last month) into the http-get query and the MySQL WHERE clause, then use the Run button to trigger one manual execution. Open the run in your execution history and check each step's output: confirm posSales returns sales lines, storeMetrics returns one row, and totalRevenue.result matches a figure you can verify against your point-of-sale reports. Point the Send Email recipient at your own inbox first and read the rendered digest end to end. Once the numbers reconcile, switch the recipients to the managers and enable the Schedule trigger.