Calculations
Contents
Add calculated fields to your Business Intelligence worksheets to show data for specific stats.
Calculations are saved to your current workbook and can be used across all worksheets in the workbook that use the same datasource. If you create a new workbook or add a secondary data source, you will need to recreate the calculated fields.
Add a Calculated Field
 Create a new sheet or select Edit for an existing sheet.
 Select the data source you wish to work with from the Data tab if your worksheet contains more than one data source.
 Follow one of two paths to open the Create Calculated Field modal window:
 Open the Analysis menu in the main menu bar. Select Create Calculated Field from the menu.
 Select the dropdown arrow next to the Search bar. Select Create Calculated Field from the menu.
 Give your calculation a unique name.
Note: The Create Calculated Field modal may initially show that the calculation contains errors.  Enter your formula in the box. The easiest method is to copy and paste a formula from the Calculations documentation for your selected data source.
 If the field names don't match what exists in the data source, they show red.
 Correct the field names by dragging the data points from the lefthand data table.
 The fields validate automatically as you select the correct fields.
 Select Okay. The new calculation shows under Measure Names in the Data Sources panel.
 To add the calculation to your report, drag and drop it to a column or row.
 Once you add your calculation, you may wish to reformat the calculation to, for example, a percentage.
 Hover over your calculation and select the down arrow.
 Select Format Number from the menu.
 Select your new format from the modal window.
Add Complex Calculated Fields
Sometimes the calculated field you want to use in a report requires a more complex formula to get the data you need. All of the calculations available are listed in the Calculations documentation, separated by data source.
 Create a new worksheet or open an existing one.
 Open the Analysis menu in the main menu bar. Select Create Calculated Field from the menu.
The Create Calculated Field modal opens.  Locate the data source you're working with in the Calculations documentation. Select and copy the formula for the field you wish to add.
 Paste the formula into the Create Calculated Field modal.
 Your formula should validate automatically. If the modal shows a validation error, check to make sure you selected a formula from the correct data source.
 Select OK.
 Locate your new calculated field in the Measure Names table. Drag the field and drop it to a column or row.
Calculations
The following calculations can be used to create metrics within Business Intelligence. Calculations are sorted by data source to allow you to copy and paste the calculation into Business Intelligence as a new Calculated Field under the Analysis menu.
Email and List Stats
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Unique clicks & CTR  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Unique Real Clicks & CTR  SUM([real_click_count]) 
Total Real Clicks & CTR  SUM([real_total_click_count]) 
Total Clicks & CTR  SUM([Total Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Delivered Count])*1000 
Delivery rate  SUM([Delivered Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue]/100)/[Delivered Count])*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Delivered Count]))*10 
Campaign Stats by Domain & Engagement
campaign_stats_by_domain
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Send Count][Hardbounce Count][Softbounce Count]) 
Unique clicks & CTR  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Unique Real Clicks & CTR  SUM([real_click_count]) 
Total Real Clicks & CTR  SUM([real_total_click_count]) 
Total Clicks & CTR  SUM([Total Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Send Count][Hardbounce Count][Softbounce Count])*1000 
Delivery rate  SUM([Send Count][Hardbounce Count][Softbounce Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue])/100)/SUM([Send Count][Hardbounce Count][Softbounce Count]))*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Send Count][Hardbounce Count][Softbounce Count]))*10 
campaign_stats_by_engagement
Unique opens  SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Beacon Noclick Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)]) 
Open rate  SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Beacon Noclick Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)][Hardbounce Count (Campaign Stats By Engagement)][Softbounce Count (Campaign Stats By Engagement)]) 
Unique clicks & CTR  SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)])

Unique Real Clicks & CTR  SUM([real_click_count (campaign_stats_by_engagement)])

Total Real Clicks & CTR  SUM([real_total_click_count (campaign_stats_by_engagement)])

Total Clicks & CTR  SUM([Total Click Count (Campaign Stats By Engagement)])

