Weld logo
shopify logo
Shopify

Yealy 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_example.order_line}} order_lines
6    inner join {{raw.shopify_example.order}} orders on order_lines.order_id = orders.id
7group by
8    year
9order by
10    year desc
Example 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 Yealy revenue SQL template is an essential tool for any Shopify user.

Ready to start modeling your own shopify data?

Get started building your data warehouse with shopify and connect all your apps and databases.

shopify logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.