Weld logo
xero logo
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 desc
Example 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.

Ready to start modeling your own xero data?

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

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