A Technical Deep-Dive into Daasity's Marketing Dashboard

Oren Rosenberg
November 22, 2022

This article provides a technical explanation of how Daasity normalizes marketing data (via our Unified Marketing Schema) as well as some visualizations built in Looker.

Daasity’s out-of-the-box marketing dashboard provides an actionable and digestible foundation for brands to understand their cross-channel marketing spend, how that spend is driving sales, and to provide a holistic look at their performance. 

Data is presented in both detailed reports as well as in top-line metrics to answer questions at a glance, including:

  • How many sales dollars does the brand receive for each dollar of marketing spend?
  • What is the cost of acquiring a new customer?
  • How many orders are coming from new vs. returning customers?
  • What are the trends in acquisition cost and return on ad spend?
  • How do metrics vary between first-click, last-click, and vendor-reported models?
  • What are the trends in clicks, impressions, and cost-per-click/impression?

Notes on Tables and Construction

Necessary familiarity: 

Table construction and notes:

  • Marketing ELTs provide both integration-specific explores as well as a rolled-up channel- and vendor-level marketing explore (with a campaign-level add-on available in Pro). The ELTs are specific to each integration and insert into their respective integration-specific table before inserting into ums_staging.mktg_spend_source, which eventually feeds the ums.master_spend table.
  • Master spend is then JOINed with marketing performance metrics originating from Order Line Revenue. This key for this JOIN is performance_id, which is a hashed concatenation of several fields. Oftentime when these explores break, one or more of the fields in the performance_id field are different between master spend and marketing performance.

Relevant Data and Considerations

For this analysis, you’ll need to have access to the UMS section of your data warehouse, which contains the following key tables:

  • Master Spend: contains vendor-reported orders and revenue along with clicks, impressions, and spend
  • Marketing Performance: rolls up order counts, item counts, and revenue metrics using first-click, last-click, and vendor-reported attribution models
  • Vendor-Specific Marketing Tables: contains the same data as exists in the channel- and vendor-level marketing performance explore, but contained to a single integration

Marketing Integrations / Master Spend

Similar data is extracted across Daasity’s different out-of-the-box marketing integrations. This data includes clicks, impressions, and spend as well as vendor-reported orders and revenue. Daasity offers the following marketing integrations out of the box:

  • Facebook
  • Google Ads
  • Amazon Ads
  • Pinterest
  • Criteo
  • Pepperjam
  • Snapchat

Each extractor creates a variable number of tables that are then used to populate that integration’s own UMS table, which is in a standardized table structure. Here’s an example for Pepperjam:


WITH
  last_sync_date AS
(
  SELECT
    CASE
      WHEN MAX(__loaded_at) IS NULL THEN '2000-01-01'
      ELSE MAX(__loaded_at)
    END AS last_load_date
  FROM ums.pepperjam_spend
),
  publisher_spend AS
(
  select
      pp.publisher_id
    , SUM(pp.bonus_amount+pp.total_commission) AS total_spend
    , SUM(pp.publisher_bonus+pp.publisher_commission) AS total_pub_spend
    , SUM(pp.sales) vendor_reported_orders
    , transaction_date
  from pepperjam.publisher_performance pp --where transaction_date::DATE > '2020-09-14' and transaction_date::DATE <= '2020-09-15'
  group by pp.publisher_id, transaction_date
)
SELECT
      MD5(COALESCE(cpp.ad_date::DATE,'2099-12-31') || ':' || 'Affiliates' || ':' || 'Pepperjam' || ':' || cpp.publisher_id || ':' || cpp.creative_id || ':' || COALESCE(cpp.publisher,'UNKNOWN') || ':' || COALESCE(cpp.creative_id,'UNKNOWN')) AS id
    , cpp.ad_date::DATE AS activity_date
    , cpp.__source_id AS store
    , LOWER(CAST('Affiliates' AS TEXT)) AS channel
    , LOWER(CAST('Pepperjam' AS TEXT)) AS vendor
    , NULL AS subchannel
    , NULL AS media_type
    , cpp.publisher_id AS ad_account_id
    , cpp.publisher AS ad_account_name
    , cpp.creative_id AS campaign_id
    , cpp.creative_type AS campaign_type
    , cpp.creative_name AS campaign_name
    , sum(total_spend) AS total_spend
    , sum(total_pub_spend) AS total_pub_spend
    , sum(vendor_reported_orders) AS vendor_reported_orders
    , SUM(COALESCE(cpp.clicks,0)) AS total_clicks
    , SUM(COALESCE(CAST(cpp.impressions AS INTEGER),0)) AS total_impressions
    , SUM(COALESCE(cpp.sales,0)) AS vendor_reported_revenue
    , NULL AS attribution_window
    , CAST(90 AS INTEGER) AS source_rank
    , CURRENT_TIMESTAMP::TIMESTAMP AS __loaded_at
    , MAX(cpp.__synced_at) AS __synced_at
