Need to create conditional stacked bar chart in Google Data Studio

Question

I have SEO data and need to create a Stacked Bar chart with it. I have Landing Pages (URLs) and I need to sort them into 4 categories.

1. Categorize Landing pages into Top 1-3 Landing pages by clicks; same for 4-10, 11-20, 21-100.
2. Then I need to create Stacked Bar chart with this view. Sample chart attached below:

Need Month on X axis, Total view count on X axis.

I have generated Random Data here to create sample chart. hopefully it will be enough.

Answer 1

For a categorization of the grouped items, there has to be some preprocessing. Data Studio cannot do it.

Possible approaches can be done in BigQuery, Sheets (pivot). A route in Data Studio would be to do it by the PERCENTILE in combination with a self blend, obtaining the rank (e.g. first 10% of clicks) instead of the top visited sites.

Since you added the tag "custom-visuals", here a way to do it by the VEGA LITE plugin.

First add the Community visualization "Vega/Vega Lite" to your report.

Add as dimension the date and the Landing page, and the metrics are the sum of the clicks.

The add following Vega Lite code in the style tab:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

  "data": 
    {
      "values": [
{"$dimension0": "Jan 18, 2022", "$dimension1": "abs", "$metric0": 3},
{"$dimension0": "Jan 19, 2022", "$dimension1": "jsofj", "$metric0": 1},
{"$dimension0": "Jan 20, 2022", "$dimension1": "hfkh", "$metric0": 5},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 7},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 2},
{"$dimension0": "Jan 18, 2022", "$dimension1": "vnwso", "$metric0": 5},
{"$dimension0": "Jan 24, 2022", "$dimension1": "ojjvl", "$metric0": 6},
{"$dimension0": "Jan 31, 2022", "$dimension1": "vgoe", "$metric0": 8},
{"$dimension0": "Jan 26, 2022", "$dimension1": "abs", "$metric0": 1},
{"$dimension0": "Jan 27, 2022", "$dimension1": "jsofj", "$metric0": 6},
{"$dimension0": "Jan 18, 2022", "$dimension1": "hfkh", "$metric0": 9},
{"$dimension0": "Jan 19, 2022", "$dimension1": "iefi", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "vnwso", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "ojjvl", "$metric0": 5},
{"$dimension0": "Feb 2, 2022", "$dimension1": "olwoe", "$metric0": 4},
{"$dimension0": "Feb 3, 2022", "$dimension1": "vgoe", "$metric0": 7},
{"$dimension0": "Feb 4, 2022", "$dimension1": "abs", "$metric0": 9},
{"$dimension0": "Feb 5, 2022", "$dimension1": "jsofj", "$metric0": 3},
{"$dimension0": "Jan 21, 2022", "$dimension1": "hfkh", "$metric0": 1},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 2},
{"$dimension0": "Feb 8, 2022", "$dimension1": "eil", "$metric0": 5},
{"$dimension0": "Jan 20, 2022", "$dimension1": "vnwso", "$metric0": 8},
{"$dimension0": "Feb 10, 2022", "$dimension1": "ojjvl", "$metric0": 15},
{"$dimension0": "Jan 18, 2022", "$dimension1": "olwoe", "$metric0": 11},
{"$dimension0": "Feb 12, 2022", "$dimension1": "vgoe", "$metric0": 16},
{"$dimension0": "Jan 31, 2022", "$dimension1": "abs", "$metric0": 12},
{"$dimension0": "Jan 19, 2022", "$dimension1": "jsofj", "$metric0": 13},
{"$dimension0": "Feb 15, 2022", "$dimension1": "hfkh", "$metric0": 14},
{"$dimension0": "Feb 16, 2022", "$dimension1": "iefi", "$metric0": 10},
{"$dimension0": "Feb 17, 2022", "$dimension1": "eil", "$metric0": 5},
{"$dimension0": "Jan 31, 2022", "$dimension1": "jsofj", "$metric0": 8},
{"$dimension0": "Jan 18, 2022", "$dimension1": "hfkh", "$metric0": 7},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 6},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 3},
{"$dimension0": "Jan 19, 2022", "$dimension1": "shf", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "jildo", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "kahif", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "ikhqfl", "$metric0": 5},
{"$dimension0": "Feb 2, 2022", "$dimension1": "hqifkql", "$metric0": 4},
{"$dimension0": "Jan 19, 2022", "$dimension1": "guwk", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "qfhuw", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "ihqo3", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "wfoj", "$metric0": 5}
      ]
    },
       "transform": [
 {
"joinaggregate": [{"op":"sum","field":"$metric0","as":"counts"}],"groupby": ["$dimension1"]
},
{
"sort":[{"field":"counts","order": "descending"},{"field":"$dimension1","order": "descending"}],
"window":[{"op": "row_number","as": "rownum"},{
      "op": "lead","field":"$dimension1","as": "last"}]
},
{
"calculate": " datum.last == datum.$dimension1 ? 0 : 1 ", "as": "adding"
},
{
"sort":[{"field":"rownum","order": "ascending"}],
"window":[{"op":"sum","field":"adding","as": "TOPs"}]
},

{
"calculate": " datum.TOPs<3 ? 'Top 1-3' : datum.TOPs<10 ? 'Top 4-10' : datum.TOPs<20 ? 'Top 11-20'  : 'Top 21-100'  ", "as": "TOPid"
} 

]

