Advanced eCommerce reporting

Gain deeper insights into your e-commerce customers by using HubSpot’s reporting and data tools. This article explains how to create and use datasets, custom properties, and modules to better understand customer behaviors, segment your audience, track purchase history, and improve your sales and marketing strategies.

In this article learn how to set up loyalty scoring, analyze purchase patterns, and monitor shipping processes with step-by-step instructions for configuring datasets and custom reports. With these tools, you can segment contacts, personalize marketing, and generate actionable reports.

Before you get started

Before setting up the use cases, create an ecommerce pipeline on the Deal object with the following stages:

• Checkout pending.
• Checkout abandoned.
• Checkout completed.
• Shipped.
• Cancelled.

Customer loyalty rating

Use datasets to calculate a customer loyalty score, and filter the loyalty score further to add customers to tiers (e.g., most loyal customers in tier 1). Use the scores and tiers to build reports and segments, to tailor your sales and marketing approach to your customers

You can set your own values for the loyalty score, but the loyalty score in the example is calculated as follows:

• Give a score of 5 if a contact has a deal that has closed within the last 60 days.
• Give a score of 10 if the contact's total revenue is above $100.
• Give a score of 10 if the contact's average page views are greater than three.
• Give a score of 5 if the contact's number of visits to your website is greater than 8. 

The tier formula is calculated as follows:

• Contacts with a loyalty score of 15 or above are ranked tier 1.
• Contacts with a loyalty score between 9 and 14 are ranked tier 2.
• Contacts with a loyalty score of less than 9 are ranked tier 3.

Set up loyalty and tier scoring using datasets

To set up loyalty and tier scoring using datasets:

1. In your HubSpot account, navigate to Data Management > Data Studio.
2. In the upper right, click Create dataset. If adding this field to an existing dataset, at the top, click the Datasets tab. Then, click an existing dataset.
3. Select Contacts as your primary object and click Continue.
   
   
4. In the right panel, click + Add source.
   
   
5. Select Deals and click Continue.
6. Click Columns in the right panel, then click + Create new column.
   
   
7. Click Formulas to collapse the Formulas menu, then click Build custom formula (Advanced).
   
   
8. At the bottom, in the Column name field, enter Loyalty score.
9. In the editor box, enter the following:
   IF(DATEDIFF("DAY",[CONTACT.recent_deal_close_date], NOW())<60,5,0)+

   IF([CONTACT.total_revenue]>100, 10,0)+
   IF([CONTACT.hs_analytics_average_page_views]>3,10,0)+
   IF([CONTACT.hs_analytics_num_visits]>8,5,0)

10. In the upper right of the formula window, click Save.
    
    
11. Click + Create new column.
12. Click Formulas to collapse the Formulas menu, then click Build custom formula (Advanced).
13. At the bottom, in the Column name field, enter Loyalty tier.
14. In the editor box, enter the following:
    IF(
 [Loyalty score] >= 15,
 "Tier 1",
 IF([Loyalty score] >= 9, "Tier 2", "Tier 3"))
15. In the upper right of the formula window, click Save.
16. In the upper right, click Save to save the dataset.
    
    
17. Enter a name and description, then click Save.
18. To use the dataset, in the upper right, click Use in downCarat and select an option. 

Purchase history

Use custom properties and datasets to report on the purchase history of contacts. Report on:

• Average order total by basket size.
• Days since last purchase.
• Repeat purchasers vs single purchasers.
• Average purchase amount by original source. 

Average order total by basket size

Use the average order total to find ways to increase average order value through upsell or cross-sell opportunities. 

In this example, create two custom properties to calculate the average purchase amount and number of items purchased. Then, create a report to view contacts' average purchase amount, based on their order size (number of items purchased).

1. Create a rollup property for the average purchase amount with the following attributes:
   • Object type: Contact
   • Field type: Rollup
   • Rollup type: Average
   • Number format: Currency
   • Associated record type: Deal
   • Associated record property: Amount
