ShopifySQL.md

ShopifyQL Query Gist

This Gist contains a collection of ShopifyQL queries designed for in-depth analysis of sales, customer behavior, and channel performance metrics. Each query focuses on a specific aspect of e-commerce analytics, offering insights into trends and operational efficiency.

1. Daily Orders by Sales Channel (Online Store)

Description: Analyzes daily orders for the Online Store sales channel, including totals and averages for the past 7 days.

FROM sales
SHOW orders, average_order_value, shipping_charges, taxes, total_sales
WHERE sales_channel IN ('Online Store')
GROUP BY order_id, order_checkout_currency, sales_channel
WITH GROUP_TOTALS, TOTALS
SINCE startOfDay(-7d)
UNTIL endOfDay(-1d)
ORDER BY orders__order_id_totals DESC, orders__order_id_order_checkout_currency_totals DESC, orders DESC, order_id ASC, order_checkout_currency ASC, sales_channel ASC
LIMIT 1000

2. Visitor Conversion Rates by Country

Description: Displays visitor conversion rates by country, focusing on added-to-cart and checkout completion rates for the last 7 days.

FROM sessions
SHOW online_store_visitors, added_to_cart_rate, completed_checkout_rate
WHERE session_country IS NOT NULL AND customer_number_of_orders > 0
GROUP BY session_country
WITH TOTALS
SINCE startOfDay(-7d)
UNTIL today
ORDER BY completed_checkout_rate DESC
LIMIT 1000

3. Customer Analysis by Day of Week (New vs Returning)

Description: Evaluates new versus returning customer behavior by day of the week over the past 30 days, with a comparison to the previous period.

FROM sales
SHOW returning_customers, new_customers, average_order_value, orders, orders_returning
GROUP BY day_of_week
TIMESERIES day_of_week
WITH TOTALS, PERCENT_CHANGE
SINCE startOfDay(-30d)
UNTIL endOfDay(-1d)
COMPARE TO previous_period
ORDER BY day_of_week ASC
LIMIT 1000

4. Customer Order Analysis (Combining Sessions and Sales)

Description: Examines the relationship between sessions and sales, focusing on customer order counts and total sales over the last 30 days.

FROM sessions, sales
SHOW online_store_visitors, number_of_orders_per_customer, total_sales
WHERE customer_number_of_orders > 0
GROUP BY customer_id, customer_number_of_orders
WITH TOTALS
SINCE -30d
UNTIL today
ORDER BY customer_number_of_orders DESC, online_store_visitors DESC
LIMIT 1000

5. Repeat Customer Orders by Sales Channel (Detailed)

Description: Highlights repeat orders segmented by sales channel, visualized as a bar chart.

FROM sales
SHOW orders
WHERE customer_number_of_orders > 0 AND customer_number_of_orders > 1
GROUP BY sales_channel, customer_id, customer_number_of_orders
WITH TOTALS, GROUP_TOTALS
SINCE -30d
UNTIL today
ORDER BY orders__sales_channel_totals DESC, orders__sales_channel_customer_id_totals DESC, orders DESC, sales_channel ASC, customer_id ASC, customer_number_of_orders ASC
LIMIT 1000
VISUALIZE orders TYPE bar

6. Sales Channel Performance for Repeat Customers

Description: Analyzes performance metrics for repeat customers across various sales channels.

FROM sales
SHOW orders, total_sales, average_order_value
WHERE customer_number_of_orders > 1
GROUP BY sales_channel
WITH TOTALS
SINCE -30d
UNTIL today
ORDER BY orders DESC
VISUALIZE orders, total_sales TYPE bar

7. Weekly Repeat Customer Trends (12-Week Comparison)

Description: Tracks weekly trends in repeat customer orders and sales over a 12-week period with comparisons to the previous period.

FROM sales
SHOW orders, total_sales
WHERE customer_number_of_orders > 1
GROUP BY week
TIMESERIES week
SINCE -12w
UNTIL today
COMPARE TO previous_period
VISUALIZE orders, total_sales TYPE line

8. 90-Day Multi-Channel Performance Overview (Weekly)

Description: Provides a 90-day overview of performance across multiple channels, grouped by week and visualized as grouped bars.

FROM sales, sessions
SHOW orders, total_sales, average_order_value, customers
WHERE (sales_channel IN ('Draft Orders', 'Online Store', 'POS', 'Mobile App') OR sales_channel IS NULL)
GROUP BY week
TIMESERIES week
WITH TOTALS, PERCENT_CHANGE
SINCE -90d
UNTIL today
COMPARE TO previous_period
ORDER BY week ASC
LIMIT 1000
VISUALIZE orders, total_sales, average_order_value TYPE grouped_bar

9. 30-Day Daily Sales and Customer Metrics

Description: Tracks daily sales and customer metrics over the last 30 days, comparing to the previous period.

FROM sales, sessions
SHOW orders, total_sales, average_order_value, customers
WHERE sales_channel IN ('Draft Orders', 'Online Store', 'POS', 'Mobile App')
GROUP BY day
TIMESERIES day
WITH TOTALS, PERCENT_CHANGE
SINCE -30d
UNTIL today
COMPARE TO previous_period
VISUALIZE orders, total_sales TYPE line

10. 90-Day Sales Distribution by Channel and Day of Week

Description: Analyzes sales distribution across channels and days of the week for the past 90 days, visualized as stacked bars.

FROM sales, sessions
SHOW orders, total_sales, average_order_value, customers
WHERE sales_channel IN ('Draft Orders', 'Online Store', 'POS', 'Mobile App')
GROUP BY day_of_week
TIMESERIES week
WITH TOTALS, GROUP_TOTALS
SINCE -90d
UNTIL today
ORDER BY week ASC, total_sales DESC
VISUALIZE total_sales TYPE stacked_bar

11. 90-Day Customer Acquisition and Value Analysis

Description: Tracks customer acquisition and value metrics over the past 90 days, segmented by week.

FROM sales, sessions
SHOW customers, average_order_value, returning_customers, new_customers
WHERE sales_channel IN ('Draft Orders', 'Online Store', 'POS', 'Mobile App')
GROUP BY week
TIMESERIES week
WITH TOTALS, PERCENT_CHANGE
SINCE -90d
UNTIL today
COMPARE TO previous_period
VISUALIZE customers TYPE bar