Shopify
Yearly revenue
Retrieve the total sales revenue for each year by joining the order and order line tables and grouping the results by year. The template uses the date_trunc function to extract the year from the created_at column.
1select
2 date_trunc(date(created_at), year) as year
3 , sum(quantity * price) as total_sales
4from
5 {{raw.shopify.order_line}} order_lines
6 inner join {{raw.shopify.order}} orders on order_lines.order_id = orders.id
7group by
8 year
9order by
10 year descExample of output from model:
+------+-------------+
| year | total_sales |
+------+-------------+
| 2021 | 50,000 |
+------+-------------+
| 2020 | 45,000 |
+------+-------------+
| 2019 | 40,000 |
+------+-------------+By integrating with Shopify, this SQL model allows you to select the year and total sales revenue for each year, based on the data stored in your Shopify order lines and orders. The SQL code uses the date_trunc function to group sales data by year, and then calculates the total sales revenue by multiplying the quantity and price of each order line. The resulting output provides a clear picture of your annual sales performance, allowing you to identify trends and make informed decisions about your business strategy. Whether you're looking to track your growth over time, identify areas for improvement, or simply gain a better understanding of your sales data, the Yearly revenue SQL template is an essential tool for any Shopify user.
