이커머스 SQL

기초 SQL

• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
• COUNT, COUNT(DISTINCT), MIN, MAX, AVG, SUM
• (INNER) JOIN, LEFT JOIN, FULL (OUTER) JOIN, UNION
• mysql 자동정렬: ctrl[command] + B

 

Maven Fuzzy Factory 데이터베이스 개요

Maven Fuzzy Factory 데이터베이스는 전자상거래(e-commerce)에서 자주 사용되는 데이터를 다룬다. 이 데이터베이스에는 웹사이트 세션, 웹사이트 페이지 뷰, 주문, 주문 항목, 제품, 주문 항목 환불 등의 테이블이 포함되어 있다.

주요 테이블 개요

 

이 데이터베이스의 구조는 전자상거래에서 흔히 볼 수 있는 형태로, 이를 잘 활용하면 비즈니스의 약점이나 개선 기회를 파악할 수 있다. 이 데이터베이스를 통해 다양한 분석을 수행하여 전자상거래 회사에서 즉각적으로 가치를 더할 수 있다. 이후 과정에서 각 테이블을 더 깊이 탐구할 예정이다.

SELECT
	website_sessions.utm_content,
	COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
	COUNT(DISTINCT orders. order_id) AS orders,
    COUNT(DISTINCT orders. order_id)/COUNT(DISTINCT website_sessions.website_session_id) AS CVR
FROM website_sessions
	LEFT JOIN orders
		ON orders.website_session_id = website_sessions.website_session_id
WHERE website_sessions.website_session_id BETWEEN 1000 and 2000 -- arbitrary
GROUP BY 1
ORDER BY 2 DESC;

 

-- We've been live for almost a month now and we’re starting to generate sales.
-- Can you help me understand where the bulk of our website sessions are coming from, through yesterday?
-- I’d like to see a breakdown by UTM source, campaign and referring domain if possible. Thanks!
SELECT 
    utm_source,
    utm_campaign,
    http_referer,
    COUNT(website_session_id) AS sessions
FROM
    website_sessions
WHERE
    created_at < '2012-04-12'
GROUP BY utm_source , utm_campaign , http_referer
ORDER BY sessions DESC;

 

SELECT 
    YEAR(created_at) AS created_yr,
    WEEK(created_at) AS created_wk,
    MIN(DATE(created_at)) AS week_start,
    COUNT(DISTINCT website_session_id) AS sessions
FROM
    website_sessions
WHERE
    website_session_id BETWEEN 100000 AND 115000
GROUP BY 1, 2

 

SELECT
	primary_product_id,
    -- order_id,
    -- items_purchased,
    COUNT(DISTINCT CASE WHEN items_purchased = 1 THEN order_id ELSE NULL END) AS single_item_orders,
    COUNT(DISTINCT CASE WHEN items_purchased = 2 THEN order_id ELSE NULL END) AS two_items_orders
    -- CASE WHEN items_purchased = 1 THEN order_id ELSE NULL END AS single_item_orders,
    -- CASE WHEN items_purchased = 2 THEN order_id ELSE NULL END AS two_items_orders
FROM orders
WHERE order_id BETWEEN 31000 AND 32000
GROUP BY primary_product_id;

 

입찰 최적화 [Bid Optimization]

입찰 최적화 분석은 다양한 유료 트래픽 세그먼트의 가치를 이해하여 마케팅 예산을 최적화하는 것을 목표로 한다. 이 과정에서 마케터가 각 트래픽 세그먼트에 대해 적절한 입찰 금액을 결정할 수 있도록 돕는다. 이때 각 세그먼트가 얼마나 많은 매출을 발생시키는지에 따라 입찰 금액을 조정하게 된다.

  

• Segment A: 전환율(CVR)이 10%로 양호하다. 입찰 금액을 올려 더 많은 트래픽을 유도한다.
• Segment B: 전환율이 3%로 다른 세그먼트에 비해 성과가 저조하다. 입찰 금액을 낮춰야 한다.
• Segment C: 전환율이 17%로 매우 우수하다. 입찰가를 올려 더 많은 트래픽을 유도해야 한다.

주요 사용 사례

1. 전환율과 클릭당 수익을 분석하여 클릭당 적절한 비용을 산정한다. 고객 확보를 위해 얼마를 지출할 수 있는지를 결정한다.
2. 트래픽의 하위 세그먼트(예: 모바일 vs 데스크탑) 성과를 비교하고, 성과가 좋은 채널에 더 많은 자원을 투입한다.
3. 입찰 변경이 순위와 고객 유입량에 미치는 영향을 분석하여, 마케팅 채널에서 최대 효율을 이끌어낸다.

정리

입찰 최적화는 마케팅 전략의 핵심 요소로, 효율적인 예산 사용을 통해 트래픽을 최대화할 수 있다. 각 트래픽 세그먼트의 성과를 철저히 분석하여 입찰 전략을 최적화해야 한다.

/* Based on your conversion rate analysis,
we bid down gsearch nonbrand on 2012-04-15.
Can you pull gsearch nonbrand trended session volume, by week,
to see if the bid changes have caused volume to drop at all?*/
SELECT 
    -- YEARWEEK(website_sessions.created_at) AS year_week,
    MIN(DATE(created_at)) AS week_start_date,
    COUNT(DISTINCT website_sessions.website_session_id) AS sessions
FROM
    website_sessions
WHERE
    website_sessions.created_at < '2012-05-10'
        AND website_sessions.utm_source = 'gsearch'
        AND website_sessions.utm_campaign = 'nonbrand'
GROUP BY YEARWEEK(website_sessions.created_at);

• group by yearweek으로 주별 첫째날 집계

/* I was trying to use our site on my mobile device the other day, and the experience was not great.
Could you pull conversion rates from session to order, by device type?
If desktop performance is better than on mobile
we may be able to bid up for desktop specifically to get more volume?*/
SELECT 
    website_sessions.device_type,
    COUNT(DISTINCT website_sessions.website_session_id) AS sessions,
    COUNT(DISTINCT order_id) as orders,
    COUNT(DISTINCT order_id)/COUNT(DISTINCT website_sessions.website_session_id) as CVR
FROM
    website_sessions
LEFT JOIN ORDERS
	ON ORDERS.website_session_id = website_sessions.website_session_id
WHERE
    website_sessions.created_at < '2012-05-11'
        AND website_sessions.utm_source = 'gsearch'
        AND website_sessions.utm_campaign = 'nonbrand'
GROUP BY
	device_type;

• COUNT 할 때 DISTINCT 붙이기, Join 할 때 중복되지 않는 테이블명도 붙이는 게 더 구분하기 좋다.
• ORDER (주문) 없이 sessions만 있는 경우도 있으니까 LEFT JOIN