CODEWITHSUNDEEP
google-analyticscaselooker-studio
Devarshi Goswami
Devarshi Goswami

Reputation: 1225

How to Subtract Pageviews in Google Data Studio using a CASE statement?

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)

This gives me a wrong COUNT:

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

What I ultimately want to do is:

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)

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).

Upvotes: 1

Views: 1870

Answers (1)

Nimantha
Nimantha

Reputation: 6471

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:

Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values). Please check the aggregation types of the fields used in this formula. Learn more.

For future reference, added an Image:

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

1) Data Blending

Data Source 1

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

Data Source 2

  • Join Key 1: Date
  • Join Key 2: Page

An image to elaborate:

2) RegEx Formula

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) (Alternative Calculated Field) CASE Statement

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 Report and a GIF to elaborate:

Upvotes: 2

Related Questions