How to Set Up HubSpot Line Item Reporting in Dear Lucy

If you're using HubSpot CRM, Dear Lucy recommends using HubSpot Line Items for the most accurate representation of recurring revenue reporting.

A line item is a product or service attached to a deal. This allows you to correctly allocate, for example, a one-time setup project and a recurring subscription within the same deal — and accurately show the time points for when payments are made.

Looking for a broader guide on how CROs and RevOps teams use this in practice? See our HubSpot MRR reporting blog.

Key advantages of Dear Lucy's Line Item Reports for HubSpot:

1. One-Time vs Recurring Revenue: Immediate split between One-Time and Recurring payments by month and quarter.
2. Currency Conversion: Dear Lucy handles currency conversions of line items in different currencies, correctly converting them to your main HubSpot reporting currency. This is something that is not possible in HubSpot directly.
3. Filter & Drill-down: With Dear Lucy's line item reports you can filter and drill-down into your line item data on any sales dimension available in your data. E.g. Sales person, product, pipeline, customer etc.

Required Fields for Line Item Reporting

To generate a basic line item report, Dear Lucy needs just five fields in HubSpot:

1. Unit Price: All products / services must have a unit price in order for us to correctly display the Net Price, which is the value shown on the report.
2. Billing Start Date / Close Date: It's important that there is a start date for all line items. If you have not added the billing start date for your line items, but instead have chosen the default option "At Payment", then Dear Lucy will automatically use the Close Date for the deal.
3. Term: The term is important to fill in for all line items that have a fixed contract end. This enables Dear Lucy to calculate the Billing End Date as well.
4. Billing Frequency: This allows you to separate between e.g. monthly, quarterly and annual payments. But it also allows you to add and segment out one-off projects.
5. Quantity: If quantity is 0, the line item will not show any value.

If you have populated these fields in your HubSpot, the basic line item report in Dear Lucy requires no customisations from Dear Lucy, please just contact us and we will add it to your environment.

Report specs:

Periodic Segmentation: A line item with a "Monthly" Billing Frequency, will show up on a monthly basis in the report, "Quarterly" once per quarter and an "Annually" once per year. 

However, the deepest level of payment allocation in Dear Lucy is monthly. Meaning that it does not go into the specific week, or day of the month that the billing occurs. It will just allocate the amount to the correct month, quarter and/or year of the payment. In other words, the level of detail of the report does not go beyond a monthly level in terms of level of depth and weekly and bi-weekly billing frequencies are summed up to a monthly level.

Billing Period: Dear Lucy will sum values from the first billing month until the second to last billing month. For example, if you have a line item starting 15th October 2026 with a 12-month term, the start date will be 2026-10-15 and the end date will be 2027-10-15. Dear Lucy's line item reports will then add the monthly value of the line item starting in October 2026 up until (and including) September 2027. No value for the line item will be included in the last month of the term, as that would sum up to 13 months, not 12 months.

Weekly & Bi-Weekly Payment terms: Dear Lucy will create and spread the monthly value of the weekly or bi-weekly billing amount, over all the months that the weekly line item touches.

One-Time vs Recurring Revenue: Any line item tagged "One-time" (or without any payment frequency) will be counted as a One-time line item and all of its value will be attributed to the first month of the period. All other ("Weekly" through to "Every four years") are considered recurring revenue line items.

Currency conversions: Dear Lucy is able to convert all line items (regardless of the currency of the line items) into the equivalent correct value of your main HubSpot reporting currency. However, it is not possible to convert the full line item reports into separate currencies. Please note, a unit price for each currency option of the product is needed in your product library for this to work properly.

Additional recommendations:

In order to better account for Renewals, Downgrades and Churn, we recommend that you add custom fields to your line items in HubSpot, which will allow for Dear Lucy to further customise your line item reporting. As shown in the screenshot above, we have added fields for Deal Type (line item type) and Line Item Status:

Renewals, Upgrades & Downgrades:

(Line Item) Deal Type: This field has the options of New Sales, Renewal, Upgrade, Downgrade. If an initial line item's contract term runs out, a new line item with "Renewal" can be added to the deal. The same process applies to Upgrade and Downgrades, where new line items should be added to the same deal.

Line Item Status: This field has the option of Won, Lost, Open. Allowing you to also show a line item pipeline and also separate between upgrades and downgrades in your reporting.

Churn:

The default way to indicate Churn with line item reporting is simply to collect line items whose contracts end at a given point in time, and there is no renewal line item in the same deal. However, this might not always be accurate and/or might not be true, as one line item's contract might end, but not other line items under the same deal. 

We therefore recommend adding a custom Churn Date field, either on the Line Item level or on the Deal level (depending on what would be most accurate in your case). Dear Lucy will then be able to use that field to capture true churned line items, deals or customers, as opposed to line item contracts that were not renewed.