Measuring the ROI of Your Marketing Campaigns

The goal of marketing campaigns is to drive engagement and conversions. Most commonly performed by attracting traffic to the site, these campaigns use UTM parameters for attribution. In our analysis, we'll be heavily relying on UTM parameters to analyze not only campaign, but also channel performance.

Learn how to effectively use UTM parameters in your marketing campaign strategies.

For our analysis walkthrough, we'll use fictitious e-commerce and marketing data from on-demand artisanal toast company, Toastmates.

Toastmates is currently running these two campaigns:

• "National Toast Day", where $5 off was applied if you made a purchase on that day
• "A Toast To Your Friend", where you can buy toast for a friend at $5 off

Each of these campaigns used a combination of channels. Here is a table with the channels and corresponding UTM parameters so when we build the SQL query, we can make sure all of the traffic sources are accounted for.

Expand image

We'll use SQL below to measure the performance of each campaign and what that means for future marketing activities.

The following query creates a table where each row is a customer and the columns are the date time when a key funnel event happens that have the context_campaign_name to match that of the UTM_campaign . The key funnel events in this analysis are Store Visited(based on a page view to the store URL), Product Viewed , and Order Completed . Given that each channel may have some key top of the funnel action that is unique to itself, let's save that analysis for when we're analyzing across channels.

Feel free to copy and paste the below query for your analysis so long as you replace national-toast-day with your own UTM campaign parameter.

1
    with
2

3
    users as (
4
        select  *
5
          from  toastmates.users
6
    ),
7

8
    page_viewed as (
9
        select  p.received_at as page_viewed_at,
10
                p.context_campaign_name,
11
                p.user_id
12
          from  toastmates.pages p
13
     left join  users u
14
            on  u.id = p.user_id
15
         where  p.context_campaign_name is not null
16
           and  p.url ilike '%toastmates.com/store%'
17
    ),
18

19
    product_viewed as (
20
        select  v.received_at as product_viewed_at,
21
                v.context_campaign_name,
22
                v.user_id
23
          from  toastmates.product_viewed v
24
     left join  users u
25
            on  u.id = v.user_id
26
    ),
27

28
    order_completed as (
29
        select  c.received_at as order_completed_at,
30
                c.context_campaign_name,
31
                c.user_id
32
          from  toastmates.order_completed c
33
     left join  users u
34
            on  u.id = c.user_id
35
    )
36

37
        select  p.user_id as user_id,
38
                page_viewed_at,
39
                product_viewed_at,
40
                order_completed_at,
41
                p.context_campaign_name
42
          from  page_viewed p
43
     left join  product_viewed v
44
            on  p.user_id = v.user_id
45
     left join  order_completed c
46
            on  p.user_id = l.user_id
47
      order by  5 desc

Here are the first four rows of the resulting table:

Expand image

Then, we can use tweak the query above into the one below to perform some simple COUNT and SUM on the previous table to get conversion metrics as well as total revenue derived from the campaign.

1
    with
2

3
    users as (
4
        select  *
5
          from  toastmates.users
6
    ),
7

8
    page_viewed as (
9
        select  p.received_at as page_viewed_at,
10
                p.context_campaign_name,
11
                p.user_id
12
          from  toastmates.pages p
13
     left join  users u
14
            on  u.id = p.user_id
15
         where  p.context_campaign_name is not null
16
           and  p.url ilike '%toastmates.com/store%'
17
    ),
18

19
    product_viewed as (
20
        select  v.received_at as product_viewed_at,
21
                v.context_campaign_name,
22
                v.user_id
23
          from  toastmates.product_viewed v
24
     left join  users u
25
            on  u.id = v.user_id
26
    ),
27

28
    order_completed as (
29
        select  c.received_at as order_completed_at,
30
                c.context_campaign_name,
31
                c.total,
32
                c.user_id
33
          from  toastmates.order_completed c
34
     left join  users u
35
            on  u.id = c.user_id
36
    )
37

38
        select  p.context_campaign_name,
39
                count(page_viewed_at) as store_visits,
40
                count(product_viewed_at) as product_views,
41
                count(order_completed_at) as orders_completed,
42
                sum(total) as total_revenue
43
          from  page_viewed p
44
     left join  product_viewed v
45
            on  p.user_id = v.user_id
46
     left join  order_completed c
47
            on  p.user_id = l.user_id
48
      group by  5
49
      order by  5 desc

Here is the resulting table:

Expand image

This analysis not only gives us a great snapshot of the conversion points along each campaign's funnel, but also shows that we've generated $3,100.37 from the National Toast Day campaign and $3,824.68 from the Toast Your Friend campaign. Also we can see that the quality of the traffic from the National Toast Day is higher, but we've had more total traffic from Toast Your Friend, which makes sense since it's an ongoing campaign.