2. Create a rollup property for the purchase order size with the following attributes:
   • Object type: Deal
   • Field type: Rollup
   • Rollup type: Count
   • Number format: Formatted or Unformatted number
   • Associated record type: Line item
   • Associated record property: Quantity
3. Create a custom report with the following attributes:
   • Primary data source: Contacts
   • Additional data source: Deals
   • Y-axis property: Average purchase amount
   • X-axis property: Purchase order size

Days since last purchase

Use datasets to segment contacts based on how many days have passed since their last purchase. You can use the data in segments, email marketing, and other marketing channels, to stay engaged with customers who've previously purchased from you.

1. In your HubSpot account, navigate to Data Management > Data Studio.
2. In the upper right, click Create dataset. If adding this field to an existing dataset, at the top, click the Datasets tab. Then, click an existing dataset.
3. Select Contacts as your primary object and click Continue.
   
   
4. In the right panel, click + Add source.
   
   
5. Select Deals and click Continue.
6. Click Columns in the right panel, then click + Create new column.
   
   
7. Click Formulas to collapse the Formulas menu, then click Build custom formula (Advanced).
   
   
8. At the bottom, in the Column name field, enter Time since last purchase.
9. In the editor box, enter the following:
   DATEDIFF("DAY", [CONTACT.recent_deal_close_date], NOW())
10. In the upper right of the formula window, click Save.
    
    
11. In the upper right, click Save to save the dataset.
    
    
12. Enter a name and description, then click Save.
13. To use the dataset, in the upper right, click Use in downCarat and select an option. 

Repeat buyers

Use datasets to segment contacts based on their number of purchases. You can use the data in segments, email marketing, and other marketing channels, to target customers for repeat purchases.

1. In your HubSpot account, navigate to Data Management > Data Studio.
2. In the upper right, click Create dataset. If adding this field to an existing dataset, at the top, click the Datasets tab. Then, click an existing dataset.
3. Select Contacts as your primary object and click Continue.
   
   
4. In the right panel, click + Add source.
   
   
5. Select Deals and click Continue.
6. Click Columns in the right panel, then click + Create new column.
   
   
7. Click Formulas to collapse the Formulas menu, then click Apply conditional logic.
   
   
8. At the bottom, in the Column name field, enter Repeat buyer.
9. Next to IF:
   • Click the Select property or column dropdown menu and select Number of Associated Deals.
   • Click the = dropdown menu and select > (greater than).
   • In the Enter value field, enter 1.
10. Next to THEN:
    • Leave the Type field as String.
    • In the Enter value field, enter Repeat purchaser.
11. Under the IF THEN statement, click + Add else if statement.
12. Next to IF:
    • Click the Select property or column dropdown menu and select Number of Associated Deals.
    • Click the = dropdown menu and select = (equal to).
    • In the Enter value field, enter 1.
13. Next to THEN:
    • Leave the Type field as String.
    • In the Enter value field, enter Single purchaser.
14. Next to OTHERWISE:
    1. Leave the Type field as String.
    2. In the Enter value field, enter No purchase history.
       
       
15. In the upper right of the formula window, click Save.
    
    
16. In the upper right, click Save to save the dataset.
    
    
17. Enter a name and description, then click Save.
18. To use the dataset, in the upper right, click Use in downCarat and select an option. 

Average purchase amount by original source

Use an average purchase amount by original source calculation to analyze purchase amounts by channel (e.g., direct traffic, referrals, paid social).

In this example, create a custom property to calculate the average purchase amount. Then create a report to view contacts' average purchase amount by original source.

Please note: if you previously set up a custom property for average purchase amount for the average order total by basket size use case, you can use the same custom property for this use case, and don't need to create an additional custom property.

