See attached report.
I found a pre-built campaign report that shows the cost per contact from PPC Camaigns. I am wondering if I could make a report so I could see a trend over time and be able to know if the cost is trending up or down over time and see how we are pacing.
I can’t figure out how to do this or if it is possible. Any ideas?
Matthew
Hey @Stacks42
Campaign reporting in HubSpot is missing a “spend report date,” which makes it hard to track spend over time.
Right now, all spend items- whether manually entered or synced from ad tools- get rolled up into CAMPAIGN/hs_spend_items_sum_amount. That means you can only see the total spend, not how it trends over time.
Ironically, “spend report date” does exist as a metric in the “Ad performance metrics” data source, but that source can’t be joined with anything else in HubSpot.
Workarounds:
- If you want to build a timeline-based “Cost per Lead” report, you’ll need to join Ad performance metrics (for spend report date) with Contacts (for contact create date). You can do this in Ops Hub Pro by creating a dataset; maybe using a CSV with campaign names and matching it to contact original source data (CSV is needed as a data join since you can’t directly join ad performance source)
- Alternatively, use an API data pull tool like Superjoin or Coefficient to bring in both spend data from your ad manager and contact data from HubSpot. Use contact original traffic source as your join key.
Both options take some setup, but method 2 is much cleaner and scalable.
Let me know if this was helpful.
Thanks