# Hints

### Hint #1

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.

<figure><img src="/files/6frjCffYqw4gXhomvcw6" alt=""><figcaption></figcaption></figure>

***

### Hint #2

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.&#x20;

{% hint style="info" %}
The Combine tools is known as a vlookup or index/match in Excel, as a Join in SQL and as a Merge in Pandas
{% endhint %}

We also calculate the average order size with a Calculate tool on the Shopify data before combining.

<figure><img src="/files/0OvZfxbYtMcFMSmVHRs9" alt=""><figcaption></figcaption></figure>

***

### Hint #3

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](https://app.gitbook.com/o/RcswY5TelREASsdLiDLX/s/aSJL6Od4KuV8hrdCZbXp/~/changes/38/exercises/data-driven-ecommerce#introduction-ecommerce)

<figure><img src="/files/KgRgRUrZHZuOGzlgQidI" alt=""><figcaption></figcaption></figure>

***

### Hint #4

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.&#x20;

<figure><img src="/files/5XZvcaOzwmkLIdHlAXPV" alt=""><figcaption></figcaption></figure>

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.

<figure><img src="/files/397XYNIaQFMhQWD01Ogw" alt=""><figcaption></figcaption></figure>

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.

###


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://resources.less.tech/less-tech/exercises/data-driven-ecommerce/hints.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.