1. Create a rollup property for the average purchase amount with the following attributes:
   • Object type: Contact
   • Field type: Rollup
   • Rollup type: Average
   • Number format: Currency
   • Associated record type: Deal
   • Associated record property: Amount
2. Create a custom report with the following attributes:
   • Primary data source: Contacts
   • Additional data source: Deals
   • Y-axis property: Average purchase amount
   • X-axis property: Original traffic source (deal property)

Shipping and logistics

Use datasets to report on the duration to ship and how many orders have overdue shipping.

Duration to ship and overdue shipping

In this example, set up a custom property and use it in a workflow to record the time and date when the checkout was completed. Create a dataset field to calculate the duration to complete shipping, and a field to calculate when shipping is overdue. The fields can then be used in reports.

1. Set up your pipeline.
2. Create a custom property with the following attributes:
   • Label: Checkout complete timestamp
   • Object type: Deal
   • Field type: Date and time picker (show date and time only) and allow any dates.
     
     
3. Create a workflow that has the following trigger and action:
   • Trigger: Deal is in the Ecommerce pipeline and is at the Checkout completed stage.
   • Action: Set the Checkout record timestamp property to the date that checkout was completed.
     
     
4. In your HubSpot account, navigate to Data Management > Data Studio.
5. In the upper right, click Create dataset. If adding this field to an existing dataset, at the top, click the Datasets tab. Then, click an existing dataset.
6. Select Contacts as your primary object and click Continue.
   
   
7. In the right panel, click + Add source.
   
   
8. Select Deals and click Continue.
9. Click Columns in the right panel, then click edit Choose columns.
   
   
10. In the right panel, click Deals to collapse the deal properties and select the Record ID and Region checkboxes.
11. Click Formulas to collapse the Formulas menu, then click Build custom formula (Advanced).
    
    
12. At the bottom, in the Column name field, enter Duration to complete shipping.
13. In the editor box, enter the following:
    ABS(DATEDIFF("DAY", [DEAL.checkout_complete_timestamp], [DEAL. closedate]))
14. In the upper right of the formula window, click Save.
    
    
15. Click Columns in the right panel, then click + Create new column.
    
    
16. Click Formulas to collapse the Formulas menu, then click Apply conditional logic.
    
    
17. At the bottom, in the Column name field, enter Shipping overdue.
18. Next to IF:
    • Click the Select property or column dropdown menu and select Duration to complete shipping.
    • Click the = dropdown menu and select > (greater than).
    • In the Enter value field, enter 7.
19. Next to THEN:
    • Leave the Type field as String.
    • In the Enter value field, enter Overdue.
20. Next to OTHERWISE:
    • Leave the Type field as String.
    • In the Enter value field, enter Within range.
      
      
21. In the upper right of the formula window, click Save.
    
    
22. In the upper right, click Save to save the dataset.
    
    
23. Enter a name and description, then click Save.
24. To use the dataset, in the upper right, click Use in downCarat and select an option. 
25. You can then create a custom report with the following attributes:
    • Overdue shipping rate report:
      • Primary data source: the dataset you created

• • • Chart type: Pie
    • Values: Deal Record ID (distinct count)
    • Break down by: Shipping overdue
      
      
  • Shipping overdue by region report:
    
    • Primary data source: the dataset you created
    • Chart type: Pie
    • Values: Shipping overdue
    • Break down by: Deal region
  • Average duration to complete shipping:
    • Primary data source: The dataset you created
    • Chart type: Vertical bar
    • Y-axis: Duration to complete shipping (average)
    • Break down by: Deal region

Product gross profit breakdown by audience or product

Use datasets to break down products by product audience or product name, so you can see which audiences or products are providing the best returns.

