Visual Analysis - Comparing KPI to a Benchmark

What methods do you use to effectively communicate "Here is the KPI versus a Benchmark"? One of my favorite is to use the chart series type to indicate the difference between a realized KPI and the theoretical goal. Here's an example:

To add this line, simply reference it in your select statement as an additional column. Then you can define the series settings on the right hand side of your chart editor to display benchmark as a line while the rest of your data is displayed in bar format.

select
value
, 9 as benchmark
from
table
3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Neha,

    I think this is great, but personally I would like to create a stack up bar chart to show the a nicer visual to my user, like the one below here, the blue bar highlight how much more would the team require to hit their benchmark for their KPI.  Just sharing my though :)

    Reply Like
    • Wai Pui Neha Kumar These remind me of the way we track these types of goals here at Periscope Data!

       

       

      You've got a goal line, a bar for month to date, and then you can see for previous months how far we'd come by this time of month, and how much farther those months got. Nicely gives you a quick glance at what you can expect for the rest of the month.

      Reply Like
  • Oh, goal tracking!

    I like this method for watching daily progress towards a monthly goal:

     

    with
      month as (
        select
          [dte:date] dte
        from
          [all_dates]
        where
          [dte:month] = [now():month]
      )
      , mqls_cte as (
        select
          dte
          , ([goal] / [(date_trunc('month', now()) + interval '1 month' - interval '1 day'):day_of_month]::float) * [dte:day_of_month]::float as target
          , count(distinct lead_id) MQLs
        from
          month
          join [leads] on
            leads.signup_at <= month.dte
        where
          [signup_at:month] = [now():month]
        group by
          1
      )
    
    select
      month.dte
      , mqls_cte.target
      , case when mqls_cte.dte <= [now():pst:date] then _cte.MQLs else null end as MQLs
    from
      month
      left join mqls_cte on
        month.dte = mqls_cte.dte 
    Reply Like
Like2 Follow
  • 2 Likes
  • 1 yr agoLast active
  • 3Replies
  • 1011Views
  • 5 Following