← All use cases
A headline in Slack, the workbook attached
Most people who depend on a report never open the file: they want last month’s revenue, the change, the top regions. With a message output, one run of the queries builds the workbook and a short headline, and the headline arrives in Slack with the workbook attached.
queries: [msg_headline, msg_regions]
output:
- name: workbook
format: xlsx
queries: [msg_regions]
destination: {profile: local, path: "out/messages/regions-{{ run.date.yyyymmdd }}.xlsx"}
- name: headline
format: message
title: "{{ var('company') }}: {{ period(var('period')).start.format('%B %Y') }}"
text: |
Net revenue: **{{ results.msg_headline.first.net | currency('EUR') }}** ({{ results.msg_headline.first.change | percent | signed }} on the month before)
Orders: {{ results.msg_headline.first.orders | number }}
Top regions:
{% for r in results.msg_regions.rows[:3] %}- {{ r.region_name }}: {{ r.net | currency('EUR') }}
{% endfor %}
destination:
- {profile: slack, attach: [workbook]}{% set p = period(var('period')) %}
{% set before = period('last_month', as_of=p.start.date) %}
select
sum(net_amount) filter (where order_date between '{{ p.start.date }}' and '{{ p.end.date }}') as net,
count(distinct order_id) filter (where order_date between '{{ p.start.date }}' and '{{ p.end.date }}') as orders,
sum(net_amount) filter (where order_date between '{{ p.start.date }}' and '{{ p.end.date }}')
/ nullif(sum(net_amount) filter (where order_date between '{{ before.start.date }}' and '{{ before.end.date }}'), 0) - 1 as change
from {{ ref('paid_order_facts') }}dre run msg_daily --preview # print the message, send nothing
dre run msg_daily