Automate Shopify Sales Reports with Python and SQL

Automating Shopify sales reports means pulling order data through the Shopify Admin API with a Python script, storing it in a SQL database, and running scheduled queries that output daily revenue, product performance, refunds, and fees. It replaces manual CSV exports with a repeatable pipeline that a store owner with basic Python skills can run on a laptop or a small server.

Key takeaways

• Manual CSV exports break as order volume grows: copy-paste mistakes, stale numbers, and hours of spreadsheet work every week.

• A short Python script using the Shopify REST Admin API pulls every order with cursor-based pagination and stores it in SQLite or Postgres.

• Five SQL queries cover what most stores actually need: daily revenue, revenue by product, refunds, fees versus net, and tax collected.

• Shopify's rate limit is 2 requests per second with a 40-request bucket, so the script must pause and retry on 429 responses.

• This pipeline is a reporting tool, not an accounting system: it does not create invoices in Xero and does not replace bookkeeping.

• A cron job runs the whole thing automatically every morning before you open your laptop.

Why manual sales reporting breaks

Every Shopify store starts with the same ritual: open the admin, export orders to CSV, filter by date in a spreadsheet, build a pivot table, and email it to yourself or your accountant. It works until it does not. At a few hundred orders a month the export takes minutes and someone has to remember to do it. Copy a formula one row too far and revenue is wrong for a week before anyone notices. Save over the wrong file and last month's numbers are gone. Multi-currency stores have it worse: euros, pounds, and dollars land in a single column and the spreadsheet has no idea which is which. Then comes a sale or a holiday spike, the export times out, and the Monday report is built from half the data.

The deeper problem is that a CSV export is a photograph. The moment you download it, it is out of date. A pipeline, by contrast, is a live feed: the same script pulls the same fields, the same queries run, and the numbers are fresh every morning with no human in the loop to mistype them.

There is a cost beyond your own time. Accountants routinely charge by the hour to untangle a year of inconsistent CSVs at tax time, and every hour they spend fixing your spreadsheet is an hour you pay for. A pipeline that has been running all year hands them clean, consistent tables instead. The stores that feel this most are the ones that just had their first real spike: a Black Friday, a viral post, a large wholesale order. Volume is what turns an annoyance into a mess.

How do I connect Python to my Shopify store?

Shopify does not want your admin password inside a script, and neither should you. Instead you create a custom app, which is simply a named set of API permissions with its own token. In your Shopify admin go to Settings, then Apps and sales channels, then Develop apps, and create an app. Under Configuration, grant the Admin API scopes read_orders and, if you want fee data later, read_shopify_payments. Install the app and Shopify shows an Admin API access token starting with shpat_. It is displayed exactly once, so copy it somewhere safe immediately.

Treat the token like a password. Keep it in an environment variable, never hard-code it in the script, and never commit it to git. If it ever leaks, rotate it from the same screen in seconds. You need store-owner level permissions to create the app; a staff account with only order-viewing rights cannot do this step. The token is all the script needs. No OAuth dance, no browser login.

If you want to practise safely first, create a free development store from the Shopify Partners dashboard. It exposes the same API with sample orders, and nothing you do there touches your real shop. Get the script working there, then point it at production.

How do I pull every order without missing one?

The REST Admin API returns at most 250 orders per call and tells you where the next page starts through a Link response header containing a cursor token. Your script follows that cursor until no next page remains. Two details matter. First, pass status=any so cancelled and archived orders are included; without it you silently miss them. Second, respect the rate limit: Shopify's documentation states 2 requests per second with a 40-request bucket, and the API answers HTTP 429 with a Retry-After header when you go too fast. The script below pauses and retries instead of crashing. Request only the fields you need; it keeps responses small and fast.

import time
import requests

SHOP = "your-store.myshopify.com"   # your shop domain
TOKEN = "shpat_xxxxxxxxxxxxxxxx"     # Admin API access token, keep secret
VERSION = "2024-10"                  # pin a dated API version
HEADERS = {"X-Shopify-Access-Token": TOKEN}
BASE = f"https://{SHOP}/admin/api/{VERSION}"

def get_json(url):
    """GET with backoff on 429. Shopify allows 2 req/s, 40-request bucket."""
    while True:
        r = requests.get(url, headers=HEADERS, timeout=30)
        if r.status_code == 429:
            time.sleep(int(r.headers.get("Retry-After", 2)))
            continue
        r.raise_for_status()
        return r

