Invoice Reporting for Accounts

How to generate and use invoice reports for accounts

Last updated 4 months ago

Upmind is built for accounting compliance and supports businesses with $100M+ turnovers requiring external audits.

Reports are accessible through the Upmind app, and advanced users can export data to their own data warehouse. This guide explains the reporting process and invoice data structure.

Accessing reports

Reports can be downloaded from Insights and Reports > Reports. For instance, the Invoice Items Export Only report covers most revenue and billing data.

Automated report delivery

You can automate daily report delivery to Amazon S3 or similar in Settings > Accounting and Reports. This will send updated database rows for integration with data lake tools like Snowflake.

You can follow this guide to set up export to Amazon S3 in Upmind.

Real-time data access for enterprise users

Enterprise customers have full API access and can also export raw data into S3 buckets for their own analysis.

If they need database-level access or encounter issues, they can contact support for assistance.

Key data concepts

Relations: Upmind stores IDs as UUID strings. Relations in tables (for example, company id in Invoice Items Export) link to related records in other exports, such as companies.
Currencies: Each brand has a fixed base currency in which all amounts are stored. Invoices or payments in other currencies include the exchange rate to the base currency. Multiple brands can have different base currencies.
Invoices and Credits: Cancelled invoices always generate credit notes, ensuring no leakage. The sum of all invoice totals equals payments received + accounts receivable + wallet balance.
Service Periods: Contract products always have consistent service date periods. Changing due dates or adding contract products with past or future start dates may include (0) zero-value items.
Tax: Upmind’s tax rules are flexible, summing tax per invoice item, with tax reports provided separately.
Product Codes: Product codes (product code 1 and 2) are defined on individual projects per product for reporting and mapping purposes. (See How to Create Products).
Billings vs. Revenue: Billings track invoice creation dates, while revenue is apportioned over the service lifetime. Typically, the invoice creation date is used as the billing date.

Constructing revenue data

Upmind invoices are divided into Invoice Items, with each item representing a row on the invoice containing detailed information.

Field Name	Description	Can this change	Type
client id	The unique client ID	No	String
gateway id	The payment gateway ID	Yes	String
payment details id	The payment details associated	Yes	String
company id	The client company invoiced	No	String
import id	ID from the external system if the invoice is imported	No	String
id number	Unique Invoice Item ID	No	String
create datetime	Timestamp of invoice creation	No	Date
cancellation datetime	Date of invoice cancellation	Yes	Date
paid datetime	Date when the invoice was paid	Yes	Date
due date	Invoice due date	No	Date
next charge date	Next automated charge attempt	Yes	Date
payment failed attempts	Number of failed payment attempts	Yes	Integer
payment currency code	Currency used if the invoice is paid	Yes	String
payment currency exchange rate	The exchange rate is paid in a different currency	Yes	Float
status	Current invoice status	Yes	String
invoice type	Whether the invoice is New Contract Renewal Migration (Upgrade or Downgrade) Credit	No	String
invoice item description	Description of invoice line	No	String
product	Product from the catalog for the invoice	No	String
product category	Category of the product	No	String
document currency code	Currency of the invoice	No	String
currency exchange rate	Exchange rate from base currency to document currency	No	Float
invoice product id	Invoice product identifier		String
from date	Service period start date	Possibly - see note below	Date
to date	Service period end date	Possibly - see note below	Date
net selling price	Net selling price		Float
quantity	Quantity		Integer
billing cycle months	Length of service in months		Integer
net amount	Net amount		Float
tax amount	Tax amount		Float
total amount	Total amount		Float
net global discount amount	Net global discount		Float
net product discount amount	Net discount on product		Float
invoice net amount	Invoice net amount		Float
invoice tax amount	Invoice tax amount		Float
invoice total amount	Invoice total amount		Float
invoice paid amount	Amount paid on the invoice		Float
credited invoice	Linked the credited invoice		String
credit note invoice	Linked credit note invoice		String
contract id	Contract identifier		String
contract product id	Contract product identifier		String
created at	Record creation date		Date
updated at	Last update date		Date
deleted at	Deletion date (if applicable)		Date
invoice partial credited amount	Partially credited amount		Float
product code 1	Freeform product code 1 for reporting		String
product code 2	Freeform product code 2 for reporting		String
main invoice product id	Main product ID on invoice		String

Service start and end dates

📘 Service start and end dates may be reversed in credit notes where due dates roll back.

If service dates are missing when they are not created:

When start_date and end_date are null and billing cycle months = 0, replace with the invoice creation date.
When start_date and end_date are null and billing cycle months > 0:
- In the billings model, use the invoice create date as both start and end dates.
- In the revenue model, keep dates null as the product is unprovisioned.

