CRM 전략에 필요한 데이터를 직접 분석했습니다

/*
고객 프로파일링 + 혼자 그리기 횟수
*/
USE bonbon;


WITH FirstSuccessfulPayments AS (
    SELECT 
        s.id AS studentID,
        o.price AS fstpr,
        o.package_name AS fstpkg,
        p.created_at AS fsttime
    FROM
        bonbon.`User` u 
    JOIN
        bonbon.Student s ON u.id = s.user_id
    LEFT JOIN
        bonbon.`Order` o ON s.id = o.student_id 
    LEFT JOIN 
        bonbon.Payment p ON o.id = p.order_id 
    WHERE 
        p.status LIKE "%SUCCESS%"
    AND 
        p.created_at = (
            SELECT MIN(p2.created_at)
            FROM bonbon.Payment p2
            JOIN bonbon.`Order` o2 ON p2.order_id = o2.id
            WHERE o2.student_id = s.id AND p2.status LIKE "%SUCCESS%"
        )
    GROUP BY
        s.id
), LatestSuccessfulPayments AS (
    SELECT 
        s.id AS studentID,
        o.price AS lstpr,
        o.package_name AS lstpkg,
        MIN(p.created_at) AS lsttime
    FROM
        bonbon.`User` u 
    JOIN
        bonbon.Student s ON u.id = s.user_id
    LEFT JOIN
        bonbon.`Order` o ON s.id = o.student_id 
    LEFT JOIN 
        bonbon.Payment p ON o.id = p.order_id 
    WHERE 
        p.status LIKE "%SUCCESS%"
    AND 
        p.created_at = (
            SELECT MAX(p2.created_at)
            FROM bonbon.Payment p2
            JOIN bonbon.`Order` o2 ON p2.order_id = o2.id
            WHERE o2.student_id = s.id AND p2.status LIKE "%SUCCESS%"
            )
    GROUP BY
        s.id
), LatestLesson AS (
                SELECT 
                        s.id AS studentID,
                        l.start_at AS lstLsntime,
                        l.status AS lstLsnSt,
                        l.curriculum_type_id AS lstLsnTp
                FROM bonbon.Student s 
                JOIN bonbon.Lesson l ON l.student_id = s.id 
                WHERE 
                        l.start_at = (
                                SELECT MAX(l2.start_at)
                                FROM bonbon.Student s2
                                JOIN bonbon.Lesson l2 ON l2.student_id = s2.id
                                WHERE l2.student_id = s.id
                                )
                GROUP BY s.id
)


SELECT 
        u.id AS userID,
        u.name AS username,
        NULL AS prntGender,
        NULL AS prntBirth,
        TRUNCATE((TO_DAYS(NOW()) - TO_DAYS(NULL)) / 365,0) AS prntAge,
        NULL AS prntOcptn,
        NULL AS prntAdr,
        NULL AS childInfo,
        u.phone_no AS phoneNumber,
        u.email_addr AS Email,
                    u.login_id AS loginID,
        u.created_at + INTERVAL 9 HOUR AS signUpDate,
        u.marketing_argreed_at + INTERVAL 9 HOUR AS mktAgrAt,
        MIN(p.created_at) + INTERVAL 9 HOUR AS firstPayment,
        fst.fsttime + INTERVAL 9 HOUR AS noRfndFstPTime,
        fst.fstpkg AS noRfndFstPName,
        fst.fstpr AS noRfndFstPPrice,
        lst.lsttime + INTERVAL 9 HOUR AS noRfndLstPTime,
        lst.lstpkg AS noRfndLstPName,
        lst.lstpr AS noRfndLstPPrice,
        lstlsn.lstLsnTp,
        lstlsn.lstLsnSt,
        SUM(CASE WHEN p.status LIKE '%SUCCESS%' THEN o.price ELSE 0 END) AS allPaid,
        DATEDIFF(lstlsn.lstLsntime, NOW()) AS dayPssdLstLsn,
        DATEDIFF(u.last_logined_at, NOW()) AS dayPssdLstgn,
        COUNT(CASE WHEN p.status LIKE '%SUCCESS%' THEN 1 END) AS successCount,
        COUNT(CASE WHEN p.status LIKE '%CANCEL%' THEN 1 END) AS cancelCount,
        COUNT(CASE WHEN p.status LIKE '%SUCCESS%' AND o.price = 0 THEN 1 END) AS zeroSuccess,
        COUNT(CASE WHEN p.status LIKE '%CANCEL%' AND o.price = 0 THEN 1 END) AS zeroCancel,
        COUNT(p.status) AS paymentCount,
        s.id AS stdntID,
        s.name AS stdntName,
        s.gender AS stdntGender,
        s.birth_date AS stdntBirth,
        TRUNCATE((TO_DAYS(NOW()) - TO_DAYS(s.birth_date)) / 365,0) AS stdntAge,
        drwcnt.drwcnt AS slfDrwCnt

FROM
        bonbon.`User` u 
JOIN
        bonbon.Student s ON u.id = s.user_id
LEFT JOIN
        bonbon.`Order` o ON s.id = o.student_id 
LEFT JOIN 
        bonbon.Payment p ON o.id = p.order_id 
LEFT JOIN 
        FirstSuccessfulPayments fst ON fst.studentID = s.id
LEFT JOIN 
        LatestSuccessfulPayments lst ON lst.studentID = s.id
LEFT JOIN
        LatestLesson lstlsn ON lstlsn.studentID = s.id
LEFT JOIN 
				(
				SELECT
					COUNT(*) AS drwcnt
					, da.student_id
				FROM
					bonbon.DrawingArchive da 
				WHERE 
					da.lesson_id IS NULL AND 
					da.category LIKE "%drawing%"
				GROUP BY
					da.student_id
				) drwcnt ON drwcnt.student_id = s.id
WHERE 
        o.id IS NOT NULL
GROUP BY 
        s.id 
ORDER BY allPaid DESC, u.id

보관 중인 작업 코드입니다. 실제 데이터베이스에 연결하거나 실행하지 않습니다.

17: 고객 프로파일링
21: 고객 프로파일링 + 혼자 그리기 횟수
22: 모든 구매 건에 대해 프로파일링
23: 한 달 기준 수업 스케줄 신청 분포
24: 선생님의 수업 가능한 슬롯 분포
25: 결제가 일어난 시각 분포
28: 체험 수업 완료 고객의 구매 이력
29: 체험이 아닌 결제를 성공한 고객의 구매 정보