def fetch_all_orders():
    url = (BASE + "/orders.json?status=any&limit=250"
           "&fields=id,name,created_at,total_price,subtotal_price,"
           "total_tax,total_discounts,financial_status,currency,"
           "refunds,line_items")
    orders = []
    while url:
        r = get_json(url)
        orders.extend(r.json()["orders"])
        url = None
        for part in r.headers.get("Link", "").split(","):
            if 'rel="next"' in part:
                url = part[part.find("")]
    return orders

For the daily run you do not need the full history every time. Remember the newest created_at you have stored, and on the next run pass it as created_at_min so the API returns only new orders. The first ever run backfills everything; every run after that is incremental and takes seconds.

Storing orders in a SQL database

A script that prints numbers to the terminal is a toy. A database turns each pull into an asset: you can ask about last Tuesday, join orders to line items, and build any report without hitting the API again. SQLite needs no server and comfortably handles hundreds of thousands of orders on a laptop; the schema and queries below run unchanged on Postgres when you outgrow it. Three tables are enough: orders, line items, and refunds. The loader uses INSERT OR REPLACE keyed on Shopify's own IDs, so running it twice never duplicates a row, it only refreshes orders that changed. Two small indexes keep the daily queries fast as the tables grow.

import sqlite3

con = sqlite3.connect("shopify.db")
cur = con.cursor()

cur.executescript("""
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    name TEXT,
    created_at TEXT,
    total_price REAL,
    subtotal REAL,
    tax REAL,
    discounts REAL,
    financial_status TEXT,
    currency TEXT
);
CREATE TABLE IF NOT EXISTS line_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER,
    product_title TEXT,
    quantity INTEGER,
    price REAL
);
CREATE TABLE IF NOT EXISTS refunds (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    created_at TEXT,
    amount REAL
);
CREATE INDEX IF NOT EXISTS idx_orders_date ON orders(date(created_at));
CREATE INDEX IF NOT EXISTS idx_items_order ON line_items(order_id);
""")

def save_orders(orders):
    for o in orders:
        cur.execute("""
            INSERT OR REPLACE INTO orders
            (id, name, created_at, total_price, subtotal, tax,
             discounts, financial_status, currency)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (o["id"], o["name"], o["created_at"],
              float(o["total_price"]), float(o["subtotal_price"]),
              float(o["total_tax"]), float(o["total_discounts"]),
              o["financial_status"], o["currency"]))
        cur.execute("DELETE FROM line_items WHERE order_id = ?", (o["id"],))
        for li in o.get("line_items", []):
            cur.execute("""
                INSERT INTO line_items (order_id, product_title, quantity, price)
                VALUES (?, ?, ?, ?)
            """, (o["id"], li["title"], li["quantity"], float(li["price"])))
        for rf in o.get("refunds", []):
            amount = sum(float(t["amount"])
                         for t in rf.get("transactions", [])
                         if t["kind"] == "refund")
            cur.execute("""
                INSERT OR REPLACE INTO refunds (id, order_id, created_at, amount)
                VALUES (?, ?, ?, ?)
            """, (rf["id"], o["id"], rf["created_at"], amount))
    con.commit()

Two practical notes. Money is stored as REAL here for readability; if you ever do serious accounting maths, store minor units (cents) as integers to avoid floating point surprises. And created_at arrives as ISO 8601 with a timezone offset, which SQLite's date() parses and normalises to UTC, so stores far from UTC may want to shift the grouping with a modifier such as date(created_at, '+1 hour').

What are the five reports every store owner needs?

With the data in place, the reports are plain SQL. These five answer the questions store owners actually ask every week.

1. Daily revenue

How much did we make yesterday, and is the trend healthy? Only count orders that were actually paid.

SELECT date(created_at) AS day,
       COUNT(*) AS orders,
       ROUND(SUM(total_price), 2) AS gross_revenue
FROM orders
WHERE financial_status IN ('paid', 'partially_paid', 'partially_refunded')
GROUP BY day
ORDER BY day DESC
LIMIT 30;

2. Revenue by product

Which products earn their shelf space and which just take it up?

SELECT product_title,
       SUM(quantity) AS units_sold,
       ROUND(SUM(quantity * price), 2) AS revenue
FROM line_items
GROUP BY product_title
ORDER BY revenue DESC
LIMIT 20;

3. Refunds

How much are we giving back, and is it climbing? A rising refund line is often the first sign of a product or shipping problem.

SELECT date(created_at) AS day,
       COUNT(*) AS refund_count,
       ROUND(SUM(amount), 2) AS refunded
FROM refunds
GROUP BY day
ORDER BY day DESC
LIMIT 30;

4. Fees versus net

What did we really keep after the payment gateway took its cut? Fee data lives in the Shopify Payments balance transactions endpoint, which needs the read_shopify_payments scope. Note the honest limit: this covers Shopify Payments only. PayPal and manual gateways report fees elsewhere.

def fetch_balance_transactions():
    url = BASE + "/shopify_payments/balance/transactions.json?limit=100"
    txns = []
    while url:
        r = get_json(url)
        txns.extend(r.json()["transactions"])
        url = None
        for part in r.headers.get("Link", "").split(","):
            if 'rel="next"' in part:
                url = part[part.find("")]
    return txns

cur.execute("""
CREATE TABLE IF NOT EXISTS balance_txns (
    id INTEGER PRIMARY KEY,
    source_order_id INTEGER,
    type TEXT,
    amount REAL,
    fee REAL,
    net REAL,
    currency TEXT,
    payout_date TEXT
)
""")

def save_balance(txns):
    for t in txns:
        cur.execute("""
            INSERT OR REPLACE INTO balance_txns
            (id, source_order_id, type, amount, fee, net, currency, payout_date)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, (t["id"], t.get("source_order_id"), t["type"],
              float(t["amount"]), float(t["fee"]), float(t["net"]),
              t["currency"], t["payout_date"]))
    con.commit()

