Custom reports

Analytics weighted sort inside Looker Studio reports

January 10, 2024 - by Mehdi Oudjida
Analytics weighted sort with Looker Studio

Weighted sort was a nice feature from Google Universal Analytics table charts. Adjusting the sorting to inject the importance of the quantity represented by each row helped a lot, applied to rates.

If you sort by descending order a list of dimension values by conversion rate, you don’t want to see as the first row, one session with one conversion…100%…And it is the same for any rate, in any data context. Injecting a weight is a smart idea.

Inspirations and credits 🥷

Inspired by Avinash Kaushik and Dr Pete, Yehoshua Coren has written few years ago a nice article describing how implementing a weighted sort for bounce rate inside BigQuery and how to display it in a Looker Studio report: Weighted Sorting in Google Data Studio using BigQuery

I’m fortunate to work with Yehoshua (and all the amazing elites team around him…I’m lucky…), and so, It is my turn to be inspired by my peers, and the article of the analytics ninja, after discussions about some specific client needs.

I’ve transposed this already designed formula directly inside Looker Studio with some tricks. So it allows to get a weighted sort regardless of the data source! You will see, it is quite easy. Ready? Go!

The result

Ingredients of a table chart with a rate metric & its weighted sorting

For this custom solution, we will need the following elements:

  • Your data set from your data source with a dimension, a rate metric (ex: click rate) and the absolutes values of the rate dividend (ex: clicks) and the rate divider (ex: impressions)
  • Blended data tables with mentioned data to self cross join our data set, get access to all rows for each displayed rows, and apply aggregation functions on them
  • A table chart for the visualization
  • Some chart calculated fields for the metrics, including the weighted CTR for custom sorting

1. Data set

For my example, I decided to use Google Search Console (GSC) with Site data from Web property. I chose the Site CTR, the Impressions and the Clicks metrics by Query dimension. You can apply the same technic to any data source with calculated rates that need to be sorted beyond their value:

  • Add to cart rate by product
  • Interaction rate by landing page
  • Conversion rate by seller
  • Satisfaction rate by analytics vendor
  • Click rate by Ads
  • etc

All data preparations are directly made inside Looker Studio. As usual, it is not the best solution for performance purposes but it allows to implement it regardless of the data source and with less effort of data engineering.

2. Blended data

For the 2nd step, we configure blended data “GSC DB for weighted sort” as described below for our example:

  • Left table – GSC site from web property
    • Dimension: “Query”
    • Metrics: “Impressions”, “Clicks”, “Site CTR”
  • Right table – GSC site from web property
    • Dimension: “Query” renamed “query_for_aggreg”
    • Metrics:
      • “Impressions” renamed “impressions_for_aggreg”
      • “Site CTR” renamed “site_ctr_for_aggreg”
  • Join configuration
    • Operator: Cross join
    • No key
Looker Studio data blending for analytics weighted sort

3. Chart configuration

At last, let’s create a simple table chart with nice and tricky chart-level calculated metrics:

  • Char type: Table chart
  • Setup tab
    • Data source: Your blended data “GSC DB for weighted sort”
    • Dimensions:
      • “Query”
    • Metrics:
      • A chart calculated field named “Site CTR” corresponding to formula below with percent data type
      • A chart calculated field named “Impressions” corresponding to formula below
      • A chart calculated field named “Clicks” corresponding to formula below
      • A chart calculated field named “Weighted sort for CTR” corresponding to formula below with with percent data type.
        Remark: This calculation is the strict adaptation of the formula from Analytics ninja blog post, taking in account the Looker Studio constraints for aggregation. If you need more explanations, I encourage you to read this nice article (cf. my post intro) and the related resources
    • Sort by “Weighted sort for CTR” descending…of course…
    • Hide the summary row
    • Uncheck all chart interactions option
  • Style tab
    • Adapt the column styles (here bars with numbers for Site CTR and Clicks)
Site CTR
SUM(Site CTR)/COUNT_DISTINCT(query_for_aggreg)
Impressions
SUM(Impressions)/COUNT_DISTINCT(query_for_aggreg)
Clicks
SUM(Clicks)/COUNT_DISTINCT(query_for_aggreg)
Weighted sort for CTR
IF (SUM(Site CTR)/COUNT_DISTINCT(query_for_aggreg) > 0
,
(
(SUM(Impressions)/COUNT_DISTINCT(query_for_aggreg))
/
MAX(impressions_for_aggreg)
*
SUM(Site CTR)/COUNT_DISTINCT(query_for_aggreg)
)
+
(
(
1
-(
SUM(Impressions)/COUNT_DISTINCT(query_for_aggreg)
)
/
MAX(impressions_for_aggreg)
)
*
(AVG(site_ctr_for_aggreg)/COUNT_DISTINCT(Query))
)
,0)

4. Configuration review

At last, let’s review the full configuration with this small video.

Bulk data export

Google Search Console with native API was just an example. If you already import your Search Console data in BigQuery thanks to the bulk data export, the best solution will be to prepare weighted sort metric in BigQuery. Following the number of rows, crossing joining inside Looker Studio could be slow and, as usual, any solution outside of Looker Studio for data preparation is preferred.

Voilà, c’est fini.