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 

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

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([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

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 

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

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 

SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)])

Clickthrough rate (CTR)

SUM([Beacon Click 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)])

Click to open rate (CTOR)

SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Nobeacon Click Count (Campaign Stats By Engagement)])/SUM([Beacon Click Count (Campaign Stats By Engagement)]+[Beacon Noclick Count (Campaign Stats By Engagement)]+[Nobeacon 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)])

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 

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

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

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

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

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

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

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

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








Top