{"queries":[{"id":"asof","compiledQueryString":"select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete","inputQueryString":"select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete","compiled":false,"inline":true},{"id":"by_category","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n job_category as work,\n count(*) as jobs,\n sum(revenue_amount) as revenue,\n avg(revenue_amount) as avg_ticket,\n sum(gross_profit) / sum(revenue_amount) as gross_margin,\n sum(net_profit_after_overhead) as net_profit,\n sum(net_profit_after_overhead) / sum(revenue_amount) as net_margin,\n sum(gross_profit) / nullif(sum(labor_hours), 0) as gross_profit_per_hour\nfrom hvac.job_margin, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1\norder by revenue desc","inputQueryString":"with a as (select last_month from ${asof})\nselect\n job_category as work,\n count(*) as jobs,\n sum(revenue_amount) as revenue,\n avg(revenue_amount) as avg_ticket,\n sum(gross_profit) / sum(revenue_amount) as gross_margin,\n sum(net_profit_after_overhead) as net_profit,\n sum(net_profit_after_overhead) / sum(revenue_amount) as net_margin,\n sum(gross_profit) / nullif(sum(labor_hours), 0) as gross_profit_per_hour\nfrom hvac.job_margin, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1\norder by revenue desc","compiled":true,"inline":true},{"id":"cost_structure","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete)),\nt as (\n select\n sum(revenue_amount) as revenue,\n sum(material_cost) as materials,\n sum(on_site_labor_cost) as on_site,\n sum(drive_labor_cost) as drive,\n sum(allocated_overhead_amount) as overhead,\n sum(net_profit_after_overhead) as profit\n from hvac.job_margin, a\n where completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\n)\nselect 'Parts and equipment' as item, 100 * materials / revenue as per_100, 1 as ord from t\nunion all select 'Technician time on site', 100 * on_site / revenue, 2 from t\nunion all select 'Technician drive time', 100 * drive / revenue, 3 from t\nunion all select 'Overhead and shop time', 100 * overhead / revenue, 4 from t\nunion all select 'Profit', 100 * profit / revenue, 5 from t\norder by ord","inputQueryString":"with a as (select last_month from ${asof}),\nt as (\n select\n sum(revenue_amount) as revenue,\n sum(material_cost) as materials,\n sum(on_site_labor_cost) as on_site,\n sum(drive_labor_cost) as drive,\n sum(allocated_overhead_amount) as overhead,\n sum(net_profit_after_overhead) as profit\n from hvac.job_margin, a\n where completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\n)\nselect 'Parts and equipment' as item, 100 * materials / revenue as per_100, 1 as ord from t\nunion all select 'Technician time on site', 100 * on_site / revenue, 2 from t\nunion all select 'Technician drive time', 100 * drive / revenue, 3 from t\nunion all select 'Overhead and shop time', 100 * overhead / revenue, 4 from t\nunion all select 'Profit', 100 * profit / revenue, 5 from t\norder by ord","compiled":true,"inline":true},{"id":"profit_by_month","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n completed_month as month,\n sum(gross_profit) as gross_profit,\n sum(net_profit_after_overhead) as net_profit\nfrom hvac.job_margin, a\nwhere completed_month <= a.last_month\ngroup by 1\norder by 1","inputQueryString":"with a as (select last_month from ${asof})\nselect\n completed_month as month,\n sum(gross_profit) as gross_profit,\n sum(net_profit_after_overhead) as net_profit\nfrom hvac.job_margin, a\nwhere completed_month <= a.last_month\ngroup by 1\norder by 1","compiled":true,"inline":true},{"id":"overhead_stack","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n overhead_month as month,\n expense_account_name as expense,\n overhead_amount as amount\nfrom hvac.overhead_by_account, a\nwhere overhead_month <= a.last_month\norder by 1","inputQueryString":"with a as (select last_month from ${asof})\nselect\n overhead_month as month,\n expense_account_name as expense,\n overhead_amount as amount\nfrom hvac.overhead_by_account, a\nwhere overhead_month <= a.last_month\norder by 1","compiled":true,"inline":true},{"id":"ticket_buckets","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n ticket_size_bucket as size,\n job_category as work,\n count(*) as jobs,\n min(case ticket_size_bucket\n when 'Under $250' then 1 when '$250 - $999' then 2 when '$1,000 - $4,999' then 3 else 4 end) as ord\nfrom hvac.ticket_sizes, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1, 2\norder by ord","inputQueryString":"with a as (select last_month from ${asof})\nselect\n ticket_size_bucket as size,\n job_category as work,\n count(*) as jobs,\n min(case ticket_size_bucket\n when 'Under $250' then 1 when '$250 - $999' then 2 when '$1,000 - $4,999' then 3 else 4 end) as ord\nfrom hvac.ticket_sizes, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1, 2\norder by ord","compiled":true,"inline":true},{"id":"ticket_by_segment","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n job_category as work,\n client_segment as customer_type,\n avg(ticket_size_amount) as avg_ticket,\n count(*) as jobs\nfrom hvac.ticket_sizes, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1, 2\norder by 1, 2","inputQueryString":"with a as (select last_month from ${asof})\nselect\n job_category as work,\n client_segment as customer_type,\n avg(ticket_size_amount) as avg_ticket,\n count(*) as jobs\nfrom hvac.ticket_sizes, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\ngroup by 1, 2\norder by 1, 2","compiled":true,"inline":true},{"id":"worst_jobs","compiledQueryString":"with a as (select last_month from (select\n max(revenue_month) as last_month,\n strftime(max(revenue_month), '%B %Y') as label,\n strftime(max(revenue_month) - interval 11 month, '%B %Y') as start_label\nfrom hvac.revenue_targets\nwhere is_month_complete))\nselect\n cast(cast(job_number as bigint) as varchar) as job_number,\n completed_date,\n job_category as work,\n client_name as customer,\n lead_technician_name as technician,\n revenue_amount as revenue,\n total_direct_cost as direct_cost,\n allocated_overhead_amount as overhead,\n net_profit_after_overhead as net_profit,\n labor_hours as tech_hours\nfrom hvac.job_margin, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\n and revenue_amount > 0\norder by net_profit_after_overhead asc\nlimit 25","inputQueryString":"with a as (select last_month from ${asof})\nselect\n cast(cast(job_number as bigint) as varchar) as job_number,\n completed_date,\n job_category as work,\n client_name as customer,\n lead_technician_name as technician,\n revenue_amount as revenue,\n total_direct_cost as direct_cost,\n allocated_overhead_amount as overhead,\n net_profit_after_overhead as net_profit,\n labor_hours as tech_hours\nfrom hvac.job_margin, a\nwhere completed_month > a.last_month - interval 12 month and completed_month <= a.last_month\n and revenue_amount > 0\norder by net_profit_after_overhead asc\nlimit 25","compiled":true,"inline":true}]}

Jobs & Profit

Which work makes money?

Where each $100 of revenue goes

Profit by month

Overhead

How big are the tickets?

Jobs that lost money