1. In your HubSpot account, navigate to Data Management > Data Studio.
2. In the upper right, click Create dataset. If adding this field to an existing dataset, at the top, click the Datasets tab. Then, click an existing dataset.
3. Select Line items as your primary object and click Continue.
   
   
4. In the right panel, click + Add source.
   
   
5. Select Deals and click Continue.
6. In the right panel, click + Add source.
7. Select Contacts and click Continue.
8. In the Deal stage column, take note of the Shipped stage ID number.
   
   
9. Click Columns in the right panel, then click + Create new column.
   
   
10. Click Formulas to collapse the Formulas menu, then click Build custom formula (Advanced).
    
    
11. At the bottom, in the Column name field, enter Gross profit.
12. In the editor box, enter the following:
    IF([Deal stage]=="Shipped ID number (e.g., 57441674)", [Unit price] - [Unit cost], null)
13. In the upper right of the formula window, click Save.
    
    
14. In the upper right, click Save to save the dataset.
    
    
15. Enter a name and description, then click Save.
16. To use the dataset, in the upper right, click Use in downCarat and select an option. 

Customer journeys and behaviors

Page views by product type

Use custom events to track views of products and categorize them using a custom property. Create a report to analyze which product type gets the most views.

2. Create a custom event to track the views of a product. For this use case, it's recommended to create an event via API, or using JavaScript. If you need help setting up custom events, consider using a HubSpot Solutions partner.
3. When creating the custom event, create a custom enumeration property for your product types.
   
   
4. Create a custom report for your custom event. In the report, plot the product types on the x-axis, and the custom event on the y-axis.
   
   

Customer purchase journey

Use custom events to track touch points in a customer journey, from viewing a product, to adding to cart, abandoning cart, and purchase. Add the custom events to a customer journey report to track conversions between each event.

Please note: if you previously set up a custom property for page views for the page views by product type use case, you can use the same custom property for this use case, and don't need to create an additional custom property.

2. Create three custom events:
   • Product views.
   • Cart status.
   • Checkout page tracker.
   • For this use case, it's recommended to create an event via API, or use JavaScript. If you need help setting up custom events, consider using a HubSpot Solutions partner.
3. Add the custom events as stages to a customer journey report.
   
   

Send cart abandonment emails

Keep customers engaged by sending personalized abandoned cart emails. In this example, use the Checkout abandoned pipeline stage in workflows to email buyers with abandoned cart items.

1. Set up your pipeline
2. Create a module in Design Manager. Add the following custom module syntax to the module (update the header and font styling as needed):
   
   

   {#{{ module.deal.id }}#}
   
   {# Get the Contact associated to the Deal #}
   {% set associated_contact = crm_associations({{ module.deal.id }}, "HUBSPOT_DEFINED", 3, "orderBy=-createdate") %}
   
   {# Get the Line_Items associated to the Deal #}
   {% set associated_line_items = crm_associations({{ module.deal.id }}, "HUBSPOT_DEFINED", 19, "orderBy=-createdate") %}
   
   <h2 style="font-family:verdana; margin-top:20px; color:#ff6347; margin-left:10px; margin-right:10dp; text-align: center;"> Hello {{ associated_contact.results[0].firstname }}, Thank you for Visiting our Store!
   </h2>
   
   <p style="margin-left:10px; margin-right:10dp; text-align: center;"> We noticed that you haven't completed your order. Here is a little reminder of what you have in your current shopping cart. Feel free to complete your purchase by clicking on the button below. </p>
   
   {# Grab the product of each line item and display the info #}
   {% for item in associated_line_items.results %}
   {% set product = crm_object("product", item.hs_product_id, "name,price,hs_url", false) %}
   <div style="text-align:center"> <img style="width: 40%" src={{ product.hs_url }} /> <p> {{ product.name }} </p> <h4 style="font-family:verdana;"> ${{ product.price }} </h4>
   </div>
   {% endfor %}
   
   

3. Create a marketing email and add the module.
4. Create a workflow that has the following trigger and action:
   • Trigger: Deal is in the Ecommerce pipeline and is at the Abandoned cart stage. Turn on re-enrollment.
   • Action: Send email.