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

Ready to start modeling your own xero data?

Get started building your data warehouse with xero and 100+ more apps and databases available.

xero
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.