目录：
● 项目背景
● 分析目标
● 分析过程
一、项目背景
XX快运是XX旗下的集运平台，主营10kg以上产品快运。通过现有数据监控车辆承运与货主签收情况，并建立dashboard可视化展示，生成周报、月报。
二、分析目标
通过对常用指标分析，建立dashboard看板。
三、分析过程
1、SQL数据提取
先从数据库中提取指标相关数据，本文对下列6个指标的计算进行演示与练习。
（1）计算每月每周下单情况
（2）计算每周订单运单送达情况
（3）每月每周取件情况
（4）每周车辆使用情况
（5）各车型数量
（6）每月每周新增车辆情况
（1）计算每月每周下单情况
select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1
image.png
（2）计算每周订单运单送达情况
select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num,
sum(case when is_service='1' then 1 else 0 end) as service,
format(sum(case when is_service='1' then 1 else 0 end)/count(1),2) as service_prop,
sum(case when is_service='0' then 1 else 0 end) as no_service,
format(sum(case when is_service='0' then 1 else 0 end)/count(1),2)as no_service_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 ;
image.png
（3）每月每周取件情况
select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week ,
count(1) as order_num,
sum(case when is_pick_up='1' then 1 else 0 end) as pick,
format(sum(case when is_pick_up='1' then 1 else 0 end)/count(1),2) as pick_prop,
sum(case when is_pick_up='0' then 1 else 0 end) as no_pick,
format(sum(case when is_pick_up='0' then 1 else 0 end)/count(1),2)as no_pick_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 ;
image.png
（4）每周车辆使用情况
select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) car_num,
concat(format((count(distinct car_info) /(select count(1) from ds_car_info))*100,2),'%') as car_num_prop
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1
image.png
（5）各车型数量
select model,count(order_no) as use_car
from ds_order_info t1
left join ds_car_info t2
on t1.car_info= t2.license_plate
group by model
image.png
（6）每月每周新增车辆情况
select t1.month,t1.month_week,t1.car_num,t2.month_week,t2.car_num,(t1.car_num-t2.car_num) as 新增 from
(select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) as car_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1
) t1
left join
(select
substr(paytime,1,7) as month,
(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1 as month_week,
count(distinct car_info) as car_num
from ds_order_info
group by substr(paytime,1,7),(day(paytime)+WEEKDAY(paytime-interval day(paytime) day)) div 7 + 1
) t2
on t1.month=t2.month and t1.month_week -1 =t2.month_week
image.png
2、dashboard制作
将SQL的查询结果导入表格，或者用power bi连接数据库进行可视化。本文把结果导入Excel进行可视化。
（1）dashboard素材获取途径
image.png
（2）制作过程
本文从花瓣网寻找合适的模板，平时可以在素材网上多看多模仿。
image.png
a. 指标确认
为了匹配模板布局，指标生成如下（本次仅为练习，指标用随机数生产）。
模板.png
b.模板&字体&配色
字体&配色.png
image.png
c.看板背景准备
将单元格列宽调整至2.69，然后将模板的布局大致描绘出来。
image.png
然后将模板移走，在excel中调整好各模块布局，并把Excel背景颜色设置为91198（背景颜色可以在微信/qq截图时看到）。
image.png
设置网格线方便作图与布局，依次把刚才描好的形状填充成黑色。
image.png
依次将每个区域绘制成图表，并调整成目标格式。常见图表制作可参考：https://www.bilibili.com/video/BV15Z4y1L7Wh
image.png
看板最终呈现为：
image.png
