웹알못의 데이터 어드민 사이트 만들기(feat. Streamlit) 3. 오픈은 실전

두 번째로 만든 서비스는 'userSelector'였습니다.

이번에도 다짜고짜 코드 먼저 첨부합니다.

import streamlit as st
import pandas as pd
from datetime import datetime, timedelta

st.set_page_config(page_title="userSelector", page_icon="📝", layout='wide')
st.title("dat-a-dmin [userSelector]")

###

@st.cache(ttl=10800)
def get_categories():
    engine = db.ps_engine()
    categories = pd.read_sql("select ", engine)['category']
    return categories

@st.cache(ttl=10800)
def get_events():
    engine = db.ps_engine()
    events = pd.read_sql("select ", engine)['event']
    return events

@st.cache(ttl=10800)
def get_pagename():
    engine = db.ps_engine()
    pagename = pd.read_sql("select ", engine)['_name']
    return pagename

userTable = ''
logTable = ''
orderTable = ''
wishTable = ''
subscriptionTable = ''

d0_option = [
    '가입 시점',
    '최종 로그인 시점',
    'PV 횟수',
    '구매 횟수',
    '유입 경로'
    #...
]

depth0 = st.multiselect(
    '어떤 조건으로 유저를 추출할까요? (복수 선택)',
    d0_option
)

where_sentence = "\nwhere 1=1"
having_sentence='\ngroup by 1\nhaving 1=1'

signed_query=''
login_query=''
pv_query=''
order_query=''
utm_query=''
event_query=''
signed_join=''
login_join=''
pv_join=''
order_join=''
utm_join=''
event_join=''
extra_where='where 1=1'

