Shopify
Monthly sales
Retrieves monthly sales data from a Shopify integration by joining order and order line tables and grouping the results by month. The template uses the date_trunc function to extract the month from the created_at date field and calculates the total sales by multiplying the quantity and price fields.
1select
2 date_trunc(date(created_at), month) as month
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 month
9order by
10 monthExample of output from model:
+------------+-------------+
| month | total_sales |
+------------+-------------+
| 2019-01-01 | 1500.00 |
| 2019-02-01 | 2500.00 |
| 2019-03-01 | 1800.00 |
| 2019-04-01 | 3200.00 |
| 2019-05-01 | 2100.00 |
| 2019-06-01 | 2800.00 |
| 2019-07-01 | 1900.00 |
| 2019-08-01 | 3500.00 |
| 2019-09-01 | 2400.00 |
| 2019-10-01 | 2900.00 |
| 2019-11-01 | 1800.00 |
| 2019-12-01 | 4000.00 |
+------------+-------------+This SQL model works by selecting the date of each order and grouping them by month, allowing you to see how your sales have fluctuated over time. By integrating with Shopify, this SQL template pulls data from your order lines and orders, and calculates the total sales for each month. This information can be incredibly useful for identifying trends in your sales, such as which months are your busiest or slowest, and can help you make informed decisions about inventory management and marketing strategies. With the Monthly Sales SQL template, you can easily visualize your sales data and gain valuable insights into your business performance.
