HubSpot–Ramp Integration Case Study: Cost to Serve on the Account for an Agency

Summary

Weyland Creative runs paid media and production for 90 retained clients, at an average retainer of $14,800 a month. Ramp held card spend, bill payments and vendor records. HubSpot held the client relationship. Revenue was reported per client and cost per department, and the two had never been joined.

The engagement attributed pass-through and direct vendor spend to the client account it was incurred for, and surfaced gross margin as a property on the company record. Retainers running below target margin were identified in a median 9 days rather than at quarter close, unattributed spend fell from 31 percent to 6 percent of direct cost, and three loss-making retainers were repriced within the first quarter.

Background: Revenue by Client, Cost by Department

The agency knew what each client paid. It knew what each department spent. It did not know what any individual client cost, because spend was categorised by the team that incurred it rather than by the account it served.

For a business whose direct costs are largely freelancers, stock licensing and media buys, this is not a rounding problem. Direct cost ran at roughly 46 percent of revenue, and none of it was attributable to a client without someone reconstructing it from receipts.

Haug, Zachariassen, and van Liempd (2011) separate the operational cost of working around defective data from the cost of decisions taken on it, and note that neither is ordinarily booked against data. Weyland was paying both: a quarterly reconstruction exercise consuming finance time, and pricing decisions taken on a margin figure that was an average across a portfolio containing both its best and its worst accounts.

The Audit

Three weeks against Ramp exports, the HubSpot portal and four quarters of management accounts.

Attribution coverage. 31 percent of direct cost carried no client attribution of any kind. A further 22 percent carried a free-text memo that named a client inconsistently.

Margin dispersion. Reconstructing twelve accounts by hand showed gross margin ranging from 71 percent to negative 4 percent against a portfolio average of 54 percent. The average had been the number used for pricing.

Detection. No loss-making retainer had been identified before a quarter close in the preceding two years.

Vendor overlap. 40 vendors were used across more than one client, so vendor-level reporting could not substitute for client-level attribution.

Attribution at the Point of Spend

The decision that made this work was to capture attribution when the money is committed rather than reconstruct it afterwards. Pipino, Lee, and Wang (2002) set out how data quality can be assessed in practice, pairing metrics computed from the data with assessment from the people who use it, and completeness is the metric that matters here: an attribution rate is computable at the point of capture and merely estimable at the point of reconstruction.

Ramp supports required custom fields on transactions. A client field was made mandatory on any card transaction or bill above a threshold, populated from a list synced from HubSpot companies, so the value is an identifier rather than a typed name.

Wand and Wang (1996) treat an information system as a representation of the real world and define a data quality problem as a breakdown in the mapping between the two. A memo reading "Northbank shoot" is not an attribution; it is a description that a person can resolve and a report cannot. Replacing it with an identifier is what turns the field into data.

The Build

Six weeks, four phases, using the Ramp API, a scheduled job in the client's tenancy, and a private HubSpot app.

Phase one, client list sync. Active HubSpot companies sync nightly into Ramp as the permitted values of the client field. A cardholder chooses from a list rather than typing, which is what removes the eleven-spellings problem before it starts.

Phase two, policy. The field was made required above a threshold, with spend below it pooled to an overhead bucket deliberately rather than by omission. Requiring it on every coffee would have produced compliance theatre and worse data.

Phase three, aggregation. A nightly job aggregates attributed spend by client and writes cost to date and gross margin to the HubSpot company. Both are read-only and carry the run timestamp.

Phase four, alerting. A retainer whose rolling three-month margin falls below its target raises a task to the account lead, not a line in a report.

Outcomes

Attribution coverage. Unattributed direct cost fell from 31 percent to 6 percent, the residual being genuine overhead now pooled by policy rather than by accident.

Detection interval. Median 9 days from a retainer crossing its margin threshold to a task existing, against no pre-close detection at all previously.

Repricing. Three loss-making retainers were repriced in the first quarter, and one was declined at renewal.

Portfolio margin. Gross margin across the retained book rose from 54 percent to 58 percent over two quarters.

Finance time. The quarterly reconstruction exercise, previously an estimated 34 hours, no longer runs.

Pricing basis. New retainers are now priced against the margin of comparable accounts rather than the portfolio average, which the firm reports as the most consequential change and which these figures do not directly measure.

What Resisted

Shared vendors resisted. A single freelance invoice frequently covers work for three clients, and Ramp attributes a bill to one field value.

The mechanism was to split at the bill rather than the vendor: bills covering multiple clients are entered as separate line items with their own attribution, which the finance team resisted until the alternative was stated plainly, which was continuing to reconstruct it quarterly by hand. The split adds an estimated four minutes per multi-client invoice and there are roughly 40 a month.

The second difficulty was that attribution is a behaviour, and behaviours decay. Compliance ran at 94 percent in month one and 78 percent by month three. What restored it was a weekly list of unattributed transactions sent to the cardholder rather than to finance. Karimi, Somers, and Bhattacherjee (2007) found that a deployment becomes a capability only where its outputs enter the working routine of the people the process runs through, and the routine that needed changing belonged to the person holding the card.

Limits

This does not prove attribution improves margin. Weyland repriced three accounts during the period, and that action rather than the visibility produced most of the portfolio movement. What the integration demonstrably changed is the interval before a problem is visible, and the pricing basis for new work.

The approach also depends on a spend platform that supports required custom fields with controlled values. A business on cards without that capability would likely be reconstructing attribution after the fact, which is the condition this project existed to leave.

Figures come from the client's own management accounts. The two-quarter comparison spans a period in which the client mix changed, and no adjustment has been made for it.

Conclusion

The integration attributes a cost to an account at the moment the cost is committed. Everything else appears to follow from that, and nothing else in the design is novel.

The general form is that a number reconstructed quarterly is a number nobody acts on, because by the time it exists the decision it informs may already have been taken several times.

References

Haug, Anders, Frederik Zachariassen, and Dennis van Liempd. 2011. "The Costs of Poor Data Quality." Journal of Industrial Engineering and Management 4 (2): 168–193. https://doi.org/10.3926/jiem.2011.v4n2.p168-193

Karimi, Jahangir, Toni M. Somers, and Anol Bhattacherjee. 2007. "The Role of Information Systems Resources in ERP Capability Building and Business Process Outcomes." Journal of Management Information Systems 24 (2): 221–260. https://doi.org/10.2753/MIS0742-1222240209

Pipino, Leo L., Yang W. Lee, and Richard Y. Wang. 2002. "Data Quality Assessment." Communications of the ACM 45 (4): 211–218. https://doi.org/10.1145/505248.506010

Umble, Elisabeth J., Ronald R. Haft, and M. Michael Umble. 2003. "Enterprise Resource Planning: Implementation Procedures and Critical Success Factors." European Journal of Operational Research 146 (2): 241–257. https://doi.org/10.1016/S0377-2217(02)00547-7

Wand, Yair, and Richard Y. Wang. 1996. "Anchoring Data Quality Dimensions in Ontological Foundations." Communications of the ACM 39 (11): 86–95. https://doi.org/10.1145/240455.240479

Conflict of Interest

RevOps HQ is a HubSpot Solutions Partner and was engaged and paid by the client described.

Acknowledgments

Prepared by RevOps HQ.