SQL 심화학습 PV, UV

-- 테이블 생성

CREATE OR REPLACE TABLE `dev-aileron-447402-k9.modulabs.orders` (

   order_id STRING NOT NULL,

   user_id STRING NOT NULL,

   order_timestamp TIMESTAMP

);

-- 테이블 생성

CREATE OR REPLACE TABLE `dev-aileron-447402-k9.modulabs.payments` (

   order_id STRING NOT NULL,

   value FLOAT64

);

-- 데이터 삽입

INSERT INTO modulabs.orders (order_id, user_id, order_timestamp) VALUES

('order_1', 'user_1', TIMESTAMP('2018-01-01 10:00:00')),

('order_2', 'user_2', TIMESTAMP('2018-01-01 12:30:00')),

('order_3', 'user_3', TIMESTAMP('2018-01-02 09:20:00')),

('order_4', 'user_4', TIMESTAMP('2018-01-02 10:15:00')),

('order_5', 'user_5', TIMESTAMP('2018-01-02 14:05:00')),

('order_6', 'user_6', TIMESTAMP('2018-01-03 11:00:00')),

('order_7', 'user_7', TIMESTAMP('2018-01-03 13:45:00')),

('order_8', 'user_8', TIMESTAMP('2018-01-04 15:30:00')),

('order_9', 'user_9', TIMESTAMP('2018-01-04 18:00:00')),

('order_10', 'user_10', TIMESTAMP('2018-01-05 20:30:00')),

('order_11', 'user_11', TIMESTAMP('2018-01-06 09:00:00')),

('order_12', 'user_12', TIMESTAMP('2018-01-06 12:45:00')),

('order_13', 'user_13', TIMESTAMP('2018-01-07 16:20:00')),

('order_14', 'user_14', TIMESTAMP('2018-01-08 17:35:00')),

('order_15', 'user_15', TIMESTAMP('2018-01-09 19:50:00')),

('order_16', 'user_16', TIMESTAMP('2018-01-10 21:15:00')),

('order_17', 'user_17', TIMESTAMP('2018-01-11 22:40:00')),

('order_18', 'user_18', TIMESTAMP('2018-01-12 23:05:00')),

('order_19', 'user_19', TIMESTAMP('2018-01-13 13:15:00')),

('order_20', 'user_20', TIMESTAMP('2018-01-14 14:25:00')),

('order_21', 'user_21', TIMESTAMP('2018-01-15 15:35:00')),

('order_22', 'user_22', TIMESTAMP('2018-01-16 16:45:00')),

('order_23', 'user_23', TIMESTAMP('2018-01-17 17:55:00')),

('order_24', 'user_24', TIMESTAMP('2018-01-18 18:05:00')),

('order_25', 'user_25', TIMESTAMP('2018-01-19 19:15:00')),

('order_26', 'user_26', TIMESTAMP('2018-01-20 20:25:00')),

('order_27', 'user_27', TIMESTAMP('2018-01-21 21:35:00')),

('order_28', 'user_28', TIMESTAMP('2018-01-22 22:45:00')),

('order_29', 'user_29', TIMESTAMP('2018-01-23 23:55:00')),

('order_30', 'user_30', TIMESTAMP('2018-01-24 11:05:00'));

-- 데이터 삽입

INSERT INTO modulabs.payments (order_id, value) VALUES

('order_1', 100.00),

('order_2', 150.00),

('order_3', 200.00),

('order_4', 110.00),

('order_5', 120.00),

('order_6', 130.00),

('order_7', 140.00),

('order_8', 210.00),

('order_9', 220.00),

('order_10', 230.00),

('order_11', 240.00),

('order_12', 250.00),

('order_13', 260.00),

('order_14', 270.00),

('order_15', 280.00),

('order_16', 290.00),

('order_17', 300.00),

('order_18', 310.00),

('order_19', 320.00),

('order_20', 330.00),

('order_21', 340.00),

('order_22', 350.00),

('order_23', 360.00),

('order_24', 370.00),