But this is not yet ROI, since we haven't incorporated the spend—the labor of your marketing team and the paid acquisition channels to source part of this traffic—that went into these channels.

Add campaign costs

The main costs that are incorporated in an ROI calculation are salaries (pro-rated by person-hour) and media spend. While we could conceivably create a custom, static table in SQL that contains the spend information over time, the faster and more practical way would be a back of the envelope calculation.

The costs associated with a given campaign consist of two major pieces: the person-hour cost and any associated media spend.

• Calculating the pro-rated person-hour is an estimate of the number of hours and people used to set up and manage the campaign, then multiplied by the hourly rates based off their annual salaries.
• The media spend is the advertising cost for distributing creatives to generate traffic to your store

Want to easily export advertising data from Google Adwords or Facebook Ads? Check out Segment Sources.

When we have the aggregate cost numbers, the formula for ROI is:

Campaign ROI = (Profit Attributed to Campaign – Campaign Cost) / Campaign Cost

Here is a spreadsheet to illustrate the ROI calculation for both campaigns:

Expand image

Though ROI numbers are one success metric, it's an important benchmark for comparing performance when launching new campaigns or comparing against past campaigns.

But how can we go one step further and see what worked and what didn't? One approach is to see which channels convert better, so you know how to adjust your marketing spend or media buys in your current campaigns or future ones.

A single campaign can include a wide variety of channels: email, display ads, push notifications, forums, etc. all of which yields different engagement and conversion rates. Effective marketers will keep a pulse on each channel throughout the duration of the campaign to understand whether a target audience is being saturated, a creative refresh is needed (for advertising), or how to efficiently allocate future spend towards a source that converts.

The analysis is similar to measuring the performance across a single campaign, with the only change being finding events where we focus on context_campaign_medium or context_campaign_source instead of context_campaign_name . The SQL below measures the conversion rates at key funnel events for national-toast-day , but broken down by utm_medium .

You can copy the below into your favorite editor, as long as you change out the context_campaign_name and context_campaign_medium parameters to ones that applies to your business.

1
    with
2

3
    users as (
4
        select  *
5
          from  toastmates.users
6
    ),
7

8
    page_viewed as (
9
        select  p.received_at as page_viewed_at,
10
                p.context_campaign_name,
11
                p.user_id
12
          from  site.pages p
13
     left join  users u
14
            on  u.id = p.user_id
15
         where  p.context_campaign_name = 'national-toast-day'
16
           and  p.context_campaign_medium is not null
17
           and  p.url ilike '%toastmates.com/store%'
18
    ),
19

20
    product_viewed as (
21
        select  v.received_at as product_viewed_at,
22
                v.context_campaign_medium,
23
                v.user_id
24
          from  toastmates.product_viewed v
25
     left join  users u
26
            on  u.id = v.user_id
27
    ),
28

29
    order_completed as (
30
        select  c.received_at as order_completed_at,
31
                c.context_campaign_medium,
32
                c.user_id,
33
                c.total
34
          from  toastmates.order_completed c
35
     left join  users u
36
            on  u.id = c.user_id
37
    )
38

39
        select  p.context_campaign_medium as utm_medium,
40
                count(page_viewed_at) as store_visits,
41
                count(product_viewed_at) as product_views,
42
                count(order_completed_at) as orders_completed,
43
                sum(c.total) as total_revenue
44
          from  page_viewed p
45
     left join  product_viewed_at v
46
            on  p.user_id = c.user_id
47
     left join  order_completed c
48
            on  p.user_id = c.user_id
49
      group by  1
50
      order by  1 desc

The resulting table:

Expand image

Since the National Toast Day campaign is relatively new, the majority of the traffic is from the email and an article ("news"). But we can see that the social channels have a lower conversion from store visits to product views. Email has the best overall conversion to revenue, which may be attributed to the recipients already familiar with the Toastmates brand or having previously had a stellar end-to-end shopping experience.

We can further breakdown this analysis by seeing which email, display ads, and social channels performed the best, by adding utm_source and utm_content ,assuming that you've properly added them in your earned and paid media links. Also note that this preliminary analysis in SQL doesn't account for double-counted users, who had impressions with our brand on multiple channels (e.g. someone seeing a display ad, yet converted on the email outreach). Fortunately, there are multi-touch attribution models that can be applied to better understand the weights of each activity towards conversion.

Learn more about multi-touch attribution models.

Measuring the ROI and performance of marketing campaigns and marketing channels tells a compelling story about what types of campaigns resonate with your audience. How does your audience like to be engaged? Text, push notifications, email? What campaign messaging hooks work the best in getting them back at your store?

You can apply this analytical approach and performance measurement techniques to a wide variety of marketing activities, such as offline marketing, billboards, or sponsoring events. These insights can empower your team to focus on what works and eliminate what doesn't.

Talk to a product specialist to learn how companies like Warby Parker and Crate & Barrel use a data warehouse to increase engagement and sales.