Invoice Reporting for Accounts

How to generate and use invoice reports for accounts

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.

Insights and reports > Reports

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.

Settings > Miscellaneous > Accounting

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 external system if invoice 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 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 invoice is paid

Yes

String

payment currency exchange rate

Exchange rate if paid in 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 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 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 it is not created:

  1. When start_date and end_date are null and billing cycle months = 0, replace with the invoice create date.
  2. 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 NameDescription
Document currency codeThe currency shown on the invoice, visible to the customer.
Payment currency codeUsed to determine how to calculate the customer’s payment amount in local currency.
Brand currencyThe currency for which the document currency exchange rate equals 1, used to convert from local to brand currency.
Currency exchange rate (invoice items)The rate in the payments export used to convert from brand currency to the document currency.
Currency exchange rate (payments)The rate in the payments export showing 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.

CalculationDefinition
Service periodThe number of days between the invoice start and end dates; if equal, service period is set to 1 day.
Day valueThe (net/total) amount from the billings table divided by the service period days, representing a daily rate.
Cumulative daysTotal 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 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 as 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 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 StatusLogic
Pending warmNo active invoices and within 30 days of customer start date.
Pending coldNo active invoices and more than 30 days since start date.
NewWithin 60 days of first active invoice.
EstablishedActive invoice exists after the initial 60-day period.
InactiveNo active invoices but had active invoices previously.
ClosedCustomer 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 historic 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.