分析背景
偶然得到一份关于某连锁火锅品牌在2020年1月-8月的线上平台业务数据（如下图），心想正好利用这份数据，模拟实际业务中基于数据库与bi工具，实践开发可视化图表。一开始考虑用tableau，因为在大学跟刚工作的时候曾系统学习使用过，但想想近年来power bi好像更加流行，于是决定借这个案例上手使用power bi作可视化。
说干就干，第一步是观察字段，可以分成4类：
1. 时间维度（日期）、品牌（品牌ID、品牌名称）、区域维度（门店ID、门店名称
、城市）、平台维度（平台、平台i、平台店铺名称）；
2. 结果业务指标有 GMV、商家实收、有效订单、无效订单
3. 过程业务指标有门店曝光量、门店访问量、门店下单量、曝光人数、进店人数、下单人数；
4. 营销成本指标：cpc总费用、cpc曝光量、cpc访问量、商户补贴、平台补贴。
此外，基于上述字段，我们很快可以知道可以计算一些指标使得业务分析更完整，如客单价、单均价、实收率、各营销流程转化率、cpc单次费用、总营销成本（cpc费用+商户补贴+平台补贴）等。
数据检查&可视化思路
因为对数据字段有了的认识，我们可以考虑根据这些数据我们需要呈现什么信息出来。在实际工作中bi图表的目的是快速传递出信息给业务人员与决策者，往往会设计一些general点的图表以展示业务总体情况，并且会根据不同业务/职能需求开发一些更详细、专题的报表。在本例中，我们可先按照结果业务指标、过程业务指标、营销成本指标三个角度的业务需求来初步梳理可视化思路，如下：
注：这是初步思路，与最终呈现不是完全一致，如结果业务指标方面，考虑到信息过载的问题，最终并不未对每个度量的计算年累计、月趋势及环比都作呈现。
从数据库提取数据（MySQL）
在本地数据库创建一个表
为了模拟从数据库提取数据到bi的业务场景，我们需要先为数据建一个数据库表hotpot_stores,该表记录了企业的历史业绩数据。我们这里使用Python的读入数据后，再通过pymysql与mysql交互实现建表，还顺便练习一下Python中与sql的交互~
import pandas as pd
import pymysql
# 数据库连接参数
host="localhost"
port = 3306
user = 'root'
password = '********'
database = 'demo'
#文件路径
excel_file = '连锁火锅店业务数据.xlsx'
#读取Excel表，选中某个sheet
df = pd.read_excel(excel_file,sheet_name='拌客源数据1-8月')
# 连接到MySQL数据库
conn = pymysql.connect(host=host,user=user,passwd=password,db=database,charset='utf8')
cursor = conn.cursor()
#将每列数据格式替换到mysql格式
mysql_dtypes=df.dtypes.astype('str').map({'datetime64[ns]':'DATETIME','int64':'INT','object':'VARCHAR(255)','float64':'FLOAT'})
#建一个表
table_name = 'hotpot_stores'
#利用循环写建表sql语句
create_table_sql=f'CREATE TABLE {table_name} ('
for column,mysql_type in zip(df.columns,mysql_dtypes):
create_table_sql += f'{column} {mysql_type}, '
create_table_sql=create_table_sql[:-2] + ')'
#执行建表语句
cursor.execute(create_table_sql)
# 获取结果并检查是否包含hotpot_stores表
tables = [table[0] for table in cursor.fetchall()]
if 'hotpot_stores' in tables:
print('Table hotpot_stores exists.')
else:
print('Table hotpot_stores does not exist.')
#插入值
for index, row in df.iterrows():
insert_row_sql=f'INSERT INTO {table_name} VALUES ('
for value in row:
if isinstance(value,str):
value=value.replace('"','\\"')
#检查它是否为字符串类型。如果是，则使用replace方法将其中所有双引号替换为转义后的双引号
#（以避免在SQL语句中产生语法错误），然后将其添加到SQL语句中。否则，直接将值添加到SQL语句中。
insert_row_sql +=f'"{value}",'
elif isinstance(value, pd.Timestamp):
value = value.strftime('%Y-%m-%d %H:%M:%S')
insert_row_sql += f'"{value}", '
else:
insert_row_sql +=f'{value},'
insert_row_sql = insert_row_sql[:-1] + ')'
#执行每行赋值
cursor.execute(insert_row_sql)插入后我们查看一下行数和每列的情况：
select_sql='select * from hotpot_stores '
response=cursor.execute(select_sql)
response
#查看表的每列情况'DESCRIBE hotpot_stores'
cursor.execute('DESCRIBE hotpot_stores')
cursor.fetchall()
在power bi连接数据库
确认数据库没问题后，我们打开power bi desktop，点击获取数据，选择MySQL数据库：
然后输入对应的服务器、数据库及查询语句，我们查询第一个表“历史业绩”
接着同样道理，我们查询一个“今年（2020）以来的业绩”表、以及“维度表”
在power bi的数据视图如下：
然后我们开始进行数据清洗，由于数据比较干净，我们仅需要在power bi内增加一个“时间表”
但随着开始分析，我们发现一些数据的呈现，如最近一个月的GMV环比上月，如果用power bi的M语言来写新表，会很复杂，而且M语言语法相对软件新人来说多少有点不友好，咱们还是通过sql直接把需要的数据从数据库查询出来吧。比如我们需要构建一个含最近一个月及上个月项业务聚合值的表，以计算当前月环比上月情况：
#仅含平台、品牌的环比业绩
with t1 as
(select
month_rank,YearMonth,品牌ID,品牌名称,门店ID,门店名称,城市,平台i as 平台,平台门店名称,
sum(GMV) as GMV,sum(商家实收) as 商家实收,sum(门店曝光量) as 门店曝光量,
sum(门店访问量) as 门店访问量,sum(门店下单量) as 门店下单量,sum(无效订单) as 无效订单,
sum(有效订单) as 有效订单,sum(曝光人数) as 曝光人数,sum(进店人数) as 进店人数,
sum(下单人数) as 下单人数,sum(cpc总费用) as cpc总费用,sum(cpc曝光量) as cpc曝光量,
sum(cpc访问量) as cpc访问量,sum(商户补贴) as 商户补贴,sum(平台补贴) as 平台补贴,
sum(商家实收)/sum(GMV) as 实收率,
sum(门店访问量)/sum(门店曝光量) as 访问曝光转化率,sum(门店下单量)/sum(门店访问量) as 到店下单转化率
from
(select
*,date_format(日期,'%Y%m') as YearMonth,
dense_rank() over(order by date_format(日期,'%Y%m') desc) as month_rank
from hotpot_stores) t
where month_rank<=2
group by month_rank,YearMonth,品牌ID,品牌名称,门店ID,门店名称,城市,平台,平台门店名称)
select c.*,l.*
from
(select * from t1 where month_rank=1) c#c menas current_month
left join (select * from t1 where month_rank=2) l#l means last_month
on c.month_rank=l.month_rank-1
同理，我们就在power bi创建了多个具有关联的表
power bi表关联
接下来对于创建的表，我们需要通过在模型视图“管理关系”来为各表建立关联，可以理解为图形化的sql中的表连接：
创建好管理各表关联关系后可以在模型视图中看到各表关系：
可视化
数据表建好以后，我们还需要针对数据再power bi里创建一些度量如实收率、客单价、单均价、营销费用ROI等，篇幅问题，此过程就不作展示了~
我们直接看最后可视化的三个仪表板：
汇总分析
转化分析：
营销费用分析：
最后我们对可视化结果进行发布：
结语
总结
本次项目模拟真实从数据库取数的流程，并从结果业务指标汇总分析、转化流程分析、营销费用分析三个角度进行可视化，并以城市、平台、品牌、门店作为切片器，方便报表使用者查看具体维度的业务信息。
不足与展望
在第一页汇总分析时，可以考虑加上累计业绩达成率的度量，方便实时了解达成情况，但考虑到页面空间有限及信息过载的情况，最终只呈现重要指标的累计及环比，当然在实际业务中需要根据使用者的需求来考虑呈现信息；
切片器中没有时间维度的参数，意味着图表始终是默认以最新时间来呈现的，这也是考虑到页面位置有限。这样设计的假设是使用者最关心的是当下的情况，同时给到一些指标的每月趋势也帮助对比往期情况；
对于单个业务指标值如环比，没能根据数字正负设置条件颜色，导致环比信息不够突出，后面查了一下资料只能在表格中实现，后续会继续了解改进~
当然本案例只是作为上手之作，还有很多需要改善的地方，也期待后面对工具更深入了解后能不断完善，也期待各位读者阅读完后也能给出见解与建议~谢谢阅读
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