PV/M  SUM([Pageview Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)][Hardbounce Count (Campaign Stats By Engagement)][Softbounce Count (Campaign Stats By Engagement)])*1000 
Delivery rate  SUM([Send Count (Campaign Stats By Engagement)][Hardbounce Count (Campaign Stats By Engagement)][Softbounce Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)]) 
Complaint rate  SUM([Spam Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)]) 
Rev/M  (SUM([Revenue (Campaign Stats By Engagement)]/100)/SUM([Send Count (Campaign Stats By Engagement)][Hardbounce Count (Campaign Stats By Engagement)][Softbounce Count (Campaign Stats By Engagement)]))*1000 
Conversion rate  SUM([Purchase Count (Campaign Stats By Engagement)])/sum([Send Count (Campaign Stats By Engagement)]) 
Hard bounce rate  SUM([Hardbounce Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)]) 
Soft bounce rate  SUM([Softbounce Count (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)]) 
Average Order Value (AOV)  sum([Revenue (Campaign Stats By Engagement)])/SUM([Purchase Count (Campaign Stats By Engagement)])/100 
Pageviews per click  SUM([Pageview Count (Campaign Stats By Engagement)])/SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)]) 
RPM  (SUM([Revenue (Campaign Stats By Engagement)])/SUM([Send Count (Campaign Stats By Engagement)][Hardbounce Count (Campaign Stats By Engagement)][Softbounce Count (Campaign Stats By Engagement)]))*10 
Campaign Stats by Signup Month
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Send Count][Hardbounce Count][Softbounce Count]) 
Unique clicks & CTR  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Unique Real Clicks & CTR  SUM([real_click_count])

Total Real Clicks & CTR  SUM([real_total_click_count])

Total Clicks & CTR  SUM([Total Click Count])

