Shopify plugin

Run SQL queries on your Shopify store.

Installation

You need Anyquery to run this plugin.

Then, install the plugin with the following command:

anyquery install shopify

At some point, you will be asked to provide your Shopify API key. You can find it by creating a private app. To do so, follow these steps:

Now we'll configure the scopes needed for the app. Click on Select scopes and select the following scopes:

Once done, click on Install app. Read the warning and click on Install. Copy the API key (reveal it once) and paste it when asked by anyquery.

You'll also need to provide the shop name. You can find it in the URL of your Shopify store. For example, if your store URL is https://my-store.myshopify.com, then your shop name is my-store.

⚠️ Note that anyquery might access data that is subject to the GDPR. While all anyquery processing happens locally, you should be aware of the risks associated with sharing your data with third-party services. For example when using a BI tool connected to anyquery, the data might be sent to the BI tool's servers.

Usage

Once configured, you can run SQL queries on your Shopify store. Here are some examples:

-- Get the top paying customers of your store
SELECT email, first_name, last_name, amount_spent || amount_spent_currency as total FROM shopify_customers ORDER BY amount_spent DESC LIMIT 1;
-- Get the revenue per month
SELECT strftime('%Y-%m', created_at) as month, sum(total_price) as revenue FROM shopify_orders GROUP BY month ORDER BY month;
-- List your products and their stock
SELECT title, total_inventory FROM shopify_products;
-- List how much orders were returned
SELECT count(*) as returned_orders FROM shopify_orders WHERE return_status <> 'NO_RETURN';
-- List product variants that are out of stock
SELECT display_name, product_title FROM shopify_product_variants WHERE inventory_quantity = 0;

Schema

shopify_customers

List of customers of your store.

Column index Column name type
0 id TEXT
1 created_at TEXT
2 updated_at TEXT
3 display_name TEXT
4 email TEXT
5 first_name TEXT
6 last_name TEXT
7 locale TEXT
8 note TEXT
9 phone TEXT
10 subscription_status TEXT
11 state TEXT
12 tags TEXT
13 amount_spent INTEGER
14 amount_spent_currency TEXT
15 data_sale_opt_out INTEGER
16 valid_email INTEGER
17 verified_email INTEGER
18 tax_exempt INTEGER

shopify_orders

List the orders of your store.

Column index Column name type
0 id TEXT
1 name TEXT
2 financial_status TEXT
3 fulfillment_status TEXT
4 return_status TEXT
5 consumer_email TEXT
6 created_at TEXT
7 processed_at TEXT
8 updated_at TEXT
9 unpaid INTEGER
10 confirmed INTEGER
11 fulfillable INTEGER
12 fully_paid INTEGER
13 requires_shipping INTEGER
14 total_weight REAL
15 total_price REAL
16 current_total_price REAL
17 total_discounts REAL

shopify_products

List the products of your store.

Column index Column name type
0 id TEXT
1 title TEXT
2 vendor TEXT
3 product_type TEXT
4 created_at TEXT
5 updated_at TEXT
6 status TEXT
7 description TEXT
8 description_html TEXT
9 store_url TEXT
10 store_preview_url TEXT
11 total_inventory INTEGER

shopify_product_variants

List all the product variants of the products of your store.

Column index Column name type
0 id TEXT
1 barcode TEXT
2 created_at TEXT
3 updated_at TEXT
4 display_name TEXT
5 sku TEXT
6 title TEXT
7 available_for_sale INTEGER
8 inventory_quantity INTEGER
9 position TEXT
10 sellable_online_quantity TEXT
11 product_id TEXT
12 product_title TEXT
13 product_vendor TEXT
14 product_type TEXT
15 product_created_at TEXT
16 product_updated_at TEXT
17 product_status TEXT
18 product_description TEXT
19 product_description_html TEXT
20 product_store_url TEXT
21 product_store_preview_url TEXT
22 product_total_inventory INTEGER

Known limitations