Table: shopify_product - Query Shopify Products using SQL
Shopify is an e-commerce platform that allows businesses to create online stores and sell their products. It provides a variety of tools for managing products, inventory, payments, and shipping. Shopify's Product resource represents individual items that a store's customers can purchase.
Table Usage Guide
The shopify_product
table provides insights into products within Shopify. As a store manager or business analyst, explore product-specific details through this table, including pricing, inventory, and associated metadata. Utilize it to uncover information about products, such as their availability status, variant details, and the verification of product details.
Examples
Basic info
Explore the basic attributes of your Shopify products such as their ID, title, type, creation date, and vendor. This query is beneficial in providing a quick overview of your product catalog.
select id, title, product_type, created_at, vendorfrom shopify_product;
select id, title, product_type, created_at, vendorfrom shopify_product;
Count how many items of a specific type are there
Explore the variety of items in your Shopify store by assessing the total count of each product type. This can aid in inventory management and understanding your product diversity.
select product_type, count(*) as product_countfrom shopify_productgroup by product_type;
select product_type, count(*) as product_countfrom shopify_productgroup by product_type;
List products with a specific tag
Explore which products in your Shopify store are labeled as 'Premium'. This can help you identify your high-end offerings and analyze their performance.
select id, title, created_atfrom shopify_productwhere tags like '%Premium%';
select id, title, created_atfrom shopify_productwhere tags like '%Premium%';
List products created within the last 30 days
Explore which products have been added to your Shopify store in the past month. This is useful for tracking inventory updates and identifying recent additions to your product catalog.
select id, title, created_atfrom shopify_productwhere created_at >= now() - interval '30' dayorder by created_at;
select id, title, created_atfrom shopify_productwhere created_at >= datetime('now', '-30 day')order by created_at;
List archived products
Discover the segments that contain archived products in your Shopify store. This is beneficial for assessing inventory management and identifying products that are no longer active.
select id, title, created_atfrom shopify_productwhere status = 'archived';
select id, title, created_atfrom shopify_productwhere status = 'archived';
List the product variants with quantities less than 20
Discover the segments that have product variants with low stock levels. This can help businesses to plan for restocking and prevent potential sales losses due to unavailability of popular products.
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.inventory_quantity as variant_inventory_quantityfrom shopify_product as p, shopify_product_variant as vwhere v.inventory_quantity < 20;
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.inventory_quantity as variant_inventory_quantityfrom shopify_product as p, shopify_product_variant as vwhere v.inventory_quantity < 20;
List the product variants which require shipping
Explore which product variants require shipping, helping you to plan logistics and shipping costs more effectively. Similarly, determine the product variants that are taxable, providing crucial information for accurate financial planning and tax compliance.
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.requires_shipping as requires_shippingfrom shopify_product as p, shopify_product_variant as vwhere v.requires_shipping;
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.requires_shipping as requires_shippingfrom shopify_product as p, shopify_product_variant as vwhere v.requires_shipping;
List the product variants which are taxable
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.taxable as taxable, v.tax_code as tax_codefrom shopify_product as p, shopify_product_variant as vwhere v.taxable;
select p.id as product_id, p.title as product_title, v.inventory_item_id as variant_inventory_item_id, v.taxable as taxable, v.tax_code as tax_codefrom shopify_product as p, shopify_product_variant as vwhere v.taxable;
Get the best selling product of the last month
Determine the top-selling product over the past month for strategic business insights. This aids in understanding consumer behavior, product performance, and can guide inventory management and marketing efforts.
select p.id, p.title, p.product_type, p.created_at, p.vendor, q.c as sales_countfrom shopify_product as p join ( select item ->> 'product_id' as id, count(*) as c, min(created_at) AS order_date from shopify_order, jsonb_array_elements(line_items) as item where created_at >= (CURRENT_DATE - interval '30' day) group by item ->> 'product_id' order by c desc, order_date limit 1 ) as q on p.id = q.id :: bigint;
select p.id, p.title, p.product_type, p.created_at, p.vendor, q.c as sales_countfrom shopify_product as p join ( select json_extract(item.value, '$.product_id') as id, count(*) as c, min(created_at) AS order_date from shopify_order, json_each(line_items) as item where date(created_at) >= date(julianday('now') - 30) group by json_extract(item.value, '$.product_id') order by c desc, order_date limit 1 ) as q on p.id = q.id;
Schema for shopify_product
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
admin_graphql_api_id | text | The unique identifier for the product used in the GraphQL Admin API. | |
body_html | text | The HTML content for the product description. | |
created_at | timestamp with time zone | The date and time when the product was created. | |
handle | text | The unique URL for the product page. | |
id | bigint | = | The unique identifier for the shopify product. |
image | jsonb | The primary image of the product. | |
images | jsonb | Image objects representing additional images of the product. | |
metafields | jsonb | The additional metadata associated with the product. | |
metafields_global_description_tag | text | The description tag used for the product page. | |
metafields_global_title_tag | text | The title tag used for the product page. | |
options | jsonb | The various options available for the product. | |
product_title | text | The title of the product. | |
product_type | text | The type or category of the products sold. | |
published_at | timestamp with time zone | The date and time when the product was published. | |
published_scope | text | The visibility of the product. | |
status | text | The product status. | |
tags | text | The comma-separated list of tags assigned to the product. | |
template_suffix | text | The template used for the product page. | |
title | text | Title of the resource. | |
updated_at | timestamp with time zone | The date and time when the product was last updated. | |
vendor | text | The name of the vendor who supplies shopify products. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- shopify
You can pass the configuration to the command with the --config
argument:
steampipe_export_shopify --config '<your_config>' shopify_product