Top 20 Customers Based on Specific Pipeline

[winsorcm]

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:

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

Aggregating a Customers Total Revenue Report

1. 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
2. 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.
3. 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

1. 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!

[BBaber]

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)

[winsorcm]

@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!

CW

 

[BBaber · Accepted solution]

@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)

1. 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
2. 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
3. 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: 

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

Hopefully, this helps!

[winsorcm]

@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. 

So 

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.

[winsorcm]

@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!!