('order_25', 380.00),

('order_26', 390.00),

('order_27', 400.00),

('order_28', 410.00),

('order_29', 420.00),

('order_30', 430.00);

orders 테이블

payments테이블

SELECT

    DATE(order_timestamp) AS dt,         -- 연월일 추출한 dt

    SUM(P.value) AS daily_value          -- daily_value를 계산 (value 합계)

FROM modulabs.orders O

LEFT JOIN modulabs.payments P ON O.order_id = P.order_id

GROUP BY dt                                  -- dt로 그룹화

ORDER BY dt;          

 

 

PV, UV 계산하기

PV(Page View)	페이지 단위의 뷰 수	허수가 많기 때문에 주의 필요
Unique PV	PV에서 중복 뷰를 제거한 수
Visits	사이트에 방문한 총 사용자 수	세션 기준으로 집계
UV(Unique Visits)	Visits에서 중복을 제거한 방문자 수	특정 기간을 설정해 집계

UV와 PV가 모두 증가했다는 의미는 순방문자 수와 방문 페이지의 수가 늘었다는 것이므로 긍정적인 신호입니다.

UV가 증가했지만 PV가 감소했다면 자사 페이지가 매력적인 상품 또는 콘텐츠를 제공하지 못하고 있다는 의미일 수도 있습니다.

UV와 PV가 모두 감소한다는 것은 제품 또는 콘텐츠가 대중의 이목을 끌지 못하거나 만족스럽지 않은 서비스를 제공하고 있다는 부정적인 신호입니다

 

CREATE OR REPLACE TABLE `dev-aileron-447402-k9.modulabs.visits` (

 `log_id` INT64,

 `user_id` INT64,

 `page_url` STRING,

 `timestamp` TIMESTAMP

);

-- 데이터 삽입

INSERT INTO `dev-aileron-447402-k9.modulabs.visits` (`log_id`, `user_id`, `page_url`, `timestamp`)

VALUES

 (1, 101, 'home', TIMESTAMP '2023-10-31 10:00:00'),

 (2, 102, 'product_detail', TIMESTAMP '2023-10-31 10:15:00'),

 (3, 101, 'cart', TIMESTAMP '2023-10-31 10:30:00'),

 (4, 103, 'home', TIMESTAMP '2023-10-31 11:00:00'),

 (5, 104, 'order', TIMESTAMP '2023-10-31 11:15:00'),

 (6, 102, 'product_detail', TIMESTAMP '2023-10-31 11:30:00'),

 (7, 105, 'product_detail', TIMESTAMP '2023-10-31 12:00:00'),

 (8, 101, 'home', TIMESTAMP '2023-10-31 12:15:00'),

 (9, 102, 'cart', TIMESTAMP '2023-10-31 12:30:00'),

 (10, 104, 'order', TIMESTAMP '2023-10-31 13:00:00');

visits테이블

• 페이지별 PV를 구하는 

SELECT page_url,

    COUNT(*) AS page_views

FROM   `modulabs.visits`

GROUP BY page_url

ORDER BY page_views DESC;

페이지별 PV를 구하는

SELECT page_url,

    COUNT(DISTINCT user_id) AS unique_page_views

FROM `modulabs.visits`

GROUP BY page_url

ORDER BY unique_page_views DESC;

페이지 별 Unique PV를 구하는

SELECT page_url,

    COUNT(DISTINCT user_id) AS unique_visitors

FROM`modulabs.visits`

GROUP BY page_url

ORDER BY unique_visitors DESC;

페이지별 Unique Visitors를 구하는

ARPPU는 유료 결제 사용자당 평균 수익을 의미합니다. ARPU는 Average Revenue Per User의 약자입니다. ARPU는 사용자당 평균 수익을 의미합니다.

 

CREATE OR REPLACE TABLE `dev-aileron-447402-k9.modulabs.arpu` (

 user_id INT64,

 purchase_date DATE,

 revenue FLOAT64

);

INSERT INTO `dev-aileron-447402-k9.modulabs.arpu` (user_id, purchase_date, revenue) VALUES

