销售额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';

用券销售额

-- 三个平台之和
-- 天猫
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;

　　

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';

　会员复购率

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 ;