How to Sync Revenue Data to a Database for Reporting

Store e-commerce revenue data in MySQL or MongoDB for custom reporting and analytics.

What This Integration Does

Most reporting tools struggle to query a live e-commerce store at the speed dashboards need, and shop admins limit how far back you can slice the data. The fix is to mirror order-level revenue into your own database every day, then point your BI tools at that. Queries get faster, history sticks around, and finance and growth can join revenue against any other dataset you already have.

The workflow runs nightly, pulls yesterday's orders from your store, extracts revenue-relevant fields, and writes them into MySQL or MongoDB. Each run is idempotent: an existing row for the same order ID is updated rather than duplicated, so re-runs and backfills are safe.

Prerequisites

A Shopify connection with read access to orders.
A MongoDB connection (or MySQL if you prefer relational storage).
A target database and collection, e.g. revenue.daily_orders.

Step 1: Schedule Trigger

Add a Trigger node set to Schedule. Daily at 02:00 is the typical setting; use the trigger timestamp to compute yesterday's window for the order fetch.

Step 2: Fetch Yesterday's Orders

Add a Connector node pointing at shopify using list-orders. Filter to created_at within yesterday's range and any financial status you care about (typically paid and partially_refunded). Wrap it in a Loop to paginate; collect into a flat array.

Step 3: Transform to a Revenue Shape

Add a Transform node that projects each order to just the revenue fields. Aim for a flat, dashboard-friendly shape:

{
  "_id": "{{ order.id }}",
  "orderNumber": "{{ order.name }}",
  "createdAt": "{{ order.created_at }}",
  "currency": "{{ order.currency }}",
  "subtotal": {{ order.subtotal_price }},
  "tax": {{ order.total_tax }},
  "shipping": {{ order.total_shipping_price_set.shop_money.amount }},
  "discount": {{ order.total_discounts }},
  "total": {{ order.total_price }},
  "customerId": "{{ order.customer.id }}",
  "channel": "{{ order.source_name }}",
  "lineCount": {{ order.line_items.length }}
}

Setting _id to the order ID is what makes the upsert idempotent on the next step.

Step 4: Upsert into MongoDB

Add a Connector node pointing at mongodb using update-documents. Configure:

Database: revenue
Collection: daily_orders
Filter: { "_id": "{{ record._id }}" }
Update: { "$set": {{ record }} }
Upsert: true

Wrap this in a Loop over the transformed array. For large days, batch records and use insert-documents with an ordered: false option for throughput.

Step 5: Compute Daily Roll-up

Add a Connector node pointing at mongodb using aggregate to produce a daily summary row in revenue.daily_summary. Group by currency and sum the totals. Dashboards that just want headline numbers query this collection instead of scanning every order.

Step 6: Notify and Record Run Health

Send a slack send-message with yesterday's order count and gross revenue. On failure, route the error to the same channel with the run trace link. Optionally store run metadata (order count, sync window) in revenue.sync_runs so you can chart freshness over time.

Tips

Index createdAt, customerId, and currency in your collection. Every dashboard groups or filters on at least one of these.
For MySQL, define the table with the order ID as the primary key and use INSERT ... ON DUPLICATE KEY UPDATE via the mysql connector's execute-query tool to keep upserts atomic.
Store both the order's currency and a normalized USD total so cross-currency analytics work without per-query FX conversion.

Common Pitfalls

Late-arriving refunds. A refund posted three days after the order won't change yesterday's row. Add a second weekly sweep that re-fetches orders updated in the last 30 days to keep historical rows accurate.
Timezone confusion. Use UTC on the storage side and let dashboards convert to local. The sync window itself should match your reporting timezone, not the store's UTC clock.
Pagination limits. Shopify caps page size at 250 orders. Don't assume one call covers a busy day.

Testing

Run the workflow manually with a one-day window for last week. Spot check a handful of orders against the store admin to confirm fields match. Drop a sample dashboard onto the resulting collection; if the numbers reconcile with Shopify's own reports for that day, enable the schedule.