for d0 in depth0:
    if '가입 시점' in d0:
        signed_where=''
        signed_at = st.date_input(
            '가입 기간을 선택하세요',
            [datetime.date(datetime.now() - timedelta(days=30)), datetime.date(datetime.now())]
        )
        signed_start = datetime.strftime(signed_at[0], '%Y-%m-%d')
        signed_end = datetime.strftime(signed_at[1], '%Y-%m-%d')
        signed_where += f"  \nand date(created_at_kst) between date'{signed_start}' and date'{signed_end}'"

        signed_query=f""", signed as (
    select *
    from {userTable}
    {where_sentence}
    {signed_where}  
)      
        """
        signed_join = "join signed s on u.user_id = s.user_id"
    
    if '최종 로그인 시점' in d0:
        login_where=''
        login_at = st.date_input(
            '최종 로그인 기간을 선택하세요',
            [datetime.date(datetime.now() - timedelta(days=30)), datetime.date(datetime.now())] 
        )
        login_start = datetime.strftime(login_at[0], '%Y-%m-%d')
        login_end = datetime.strftime(login_at[1], '%Y-%m-%d')
        login_where += f"   \nand date(created_at_kst) between date'{login_start}' and date'{login_end}'"

        login_query=f""", login as (
    select *
    from {userTable}
    {where_sentence}
    {login_where}  
)      
        """
        login_join = "join login l on u.user_id = l.user_id"
    
    if 'PV 횟수' in d0 or '구매 횟수' in d0 :
        log_where=''
        order_where=''
        wish_where=''
        log_having=''
        order_having=''
        wish_having=''

        d1_selector = st.selectbox(
            f'{d0} 기준을 선택하세요',
            ('전체', '특정 카테고리(depth 1)', '특정 상품')
        )

        date_selector = st.date_input(
            f'{d0} 기간을 선택하세요',
            [datetime.date(datetime.now() - timedelta(days=30)), datetime.date(datetime.now())] 
        )
        at_start = datetime.strftime(date_selector[0], '%Y-%m-%d')
        at_end = datetime.strftime(date_selector[1], '%Y-%m-%d')
        pymd_start = datetime.strftime(date_selector[0], '%Y%m%d')
        pymd_end = datetime.strftime(date_selector[1], '%Y%m%d')

        if 'PV' in d0:
            log_where += f" \nand event = 'Product Viewed'\nand date(timestamp_kst) between date'{at_start}' and date'{at_end}'\nand p_ymd between '{pymd_start}' and '{pymd_end}'"
        if '구매' in d0:
            order_where += f"   \nand date(created_at_kst) between date'{at_start}' and date'{at_end}'" 
       
        if d1_selector == '전체':
            pass
            
        if d1_selector == '특정 카테고리(depth 1)':
            categories = get_categories()
            category_selector = st.multiselect(
                f'{d0} 기준 카테고리준 선택하세요 (복수 선택)',
                categories
            )
            cate_list = []
            for c in category_selector:
                cate_list.append("'"+c+"'")
            category = ', '.join(cate_list)
            if 'PV' in d0:
                log_where += f" \nand json_extract_scalar(properties, '$.main_category') in ({category})"
            if '구매' in d0:
                order_where += f"   \nand depth1 in ({category})" 
            
        if d1_selector == '특정 상품':
            text_selector= st.text_input(
                f'{d0} 기준 상품 검색어를 입력하세요'
            )
            if 'PV' in d0:
                log_where += f" \nand json_extract_scalar(properties, '$.product_title') like '%%{text_selector}%%'"
            if '구매' in d0:
                order_where += f"   \nand product_title like '%%{text_selector}%%'"
                   
        score_selector = st.slider(
            f'{d0} 기준을 선택하세요',
            0, 200, (1, 10)
        )
        if score_selector == (0,0):
            if 'PV' in d0:
                pv_query=f""", pv as (
        select user_id, count(id) cnt
        from {logTable}
        {where_sentence}
        {log_where}
        {having_sentence}    
    )   
                """
                pv_join = "left join pv p on u.user_id = p.user_id"
                extra_where += "\nand p.user_id is null"

            if '구매' in d0:
                order_query=f""", orders as (
        select user_f_id, count(distinct order_id) cnt
        from {orderTable}
        {where_sentence}
        {order_where}
        {having_sentence}    
    )   
                """
                order_join = "left join orders o on u.user_id = o.user_id"
                extra_where += "\nand o.user_id is null"

        else:
            if 'PV' in d0:
                log_having += f"    \nand count(id) between {score_selector[0]} and {score_selector[1]}"
                pv_query=f""", pv as (
        select user_id, count(id) cnt
        from {logTable}
        {where_sentence}
        {log_where}
        {having_sentence}    
        {log_having} 
    )   
                """
                pv_join = "join pv p on u.user_id = p.user_id"

            if '구매' in d0:
                order_having += f"  \nand count(distinct order_id) between {score_selector[0]} and {score_selector[1]}"
                order_query=f""", orders as (
        select user_f_id, count(distinct order_id) cnt
        from {orderTable}
        {where_sentence}
        {order_where}
        {having_sentence}    
        {order_having} 
    )   
                """
                order_join = "join orders o on u.user_id = o.user_id"

    if '유입 경로' in d0:
        utm_where=''
        d1_selector = st.multiselect(
            '검색할 항목을 선택하세요 (복수 선택)',
            ['utm_source', 'utm_medium', 'utm_campaign', 'utm_content', 'utm_term']
        )
        utm_at = st.date_input(
            f'{d0} 기간을 선택하세요',
            [datetime.date(datetime.now() - timedelta(days=30)), datetime.date(datetime.now())]  
        )
        at_start = datetime.strftime(utm_at[0], '%Y-%m-%d')
        at_end = datetime.strftime(utm_at[1], '%Y-%m-%d')
        pymd_start = datetime.strftime(utm_at[0], '%Y%m%d')
        pymd_end = datetime.strftime(utm_at[1], '%Y%m%d')
        
        utm_where += f" \nand p_ymd between '{pymd_start}' and '{pymd_end}' and date(timestamp_kst) between date'{at_start}' and date'{at_end}'"

        for utm_d1 in d1_selector:
            d2_input = st.text_input(
                f'{utm_d1} 검색어를 입력하세요'  
            )
            utm_where += f" \nand {utm_d1} like '%%{d2_input}%%'"

        utm_query = f""", utm as (
    select *
    from {logTable}
    {where_sentence}
    {utm_where}
)
        """
        utm_join = "join utm t on u.user_id = t.user_id"

    if '특정 로그 경험' in d0:
        event_where=''
        event_at = st.date_input(
            f'{d0} 기간을 선택하세요',
            [datetime.date(datetime.now() - timedelta(days=30)), datetime.date(datetime.now())]  
        )
        at_start = datetime.strftime(event_at[0], '%Y-%m-%d')
        at_end = datetime.strftime(event_at[1], '%Y-%m-%d')
        pymd_start = datetime.strftime(event_at[0], '%Y%m%d')
        pymd_end = datetime.strftime(event_at[1], '%Y%m%d')
        
        event_where += f"   \nand p_ymd between '{pymd_start}' and '{pymd_end}' and date(timestamp_kst) between date'{at_start}' and date'{at_end}'"

        event_type = {
            'Page/Screen Viewed':"\"type\" in ('page', 'screen')",
            'Track Event':"\"type\" in ('track')",
            'Identify':"\"type\" in ('identify')"
        }
        d1_selector = st.multiselect(
            '검색할 로그의 유형을 선택하세요 (복수 선택)',
            ['Page/Screen Viewed', 'Track Event', 'Identify']
        ) 
        for d1_type in d1_selector:
            event_where += f'\nand {event_type.get(d1_type)}'

            if 'Page/Screen' in d1_type:
                page_selector = st.multiselect(
                    f'{d1_type} 로그를 선택하세요 (복수 선택)',
                    get_pagename()
                )
                page_list = []
                for p in page_selector:
                    page_list.append("'"+p+"'")
                event_where += f"\nand event in ({', '.join(page_list)})"

            if 'Track Event' in d1_type:
                event_selector = st.multiselect(
                    f'{d1_type} 로그를 선택하세요 (복수 선택)',
                    get_events()
                )
                event_list = []
                for e in event_selector:
                    event_list.append("'"+e+"'")
                event_where += f"\nand event in ({', '.join(event_list)})"    

        event_query = f""", event as (
    select *
    from {logTable}
    {where_sentence}
    {event_where}
)
        """
        event_join = "join event e on u.user_id = e.user_id"    

