Xero
Monthly Invoice Summary Report
This template generates a monthly invoice summary report from Xero, organizing the data by year and month. It details various invoice metrics such as the total number and amount of invoices, total tax, total amount due and paid, amount credited, and the number of unique contacts involved.
1select
2 extract(
3 year
4 from
5 date
6 ) as year
7 , extract(
8 month
9 from
10 date
11 ) as month
12 , count(invoice_id) as total_invoices
13 , sum(sub_total) as total_sub_amount
14 , sum(total_tax) as total_tax_amount
15 , sum(total) as total_invoice_amount
16 , sum(amount_due) as total_amount_due
17 , sum(amount_paid) as total_amount_paid
18 , sum(amount_credited) as total_amount_credited
19 , count(distinct contact_contact_id) as number_of_unique_contacts
20from
21 {{raw.xero.invoice}}
22group by
23 1
24 , 2
25order by
26 year desc
27 , month desc;Example of output from model:
+------+-------+---------------+----------------+----------------+--------------------+----------------+---------------+--------------------+------------------------+
| year | month | total_invoices | total_sub_amount | total_tax_amount | total_invoice_amount | total_amount_due | total_amount_paid | total_amount_credited | number_of_unique_contacts |
+------+-------+---------------+----------------+----------------+--------------------+----------------+---------------+--------------------+------------------------+
| 2023 | 8 | 100 | 50000 | 7500 | 57500 | 10000 | 47500 | 5000 | 50 |
| 2023 | 7 | 90 | 45000 | 6750 | 51750 | 9000 | 42750 | 4500 | 45 |
| 2023 | 6 | 80 | 40000 | 6000 | 46000 | 8000 | 38000 | 4000 | 40 |
+------+-------+---------------+----------------+----------------+--------------------+----------------+---------------+--------------------+------------------------+Utilizing Xero invoice data, this SQL template aggregates key invoice metrics on a monthly basis, aiding in the detailed and comprehensive reporting of financial information. It showcases a summary of invoicing activities per month including the total and distinct count of invoices, accumulation of sub-total, tax, and total amounts, along with tracking the amount due, paid, and credited. Moreover, it gives insight into the client reach by reporting the number of unique contacts involved each month. By leveraging SQL’s aggregation and date extraction functions, it presents a structured report facilitating better financial monitoring and decision-making.
