)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpot can’t natively calculate daily revenue rates from monthly flight rates because it lacks the sophisticated date functions needed to handle varying month lengths and dynamic calculations.
Here’s how to solve this limitation and get precise daily rates that automatically adjust for 28-31 day months.
Convert monthly rates to daily rates using Coefficient
Coefficient connects your HubSpot line item data to spreadsheets where you can use advanced formulas that HubSpot simply can’t handle. This gives you the mathematical power to calculate accurate daily rates based on actual days in each month.
How to make it work
Step 1. Import your HubSpot flight data.
Use Coefficient to pull line items with flight start dates, end dates, and monthly revenue amounts from your HubSpot deals. Set up automatic daily refreshes so your calculations stay current with any changes in HubSpot.
Step 2. Create the daily rate formula.
In your spreadsheet, use this formula: =Monthly_Revenue/DAY(EOMONTH(Flight_Start_Date,0)). This calculates daily rates based on the actual number of days in each specific month, not a generic 30-day assumption.
Step 3. Handle multi-month flights.
For campaigns spanning multiple months, create separate calculations for each month using DATEDIF and EOMONTH functions. This ensures you’re accounting for different month lengths throughout the flight duration.
Step 4. Set up automated updates.
Configure Coefficient to refresh this data daily. Your daily rate calculations will automatically stay current with any HubSpot changes, and the rates will adjust as flights progress through months with different day counts.
Get accurate daily revenue tracking
This approach gives you precise daily revenue rates that HubSpot’s standard calculated properties simply can’t deliver. Start building your daily rate calculations today.
