DocumentationCommerceCommerce Intelligence User Guide

Marketing ROI

Last update: Mon Jul 24 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Beginner
  • Intermediate
  • Admin
  • User
NOTE
This topic contains instructions for clients that are using the original architecture and new architecture. You are on the new architecture if you have the “Data Warehouse Views” section available after selecting “Manage Data” from the main toolbar.

If you are spending money on online advertising, you want to track your return on this spend and make data-driven decisions on further investments. This topic demonstrates how to set up a dashboard that track your channel analysis – including ROI in aggregate and by campaign.

Before getting started, you want to connect your Facebook Ads, Adwords, and Google Ecommerce accounts and bring in any additional online ad spend data. This analysis contains advanced calculated columns.

Consolidated Tables

Original architecture: To bring together your spend from various sources, like Facebook Ads or Google Adwords, Adobe recommends creating a consolidated table of all of your ad spend. You need an analyst to complete this step for you. If you have not, file a support request with the subject [MARKETING ROI ANALYSIS], and an analyst creates this table.

New architecture: You can follow the example in this Analysis Library topic. Consolidated Tables are now known as Data Warehouse Views on the new architecture.

Calculated Columns

Columns to create

  • Consolidated Digital Ad Spend table
  • Campaign name is created by an Adobe analyst as part of your [MARKETING ROI ANALYSIS] ticket

Original and new architectures:

  • sales_flat_order table

    • Order's GA campaign

      • Select a definition: Joined Column

      • Create Path:

      • Many: sales_flat_order.increment_id

      • One: ecommerce####.transaction_id

      • Select a table: ecommerce####

      • Select a column: campaign

      • Path: sales_flat_order.increment_id = ecommerce#####.transactionID

    • Order's GA medium

      • Select a definition: Joined Column
      • Select a table: ecommerce####
      • Select a column: medium
      • Path: sales_flat_order.increment_id = ecommerce#####.transactionId

    • Order's GA source

      • Select a definition: Joined Column
      • Select a table: ecommerce####
      • Select a column: source
      • Path: sales_flat_order.increment_id = ecommerce#####.transactionId
        ^

  • customer_entity table

  • Customer's first order GA campaign

    • Select a definition: Max

    • Select a table: sales_flat_order

    • Select a column: Order's GA campaign

    • Path: sales_flat_order.customer_id = customer_entity.entity_id

    • Filter:

      • Orders we count
      • Customer's order number = 1

  • Customer's first order GA source

    • Select a definition: Max

    • Select a table: sales_flat_order

    • Select a column: Order's GA source

    • Path: sales_flat_order.customer_id = customer_entity.entity_id

    • Filter:

      • Orders we count
      • Customer's order number = 1

  • Customer's first order GA medium

    • Select a definition: Max

    • Select a table: sales_flat_order

    • Select a column: Order's GA medium

    • Path: sales_flat_order.customer_id = customer_entity.entity_id

    • Filter:

      • Orders we count
      • Customer's order number = 1

  • sales_flat_order table

  • Customer's first order GA campaign

    • Select a definition: Joined Column
    • Select a table: customer_entity
    • Select a column: Customer's first order GA campaign
    • Path: sales_flat_order.customer_id = customer_entity.entity_id

  • Customer's first order GA source

    • Select a definition: Joined Column
    • Select a table: customer_entity
    • Select a column: Customer's first order GA source
    • Path: sales_flat_order.customer_id = customer_entity.entity_id

  • Customer's first order GA medium

    • Select a definition: Joined Column
    • Select a table: customer_entity
    • Select a column: Customer's first order GA medium
    • Path: sales_flat_order.customer_id = customer_entity.entity_id

Metrics

  • Ad spend

  • In the Consolidated Digital Ad Spend table

  • This metric performs a Sum

  • On the adCost column

  • Ordered by the date timestamp

  • Ad impressions

  • In the Consolidated Digital Ad Spend table

  • This metric performs a Sum

  • On the Impressions column

  • Ordered by the Month timestamp

  • Ad clicks

  • In the Consolidated Digital Ad Spend table

  • This metric performs a Sum

  • On the adClicks column

  • Ordered by the Month timestamp

NOTE
Make sure to add all new columns as dimensions to metrics before building new reports.

Reports

  • Ad spend (all time)

    • Metric: Ad Spend

  • Metric A: Ad Spend

  • Time period: All time

  • Interval: None

  • Chart Type: Scalar

  • Ad customer acquisitions (all time)

    • Metric: New customers

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

  • Metric A: Ad customer acquisitions

  • Time period: All time

  • Interval: None

  • Chart Type: Scalar

  • Ad ROI

    • Metric: Ad Spend

    • Metric: New customers

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

    • Metric: Average lifetime revenue

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

    • Formula: ((C - (A / B)) / (A / B))

    • Format: Percentage

  • Metric A: Ad Spend (hide)

  • Metric B: Ad customer acquisitions (hide)

  • Metric C: Average LTV (hide)

  • Formula: Ads ROI

  • Time period: All time

  • Interval: None

  • Chart Type: Scalar

  • Orders by ga medium

    • Metric: Orders

  • Metric A: Orders

  • Time period: All time

  • Interval: By Month

  • Group by: Order's medium

  • Chart Type: Area

  • Ad ROI by campaign

    • Metric: Ad Spend

    • Metric:New customers

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

    • Metric: Average lifetime revenue

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

    • Metric: Average lifetime number of orders

    • Filters:

      • User's first order's source LIKE %google%
      • User's first order's source LIKE %facebook%
      • User's first order's source LIKE %fb%
      • User's first order's medium IN cpc, ppc
      • Filter logic: ([A] OR [B] OR [C]) AND [D]

    • Formula: (A / B)

    • Format: Currency

    • Formula: (C - (A / B))

    • Format: Currency

    • Formula: ((C - (A / B)) / (A / B))

    • Format: Percentage

    • Metric: Ad Clicks

    • Metric: Ad Impressions

    • Formula: (H / I)

    • Format: Percentage

    • Formula: (A / H)

    • Format: Currency

  • Metric A: Ad Spend (hide)

  • Metric B: Ad customer acquisitions

  • Metric C: Average LTV

  • Metric D: Average lifetime # of orders

  • Formula: CAC

  • Formula: Avg return

  • Formula: Ads ROI

  • Metric H: adClicks

  • Metric I: Impressions

  • Formula: CTR

  • Formula: CPC

  • Time period: All time

  • Interval: None

  • Group by: campaign (Use Customer's first order's campaign for non-ad spend table metrics)

  • Chart Type: Table

If you run into any questions while building this analysis, or simply want to engage the Professional Services team, contact support.

recommendation-more-help
e1f8a7e8-8cc7-4c99-9697-b1daa1d66dbc