销售额GMV/交易人数/订单数/客单价/客件数/连带率/会员数量/用券销售额/退款金额/退款单数/退款件数

?
SELECT
    SUM( c_orders.payment ) AS "销售额GMV",
    SUM( IF ( member_level > 0, c_orders.payment, 0 )) AS "会员销售额GMV",
    sum( after_refund_payment ) AS "净销售额",
    SUM( IF ( member_level > 0, c_orders.after_refund_payment, 0 )) AS "会员净销售额",
    COUNT( DISTINCT ( member_id )) AS "总交易人数",
    COUNT( DISTINCT IF( member_level > 0, member_id, NULL )) AS "会员交易人数" ,
    count(tid) as "总交易订单数",
    COUNT( DISTINCT IF ( member_level > 0, tid, NULL )) AS "会员交易订单数" ,
    SUM( IF ( member_level > 0, c_orders.payment, 0 )) / SUM( payment ) as "会员销售贡献率",
    SUM( payment ) / count( DISTINCT member_id ) AS "客单件",
    SUM(IF( member_level > 0, c_orders.payment, 0 ))/COUNT(DISTINCT IF ( member_level > 0, member_id, NULL )) as "会员平均客单价",
    SUM( good_count )/ COUNT( DISTINCT member_id ) AS "客件数",
    SUM( good_count )/ COUNT( DISTINCT IF( member_level > 0, member_id, NULL )) AS "会员平均客件数",
    SUM(good_count)/COUNT( DISTINCT tid ) AS "连带率",
    SUM(IF( member_level > 0, c_orders.good_count, 0 ))/COUNT(DISTINCT IF ( member_level > 0, tid, NULL )) as "会员平均连带率"
FROM
  crm_clarks.c_orders
WHERE
  `store_id` IN ( SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
  AND ( `step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR `step_trade_status` IS NULL )
  AND `pay_time` BETWEEN '2022-02-17 00:00:00' AND '2022-02-17 23:59:59' ;

　　

?
SELECT
    COUNT(*) as "会员数量"   
FROM c_member
WHERE register_date<='2022-02-28'
AND identity=2 AND store_id IN(SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id NOT IN(37,38,101,102,136));
 
SELECT
    count(*) as "新增会员数"
FROM
    c_member
WHERE
    identity = '2'
    AND c_member.store_id IN ( SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
    and register_date BETWEEN '2022-02-16 00:00:00'  AND  '2022-02-16 23:59:59';

用券销售额

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 三个平台之和
-- 天猫
SELECT SUM(payment) FROM (SELECT o.payment FROM c_orders o INNER JOIN c_goods g ON o.tid=g.tid INNER JOIN c_promotion p ON g.oid=p.oid
WHERE o.store_id IN (SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
AND ( o.`step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR o.`step_trade_status` IS NULL )
AND o.pay_time BETWEEN '2022-02-14 00:00:00'
  AND '2022-02-17 23:59:59' AND o.channel_id=1  GROUP BY o.payment) t;
 
  -- 京东
  SELECT SUM(payment) FROM (SELECT o.payment FROM c_orders o INNER JOIN c_jd_order_coupondetail p ON o.tid=p.order_id
WHERE o.store_id IN (SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
AND ( o.`step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR o.`step_trade_status` IS NULL )
AND o.pay_time BETWEEN '2022-02-14 00:00:00'
  AND '2022-02-17 23:59:59' AND o.channel_id=2  GROUP BY o.payment) t;
 
  -- 小程序和pos
  SELECT SUM(payment) FROM (SELECT o.payment FROM c_orders o INNER JOIN c_goods g ON o.tid=g.tid INNER JOIN c_brand_promotion p ON g.oid=p.oid
WHERE o.store_id IN (SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
AND ( o.`step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR o.`step_trade_status` IS NULL )
AND o.pay_time BETWEEN '2022-02-17 00:00:00'
  AND '2022-02-17 23:59:59' AND o.channel_id IN(3,4)  GROUP BY o.payment) t;

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    sum( r.refund_fee ) AS "退款金额" ,
    count(r.refund_id) as "退款单数",
    sum(r.num) as "退款件数"
FROM
    c_refund_order r
    INNER JOIN c_orders o ON r.tid = o.tid
    INNER JOIN c_member ON c_member.id = o.member_id
WHERE
    o.store_id IN (SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id NOT IN ( 37, 38, 101, 102, 136 ))
    AND r.refund_status = 1
    AND r.endtime BETWEEN '2022-02-14 00:00:00' AND '2022-02-17 23:59:59';

　会员复购率

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
    concat(round( COUNT( IF ( 复购人数 > 1, 1, NULL ) ) / COUNT( member_id ) * 100, 2 ),'%') AS '复购率'
FROM
(
    SELECT
        CONCAT( date_format( c_orders.pay_time, '%Y-%m-%d' ) ) AS 下单年月,
        COUNT( c_orders.member_id ) AS "复购人数" ,
        member_id
    FROM
    c_orders
    INNER JOIN c_member ON c_orders.member_id = c_member.id
    WHERE 1
    and c_orders.store_id IN ( SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
    AND date_format( c_orders.pay_time, '%Y-%m-%d' ) BETWEEN '2022-02-14'
    AND '2022-02-18'
    GROUP BY
    c_orders.member_id
) a

　会员复购金额

select
 aa.qqqq - bb.第一笔订单金额 as "会员复购金额"
from 
        (
             SELECT
                    SUM(会员销售额) as "qqqq"
            FROM
            (
                    SELECT
                            SUM( IF ( member_level > 0, c_orders.payment, 0 ))  AS "会员销售额",
                            CONCAT( date_format( c_orders.pay_time, '%Y-%m-%d' ) ) AS 下单年月,
                            COUNT( c_orders.member_id ) AS "复购人数" ,
                            member_id
                    FROM
                    c_orders
                    INNER JOIN c_member ON c_orders.member_id = c_member.id
                    WHERE 1
                            and c_orders.store_id IN ( SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
                            AND ( c_orders.`step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR c_orders.`step_trade_status` IS NULL )
                            AND pay_time BETWEEN '2022-02-01 00:00:00' AND '2022-02-22 23:59:59'
                    GROUP BY
                            c_orders.member_id 
            ) a 
    ) aa,
(
        SELECT
                SUM(aaaa) as "第一笔订单金额"    
        FROM
        (
                SELECT
                        member_id,
                        min(pay_time),
                        c_orders.payment as "aaaa"
                FROM
                c_orders
                INNER JOIN c_member ON c_orders.member_id = c_member.id
                WHERE 1
                        and c_orders.member_level > 0
                        and c_orders.store_id IN ( SELECT id FROM c_store_list WHERE is_store = 1 AND `status` = 1 AND id not IN(37,38,101,102,136))
                        AND ( c_orders.`step_trade_status` = 'FRONT_PAID_FINAL_PAID' OR c_orders.`step_trade_status` IS NULL )
                        AND pay_time BETWEEN '2022-02-01 00:00:00' AND '2022-02-22 23:59:59'
                 GROUP BY
                         c_orders.member_id 
        ) b 
) bb ;

　　

posted @   PHP小媛  阅读(614)  评论(0)  编辑  收藏  举报
相关博文：
· jmeter 脚本
· postman 验签
· 尚硅谷SQL刷题练习
· SQL - 多表连接后求和
· hive05_窗口函数
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
历史上的今天：
2021-02-18 fiddler配置手机代理
2019-02-18 域名访问配置
点击右上角即可分享
微信分享提示