FROM pepperjam.creative_performance_publisher cpp
LEFT JOIN publisher_spend ps ON cpp.publisher_id = ps.publisher_id AND cpp.ad_date = ps.transaction_date
CROSS JOIN last_sync_date lsd
WHERE DATEDIFF('day',lsd.last_load_date,cpp.__synced_at) > -7
GROUP BY
        MD5(COALESCE(cpp.ad_date::DATE,'2099-12-31') || ':' || 'Affiliates' || ':' || 'Pepperjam' || ':' || cpp.publisher_id || ':' || cpp.creative_id || ':' || COALESCE(cpp.publisher,'UNKNOWN') || ':' || COALESCE(cpp.creative_id,'UNKNOWN'))
       , cpp.ad_date
       , cpp.__source_id
       , LOWER(CAST('Affiliates' AS TEXT))
       , LOWER(CAST('Pepperjam' AS TEXT))
       , cpp.publisher_id
       , cpp.publisher
       , cpp.creative_id
       , cpp.creative_type
       , cpp.creative_name
       , CURRENT_TIMESTAMP::TIMESTAMP
ORDER BY ad_date
;

The data in the integration-specific table is then used to populate ums_staging.mktg_spend_source, which contains data from all marketing integrations. The performance_id field will JOIN marketing integration data with marketing performance data for calculations such as CPO, ROAS, etc. 

This example is again for Pepperjam:


INSERT INTO ums_staging.mktg_spend_source
SELECT
    MD5(COALESCE(ps.activity_date::DATE,'2099-12-31')||':'||COALESCE(store,'')|| ':' ||COALESCE(channel,'NONE') || ':' || COALESCE(vendor,'NONE') ||':'|| COALESCE(subchannel,'NONE') ||':'|| COALESCE(media_type,'NONE') ||':'|| COALESCE(campaign_id,'UNKNOWN') ||':'|| COALESCE(attribution_window,'NONE')) AS id
  , MD5(COALESCE(ps.activity_date::DATE,'2099-12-31')||':'||COALESCE(store,'')|| ':' ||COALESCE(channel,'NONE') || ':' || COALESCE(vendor,'NONE') ||':'|| COALESCE(subchannel,'none') ||':'|| COALESCE(media_type,'none') ||':'|| COALESCE(attribution_window,'NONE')) AS performance_id
  , activity_date::DATE
  , store
  , channel AS channel
  , vendor AS vendor
  , subchannel AS subchannel
  , media_type AS media_type
  -- , account_name AS property_name
  , NULL AS network_type
  , campaign_id  --these might be null
  , campaign_name -- this might be null
  , 'USD' AS original_currency
  , 1.00 AS currency_conversion_date
  , 'USD' AS target_currency
  , SUM(total_spend) AS total_spend
  , SUM(total_clicks) AS total_clicks
  , SUM(total_impressions) AS total_impressions
  , SUM(vendor_reported_orders) AS vendor_reported_orders
  , SUM(vendor_reported_revenue) AS vendor_reported_revenue
  , attribution_window
  , CAST(90 AS INTEGER) AS source_rank
  , CURRENT_TIMESTAMP::TIMESTAMP AS __loaded_at
  , MAX(ps.__synced_at) AS __synced_at
