Join by Key

Problem:

Suppose we have a spreadsheet from Shopify sales and the information of the platform where the products were advertised. As well we have run an ad campaign on Facebook to boost our sales. We have an export report with the result of our campaign. Now we want to evaluate the effectiveness of the campaign against the associated cost and additionally calculate ROAS*.

*ROAS - Return On Ad Spend

Resolution:

We will be using the “Join by Key” building block to connect two different datasets together with a task to map common values (campaign names, ad platform and cost) to show the relevant campaign cost and the ad platform.

For calculating ROAS on top of that we will use the “Create Report” and “Add Metric” building blocks that would serve our purpose. In order to get there we follow the steps below.

Step 1

By using the “Pull Data” building blocks we feed Conduit with our data inputs from Shopify and Facebook Ads. Then we use the “Create Report” building block for both “Pull Data” building blocks to customize our output result. For the Shopify dataset we keep only “Ad Network” and “Ad campaign” columns and “Total Sales” as a metric. For the Facebook Ads dataset we keep only “Add network” and “Campaign” columns and “Cost” as a metric.

Step 2

Add this point we end up with the list of the campaigns, the ad platform and their associated cost. We proceed further by adding the “Join by Key” building block to tie those two customized datasets together. As a primary dataset we refer to our aggregated Shopify report that has been customized. As a secondary dataset we refer to our aggregated Facebook Ads report that has also been customized. Now we will see the table with the list of campaigns, how much we have earned by running one and what was the associated campaign cost. Since it is not our final result as we want to additionally calculate the ROAS we proceed further.

Step 3

Let’s go back to our canvas and add the “Create Report” building block with the “Add metric” building block. Name your “Add Metric” building block as ROAS. In the function field we enter Total Sales / Cost (Total Sales divided by Cost). Now let’s click on the result and we will see an additional column “ROAS” that shows us the ratio of how much money we have earned per a dollar spent.