Skip to content

Automate Business Metric Reporting with DataCamp Workspace

# Install necessary packages
!pip install slack_sdk
from slack_sdk import WebClient
import os
from datetime import datetime
print(datetime.now())

Total sales by month

Spinner
DataFrameas
sales_over_time
variable
SELECT 
	DATEFROMPARTS(DATEPART(year, o.order_date), DATEPART(month, o.order_date), 1) AS month,
	SUM(quantity * list_price * (1-discount)) AS total_sales
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi ON o.order_id = oi.order_id
WHERE o.order_date < '2018-05-01'
GROUP BY DATEFROMPARTS(DATEPART(year, o.order_date), DATEPART(month, o.order_date), 1)
ORDER BY month
import plotly.express as px

fig = px.line(sales_over_time, x='month', y='total_sales', title='Total Sales Over Time')
fig.update_xaxes(title='Month')
fig.update_yaxes(title='Total Sales')

# Show the figure
fig.show()

Surface top sellers

Spinner
DataFrameas
top_sellers
variable
SELECT TOP 3
	CONCAT(first_name, ' ', last_name) AS full_name,
	SUM(quantity * list_price * (1-discount)) AS sales_per_staff
FROM sales.orders AS o
INNER JOIN sales.order_items AS oi ON o.order_id = oi.order_id
INNER JOIN sales.staffs AS s ON o.staff_id = s.staff_id
WHERE
	DATEPART(year, o.order_date) = 2018
	AND DATEPART(month, o.order_date) = 4
GROUP BY CONCAT(first_name, ' ', last_name)
ORDER BY sales_per_staff DESC;

Report metrics to Slack

sales_over_time[sales_over_time['month'] == sales_over_time['month'].max()]['total_sales'].values[0]
from babel.numbers import format_currency

total_sales = sales_over_time[sales_over_time['month'] == sales_over_time['month'].max()]['total_sales'].values[0]
sellers_dict = top_sellers.to_dict(orient='records')

sellers_list = top_sellers \
    .apply(lambda x: f"• {x['full_name']} ({format_currency(x['sales_per_staff'], 'USD')})", axis="columns")
sellers_overview = "\n".join(sellers_list)

text = f"""The past month we did {format_currency(total_sales, 'USD')} in sales!

Our sales all stars:
{sellers_overview}

_<https://app.datacamp.com/workspace/w/08e4a6f2-0581-46b5-b3da-b1a05f7f642c/edit|Link to workspace>_
"""

##channel = "sales-tracking"
## if os.environ.get('DC_SCHEDULED_RUN') == 'TRUE':
   ## client = WebClient(token=os.environ["SLACK_OAUTH_TOKEN"])
    ##client.chat_postMessage(channel=channel, text=text)
   ## client.files_upload(channels=channel, file=sales_chart_filename)
else:
    print(text)