(1, '2023-01-01', 10.00),

(2, '2023-01-01', 20.00),

(3, '2023-01-01', 0.00),

(1, '2023-01-02', 15.00),

(2, '2023-01-02', 0.00),

(3, '2023-01-02', 5.00),

(4, '2023-01-03', 20.00),

(5, '2023-01-03', 20.00),

(6, '2023-01-03', 0.00),

(1, '2023-01-04', 10.00),

(2, '2023-01-04', 25.00),

(7, '2023-01-04', 15.00),

(8, '2023-01-05', 40.00),

(5, '2023-01-05', 10.00),

(9, '2023-01-05', 0.00),

(10, '2023-01-06', 50.00),

(11, '2023-01-06', 35.00),

(6, '2023-01-06', 20.00),

(12, '2023-01-07', 15.00),

(13, '2023-01-07', 10.00),

(1, '2023-01-08', 5.00),

(14, '2023-01-08', 25.00),

(15, '2023-01-08', 30.00),

(16, '2023-01-09', 45.00),

(17, '2023-01-09', 0.00),

(18, '2023-01-10', 20.00),

(19, '2023-01-10', 35.00),

(20, '2023-01-11', 20.00),

(21, '2023-01-11', 25.00),

(22, '2023-01-12', 15.00);

arpu테이블

 

-- 테이블 생성

CREATE TABLE IF NOT EXISTS dev-aileron-447402-k9.modulabs.funnel (

 user_id INT64,

 action STRING,

 action_date DATE

);

-- 데이터 삽입

INSERT INTO dev-aileron-447402-k9.modulabs.funnel (user_id, action, action_date) VALUES

(4, 'visit', '2023-01-02'),

(5, 'visit', '2023-01-02'),

(6, 'visit', '2023-01-03'),

(4, 'signup', '2023-01-03'),

(5, 'add_to_cart', '2023-01-04'),

(6, 'purchase', '2023-01-04'),

(7, 'visit', '2023-01-04'),

(7, 'signup', '2023-01-05'),

(7, 'add_to_cart', '2023-01-06'),

(7, 'purchase', '2023-01-07'),

(8, 'visit', '2023-01-07'),

(8, 'signup', '2023-01-08'),

(8, 'add_to_cart', '2023-01-09'),

(9, 'visit', '2023-01-09'),

(9, 'signup', '2023-01-10'),

(10, 'visit', '2023-01-10'),

(10, 'add_to_cart', '2023-01-11'),

(10, 'purchase', '2023-01-12'),

(11, 'visit', '2023-01-12'),

(11, 'signup', '2023-01-13'),

(12, 'visit', '2023-01-13'),

(12, 'add_to_cart', '2023-01-14'),

(13, 'visit', '2023-01-14'),

(13, 'signup', '2023-01-15'),

(13, 'add_to_cart', '2023-01-16'),

(13, 'purchase', '2023-01-17'),

(14, 'visit', '2023-01-17'),

(14, 'signup', '2023-01-18'),

(15, 'visit', '2023-01-18'),

(15, 'signup', '2023-01-19'),

(15, 'add_to_cart', '2023-01-20'),

(15, 'purchase', '2023-01-21'),

(16, 'visit', '2023-01-21'),

(16, 'signup', '2023-01-22'),

(17, 'visit', '2023-01-22'),

(17, 'add_to_cart', '2023-01-23'),

(18, 'visit', '2023-01-23'),

(18, 'signup', '2023-01-24'),

(18, 'add_to_cart', '2023-01-25'),

(18, 'purchase', '2023-01-26'),

(19, 'visit', '2023-01-26'),

(19, 'signup', '2023-01-27'),

(20, 'visit', '2023-01-27'),

(20, 'add_to_cart', '2023-01-28'),

(21, 'visit', '2023-01-28'),

(21, 'signup', '2023-01-29'),

(21, 'add_to_cart', '2023-01-30'),

(21, 'purchase', '2023-01-31');

funnel테이블