FROM ums.pepperjam_spend ps
WHERE MD5(COALESCE(ps.activity_date::DATE,'2099-12-31')||':'||COALESCE(store,'')|| ':' ||COALESCE(channel,'NONE') || ':' || COALESCE(vendor,'NONE') ||':'|| COALESCE(subchannel,'none') ||':'|| COALESCE(media_type,'none') ||':'|| COALESCE(campaign_id,'UNKNOWN') ||':'|| COALESCE(attribution_window,'NONE'))
NOT IN
  (
    SELECT
      id
    FROM ums_staging.mktg_spend_source
  )
GROUP BY
    activity_date
  , store
  , channel
  , vendor
  , subchannel
  , media_type
  , campaign_id
  , campaign_name
  , attribution_window

The data inserted from each marketing integration into ums_staging.mktg_spend_source are then fed into ums.master_spend with this script:


INSERT INTO ums.master_spend
WITH
  mktmin AS
(
  SELECT
    MD5(COALESCE(activity_date::DATE,'2099-12-31')||':'||COALESCE(store, '')||':'||COALESCE(channel,'NONE')||':'||COALESCE(vendor,'NONE')||':'||COALESCE(subchannel,'NONE')||':'||COALESCE(media_type,'NONE')||':'||COALESCE(network_type,'UNKNOWN')||':'||COALESCE(campaign_id,'UNKNOWN')||':'||COALESCE(attribution_window,'UNKNOWN')) AS hashed_id
  , MIN(source_rank) AS source_rank
  FROM ums_staging.mktg_spend_source
  GROUP BY
    MD5(COALESCE(activity_date::DATE,'2099-12-31')||':'||COALESCE(store, '')||':'||COALESCE(channel,'NONE')||':'||COALESCE(vendor,'NONE')||':'||COALESCE(subchannel,'NONE')||':'||COALESCE(media_type,'NONE')||':'||COALESCE(network_type,'UNKNOWN')||':'||COALESCE(campaign_id,'UNKNOWN')||':'||COALESCE(attribution_window,'UNKNOWN'))
),

store_size AS
(
  SELECT
      o.__shop_id
    , COUNT(DISTINCT order_id)
    , ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT order_id) DESC) AS row_num
  FROM uos.orders o
  WHERE __uos_source = 'shopify'
  GROUP BY
      o.__shop_id
  ORDER BY
      COUNT(DISTINCT order_id) DESC
),

  largest_store AS
