In this exercise, you’ll how to work with paid marketing and orders data to generate a number of metrics to evaluate the effectiveness of your marketing campaigns.

You’ll learn tool such as Combine, Date Format, Group By and Calculate

There are hints to help you get through this exercise. You can also download our solution model and import it in your own model. The file are available at the end of this page.

​
Context

You’ll be pretending to be an analyst in the marketing team. You want to understand how your paid marketing performance in relation your orders.

The data you’ll be working with is mirroring real-life data from Shopify, Google Ads and Facebook Ads.

Our solution model is built with 33 tools.

​
Data

Create a new connection. Search for “exercise” and select the Data-Driven Ecommerce. Configure the connector and you should have access to the tables from the Canvas.

​
Task

The goals is to get the results shown below. It contains 9 rows - one for each month from Apr 2023 to Dec 2023 (inclusive).

You have to consolidate all three sources by month and then calculate the metrics. We use the following Actions extensively during this exercise: Date Format, Group By, Calculate, and Combine.

We suggest going about it in steps. First, make sure you have Shopify/Google Ads/Facebook Ads data by months. Then Combine all three data sources. Finally calculate your metrics.

​
Metric Definitions

We have four metrics in the end result. CTR, CPC, Average Order Size and ROAS. We’ll define each below:

  1. Click-Through-Rate (CTR): clicks divided by impressions. Measures proportion of individuals that see an online advertisement (impressions) and subsequently click on it.
  2. Cost-Per-Click (CPC): spend divided by clicks. Measures the cost of a click on an online advertisement.
  3. Average Order Size: sum of order amount divided by total number of orders.
  4. Return-On-Ad-Spent (ROAS): revenue generated divided by advertisement cost/spent. A ratio to measured the return (revenue) from the investment (ad spent). Note that we don’t do any advanced attribution.

​
Part 1

For the first part of the exercise the ambition is just to get to the results below. In other words, don’t focus on the ROAS metric.

​
Part 2 (Difficult)

We have added some extra complexity for those who want to go the extra mile. We assume our marketing spend has a delayed affect on revenue which differs by the channel (i.e. Facebook and Google). In other words, we spend money on Google Ads to get potential customer to know us and on Facebook to get them to purchase our product on Shopify.

Consequently, we imagine that our Google Ads spend have a 4 month delay on impacting our revenue (top-funnel) and Facebook have a 1 month delay (bottom-funnel). Our ROAS metric should take this into consideration. The formula looks like this:

ROASt=Amountt13(∑i=t−3t−1GoogleAdSpendi)+12(FacebookAdSpendt−1+FacebookAdSpendt)ROAS_t = \frac{Amount_t}{\frac{1}{3}(\sum_{i=t-3}^{t-1} GoogleAdSpend_i) + \frac{1}{2}(FacebookAdSpend_{t-1} + FacebookAdSpend_t)}

Where:

  • ROAS_t represents the Return on Advertising Spend at time t
  • Amount_t denotes the amount earned (i.e. Spotify revenue) at time t (current month)
  • GoogleAdSpend_i represents the Google Ad Spend at time i, where i ranges from t−3 to t−1 (last three months)
  • FacebookAdSpend_i represents the Facebook Ad Spend at time i, where i ranges from t−1 to t (last month and current month)

To exemplify, imagine that we’re in April. Then we want the average of our Google Ad spend from January, February and March while we want an average of our Facebook in March and April.

​
Solution File

Download the solution file here and see how to import it below. Remember to import it in a new model.