我正在尝试在数据工作室加入两个桌子。我的数据来源是Google Ads和Microsoft Ads。我想最终得到一张看起来像以下
每张表中的点击添加在一起以提供总数。
当我尝试加入这两个表时，我会得到一个看起来像这样的结果（此处的完整示例）：
| 广告系列 |
点击（表1） |
点击（表2） |
| 广告系列1 |
100 |
400 |
| 广告系列2 |
200 |
500 |
数据似乎由“竞选”加入，但“点击”并未合并为一个列，而是来自两个表的点击数据是独立的。
我已经尝试通过以下方式解决此问题：
- 在新混合的数据中创建计算出的字段（单击表1+点击表2），但是在尝试汇总其他指标时，这会产生奇怪的结果。
- 但是，使用“点击”加入，这无效，因为每个数据源的单击数量总是不同的。
- 更改加入类型这些似乎也没有起作用。
- 通过案例语句计算出的“广告系列”字段对广告系列进行分组，但这似乎不起作用 - 这通常会导致一次仅显示一组数据（可能最快的负载）。
这是我的混合方式的设置。您可以尝试使用。
加入这两个表并正确汇总的指标（如点击）的最佳方法是什么？
I'm attempting to join two tables in Data Studio. My data sources are Google Ads and Microsoft ads. I'd like to end up with a table that looks like the following example:
| Campaign |
Clicks |
| Campaign 1 |
500 |
| Campaign 2 |
700 |
The clicks from each table is added together to give a total.
When I attempt to join both tables, I get a result that looks like this (full example here):
| Campaign |
Clicks (Table 1) |
Clicks (Table 2) |
| Campaign 1 |
100 |
400 |
| Campaign 2 |
200 |
500 |
The data appears to be joined by 'campaign' but the 'clicks' are not being consolidated into one column, instead the clicks data from both tables are separate.
I've already attempted to solve this issue by:
- Creating calculated fields in the newly blended data (Clicks Table 1+ Clicks Table 2) but this yields strange results when trying to aggregate other metrics.
- Join using 'Clicks', however, this doesn't work as the number of clicks for each campaign is always likely to be different for each data source.
- Change the join type from 'Left outer' to right outer, inner, full outer and cross but none of these appear to work either.
- Grouping campaigns by a 'Campaign Group' calculated field using a CASE statement but this doesn't appear to work either- this generally results in only one set of data to show at a time (possibly whichever loads quickest).
Here's how my blend is setup. You can attempt to reproduce this issue using this page.
What is the best way to join both tables and have the metrics (like clicks) properly aggregated?
发布评论
评论（1）
可以使用单击（表1） 单击（表2）可以合并.com/dataSudio/answer/6299685“ rel =“ nofollow noreferrer”>计算的字段：
只要在 Blend ，对于任何给定的数据。
这是因为
1 + null = null（其中1用作表示数字的示例），因为null不是数字字面的（不是数字，因此不能可以计算）由于此混合物的值为null值，因此一种方法是使用代码> 函数（“ 如果输入为null，则返回结果，否则，返回下面的输入”），下面将null值视为数字文字（在这种情况下为0），以便可以计算值：
这将确保更改计算：
1 + null = null被1 + 0 = 1NULL + NULL = NULL = NULL = NULL = 1 + 0 = 1 + 0 = 1 + 0 null被0 + 0 = 0 = 0可编辑的Google Data Studio报告（嵌入 google表数据源）和gif进行详细说明：
The values in the two separate fields,
Clicks (Table 1)andClicks (Table 2)can be consolidated using the calculated field:This will work as long as there are no NULL values in either (or both) tables in the blend, for any given row of data.
This is because
1 + NULL = NULL(where1is used as an example to represent a number) as NULL is not a numeric literal (it's not a number, thus cannot be calculated)Since this blend has NULL values, one approach is to use the
IFNULLfunction ("returns a result if the input is null, otherwise, returns the input") below, which treats NULL values as a numeric literal (in this case, 0), so that the values can be calculated:This will ensure that calculations are changed:
1 + NULL = NULLis replaced by1 + 0 = 1NULL + NULL = NULLis replaced by0 + 0 = 0Editable Google Data Studio Report (Embedded Google Sheets Data Source) and a GIF to elaborate: