One of the key inquiries your customers may have is understanding how their performance stacks up against industry standards. This type of insight, known as benchmarking, provides valuable comparative analysis.
In Luzmo, benchmarking insights are effortlessly achieved through the utilization of parameterized SQL datasets.
Let's delve into an illustrative example: crafting a line chart where users can track their average monthly sales volume against the industry average.
To achieve this, we construct a parameterized SQL dataset with a metric column containing data specific to the client in question, and another column for benchmark metrics encompassing data from all clients.
The SQL query appears as follows:
select
b.row_id,
b.customer_id,
b.sale_date,
b.product,
s.volume as volume,
b.volume as benchmark_volume
from public.transactions b
left join (select * from public.transactions s where customer_id = {{metadata.customer_id|'2081'}}) s on b.row_id = s.row_id
In this example, rowid serves as a unique identifier for each data entry.
Additionally, the syntax {{metadata.customerid|'2081'}} implies that '2081' acts as the default value for the SQL dataset, which can be overridden through the embed token metadata property, as elaborated in the parameterized SQL datasets article.
With this dataset, we can construct insightful dashboards featuring line charts depicting the average volume and benchmark volume per month. Moreover, various gauge charts such as bullet charts, circular gauges, conditional colored numbers, or speedometers can be employed to visually compare customer data against benchmark data.