{"queries":[{"id":"segments","compiledQueryString":"select segment, sum(monetary) as rev from retail.customers group by 1 order by rev desc","inputQueryString":"select segment, sum(monetary) as rev from retail.customers group by 1 order by rev desc","compiled":false,"inline":true},{"id":"months","compiledQueryString":"select distinct order_month from retail.sales_month order by 1","inputQueryString":"select distinct order_month from retail.sales_month order by 1","compiled":false,"inline":true},{"id":"countries","compiledQueryString":"select country, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","inputQueryString":"select country, sum(net_revenue) as rev from retail.sales_month group by 1 order by rev desc","compiled":false,"inline":true},{"id":"f_cust","compiledQueryString":"select * from retail.customers\nwhere segment like '${inputs.segment.value}'","inputQueryString":"select * from retail.customers\nwhere segment like '${inputs.segment.value}'","compiled":false,"inline":true},{"id":"kpi_cust","compiledQueryString":"select count(*) as customers,\n sum(case when frequency >= 2 then 1 else 0 end) / count(*) as repeat_rate,\n sum(monetary) / count(*) as rev_per_cust\nfrom (select * from retail.customers\nwhere segment like '${inputs.segment.value}')","inputQueryString":"select count(*) as customers,\n sum(case when frequency >= 2 then 1 else 0 end) / count(*) as repeat_rate,\n sum(monetary) / count(*) as rev_per_cust\nfrom ${f_cust}","compiled":true,"inline":true},{"id":"kpi_aov","compiledQueryString":"-- the DateRange input reports local midnight as a UTC date, i.e. one day early east of UTC; the + n undoes that\nselect sum(net_revenue) / nullif(count(distinct order_id), 0) as aov\nfrom retail.orders_inv\nwhere order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'","inputQueryString":"-- the DateRange input reports local midnight as a UTC date, i.e. one day early east of UTC; the + n undoes that\nselect sum(net_revenue) / nullif(count(distinct order_id), 0) as aov\nfrom retail.orders_inv\nwhere order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'","compiled":false,"inline":true},{"id":"rfm","compiledQueryString":"select segment, count(*) as \"Customers\", sum(monetary) as \"Revenue\"\nfrom (select * from retail.customers\nwhere segment like '${inputs.segment.value}')\ngroup by 1 order by \"Revenue\" desc","inputQueryString":"select segment, count(*) as \"Customers\", sum(monetary) as \"Revenue\"\nfrom ${f_cust}\ngroup by 1 order by \"Revenue\" desc","compiled":true,"inline":true},{"id":"buyer","compiledQueryString":"select case when customer_type = 'Registered' then 'Account' else 'Guest' end as name,\n sum(net_revenue) as value\nfrom retail.sales_month\nwhere order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\ngroup by 1 order by 2 desc","inputQueryString":"select case when customer_type = 'Registered' then 'Account' else 'Guest' end as name,\n sum(net_revenue) as value\nfrom retail.sales_month\nwhere order_month between date_trunc('month', '${inputs.date_range.start}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0})\n and '${inputs.date_range.end}'::date + ${new Date().getTimezoneOffset() < 0 ? 1 : 0}\n and country like '${inputs.country.value}'\ngroup by 1 order by 2 desc","compiled":false,"inline":true},{"id":"value_dist","compiledQueryString":"select case when monetary < 500 then 1 when monetary < 1000 then 2 when monetary < 2500 then 3\n when monetary < 5000 then 4 when monetary < 10000 then 5 else 6 end as band_order,\n case when monetary < 500 then '<£500' when monetary < 1000 then '£500-1k' when monetary < 2500 then '£1k-2.5k'\n when monetary < 5000 then '£2.5k-5k' when monetary < 10000 then '£5k-10k' else '£10k+' end as spend_band,\n count(*) as customers\nfrom (select * from retail.customers\nwhere segment like '${inputs.segment.value}')\ngroup by 1, 2 order by 1","inputQueryString":"select case when monetary < 500 then 1 when monetary < 1000 then 2 when monetary < 2500 then 3\n when monetary < 5000 then 4 when monetary < 10000 then 5 else 6 end as band_order,\n case when monetary < 500 then '<£500' when monetary < 1000 then '£500-1k' when monetary < 2500 then '£1k-2.5k'\n when monetary < 5000 then '£2.5k-5k' when monetary < 10000 then '£5k-10k' else '£10k+' end as spend_band,\n count(*) as customers\nfrom ${f_cust}\ngroup by 1, 2 order by 1","compiled":true,"inline":true},{"id":"top_accounts","compiledQueryString":"select customer_id, frequency, monetary, last_order, segment\nfrom (select * from retail.customers\nwhere segment like '${inputs.segment.value}')\norder by monetary desc\nlimit 15","inputQueryString":"select customer_id, frequency, monetary, last_order, segment\nfrom ${f_cust}\norder by monetary desc\nlimit 15","compiled":true,"inline":true}]}

Customer & Loyalty

No Results