![]() ![]() It can be campaign name, country, hour – whatever. ![]() The ‘key’ does not have to be the ‘date’ (although it’s the most popular key.) It can any other field that exists in both tables.You need to define a ‘key’ – without it, Google Data Studio cannot understand how to do the match. ![]() In my examples above (excluding cross join), it’s easy to see that the key to match the tables was the ‘date.’ Let’s elaborate on it: Cross Join multiples it and generates 6 tables with all the values separated into different columns: In my example, we have 3 rows in Table1 and 2 rows in Table2. Cross JoinĬross join is a tricky one and I believe you will not use it on your marketing dashboards, but, since Google Data Studio supports it – let’s give it a shot.Ĭross Join, simply takes the number of rows in the first table, and multiply it by the number of rows in the second table. Yes! We got a perfect table with all the correct numbers in the right place. But if we don’t see a match, no matter from which table – who cares! Let’s put it anyway ? If we find a match, great, we can calculate the total cost. So, let me introduce you to the Full join.įull join takes all the rows from both tables. It’s easy to notice that none of the above works well for our goal, and the final “merged” table shows partial dates with partial costs. So in Data Studio, when you add data sources, you must set which is left and right.Ī quick fact: Until February 2022, “left join” was the only ‘join’ type Google Data Studio supported! Right JoinĪs you can guess, ‘right join’ is exactly like ‘left join’ but this time, it takes all the rows from the right table and merges only with the equivalent from the left table. Who cares about the side of the tables? Well, most BI platforms do care, and you should too. That’s the result we get:Īs you can see, the output table shows 100% of Facebook data, but only part of Google’s data.īefore we move on, You’ve probably noticed the left/right thing. “Left join” means that we take all the rows from the left table and “merge” the data only with the equivalent rows on the right table. What about the rest of the rows (with the red background)? Well, they stay out. The ‘key’ here is the date, and that’s the result we get:Īs you can see, only three days have ‘cost’ on both platforms hence, these are the days that we take into the total cost calculation. “Inner join” means that we “merge” only the rows with the same key on both tables. This technique is called “JOIN.” Inner Join We have four different ways to combine these tables into one in the data business. But now I wish to combine the tables to answer the simple question: What is the total daily ad spent, cross-platform. When fetching the data separately, the tables look just fine. On the 2nd, Only Facebook ads were active, and on the 7th, only Google. Since none of the platforms worked each day (just for the sake of the example), I get these results when fetching the data separately:Īs you can see, the dates are not aligned. I wish to fetch the data between 1 and 10th of January 2022. Let’s start with a simple example of two data sources – Facebook Ads and Google Ads. To understand how it works, you need to know something about blending data in general, not only using Google Data Studio. So now Google can take each day’s cost from Facebook Ads and the equivalent from Google Ads, and do the calculation, simply because it has the ‘key’ to match the records from both data sources. The only way to do it is to tell Google the following: Listen, take Facebook Ads cost and add it to Google Ads cost, and use the ‘date’ as a key. ![]() So, saying to Google: “Listen, take Facebook Ads cost and add it to Google Ads cost” won’t work. Google needs a rule, a guideline, to help it sum the correct values. Why? Because Google can’t identify the ‘key’ that connects these two metrics. Still, by default, Google Data Studio cannot SUM the values and show the SUM as one KPI. One component shows the total ‘cost’ from Facebook Ads, and another component shows the total ‘cost’ from Google Ads.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |