一、涉及业务介绍
某头部汽车新零售企业立足下沉市场,通过网络渠道和品牌广告获取大量汽车销售线索,并借助大量地面销售在线下门店完成客户成交孵化,该企业最终依靠自建供应链建立的护城河来赚取采购差价,同时也通过给客户提供低首付金融租赁产品来赚取金融费用。该公司商业分析师需要从数据层面去驱动投放团队去做效率优化,以助力业务正常健康发展。
头部汽车:top级,行业排名靠前
下沉市场:三四线城市及以下消费市场
网络渠道:汽车之家、懂车帝、易车等
品牌广告:微信朋友圈、微博、抖音
低首付金融租赁产品:车贷
二、涉及业务数据及字典
数据库:business_sample_car_retail
涉及表: clue_day,order_day
线索表clue_day
column | Type | Comment |
---|---|---|
clue_id | bigint | 线索id |
city_id | bigint | 用户城市id(线索产生时的城市) |
deal_city_id | bigint | 交易城市id |
district_id | bigint | 区县id |
cc_id | bigint | 客户id |
guid | varchar | 设备id |
customer_phone | varchar | 客户手机号(密文) |
ca_s | varchar | 一级渠道(网络渠道、品牌广告) |
ca_n | varchar | 二级渠道(具体平台抖音、微博、微信等) |
ca_s_new | varchar | 归因后一级渠道(记录最近一次渠道信息) |
ca_n_new | varchar | 归因后二级渠道 |
clue_platform | varchar | 电销侧来源平台 |
cainfo | varchar | 附加信息 |
platform | varchar | (手机wap端/pc端) |
newcar_product_type | integer | 新车产品类型,0-4s店带看,1-付一成,2是回访,3是在线客服,4是400,-250战败工单 |
is_high_quality | integer | 是否高质量线索标识 |
is_valid | integer | 线索是否有效 |
is_new | integer | 是否新线索 |
clue_created_at | varchar | 创建时间 |
成交订单表order_day
column | Type | Comment |
---|---|---|
order_id | int | 订单id |
customer_name | string | 客户姓名 |
phone | string | 电话,加密 |
city_id | int | 城市ID |
brand_id | int | 品牌ID |
chexi_id | int | 车系ID |
chexing_id | int | 车型ID |
sku_id | int | sku_id |
vin | string | 车辆vin码 |
engine_num | string | 发动机号 |
hegezheng_pic | string | 合格证照片 |
apply_id | string | 金融支付id |
color | int | 购买车型颜色 |
created_at | string | 创建时间(成交订单时间) |
clue_id | int | 线索ID |
saler_id | int | 销售id |
downpayment_paid_time | string | 首付完成时间 |
finished_time | string | 订单完成时间 |
创建数据库、数据表
#创建数据库
create database business_sample_car_retail character set utf8;
#创建数据表,clue_day
create table clue_day (
clue_id int (8),
city_id int (3),
deal_city_id int (3),
cc_id int (9),
guid varchar (36),
customer_phone varchar (26),
ca_s varchar (18),
ca_n varchar (13),
ca_s_new varchar (12),
ca_n_new varchar (13),
clue_platform varchar (23),
cainfo varchar (318),
platform varchar (5),
newcar_product_type int (1),
is_high_quality int (1),
is_valid int (1),
is_new int (1),
clue_created_at datetime,
district_id int (4)
);
#创建数据表,order_day
create table order_day(
created_at datetime,
order_id int(5),
clue_id int(10),
city_id int(5),
chexing_id int(10),
sku_id int(5),
vin varchar(46),
engine_num varchar(26)
);
三、需求与代码实现
- 需求1:查询线索(二级渠道jdsc)后续转化成交车型详情
解析:线索和渠道 --> clue表
成交购买的车型 --> order表
需要进行clue表和order表的关联查询(使用内连接进行关联查询)
条件:二级渠道jdsc
返回结果:线索id、线索所属渠道名称、购买的车型
#隐式内连接
select c.clue_id,ca_n,chexing_id
from clue_day c,order_day o
where c.clue_id = o.clue_id and ca_n = 'jdsc';
#显式内连接
select c.clue_id,ca_n,chexing_id
from clue_day c inner join order_day o on c.clue_id = o.clue_id
where ca_n = 'jdsc';
- 需求2:统计所有渠道(按照二级渠道)的转化率
解析:转化率=成单数(order表)/线索数(clue表)
渠道 --> clue表
#左外连接
select ca_n,count(o.clue_id)/count(c.clue_id) '转化率'
from clue_day c left join order_day o on c.clue_id = o.clue_id
group by ca_n;
- 需求3:查询各城市线索数并计算所有城市线索总数
解析:城市、线索
#方法一
select city_id,count(*)
from clue_day
group by city_id;
select count(clue_id) from clue_day;
#方法二
select city_id,count(clue_id)
from clue_day
group by city_id;
select count(clue_id) from clue_day;
#方法三(合并查询)
select city_id,count(clue_id) as clue_count from clue_day group by city_id
union all
select '总计' as city_id,count(clue_id) from clue_day;
- 需求4:找出优质渠道,作重点投入
解析:优质渠道(以二级渠道为基准)
该二级渠道内平均转化周期<整体平均转化周期
该二级渠道的线索量>各二级渠道平均线索量
转化周期 = 成单日期 - 线索创建日期
各二级渠道内平均转化周期,各二级渠道的线索量
select ca_n,
avg(datediff(created_at,clue_created_at)),
count(o.clue_id)
from clue_day c left join order_day o on c.clue_id = o.clue_id clue_num
where o.created_at is not null and c.clue_created_at is not null
group by ca_n;
各二级渠道内平均线索量,其中ceil()为向上取整
#方法一
select ceil(avg(clue_num)) from(
select ca_n,
avg(datediff(created_at,clue_created_at)),
count(o.clue_id) clue_num
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null
group by ca_n
) a;
#方法二
#with··· as··· 在一个sql中提取公共的子查询
with ca_value as(
select ca_n,
avg(datediff(created_at,clue_created_at)),
count(o.clue_id) clue_num
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null
group by ca_n
)
select ceil(avg(clue_num)) from ca_value;
整体线索平均转化周期
select avg(datediff(created_at,clue_created_at))
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null
需求查询,以上代码的整合
with ca_value as(
select ca_n,
avg(datediff(created_at,clue_created_at)) avg_time,
count(o.clue_id) clue_num
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null
group by ca_n
)
select ca_n,avg_time from ca_value
# 该二级渠道内平均转化周期<整体平均转化周期
where
avg_time <(
select avg(datediff(created_at,clue_created_at))
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null
)
#该二级渠道的线索量>各二级渠道平均线索量
and clue_num >(select ceil(avg(clue_num)) from ca_value);
扩展认识
数学函数
函数 | 作用 |
---|---|
abs(x) | 返回x的绝对值 |
floor(x),ceil(x),round(x) | floor向下取整,ceil向上取整,round四舍五入 |
rand() | 返回0~1的随机数 |
pi() | 返回圆周率的值 |
mod(x.y) | 返回x除以y以后的余数 |
字符串函数
函数 | 作用 |
---|---|
concat(s1,s2···) | 将字符串拼接,连接为一个字符串 |
left(s,n) | 返回左起,从字符串s开始的n个字符 |
trim(S) | 移除掉字串中s的字头或字尾处空格 |
replace(s,s1,s2) | 用字符串s2替代字符串s重的字符串s1 |
substring(s,n,len) , mid(s,n.len) | 截取字符串s中第n个位置开始,长度为len的字符串 |
reverse(s) | 将字符串s的顺序翻转过来 |
日期时间函数
函数 | 作用 |
---|---|
curdate() | 当前日期 |
curtime() | 当前时间 |
now() | 当前日期时间 |
month(curdate() ) | 获取月份 |
条件判断函数
函数 | 作用 |
---|---|
if (expo,v1,v2) | 表达式成立,执行v1,否则执行v2 |
case when | 用于计算条件列表并返回多个可能结果表达式之一 |
其中,case when的代码格式
case when 条件1 then 结果1
when 条件2 then 结果2
when ··· then ···
else 结果n+1
end