try:
    final_query = f"""
with
users as (
    select user_id
    from {userTable}
)
{signed_query}
{login_query}
{pv_query}
{order_query}
{wish_query}
{subscription_query}
{utm_query}
{event_query}
select distinct u.user_id
from users u
{signed_join}
{login_join}
{pv_join}
{order_join}
{wish_join}
{subscription_join}
{utm_join}
{event_join}
{extra_where}
    """.replace('\n\n', '\n').replace("%%", "%")


    st.subheader("쿼리 보기 (복사하세요!)")
    query = st.code(
        final_query,
        language='sql'
    )
    
    st.markdown("""
        <head>
            <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.0.0/dist/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        </head>
        <body>
            <a href="https://redash/queries/new" target = "_blank" class="btn btn-outline-primary" role="button">리대시에 쿼리 붙여넣기</a>
        </body>
        """, unsafe_allow_html=True)

except Exception as e:
    st.error(e)
    pass

 

화면으로 보면 아래와 같습니다.

유저를 추출할 조건을 도메인에 따라 구성하고,

해당 기준에 맞는 쿼리를 모듈화해서 조립해 주는 간단한 기능입니다.

 

타겟은 CRM, CX, UX 등 특정 세그먼트의 유저를 찾고 싶어하는 구성원들입니다.

 

이렇게 지난 글에서 이야기한 queryMaker와 이번 userSelector, 두 가지 서비스만 탑재하고

급 회사 메신저를 통해 공개해 버렸습니다.

목적은 '이런 것이 있습니다' 라는 안내와 아이디어 피드백을 받기 위한 것이었는데요,

 

다양한 분들이 좋은 반응을 보여주셨지만

예상치 못하게.. 사실은 조금 예상했던 피드백을 하나 받게 됩니다.

 

"데이터 구조가 다 드러나는데 보안은 괜찮나요?"

 

조금은 예상했기 때문에 보안 문제를 피하기 위해 직접 데이터 결과를 보여주지도 않고,
구글 로그인이 필요한 리대시로 쿼리만 들려 보내는 서비스로 구상했지만

'데이터 구조' 역시 회사의 중요 정보라는 사실을 간과했던 것이었습니다.

데이터 마트의 주요 테이블 이름과 필드들이 모두 노출되는 것은 사실이기에 바로 보완하기로 하고

그렇게 데이터 어드민은 phase 2로 접어들게 됩니다.

 

다음 편에서는 보안과 새로운 서비스에 대해 이야기해 보겠습니다.