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.
-
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. |
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. 
