I’m building a dashboard and need to display the following information:
- The number and list of publications
- Total Pageviews & Average Pageviews
- The percentage of publications that meet the 400-pageviews threshold
I’ve created the table below that lists the data, and I want to display the percentage of publications that meet the 400-pageviews threshold in a scoreboard. However, my issue is that the pageviews are summed by URL, so when I created the scoreboard, it’s checking if the pageviews are higher than 400, row by row.
Is there a way to create a calculated field that groups by canonical URL? Or is there an option to add a dimension in the scoreboard?
If anyone has suggestions on how I can solve this or display the metric correctly, I would really appreciate your help!
Thank you in advance!
Hi,
Here’s how we can achieve this using your bullet list as a guide:
- Number and List of Publications
To count the number of publications, we’ll use the following calculated field, which counts the number of URLs in the data source (each representing a publication):
COUNT(Canonical URL)
- Total Pageviews & Average Pageviews
For Total Pageviews, create a calculated field:
SUM(Pageviews)
For Average Pageviews, use:
AVG(Pageviews)
- Percentage of Publications Meeting the 400-Pageviews Threshold
We’ll use this calculated field:
(SUM(CASE
WHEN Pageviews >= 400 THEN 1
ELSE 0
END)) / Record Count
Explanation: Each publication is treated as a record. If a publication has 400 or more pageviews, it gets assigned a 1. Then, we simply calculate the percentage of such records relative to the total.
For reference and the expected output, check the link below:
https://lookerstudio.google.com/reporting/846232b7-6f1e-45a3-93a6-e90bbd59c2f6/page/ZY6DF