(
  SELECT
      __shop_id
  FROM store_size
  WHERE row_num = 1
)

  SELECT
      MD5(COALESCE(mss.activity_date::DATE,'2099-12-31')||':'||COALESCE(mss.store, '')||':'||COALESCE(channel,'NONE')||':'||COALESCE(vendor,'NONE')||':'||COALESCE(subchannel,'NONE')||':'||COALESCE(media_type,'NONE')||':'||COALESCE(network_type,'UNKNOWN')||':'||COALESCE(campaign_id,'UNKNOWN')||':'||COALESCE(attribution_window,'UNKNOWN')) AS id
    , MD5(COALESCE(mss.activity_date::DATE,'2099-12-31')||':'||UPPER(COALESCE(mss.store, ls.__shop_id))||':'||COALESCE(UPPER(channel),'NONE')||':'||COALESCE(UPPER(vendor),'NONE')||':'||COALESCE(UPPER(subchannel),'NONE')||':'||COALESCE(UPPER(media_type),'NONE')||':'||COALESCE(attribution_window,'NONE')) AS performance_id
    , mss.activity_date
    , COALESCE(mss.store, ls.__shop_id) AS store
    , mss.channel
    , mss.vendor
    , COALESCE(mss.subchannel,'none') AS subchannel
    , COALESCE(mss.media_type,'none') AS media_type
    -- , mss.property_name
    , mss.network_type
    , mss.campaign_id
    , mss.campaign_name
    , mss.original_currency
    , mss.currency_conversion_rate
    , mss.converted_currency
    , SUM(mss.total_spend) AS total_spend
    , SUM(mss.total_clicks) AS total_clicks
    , SUM(mss.total_impressions) AS total_impressions
    , SUM(mss.vendor_reported_orders) AS vendor_reported_orders
    , SUM(mss.vendor_reported_revenue) AS vendor_reported_revenue
    , mss.attribution_window
    , mss.source_rank
    , mss.__loaded_at
    , mss.__synced_at::DATE
  FROM ums_staging.mktg_spend_source mss
  INNER JOIN ums_staging.master_spend dsms ON mss.id = dsms.id
  INNER JOIN mktmin ON MD5(COALESCE(mss.activity_date::DATE,'2099-12-31')||':'||COALESCE(mss.store, '')||':'||COALESCE(channel,'NONE')||':'||COALESCE(vendor,'NONE')||':'||COALESCE(subchannel,'NONE')||':'||COALESCE(media_type,'NONE')||':'||COALESCE(network_type,'UNKNOWN')||':'||COALESCE(campaign_id,'UNKNOWN')||':'||COALESCE(attribution_window,'UNKNOWN')) = mktmin.hashed_id AND mss.source_rank = mktmin.source_rank
  CROSS JOIN largest_store ls
  GROUP BY
      MD5(COALESCE(mss.activity_date::DATE,'2099-12-31')||':'||COALESCE(mss.store, '')||':'||COALESCE(channel,'NONE')||':'||COALESCE(vendor,'NONE')||':'||COALESCE(subchannel,'NONE')||':'||COALESCE(media_type,'NONE')||':'||COALESCE(network_type,'UNKNOWN')||':'||COALESCE(campaign_id,'UNKNOWN')||':'||COALESCE(attribution_window,'UNKNOWN'))
    , MD5(COALESCE(mss.activity_date::DATE,'2099-12-31')||':'||UPPER(COALESCE(mss.store, ls.__shop_id))||':'||COALESCE(UPPER(channel),'NONE')||':'||COALESCE(UPPER(vendor),'NONE')||':'||COALESCE(UPPER(subchannel),'NONE')||':'||COALESCE(UPPER(media_type),'NONE')||':'||COALESCE(attribution_window,'NONE'))
    , mss.activity_date
    , COALESCE(mss.store, ls.__shop_id)
    , mss.channel
    , mss.vendor
    , COALESCE(mss.subchannel,'none')
    , COALESCE(mss.media_type,'none')
    -- , mss.property_name
    , mss.network_type
    , mss.campaign_id
    , mss.campaign_name
    , mss.original_currency
    , mss.currency_conversion_rate
    , mss.converted_currency
    , mss.attribution_window
    , mss.source_rank
    , mss.__loaded_at
    , mss.__synced_at::DATE
;

Marketing Performance

The marketing performance script JOINs data from the master spend table to Order Line Revenue data rolled up to the level of date, store, channel, vendor, subchannel, and media type. 

First, master spend data gets rolled up to the appropriate level of aggregation:


  SELECT
      activity_date
    , COALESCE(store, ls.__shop_id) AS store
    , channel AS channel
    , vendor AS vendor
    , subchannel AS subchannel
    , media_type AS media_type
    , SUM(total_spend) AS total_spend
    , SUM(total_clicks) AS total_clicks
    , SUM(total_impressions) AS total_impressions
    , SUM(vendor_reported_orders) AS vendor_reported_orders
    , SUM(vendor_reported_revenue) AS vendor_reported_revenue
    , attribution_window
  FROM ums.master_spend
  CROSS JOIN largest_store ls
  GROUP BY
      activity_date
    , COALESCE(store, ls.__shop_id)
    , channel
    , vendor
    , subchannel
    , media_type
    , attribution_window