SELECT date(payout_date) AS day,
       ROUND(SUM(amount), 2) AS gross,
       ROUND(SUM(fee), 2) AS fees,
       ROUND(SUM(net), 2) AS net_received
FROM balance_txns
WHERE type IN ('charge', 'refund') AND payout_date IS NOT NULL
GROUP BY day
ORDER BY day DESC
LIMIT 30;

5. Tax collected

How much VAT or sales tax did we collect? Your accountant will ask for this every quarter, and now it is one query instead of an afternoon.

SELECT date(created_at) AS day,
       ROUND(SUM(tax), 2) AS tax_collected
FROM orders
WHERE financial_status IN ('paid', 'partially_paid', 'partially_refunded')
GROUP BY day
ORDER BY day DESC
LIMIT 30;

Need to hand numbers to an accountant? Dump any query straight to CSV with Python's built-in csv module, no extra packages required:

import csv

rows = cur.execute(
    "SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000")
with open("orders_export.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow([d[0] for d in rows.description])
    w.writerows(rows)

Where to look first each morning: compare net received against gross revenue to see what fees and refunds actually cost you, and watch the refund rate (refunded divided by gross) week over week. A refund rate creeping upward is usually a product quality or shipping problem wearing a finance costume.

How do I run this every morning automatically?

A report you have to remember to run is a report that stops being run. On Linux or macOS, one cron line runs the script at 7am every day and appends output to a log file:

0 7 * * * /usr/bin/python3 /home/store/shopify_reports.py >> /home/store/reports.log 2>&1

On Windows, Task Scheduler does the same job with a basic daily trigger. Tie the pieces together with a small entry point:

if __name__ == "__main__":
    orders = fetch_all_orders()
    save_orders(orders)
    print(f"Saved {len(orders)} orders")
    save_balance(fetch_balance_transactions())
    print("Balance transactions updated")

The first run backfills your entire order history, which can take a few minutes on large stores. Later runs pass created_at_min so they only fetch what is new. Because every write is an upsert, reruns are safe: changed orders refresh, nothing duplicates.

Want the numbers in your inbox instead of a log file? Have the script attach the CSV export and send it with Python's built-in smtplib through your email provider. The whole pipeline then needs nothing but the standard library plus requests.

What this pipeline does not do

Honest limits, stated plainly. This is a reporting pipeline, not an accounting system: it does not create invoices in Xero, reconcile payouts against bank feeds, or file VAT returns. Amounts stay in each order's original currency; the script does not convert them. Fee data covers Shopify Payments only, not PayPal or manual gateways. Shopify versions its API every quarter, so the script pins a dated version such as 2024-10; check the changelog before upgrading. And refunds only appear after the order is re-pulled, which is why the loader refreshes instead of appending. For daily decisions this pipeline is plenty. For the books themselves, you still need proper accounting.

Want this wired into your actual books? We fix broken Shopify to Xero invoicing for a flat $149 — free diagnosis, no monthly fees.

---

Originally published on LogicStack. Run the code examples right in your browser with our free interactive Python and SQL playgrounds — no setup, no sign-up.