Xero
Monthly Sales Report
This template creates a monthly sales report by analyzing invoice data. It breaks down sales numbers and revenue per product/service and item code on a monthly basis, offering a clear view of how different offerings perform over time.
1select
2 extract(
3 year
4 from
5 i.date
6 ) as year
7 , extract(
8 month
9 from
10 i.date
11 ) as month
12 , ili.item_name as product_service
13 , ili.item_code
14 , count(ili.line_item_id) as number_of_sales
15 , sum(ili.line_amount) as monthly_revenue
16from
17 invoice_line_item ili
18 -- Join the invoice table to get the date of each invoice
19 join invoice i on ili.invoice_id = i.invoice_id
20group by
21 extract(
22 year
23 from
24 i.date
25 )
26 , extract(
27 month
28 from
29 i.date
30 )
31 , ili.item_name
32 , ili.item_code
33order by
34 year desc
35 , month desc
36 , monthly_revenue descExample of output from model:
+------+-------+-----------------+-----------+-----------------+-----------------+
| year | month | product_service | item_code | number_of_sales | monthly_revenue |
+------+-------+-----------------+-----------+-----------------+-----------------+
| 2023 | 9 | Product1 | Code1 | 150 | 30000 |
| 2023 | 8 | Product2 | Code2 | 140 | 28000 |
+------+-------+-----------------+-----------+-----------------+-----------------+The SQL template facilitates an in-depth exploration of monthly sales performance by disaggregating the data to the level of individual products/services and item codes. Leveraging SQL's EXTRACT function to derive the year and month from the invoice date, it methodically groups the data to present a detailed account of the number of sales and the monthly revenue for each product or service. By ordering the output by year, month, and monthly revenue in descending order, it ensures that users can quickly pinpoint the highest-grossing products or services in any given period, fostering data-driven decision-making in business strategies.
