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
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.dteReply