Then, first- and last-click data is rolled up to the same level of aggregation. Here we see first-click metrics:


  SELECT
      os.order_date::DATE AS order_date
    , olr.__shop_id AS store
    , oc.ga_fc_channel AS ga_fc_channel
    , NVL(oc.ga_fc_vendor, 'none') AS ga_fc_vendor
    , NVL(oc.ga_fc_sub_channel, 'none') AS ga_fc_subchannel
    , NVL(oc.ga_fc_media_type, 'none') AS ga_fc_media_type
    , COUNT(DISTINCT oc.order_id) AS num_orders
    , COUNT(DISTINCT CASE WHEN os.first_order_flag = 1 THEN oc.order_id ELSE NULL END) AS num_new_orders
    , SUM(olr.gross_sales) AS gross_sales
    , SUM(olr.net_sales) AS net_sales
    , SUM(olr.gross_margin) AS gross_margin
    , SUM(olr.net_margin) AS net_margin
    , SUM(olr.sku_cost) AS sku_cost
    , SUM(olr.tax_amount) AS tax_amount
    , SUM(olr.discount_amount) AS discount_amount
    , SUM(olr.refund_amount) AS refund_amount
    , SUM(olr.gross_amount_charged) AS gross_amount_charged
    , SUM(olr.potential_sales) AS potential_sales
  FROM drp.order_line_revenue olr
  INNER JOIN drp.order_status os ON olr.order_id = os.order_id
  INNER JOIN drp.order_channel oc ON oc.order_id = olr.order_id
  WHERE os.valid_order_flag_daasity = TRUE
  GROUP BY
      os.order_date::DATE
    , olr.__shop_id
    , oc.ga_fc_channel
    , NVL(oc.ga_fc_vendor, 'none')
    , NVL(oc.ga_fc_sub_channel, 'none')
    , NVL(oc.ga_fc_media_type, 'none')
  ORDER BY
      os.order_date::DATE
    , oc.ga_fc_channel

First- and last-click data are then used to populate separate fields and are output in the final form for reporting against master spend:


SELECT
MD5(COALESCE(activity_date::DATE,'2099-12-31')||':'||UPPER(COALESCE(store,'')||':'||COALESCE(UPPER(channel),'NONE'))||':'||COALESCE(UPPER(vendor),'NONE')||':'||COALESCE(UPPER(subchannel),'NONE')||':'||COALESCE(UPPER(media_type),'NONE')||':'||COALESCE(attribution_window,'NONE')) AS id
  , activity_date
  , COALESCE(im.__shop_id, tr.store) AS store
  , channel
  , vendor
  , subchannel
  , media_type
  , SUM(total_spend) AS total_spend
  , SUM(total_clicks) AS total_clicks
  , SUM(total_impressions) AS total_impressions
  , SUM(vendor_reported_orders) AS vendor_reported_orders
  , SUM(vendor_reported_revenue) AS vendor_reported_revenue
  , SUM(last_click_orders) AS last_click_orders
  , SUM(last_click_new_orders) AS last_click_new_orders
  , SUM(last_click_gross_sales) AS last_click_gross_sales
  , SUM(last_click_net_sales) AS last_click_net_sales
  , SUM(last_click_gross_margin) AS last_click_gross_margin
  , SUM(last_click_net_margin) AS last_click_net_margin
  , SUM(last_click_sku_cost) AS last_click_sku_cost
  , SUM(last_click_tax_amount) AS last_click_tax_amount
  , SUM(last_click_discount_amount) AS last_click_discount_amount
  , SUM(last_click_refund_amount) AS last_click_refund_amount
  , SUM(last_click_gross_amount_charged) AS last_click_gross_amount_charged
  , SUM(last_click_potential_sales) AS last_click_potential_sales
  , SUM(first_click_orders) AS first_click_orders
  , SUM(first_click_new_orders) AS first_click_new_orders
  , SUM(first_click_gross_sales) AS first_click_gross_sales
  , SUM(first_click_net_sales) AS first_click_net_sales
  , SUM(first_click_gross_margin) AS first_click_gross_margin
  , SUM(first_click_net_margin) AS first_click_net_margin
  , SUM(first_click_sku_cost) AS first_click_sku_cost
  , SUM(first_click_tax_amount) AS first_click_tax_amount
  , SUM(first_click_discount_amount) AS first_click_discount_amount
  , SUM(first_click_refund_amount) AS first_click_refund_amount
  , SUM(first_click_gross_amount_charged) AS first_click_gross_amount_charged
  , SUM(first_click_potential_sales) AS first_click_potential_sales
  , attribution_window
  , a.currency
  , tr.__loaded_at
  -- , MAX(__synced_at) AS __synced_at
