Combined Revenue Report - from Shopify AND Hubspot Payments

[winsorcm]

I am attempting to create what I think is a basic report but struggling to build it. I would love somw pointers on how to do create the report:

BACKGROUND:

• I recieve orders from my Shopify store (connected to Hubspot)
• I also recieve payments from invoices and payment links sent via Hubspot.

GOAL

• Create a total revenue report that shows both the Shopify orders and Hubspot payments together in the same place.
• Organize by:
  • tripple line graph; one for shopify, one for hubspot payments, one for total of the two OR bar graph that show both in the same bar
  • y-axis = revenue (dollars)
  • x-axis = time (weeks)

Any pointers - especially the proper object vocab for payments/order/etc  - would be VERY helpful!

Thank you in advance!!

Curtis

[danmoyle · Accepted solution]

Hey there @winsorcm. @kennedyp has a great clarification question on how the data is coming into HubSpot. 

I'll make a couple of assumptions in the meantime, and maybe this will help: 

In order to create the combined revenue report that includes both Shopify orders and HubSpot payments, I'd try this:

First, ensure you're using the Deals object in HubSpot to track both Shopify orders and HubSpot payments. Shopify orders are automatically synced as deals in HubSpot when the integration is set up correctly, so that should help.

For HubSpot payments, ensure they are also being recorded as deals in your HubSpot CRM. You may need to set up a workflow to create deals automatically when payments are received through HubSpot.

Then, create custom properties for your deals to differentiate between Shopify orders and HubSpot payments. Here's what I'd probably try:

• "Revenue Source" (Dropdown): Shopify / HubSpot
• "Order/Payment Date" (Date): To track when the transaction occurred

Next, use HubSpot reporting to build a custom report with these settings and fiters:

• Report type: Time series
• Data set: Deals
• Y-axis: Sum of Amount (deal value)
• X-axis: Week (based on Order/Payment Date)
• Breakdown: Revenue Source

Now, to create the triple line graph, add three data series to your report:

• Filtered for Revenue Source = Shopify
• Filtered for Revenue Source = HubSpot
• Total (no filter)

This approach uses the Deals object as the common denominator for both revenue sources, allowing you to combine and compare them efectively. And of course it relies on having your Shopify integration set up and syncing order data correctly to HubSpot, and all HubSpot payments being recorded as deals with the correct properties for accurate reporting. 

Hope that helps! 

[winsorcm]

Hey Everyone - There was a very easy way to do this. I made a small msitake at the very beginnning. 

In brief when starting a custom report -

• 1. Start with Contacts as the primary data source.
• 2. Then add Payments and Orders as secondaries.
• Then add amount paid for each on the yaxis with a line graph. 

Once built, by hovering, you can see the totals of each line and the sum total. See with sample data below. Hope this helps the next person. Thanks, CW

[danmoyle]

That's great news @winsorcm! Thanks for sharing the update. 

[winsorcm]

Thank you @danmoyle and @kennedyp. 

Some additional context:

1. I am able to easily to generate SEPERATE reports for each of these - one for shopify orders, one for hubspot invoices/payments. When i make the same selections in a combined report, unable to get the data for both to appear- only one at a time. I have included screenshots of the selection I have made when building the report.
2. When i look at the "data joining" tab when setting up with report, it does seem to be ecluding my invocies.
3. @danmoyle my invoices are NOT setup as deals currently..I don't think they are at least...hmm..maybe thats the issue! 

Thanks again for reviewing!

CW

[danmoyle · Accepted solution]

Hey there @winsorcm. @kennedyp has a great clarification question on how the data is coming into HubSpot. 

I'll make a couple of assumptions in the meantime, and maybe this will help: 

In order to create the combined revenue report that includes both Shopify orders and HubSpot payments, I'd try this:

First, ensure you're using the Deals object in HubSpot to track both Shopify orders and HubSpot payments. Shopify orders are automatically synced as deals in HubSpot when the integration is set up correctly, so that should help.

For HubSpot payments, ensure they are also being recorded as deals in your HubSpot CRM. You may need to set up a workflow to create deals automatically when payments are received through HubSpot.

Then, create custom properties for your deals to differentiate between Shopify orders and HubSpot payments. Here's what I'd probably try:

• "Revenue Source" (Dropdown): Shopify / HubSpot
• "Order/Payment Date" (Date): To track when the transaction occurred

Next, use HubSpot reporting to build a custom report with these settings and fiters:

• Report type: Time series
• Data set: Deals
• Y-axis: Sum of Amount (deal value)
• X-axis: Week (based on Order/Payment Date)
• Breakdown: Revenue Source

Now, to create the triple line graph, add three data series to your report:

• Filtered for Revenue Source = Shopify
• Filtered for Revenue Source = HubSpot
• Total (no filter)

This approach uses the Deals object as the common denominator for both revenue sources, allowing you to combine and compare them efectively. And of course it relies on having your Shopify integration set up and syncing order data correctly to HubSpot, and all HubSpot payments being recorded as deals with the correct properties for accurate reporting. 

Hope that helps! 

[winsorcm]

Hey @danmoyle - I spoke too soon. I realized that though i could sort by date and it would appear to be working, I was sorting EITHER by payment date OR order date. So looks like I need tor evisit your solution with custom properties. (I was trying to avoid anyother custom property initially. wish there was a buil-in report like "Total revenue paid from all sources". I'll elt you know if your solution worked for me! Thx again.

[winsorcm · Accepted solution]

@danmoyle - Officially closing the loop on this. Your solution worked great. Restating your steps a bit in categories:

STEP 1 - Create Properties

1. Created Revenue Source dropdown property.

2. Created Revenue of Payment Date/Order Date.

STEP 2 - Create Pipeline for capturing paid orders and paid invocies

1. Create pipeline called "revenue Tracking pipeline."

STEP 3 - Automation Workflow 

1. Create an Orders based workflow that  is triggered when an order is paid > then creates a deal that assigns shopify from revenue source property and assigns the date of the order > and then sends it to the closed stage of the Revenue TRacking pipeline

2. Create an invoice-based workflow that  is triggered when an invoice is paid > then creates a deal that assigns invoice from revenue sorce property and assigns the date of the order > and then sends it to the closed stage of the Revenue Tracking pipeline

Step 4 - Create Reports

1. In report, start with the primary "Deals" based report, filtering by "revenue tracking" report.

2. then build a revenue tracker with stacked bars etc.

NOTE

I've made sure to leave the "Revenue Tracker" Pipeline out of other reports to avoid duplicate informaiton since I'm only using it as an agregator of sales.

Ok thank you again!

CW

[kennedyp]

Hi @winsorcm! Welcome to the Community-- happy to have you here 😊

Do your Shopify orders come in as a particular object in HubSpot? I foresee there may be a challenge reporting on so many values at once. Ultimately this may need to be split into multiple reports on the same dashboard. Let's invite some other community members with better reporting experience! 

Hey @Shadab_Khan, @danmoyle, @bendonahower any insight for @winsorcm to build out a similar report or dashboard to see all payments and shopify orders in one place?