Status inclusion

Invoice statuses typically include:

Paid
Unpaid
Allocated (credited against a paid invoice)

Currency conversions

Exchange Rate Name	Description
Document currency code	The currency shown on the invoice is visible to the customer.
Payment currency code	Used to determine how to calculate the customer’s payment amount in local currency.
Brand currency	The currency for which the document currency exchange rate equals 1 is used to convert from local to brand currency.
Currency exchange rate (invoice items)	The rate in the payments export is used to convert from the brand currency to the document currency.
Currency exchange rate (payments)	The rate in the payments export shows the local currency of the transaction.

You can report in Upmind’s base currency or convert from brand to document currency, then apply your own spot rate (example, from openexchangerates.com) to convert to your reporting currency.

Examples of currency conversions

Calculating Billings:

Convert the native net or total amount from brand currency back to the document currency using the Currency Exchange Rate.
Convert to the reporting currency at either a spot or constant exchange rate.

Calculating Payments (example in USD):

If invoice_paid_amount = 0, Payment Amount is zero.
If Document Currency Code equals Payment Currency Code, Reported Payment Amount equals the USD (Total/Net) Amount.

If Document Currency Code does not equal Payment Currency Code:

If Payment Currency Exchange Rate = 1:
- Use invoice_paid_amount
- Payment Currency Code to convert to USD using your own exchange rates.
If Payment Currency Exchange Rate ≠ 1 (and not null):
- Convert the total amount from brand to document currency using the Currency Exchange Rate.
- Multiply this by the Payment Currency Exchange Rate to calculate the local amount paid.
- Convert this amount to USD using your own exchange rate.
If Payment Currency Exchange Rate is null:
- USD Payment Amount equals USD (Total) Amount.

Building revenue

We distinguish two revenue types:

Commercial revenue: Provides accurate periodic representation and may adjust past periods if a service is credited or unpaid.
Accounting revenue: Does not backdate revenue.

Revenue can be calculated by aggregating invoice line data as follows.

Calculation	Definition
Service period	The number of days between the invoice start and end dates; if equal, the service period is set to 1 day.
Day value	The (net/total) amount from the billings table divided by the service period days, representing a daily rate.
Cumulative days	Total days elapsed in the service period by the end of each month.

Examples of building revenue

Commercial Revenue

Calculate commercial revenue by multiplying the days in the revenue period by the day value.

Days in Month is the number of days within the month falling on or after the service start date and up to the earlier of the service end date or the month-end.
Multiply Days in Month by Day Value for each month in the service period.
Repeat for net and total amounts and for spot and constant exchange rate conversions.

Commercial Deferred Revenue

Calculate total earned revenue since the beginning of the service period as the product of cumulative days and Day Value.
Subtract this from the total invoice line value to find the remaining liability yet to be delivered.

Accounting Revenue

Accounting revenue does not backdate, allocating no revenue before the invoice create date. Calculate Day Value for commercial revenue.

To calculate the days multiplied by the Day Value for monthly Accounting Revenue, for each month (date_period):

If earlier than the invoice date month, accounting revenue is zero.
If equal to the invoice date month, multiply cumulative days by Day Value.
If after the invoice date month (within service period), multiply Days in Month by Day Value.

Accounting Deferred Revenue

For months on or after the invoice date, calculate earned revenue since the beginning of the service period as cumulative days times Day Value.
Subtract this value from the invoice line total to find the remaining deferred revenue.
For months before the invoice date, deferred revenue is zero.
Repeat for net, total, spot, and constant conversions.

Payments

The Payments table aggregates daily data from the Payments export, with rows representing each month a customer has a new transaction type, product, and date combination.

For KPIs, convert the Payment Amount using the Currency Exchange Rate from the Payments Export to get the local transaction amount. Then, multiply this local amount by the local exchange rate associated with the Currency Code to convert it to the reporting currency:

For spot rates, use the transaction date from the table.
For constant rates, use today’s date.

Tracking customer status

Customer status can be tracked using the invoice items table based on active invoice presence and timing.

Customer Status	Logic
Pending warm	No active invoices and within 30 days of the customer start date.
Pending cold	No active invoices and more than 30 days since the start date.
New	Within 60 days of the first active invoice.
Established	An active invoice exists after the initial 60-day period.
Inactive	No active invoices, but had active invoices previously.
Closed	Customer delete date is on or after the latest invoice end date, status lasts 12 months.

Accounting in transition

When importing to Upmind, only invoices generated within Upmind are reported, so historic invoices can be imported as legacy data but serve only as customer metadata.

For accounting, it's essential to clear any unpaid historical invoices from old systems. Upmind allows mapping of contract products and clients to external services using both an import ID and an external ID for reporting integration.