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.
- Grain
- year x month x currency x item
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from Xero
- invoiceinvoice_line_item
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.xero.monthly_sales_report
2-- Monthly Sales Report. This template creates a monthly sales report by analyzing
3-- invoice data. It breaks down sales numbers and revenue per product/service and
4-- item code on a monthly basis, offering a clear view of how different offerings
5-- perform over time.
6--
7-- Grain: year x month x currency x item. Only sales invoices (type ACCREC)
8-- that are approved or paid count; drafts, voided and deleted invoices and
9-- supplier bills are left out. Revenue is the line amount in the invoice
10-- currency, so each currency gets its own rows. Line amounts are net or gross
11-- of tax depending on how each invoice was entered (line_amount_types).
12--
13-- From the weld.app template library, corrected. Not reconciled against Xero;
14-- check the output against the source before relying on it.
15
16SELECT
17EXTRACT(YEAR FROM i.date) AS year,
18EXTRACT(MONTH FROM i.date) AS month,
19i.currency_code,
20ili.item_name AS product_service,
21ili.item_code,
22COUNT(ili.line_item_id) AS number_of_sales,
23SUM(ili.line_amount) AS monthly_revenue
24FROM
25{{raw.xero.invoice_line_item}} ili
26-- Join the invoice table to get the date of each invoice
27JOIN
28{{raw.xero.invoice}} i ON ili.invoice_id = i.invoice_id
29WHERE
30i.type = 'ACCREC'
31AND i.status IN ('AUTHORISED', 'PAID')
32GROUP BY
33year,
34month,
35i.currency_code,
36ili.item_name,
37ili.item_code
38ORDER BY
39year DESC,
40month DESC,This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.xero.invoice
- raw.xero.invoice_line_item
Put Xero in front
of your agents.
Connect Xero, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.