,
"mark": {"type": "bar"},
"encoding": {
"x": {
"field": "$dimension0",
"type": "ordinal",
"title": "$dimension0.name"},
"y": {
"field": "$metric0",
"type": "quantitative",
"axis": {"title": "$metric0.name"},
"aggregate": "sum"
},
"color":{
"field":  "TOPid",
"scale": {
"domain": ["Top 1-3", "Top 4-10", "Top 11-20", "Top 20-100"],
"range": ["blue", "lightblue", "#00a", "yellow"]
 }
} ,
"order": { "field": "TOPs"}
}
}

it can be also viewed and developed under: https://vega.github.io/editor/#/edited

In case you need the top groups by date and not globally, please use this version

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",

  "data": 
    {
      "values": [
{"$dimension0": "Jan 18, 2022", "$dimension1": "abs", "$metric0": 3},
{"$dimension0": "Jan 19, 2022", "$dimension1": "jsofj", "$metric0": 1},
{"$dimension0": "Jan 20, 2022", "$dimension1": "hfkh", "$metric0": 5},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 7},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 2},
{"$dimension0": "Jan 18, 2022", "$dimension1": "vnwso", "$metric0": 5},
{"$dimension0": "Jan 24, 2022", "$dimension1": "ojjvl", "$metric0": 6},
{"$dimension0": "Jan 31, 2022", "$dimension1": "vgoe", "$metric0": 8},
{"$dimension0": "Jan 26, 2022", "$dimension1": "abs", "$metric0": 1},
{"$dimension0": "Jan 27, 2022", "$dimension1": "jsofj", "$metric0": 6},
{"$dimension0": "Jan 18, 2022", "$dimension1": "hfkh", "$metric0": 9},
{"$dimension0": "Jan 19, 2022", "$dimension1": "iefi", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "vnwso", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "ojjvl", "$metric0": 5},
{"$dimension0": "Feb 2, 2022", "$dimension1": "olwoe", "$metric0": 4},
{"$dimension0": "Feb 3, 2022", "$dimension1": "vgoe", "$metric0": 7},
{"$dimension0": "Feb 4, 2022", "$dimension1": "abs", "$metric0": 9},
{"$dimension0": "Feb 5, 2022", "$dimension1": "jsofj", "$metric0": 3},
{"$dimension0": "Jan 21, 2022", "$dimension1": "hfkh", "$metric0": 1},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 2},
{"$dimension0": "Feb 8, 2022", "$dimension1": "eil", "$metric0": 5},
{"$dimension0": "Jan 20, 2022", "$dimension1": "vnwso", "$metric0": 8},
{"$dimension0": "Feb 10, 2022", "$dimension1": "ojjvl", "$metric0": 15},
{"$dimension0": "Jan 18, 2022", "$dimension1": "olwoe", "$metric0": 11},
{"$dimension0": "Feb 12, 2022", "$dimension1": "vgoe", "$metric0": 16},
{"$dimension0": "Jan 31, 2022", "$dimension1": "abs", "$metric0": 12},
{"$dimension0": "Jan 19, 2022", "$dimension1": "jsofj", "$metric0": 13},
{"$dimension0": "Feb 15, 2022", "$dimension1": "hfkh", "$metric0": 14},
{"$dimension0": "Feb 16, 2022", "$dimension1": "iefi", "$metric0": 10},
{"$dimension0": "Feb 17, 2022", "$dimension1": "eil", "$metric0": 5},
{"$dimension0": "Jan 31, 2022", "$dimension1": "jsofj", "$metric0": 8},
{"$dimension0": "Jan 18, 2022", "$dimension1": "hfkh", "$metric0": 7},
{"$dimension0": "Jan 21, 2022", "$dimension1": "iefi", "$metric0": 6},
{"$dimension0": "Jan 21, 2022", "$dimension1": "eil", "$metric0": 3},
{"$dimension0": "Jan 19, 2022", "$dimension1": "shf", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "jildo", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "kahif", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "ikhqfl", "$metric0": 5},
{"$dimension0": "Feb 2, 2022", "$dimension1": "hqifkql", "$metric0": 4},
{"$dimension0": "Jan 19, 2022", "$dimension1": "guwk", "$metric0": 4},
{"$dimension0": "Jan 21, 2022", "$dimension1": "qfhuw", "$metric0": 3},
{"$dimension0": "Jan 31, 2022", "$dimension1": "ihqo3", "$metric0": 2},
{"$dimension0": "Feb 1, 2022", "$dimension1": "wfoj", "$metric0": 5}
      ]
    },
       "transform": [
 {
"joinaggregate": [{"op":"sum","field":"$metric0","as":"counts"}],"groupby": ["$dimension1","$dimension0"]
},
{
"sort":[{"field":"counts","order": "descending"},{"field":"$dimension1","order": "descending"}],
"window":[{"op": "row_number","as": "rownum"},{
      "op": "lead","field":"$dimension1","as": "last"}],
"groupby": ["$dimension0"]
},
{
"calculate": " datum.last == datum.$dimension1 || datum.rownum==1 ? 0 : 1 ", "as": "adding"
},
{
"sort":[{"field":"rownum","order": "ascending"}],
"window":[{"op":"sum","field":"adding","as": "TOPs"}],
"groupby": ["$dimension0"]
},

{
"calculate": " datum.TOPs<3 ? 'Top 1-3' : datum.TOPs<10 ? 'Top 4-10' : datum.TOPs<20 ? 'Top 11-20'  : 'Top 21-100'  ", "as": "TOPid"
} 

]

