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:
- Relevant Marketing Integrations
- Daasity’s Transforms and UMS
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:
- Google Ads
- Amazon Ads
- 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:
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:
The data inserted from each marketing integration into ums_staging.mktg_spend_source are then fed into ums.master_spend with this script:
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:
Then, first- and last-click data is rolled up to the same level of aggregation. Here we see first-click metrics:
First- and last-click data are then used to populate separate fields and are output in the final form for reporting against master spend:
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.
