Hints
We recommend that you try to complete as much of the exercise on your own after each step
Last updated
We recommend that you try to complete as much of the exercise on your own after each step
Last updated
The first step is to input the data and summarize it by Year and Month. We do by first changing the date columns to a Datetime format in the Change Columns. Then, because they're now Datetime, we use a Date Format to create two new columns for each input - Monthname and Year. Finally, we use a Group By to group our data by the newly created monthname and year columns while summarizing the metrics we're interesting in and counting the orders from Shopify.
The second step is about consolidating our three datasets into a single dataset. We use two Combine tools to do that. We choose left joins every time and our ID's are the respective Monthname and Year columns. The Combine is essentially looking in each dataset to find mathing ID and when it finds a pairs (e.g. monthname =April, year = 2022), add combines the columns into a single row.
The Combine tools is known as a vlookup or index/match in Excel, as a Join in SQL and as a Merge in Pandas
We also calculate the average order size with a Calculate tool on the Shopify data before combining.
The next step of our analysis is about Cost-Per-Click (CPC) and Click-Through-Rate (CTR) for our two marketing sources. We do that with Calculate tools and according to the definitions outlined in the introduction presentation
The next and final step is the most complex. This is where we implement our ROAS formula. We'll explain how to do the Google Ads calculation here.
The idea is to get an average of the last three months of Google Ads Spend. The idea is that we assume that our last three months spend has an effect in sales in this month. Below you can get a visual idea of what we're trying to achieve.
In the screenshot you'll see that we're doing a past 3 month rolling average of our Google Ads spend. So in the 4th row in the ga_3month_delay, we're getting an average spend of the last three months. You can see configuration of the first Running Interval tool on the left.
The final steps after the second Running Interval tool (tool ID 39) is to remove the first rows without any ROAS - we use a Filter to say that the month (not monthname, but month as a number (jan = 1, etc.)) - which you can create this all the way back in the Date Format - should be larger than 3 (i.e. after March). Finally we calculate our ROAS by dividing our sum_order_amount with ga_3month_delay + fb_1month.
