如何使用CASE语句在Google Data Studio中减去综合浏览量?

本文介绍了如何使用CASE语句在Google Data Studio中减去综合浏览量?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想从其他页面的Pageviews中减去特定页面的Pageviews，但是当我尝试将COUNT与CASE一起使用时，我得到了1:

I want to subtract Pageviews of a particular page from Pageviews of a different page, but when I try using COUNT with CASE, I get 1:

COUNT(CASE
    WHEN page = "www.link1.com" THEN 1 END)

这给了我一个错误的COUNT:

COUNT(CASE
    WHEN page = "www.link1.com" THEN 1
    ELSE 0 END)

我最终想要做的是:

COUNT(CASE
    WHEN page="www.link1.com" OR page = "www.link2.com" THEN 1
    ELSE 0 END) - COUNT(CASE
    WHEN page="www.link3.com" THEN 1
    ELSE 0 END)

我想要访问过link3的Users的COUNT，但是从link1和link2中不访问过.这些链接是渠道中的步骤. link1是渠道的第一步，但是link2和link3具有更多的Pageviews.我想显示有多少用户来自上一个渠道步骤(即link1)以外的来源.

I want the COUNT of Users who have visited link3 but NOT from link1 and link2. These links are steps in a funnel. link1 is the first step in the funnel but link2 and link3 have more Pageviews. I want to show how many users have come from sources other than the previous funnel step (i.e, link1).

摘要

可以通过RegEx公式(#2)或 CASE 语句(#3)，但是，由于Pageviews是汇总指标，因此计算字段" 会产生以下消息:

Summary

One way it can be achieved is by using either the RegEx Formula (#2) or the CASE Statement (#3), however, as Pageviews is an aggregated Metric, the Calculated Fields will produce the below message when created at the Data Source:

为将来参考，添加了一个图像:

For future reference, added an Image:

解决方案是先使用数据混合分解Pageviews字段(#1)，然后应用计算字段(#2或#3):

The solution is to first use Data Blending to disaggregate the Pageviews field (#1) and then apply the Calculated Field (#2 or #3):

数据源1

加入键1 :Date
加入键2 :Page
指标 :Pageviews

Join Key 1: Date
Join Key 2: Page
Metric: Pageviews

数据源2

加入键1 :Date
加入键2 :Page

Join Key 1: Date
Join Key 2: Page

要详细说明的图片:

An image to elaborate:

SUM(NARY_MAX(CAST(REGEXP_REPLACE(CONCAT(Page, ";", Pageviews), "(www\\.link1\\.com|www\\.link2\\.com);(.*)", "\\2") AS NUMBER ), 0 ) ) - SUM(NARY_MAX(CAST(REGEXP_REPLACE(CONCAT(Page, ";", Pageviews), "(www\\.link3\\.com);(.*)", "\\2") AS NUMBER ), 0 ) )

3)(替代计算字段)CASE语句

SUM(CASE
    WHEN Page IN ("www.link1.com", "www.link2.com") THEN Pageviews
    ELSE 0 END) - SUM(CASE
    WHEN Page IN ("www.link3.com") THEN Pageviews
    ELSE 0 END)

Google Data Studio报告和一个详细的GIF :

Google Data Studio Report and a GIF to elaborate:

这篇关于如何使用CASE语句在Google Data Studio中减去综合浏览量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

from