FROM total_rollup tr
LEFT JOIN drp.integration_mapping im ON tr.store = im.__shop_id
CROSS JOIN platform.accounts a
WHERE MD5(COALESCE(activity_date::DATE,'2099-12-31') || ':' || COALESCE(store,'') || ':' || COALESCE(channel,'NONE') || ':' || COALESCE(vendor,'NONE') || ':' || COALESCE(attribution_window,'NONE')) IS NOT NULL
AND ((total_spend IS NOT NULL AND total_spend > 0)
OR (last_click_orders IS NOT NULL AND last_click_orders > 0)
OR (first_click_orders IS NOT NULL AND first_click_orders > 0)
OR (vendor_reported_orders IS NOT NULL AND vendor_reported_orders > 0))
GROUP BY
    MD5(COALESCE(activity_date::DATE,'2099-12-31')||':'||UPPER(COALESCE(store,'')||':'||COALESCE(UPPER(channel),'NONE'))||':'||COALESCE(UPPER(vendor),'NONE')||':'||COALESCE(UPPER(subchannel),'NONE')||':'||COALESCE(UPPER(media_type),'NONE')||':'||COALESCE(attribution_window,'NONE'))
  , activity_date
  , COALESCE(im.__shop_id, tr.store)
  , channel
  , vendor
  , subchannel
  , media_type
  , attribution_window
  , a.currency
  , tr.__loaded_at

Note: Performance_id fields in both the master spend and marketing performance tables is a hashed concatenation of date, store, channel, vendor, subchannel, media type, and attribution window. Any deviation in these fields between master spend and marketing performance will cause issues, including discrepancies in formatting or capitalization of these fields.

What you’ll see in Looker

With the master spend and marketing performance tables populated and properly configured, they can then be JOINed along with the calendar in the Looker marketing model. Metrics such as ROAS, CPO, and CPA are then calculated in Looker using these fields.

Here are a few different visualizations we build in Looker that help us answer some of the questions we posed at the beginning of this article. 

Visualization 1: Summary Metrics

At the top of the dashboard, we find WTD and MTD metrics for an at-a-glance view of marketing efforts and measures of their effectiveness. 

In this section, total spend is pulled directly from the marketing integrations, followed by acquisition cost blended across the merchant’s different marketing channels, followed by ROAS and CPO using vendor-reported metrics, and finally ROAS split out by both Facebook and Google Ads.

Visualization 2: ROAS by Vendor Over Time

This view of return on ad spend (ROAS) identifies potential trends in a brand’s marketing effectiveness, split out by channel.

Potential explanations for a downward trend like the one displayed above could be increased marketing spend in less productive channels, consistent spend with a decrease in orders attributed to the displayed channels, or decreasing spend with a still greater decrease in attributed revenue than the accompanying spend decrease.

Visualization 3: Vendor-Reported ROAS vs. First-Click and Last-Click ROAS

This graph shows three different ways of looking at the same data, which in this case is the merchant’s ROAS. 

ROAS can be read as the average return in net sales on one dollar of marketing spend, so if ROAS = 1 then every dollar spent on paid advertising results in one dollar of net sales.

Vendor-reported metrics come straight from the source integration and usually rely on an attribution window. For example, Facebook might count an order as a Facebook order if the person placing the order has seen a Facebook ad for your brand in the last day or clicked on a Facebook ad for your brand in the last 7 days. Last-click and first-click metrics come from the channel mapping you have set up in your Brand-Supplied Data sheets. So, an order with a first- or last-click source of "facebook" might be counted as a Facebook order.

Text Link

May interest you

No items found.

Stay Data-Driven.

Get tactical insights delivered to your inbox.

< src="https://unpkg.com/@lottiefiles/lottie-player@latest/dist/lottie-player.js" defer>