Create a custom Product Conversion Funnel Report with ShopifyQL Notebooks

ShopifyQL Notebooks enables any Plus store to create custom reports using SQL.

Rob Johnson

By Rob Johnson

Thu Mar 09 2023

#shopify#sql

Shopify released ShopifyQL Notebooks in Summer 2022. As a beta group member, I had the opportunity to provide feedback directly to the Product team.

I was immediately onboard with this product, as it can be a powerful tool for querying data directly within your Shopify instance without requiring a Data Warehouse connection/service such as Snowflake or PowerBI, which can be very painful if you are used to having direct access to your storefront's database for querying.

ShopifyQL Notebooks definitely has its limitations. However, it can be very helpful in quickly grabbing accurate data if it's readily available in their data model set. Also, it's not true SQL syntax and does not have the same verbose options as traditional SQL options such as Postgres or MySQL.

ShopifyQL Notebook SQL for funnel conversion by product

The query below provides insights into the performance of products, including their add-to-cart rates, checkout rates, conversion rates, net sales, and average quantity purchased. This information can help you quickly identify the best-performing products for your store, enabling you to optimize your merchandising and product placement strategies and increase your conversion rates and revenue.

As you can see, the below statement is not true SQL syntax. ShopifyQL doesn't have the same verbose options as traditional SQL options such as Postgres or MySQL. Here is the ShopifyQL syntax reference.

FROM products
SHOW
sum(view_sessions) AS views,
sum(view_cart_sessions) AS add_to_carts,
sum(view_cart_sessions) / sum(view_sessions) AS add_rate,
sum(view_cart_checkout_sessions) AS checkouts,
sum(view_cart_checkout_sessions) / sum(view_sessions) AS checkout_rate,
sum(view_cart_checkout_purchase_sessions) AS purchases,
sum(view_cart_checkout_purchase_sessions) / sum(view_sessions) AS conversion_rate,
sum(net_sales) AS net_sales,
sum(quantity_purchased) / sum(purchase_sessions) AS avg_qty
GROUP BY product_title
SINCE last_month UNTIL yesterday
ORDER BY net_sales DESC
LIMIT 50

ShopifyQL Notebook SQL for funnel conversion by Product Type

This query performs the same calculations as before, but does a GROUP BY product_type instead of product_title.

FROM products
SHOW
sum(view_sessions) AS views,
sum(view_cart_sessions) AS add_to_carts,
sum(view_cart_sessions) / sum(view_sessions) AS add_rate,
sum(view_cart_checkout_sessions) AS checkouts,
sum(view_cart_checkout_sessions) / sum(view_sessions) AS checkout_rate,
sum(view_cart_checkout_purchase_sessions) AS purchases,
sum(view_cart_checkout_purchase_sessions) / sum(view_sessions) AS conversion_rate,
sum(net_sales) AS net_sales,
sum(quantity_purchased) / sum(purchase_sessions) AS avg_qty
GROUP BY product_type
SINCE last_month UNTIL yesterday
ORDER BY net_sales DESC
LIMIT 20

Conclusion

ShopifyQL Notebooks is a developing product with a lot of potential. I hope Shopify continues to build this tool out. While it's very limited and only has one simple graph visualization option, it's free, quick, and easy to use. It has the potential to be so much more, but it's not currently Enterprise-grade and will likely never be. I still use Snowflake for most deep-dive analysis. However, if you have access, it's definitely worth a try and can be a great solution for providing reports that internal teams may benefit from without the need of an additional service license to a solution such as Snowflake.

# commerce 14 # seo 5 # tools 6 # amazon 1 # sql 4 # shopify 9 # javascript 13 # projects 4 # css 2 # git 2 # php 3 # analytics 4 # monitoring 2 # api 6 # python 2 # aws 2