销售情况分析仪

业务场景

某大型电子设备生产厂商, 旗下有四款主力产品, 采取总部、大区经理、城市经理的销售管理模式

具体执行上, 以季度为节点来指定销售目标, 每季度划分13周。销售目标中会详细制定每种产品的每周的销售节奏, 明确规定每个城市经理要完成的每种产品的季度任务额。区域经理不背负额外的任务指标, 其销售目标就是下属城市经理的任务总和

分析目的就是为了掌握销售现状, 找到影响销售进度的关键责任人, 为下一步行动措施提供依据

业务规则

1. 数据结构
   
   product
   
   region
   
   sale
   
   schedule
   
   target
   
   staff

2. 表间关联
   

数据整理

1. MySQL
   建表导数, 根据关键字段关联五张表, 将大区、产品、大区经理和目标字段补充进sale表, 生成用于分析的总表

create table sale(
	id int primary key,
    city_num varchar(10),
    city varchar(10),
	city_manager_num varchar(10),
    city_manager varchar(10),
    product_num varchar(10),
    date date,
    sales_volume int,
    id_code varchar(10)
);

load data local infile 'D:/sale.csv'
	into table sale
    fields terminated by ','
    ignore 1 lines;
    
select * from sale;

create table product(
    product_num varchar(10) primary key,
    product varchar(10)
);

load data local infile 'D:/product.csv'
	into table product
    fields terminated by ','
    ignore 1 lines;
    
select * from product;

create table region(
    id int primary key,
    region_num varchar(10),
    region varchar(10),
    city_num varchar(10),
    city varchar(10)
);

load data local infile 'D:/region.csv'
	into table region
    fields terminated by ','
    ignore 1 lines;
    
select * from region;

create table schedule(
    id int primary key,
    week_num varchar(10),
    product varchar(10),
    schedule varchar(10)
);

load data local infile 'D:/schedule.csv'
	into table schedule
    fields terminated by ','
    ignore 1 lines;
    
select * from schedule;

create table staff(
	id int primary key,
    region_num varchar(10),
    city_num varchar(10),
    region_manager_num varchar(10),
    region_manager varchar(10),
	city_manager_num varchar(10),
    city_manager varchar(10)
);

load data local infile 'D:/staff.csv'
	into table staff
    fields terminated by ','
    ignore 1 lines;
    
select * from staff;

create table target(
	id int primary key,
    product_num varchar(10),
    product varchar(10),
	city_manager_num varchar(10),
    city_manager varchar(10),
    target int,
    id_code varchar(10)
);

load data local infile 'D:/target.csv'
	into table target
    fields terminated by ','
    ignore 1 lines;
    
select * from target;

create table total(
    select r.region,p.product,st.region_manager,sa.city,sa.city_manager,sa.date,sa.sales_volume,t.target
	from sale sa
	join product p on sa.product_num = p.product_num
	join staff st on sa.city_manager_num = st.city_manager_num
	join region r on sa.city_num = r.city_num
	join target t on sa.id_code = t.id_code
);

select * from total;

2. Power Query
导入MySQL中创建的total表和schedule表, 在total表中增加季度、年周数和季周数字段, 检查并调整字段格式

3. Power Pivot

• 在total表中增加当周新增字段, 创建累计销售、目标销售、累计完成率和当周新增汇总规则
• 增加匹配列(产品+季周数), 建立total表与schedule表的关联关系
  
  
  schedule
  
  total

数据可视化