,
"mark": {"type": "bar"},
"encoding": {
"x": {
"field": "$dimension0",
"type": "ordinal",
"title": "$dimension0.name"},
"y": {
"field": "$metric0",
"type": "quantitative",
"axis": {"title": "$metric0.name"},
"aggregate": "sum"
},
"color":{
"field":  "TOPid",
"scale": {
"domain": ["Top 1-3", "Top 4-10", "Top 11-20", "Top 20-100"],
"range": ["blue", "lightblue", "#00a", "yellow"]
 }
} ,
"order": { "field": "TOPs"}
}
}

This is a Vega Lite code:

• "data" is for having some sample data
• "transform" does all the needed transformation
• "joinaggregate": obtains the sum of all clicks by "Landing page"
• next we need to generate a custom ranking TOPs for these values
• "sort" / "window" : The table is sorted from the most clicks to the fewest clisk. Then the "window" is looking for the "Landing page" to be changed to the previous row.
• "calculate" set a value of 1, if "Landing page" changed from previous row
• "sort" / "window": sums up these values. This is the custom ranking TOPs.
• "calculate" : split the custom ranking TOPs in the top groups (top 1-3, top 4-20, etc. and name it TOPid )
• "mark": Barplot
• "encoding" : sets the fields for the barplot
• "x", "y": definition of x and y axis
• "color": here the TOPid defines the stacked plot. For testing please use TOPs
• "scale": set the color of the stacked plot. For testing, please remove this.
• "order": set the order, TOPs is used, because the TOPid are string names and thus not have the desired order