topic Re: Top 20 Customers Based on Specific Pipeline in Dashboards & Reporting

Top 20 Customers Based on Specific Pipeline

winsorcm — Fri, 07 Mar 2025 18:17:05 GMT

Hi everyone,

Goal:

I'd like to creative an automatically updated table report that lists my top 20 customers.

Column A: Contact
Column B: Company
Column C: Total Revenue (based on CLOSED WON deals in a specific pipeline called 'Revenue tracker')

Important Info

Where the money comes from:

I have a Shopify Store. The sync to HubSpot generates 'ORDERS' automatically in hubspot.
I use the Hubspot Invoice tool. These are logged as 'PAYMENTS' in Hubspot Automatically

Aggregating a Customers Total Revenue Report

For whatever reason, you can't do a report allows you to automatically combine values from 'ORDERS' via Shopify and 'PAYMENTS' via hubspot Invoices...or im doing it wront
So I have a couple of automations running that takes order deals and invoices paid and creates closed deals in them in a seperate pipeline called 'Revenue tracker' with their respective values carried over.
Why? This way If I want complete picture of revenue I can break it down from that pipeline by source, month, count, total etc...

Questions

So all that said - what is the best way to create a report of top customers according to specific pipeline?

Thank you in advance!

Curtis W.

PS - Im also open to some thoer way of doing the total revenue reproting if you have experience there too!

Re: Top 20 Customers Based on Specific Pipeline

BBaber — Fri, 07 Mar 2025 19:36:20 GMT

Can you share a screenshot of what your report looks like so far? If you are using a pivot table and the orders and payments are associated to the deals in the revenue tracker pipeline, you should be able to have an aggregate report.

From there you would sort by the value you are considering for Top 20 and do "limit" (shown below)

Re: Top 20 Customers Based on Specific Pipeline

winsorcm — Fri, 07 Mar 2025 20:25:29 GMT

@BBaber - thanks for your input. Re-reading above i think i asked the quesiton wrong above and there must be a simpler way of acheiving what I'm after

Here's my edited question with an example:

Jeff is my customer.

He has placed 10 orders (shopify). Each worth $1,000 for a total of $10,000

He's also paid 5 invoices (hubspot). Each worth $1,000 for a total of $5,000

This means Jeff has paid - through Shopify and Hubspot - a total of $15,000 to me.

Question 1 - what is the best way to create a 'sum' of the orders and invoices?

Question 2 - what is the best way to create a report then ranks Jeff and other customers by that sum?

Including a drawing of updated desired report.

THank you!

Re: Top 20 Customers Based on Specific Pipeline

BBaber — Fri, 07 Mar 2025 21:19:14 GMT

@winsorcm
No worries!

Question 1 -
Option A:
If you can get this beta this is the easiest way to solve for calculating sum orders + sum invoices. (Go to https://app.hubspot.com/product-updates/{yourPortalId}/all?query=formula&update=13905273 )

Option B:
Create 3 properties on the contact or deal (whichever object you want to report to that the orders and invoices are attached to)

Total of All Shopify Orders
1. Field Type: Rollup
2. Rollup Type: Sum
3. Number Format: Currency
4. Associated record type: Order
5. Associated record property: the property you want to sum
Total of All HubSpot Invoices
1. Field Type: Rollup
2. Rollup Type: Sum
3. Number Format: Currency
4. Associated record type: Invoice
5. Associated record property: the property you want to sum
HubSpot + Shopify Total
1. Field Type: Calculation
2. Calculated Property Type: Custom equation
3. Output Type: Number
4. Number Format: Currency
5. Equation: Total of All Shopify Orders + Total of All Shopify Orders

Question 2:

Use a pivot table visualization
Click the pencil in the property in the row section
Sort
1. Option A: the custom formula field | Descending OR
2. Option B: HubSpot + Shopify Total | Descending
Limit: + Set Limit
1. Top - 20
2. By
  1. Option A: Custom Formula Field OR
  2. Option B: HubSpot + Shopify Total

Hopefully, this helps!

Re: Top 20 Customers Based on Specific Pipeline

winsorcm — Sun, 09 Mar 2025 21:08:19 GMT

@BBaber -

Option 1 - INTERESTING! I'm looking inot that next!

Option 2 - As it turns out, I stumbled into this by trial and error before seeing your reply. And it works great for totals. I had a little trouble getting it to sort customers by the date the revenu was processed however.

Option 3 - I had mentioned above that I have a specific pipeline for logging transaction by source (Shopify & HubSpot Invoices). Turns out a simple 1-dimension report did the trick:

- Single object report

- Deals based

- Filter by that Revenue Tracker pipeline and the 'closed' stage

- Add Contact Record ID

- Add Sum total of closed stage.

Works great for filtering by date..."last 30 days" and "year to date" etc

THanks so much for the reply...the beta is VERY interesting. Can think of MULTIPLE applications!!

Best,

Curtis W.

Re: Top 20 Customers Based on Specific Pipeline

winsorcm — Sun, 09 Mar 2025 21:11:09 GMT

@BBaber one mroe note - your option 2, step 3 works best when you add a check for existance of invoice or order. If none, set value to 0, that way the math adds up, otherwise the calculation ignores one of the rollups where there is no instance. Thanks again!!