PV/M  SUM([Pageview Count])/SUM([Send Count][Hardbounce Count][Softbounce Count])*1000 
Delivery rate  SUM([Send Count][Hardbounce Count][Softbounce Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue])/100)/(SUM([Send Count][Hardbounce Count][Softbounce Count])))*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Send Count][Hardbounce Count][Softbounce Count]))*10 
Campaign Stats by Device
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Send Count][Hardbounce Count][Softbounce Count]) 
Unique clicks  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Send Count][Hardbounce Count][Softbounce Count]) 
Click to open rate (CTOR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Send Count][Hardbounce Count][Softbounce Count])*1000 
Delivery rate  SUM([Send Count][Hardbounce Count][Softbounce Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  ((SUM([Revenue])/100)/(SUM([Send Count][Hardbounce Count][Softbounce Count])))*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Send Count][Hardbounce Count][Softbounce Count]))*10 
Campaign Stats
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Unique clicks  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Click to open rate (CTOR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Delivered Count])*1000 
Delivery rate  SUM([Delivered Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue])/100)/[Delivered Count])*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Delivered Count]))*10 
Campaign Stats by Label
campaign_stats_blast_label
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Unique clicks  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Click to open rate (CTOR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Delivered Count])*1000 
Delivery rate  SUM([Delivered Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue])/100)/[Delivered Count])*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Delivered Count]))*10 
campaign_stats_by_device_label
Unique opens  SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Beacon Noclick Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)]) 
Open rate  SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Beacon Noclick Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)]) 
Unique clicks  SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)]) 
Click to open rate (CTOR)  SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)])/SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Beacon Noclick Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)]) 
PV/M  SUM([Pageview Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)])*1000 
Delivery rate  SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)]) 
Complaint rate  SUM([Spam Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)]) 
Rev/M  (SUM([Revenue (Campaign Stats By Device Label)])/100)/SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)])*1000 
Conversion rate  SUM([Purchase Count (Campaign Stats By Device Label)])/sum([Send Count (Campaign Stats By Device Label)]) 
Hard bounce rate  SUM([Hardbounce Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)]) 
Soft bounce rate  SUM([Softbounce Count (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)]) 
Average Order Value (AOV)  sum([Revenue (Campaign Stats By Device Label)])/SUM([Purchase Count (Campaign Stats By Device Label)])/100 
Pageviews per click  SUM([Pageview Count (Campaign Stats By Device Label)])/SUM([Beacon Click Count (Campaign Stats By Device Label)]+[Nobeacon Click Count (Campaign Stats By Device Label)]) 
RPM  (SUM([Revenue (Campaign Stats By Device Label)])/SUM([Send Count (Campaign Stats By Device Label)][Hardbounce Count (Campaign Stats By Device Label)][Softbounce Count (Campaign Stats By Device Label)]))*10 
campaign_stats_by_domain_label
Unique opens  SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Beacon Noclick Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)]) 
Open rate  SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Beacon Noclick Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)]) 
Unique clicks  SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)]) 
Click to open rate (CTOR)  SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)])/SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Beacon Noclick Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)]) 
PV/M  SUM([Pageview Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)])*1000 
Delivery rate  SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)]) 
Complaint rate  SUM([Spam Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)]) 
Rev/M  (SUM([Revenue (Campaign Stats By Domain Label)]/100)/SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)]))*1000 
Conversion rate  SUM([Purchase Count (Campaign Stats By Domain Label)])/sum([Send Count (Campaign Stats By Domain Label)]) 
Hard bounce rate  SUM([Hardbounce Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)]) 
Soft bounce rate  SUM([Softbounce Count (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)]) 
Average Order Value (AOV)  sum([Revenue (Campaign Stats By Domain Label)])/SUM([Purchase Count (Campaign Stats By Domain Label)])/100 
Pageviews per click  SUM([Pageview Count (Campaign Stats By Domain Label)])/SUM([Beacon Click Count (Campaign Stats By Domain Label)]+[Nobeacon Click Count (Campaign Stats By Domain Label)]) 
RPM  (SUM([Revenue (Campaign Stats By Domain Label)])/SUM([Send Count (Campaign Stats By Domain Label)][Hardbounce Count (Campaign Stats By Domain Label)][Softbounce Count (Campaign Stats By Domain Label)]))*10 
Purchase
AOV (Purchase)  SUM([Total Price])/sum([Purchase Count (Purchase Stats By Hour)]) 
AVG basket size  sum([total_items_count])/sum([Purchase Count (Purchase Stats By Hour)]) 
Profile Stats
AOV  sum([Total Purchase Revenue])/sum([Total Purchase Count]) 
Buyers  if [Is Buyer] = 'Buyer' then [Num Profiles] END 
Cohort conversion  sum(if [Is Buyer] = 'Buyer' then [Num Profiles] END)/sum([Num Profiles]) 
Conversion rate  SUM([Num Profiles Gte 1 Purchases])/SUM([Num Profiles]) 
1 to 2 Retention  SUM([Num Profiles Gte 2 Purchases])/SUM([Num Profiles Gte 1 Purchases]) 
2 to 3 Retention  SUM([Num Profiles Gte 3 Purchases])/SUM([Num Profiles Gte 2 Purchases]) 
3 to 4 Retention  SUM([Num Profiles Gte 4 Purchases])/SUM([Num Profiles Gte 3 Purchases]) 
Email Stats
Unique opens  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
Open rate  SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Unique clicks  SUM([Beacon Click Count]+[Nobeacon Click Count]) 
Clickthrough rate (CTR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Delivered Count]) 
Click to open rate (CTOR)  SUM([Beacon Click Count]+[Nobeacon Click Count])/SUM([Beacon Click Count]+[Beacon Noclick Count]+[Nobeacon Click Count]) 
PV/M  SUM([Pageview Count])/SUM([Delivered Count])*1000 
Delivery rate  SUM([Delivered Count])/SUM([Send Count]) 
Complaint rate  SUM([Spam Count])/SUM([Send Count]) 
Rev/M  (SUM([Revenue]/100)/[Delivered Count])*1000 
Conversion rate  SUM([Purchase Count])/sum([Send Count]) 
Hard bounce rate  SUM([Hardbounce Count])/SUM([Send Count]) 
Soft bounce rate  SUM([Softbounce Count])/SUM([Send Count]) 
Average Order Value (AOV)  sum([Revenue])/SUM([Purchase Count])/100 
Pageviews per click  SUM([Pageview Count])/SUM([Beacon Click Count]+[Nobeacon Click Count]) 
RPM  (SUM([Revenue])/SUM([Delivered Count]))*10 