How to Export Shopify Data to a SQL Database for Custom Analytics

Shopify’s admin dashboard shows you sales trends and basic metrics. But try answering these questions with it:

• What’s the average time between a customer’s first and second purchase?

• Which product combinations are most frequently bought together?

• How does customer lifetime value differ by acquisition source?

• What’s my true profit margin after returns, discounts, and shipping?

You can’t. These require SQL access to your raw data. Here’s how to set it up.

Option 1: CSV Export (Quick & Free)

The simplest approach. Export data from Shopify Admin and import into a local database.

What You Can Export

• Orders (Admin → Orders → Export)

• Products (Admin → Products → Export)

• Customers (Admin → Customers → Export)

• Discount codes (Admin → Discounts)

• Financial reports (Admin → Analytics → Reports)

Import into MySQL

-- Create orders table
CREATE TABLE shopify_orders (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255),
    financial_status VARCHAR(50),
    fulfillment_status VARCHAR(50),
    total_price DECIMAL(10,2),
    subtotal_price DECIMAL(10,2),
    total_discounts DECIMAL(10,2),
    total_tax DECIMAL(10,2),
    currency VARCHAR(3),
    created_at DATETIME,
    updated_at DATETIME,
    cancelled_at DATETIME,
    referring_site VARCHAR(500),
    landing_site VARCHAR(500),
    source_name VARCHAR(100),
    discount_codes TEXT,
    shipping_country VARCHAR(100),
    shipping_city VARCHAR(100)
);

-- Load CSV
LOAD DATA LOCAL INFILE '/path/to/orders_export.csv'
INTO TABLE shopify_orders
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS;

Pros & Cons

• Pro: Free, no tools needed

• Pro: Works with any database

• Con: Manual process, data gets stale

• Con: Limited to what Shopify exports

Option 2: Shopify API + Script (Automated)

Write a script that pulls data from Shopify’s REST or GraphQL API on a schedule.

Key API Endpoints

Endpoint	Data	Rate Limit
/admin/api/orders.json	Orders with line items	2 req/sec
/admin/api/products.json	Product catalog	2 req/sec
/admin/api/customers.json	Customer profiles	2 req/sec
/admin/api/inventory_levels.json	Stock levels	2 req/sec

Example: Sync Orders to PostgreSQL

-- Table structure matching Shopify API response
CREATE TABLE shopify_orders (
    id BIGINT PRIMARY KEY,
    order_number INTEGER,
    customer_id BIGINT,
    email VARCHAR(255),
    total_price DECIMAL(10,2),
    subtotal_price DECIMAL(10,2),
    total_discounts DECIMAL(10,2),
    total_tax DECIMAL(10,2),
    financial_status VARCHAR(50),
    fulfillment_status VARCHAR(50),
    referring_site TEXT,
    landing_site TEXT,
    source_name VARCHAR(100),
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    tags TEXT,
    note TEXT
);

CREATE INDEX idx_orders_created ON shopify_orders(created_at);
CREATE INDEX idx_orders_customer ON shopify_orders(customer_id);
CREATE INDEX idx_orders_status ON shopify_orders(financial_status);

Option 3: ETL Tools (Best for Scale)

For stores with 1,000+ orders/month, use a managed ETL pipeline:

Tool	Destination	Price	Setup Time
Stitch	BigQuery, Redshift, PostgreSQL	From $100/mo	15 minutes
Fivetran	Snowflake, BigQuery, Redshift	From $250/mo	10 minutes
Airbyte	Any SQL database	Free (self-hosted)	30 minutes
Coupler.io	BigQuery, PostgreSQL	From $49/mo	5 minutes

These tools handle incremental syncs, schema changes, and API pagination automatically.

Option 4: ShopifyQL (Shopify Plus)

If you’re on Shopify Plus, you get ShopifyQL Notebooks for direct querying:

FROM orders
SHOW total_sales, order_count
GROUP BY day
SINCE -30d
ORDER BY day

ShopifyQL is simpler than SQL but limited in capabilities. For complex analysis, export to a real database.

Queries You Can Run Once Data Is in SQL

Customer Acquisition Cost by Channel

SELECT 
    CASE 
        WHEN referring_site LIKE '%google%' THEN 'Google'
        WHEN referring_site LIKE '%facebook%' OR referring_site LIKE '%instagram%' THEN 'Meta'
        WHEN referring_site LIKE '%tiktok%' THEN 'TikTok'
        WHEN referring_site IS NULL THEN 'Direct'
        ELSE 'Other'
    END as channel,
    COUNT(DISTINCT customer_id) as new_customers,
    SUM(total_price) as revenue,
    AVG(total_price) as avg_order
FROM shopify_orders
WHERE financial_status = 'paid'
  AND cancelled_at IS NULL
  AND created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY channel
ORDER BY new_customers DESC;

Product Pair Analysis (Bought Together)

SELECT 
    li1.title as product_a,
    li2.title as product_b,
    COUNT(DISTINCT li1.order_id) as times_together
FROM order_line_items li1
JOIN order_line_items li2 
    ON li1.order_id = li2.order_id 
    AND li1.product_id < li2.product_id
GROUP BY li1.title, li2.title
HAVING times_together >= 5
ORDER BY times_together DESC
LIMIT 20;

Time Between Purchases

SELECT 
    ROUND(AVG(days_between), 0) as avg_days_between_orders,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_between), 0) as median_days
FROM (
    SELECT 
        customer_id,
        DATEDIFF(
            created_at, 
            LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)
        ) as days_between
    FROM shopify_orders
    WHERE financial_status = 'paid' AND cancelled_at IS NULL
) intervals
WHERE days_between IS NOT NULL;

Let AI Generate Your Queries

Setting up the data pipeline is the hard part. Once your Shopify data is in SQL, getting insights should be easy.

AI2SQL turns your business questions into SQL queries instantly. No syntax memorization, no JOIN debugging, no date function lookups.

Set up your data pipeline. Let AI handle the queries. Start at ai2sql.io.

Share this

More Articles

More Articles

More Articles

TOOLS

Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide

Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide

Feb 5, 2026

TOOLS

OpenClaw AI Assistant: Local 24/7 Automation Guide 2026

OpenClaw AI Assistant: Local 24/7 Automation Guide 2026

Feb 4, 2026

TOOLS

SQL WITH Clause (CTE): Complete Guide with Examples

SQL WITH Clause (CTE): Complete Guide with Examples

Jan 14, 2026

TOOLS

MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion

MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion

Jan 14, 2026

TOOLS

SQL vs Excel: When Should You Make the Switch? [2026]

SQL vs Excel: When Should You Make the Switch? [2026]

Jan 14, 2026

Copyright © AI2sql 2026

Cross Regions Technology

13553 Atlantic Blvd, Suite 201

FL 32225

support@ai2sql.io

Company