Filter metric by Beast mode dimensions

Options
LenLaCara
LenLaCara Member
edited May 2023 in Beast Mode

I have a Beast mode that configures source and medium dimensions into broad categories because Google's default channel grouping dimension is incomplete.

(CASE
WHEN Session Medium LIKE 'cpc' THEN 'Search'
WHEN Session Medium LIKE 'paidsocial' THEN 'Social'
WHEN Session Source LIKE 'google' OR Session Source LIKE 'bing' OR Session Source LIKE 'yahoo' THEN 'Search'
WHEN Session Source LIKE 'facebook' OR Session Source LIKE 'facebook%' OR Session Source LIKE '%facebook' OR Session Source LIKE '%facebook%' OR Session Source LIKE 't.co' OR Session Source LIKE 'newsin.bio' OR Session Source LIKE 'linkin.bio' THEN 'Social'
WHEN Session Medium LIKE 'organic' THEN 'Search'
WHEN Session Medium LIKE 'social' THEN 'Social'
WHEN Session Medium LIKE 'PostUp' OR Session Medium LIKE 'email' OR Session Medium LIKE 'e-mail' OR Session Medium LIKE 'newsletter' THEN 'Newsletters'
ELSE 'All Others'
END)

I would like to create Beast modes based on these dimensions to create columns in a table:
Search pageviews
Social pageviews
Newsletter pageviews

Any thoughts on how to set those up, please?

FYI, I know I could do this in a pivot table, but the results are too small to be viewed when I schedule reports to email.

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @LenLaCara What values do you want in these columns? If you want to see a sum or count, you can set up a beast mode for each column like the ones below and add each one to the table. As a side note, you only need the LIKE operator when you are matching string patterns and using a % wildcard. If the values you are searching for match exactly, you can use either = or in ().

    Search Pageviews:

    sum(case 
  WHEN Session Source in ('google','bing','yahoo') THEN `PageViews`
  WHEN Session Medium in ('organic','cpc') THEN `PageViews`
  end)

    Social Pageviews:

    sum(case
  WHEN `Session Medium` = 'paidsocial' THEN `PageViews`
  WHEN `Session Source` LIKE '%facebook%' then `PageViews` 
  WHEN `Session Source` in ('t.co','newsin.bio','linkin.bio') THEN `PageViews`
  end)

    Newsletter Pageviews:

    sum(
  WHEN Session Medium in ('PostUp','email','e-mail','newsletter') THEN `PageViews`
  end)

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

    @LenLaCara What values do you want in these columns? If you want to see a sum or count, you can set up a beast mode for each column like the ones below and add each one to the table. As a side note, you only need the LIKE operator when you are matching string patterns and using a % wildcard. If the values you are searching for match exactly, you can use either = or in ().

    Search Pageviews:

    sum(case 
  WHEN Session Source in ('google','bing','yahoo') THEN `PageViews`
  WHEN Session Medium in ('organic','cpc') THEN `PageViews`
  end)

    Social Pageviews:

    sum(case
  WHEN `Session Medium` = 'paidsocial' THEN `PageViews`
  WHEN `Session Source` LIKE '%facebook%' then `PageViews` 
  WHEN `Session Source` in ('t.co','newsin.bio','linkin.bio') THEN `PageViews`
  end)

    Newsletter Pageviews:

    sum(
  WHEN Session Medium in ('PostUp','email','e-mail','newsletter') THEN `PageViews`
  end)

  • LenLaCara
    LenLaCara Member
    Options

    Perfect. Thank you for your quick response!

Categories