Análise de ponta a ponta do Azure SQL + dbt + Github Actions + Metabase

Olá, Habr! Meu nome é Artemy Kozyr.

Nos últimos anos, acumulei uma vasta experiência com dados e com o que agora é chamado de Big Data .

Não muito tempo atrás, o interesse no campo de marketing na Internet e análise de ponta a ponta também aumentou , e não do zero. Meu amigo da agência de atuação me forneceu dados e casos de clientes reais, e aquipuladolá vamos nós.

Acontece bastante interessante: Azure SQL + dbt + Github Actions + Metabase.

Metade do sucesso em acertar a tarefa

Então, vamos tentar sem elogios e direto ao ponto. Retrato do cliente (este é para quem estamos prestando o serviço): o proprietário de uma loja online / rede de varejo / aplicativo móvel / plataforma educacional. Ele persegue os seguintes objetivos:

• Posicionar e promover seu produto; o crescimento do negócio
• Otimização dos canais de promoção: foco nas áreas que dão o melhor resultado
• Controle de despesas e indicadores de retorno para cada rublo investido
• Segmentação de usuários e construção de comunicação com eles

Em média, ele usa 4 grupos de serviços:

• CRM ( AmoCRM , Bitrix24 ) - leads, funis e vendas; ciclo de vida e atributos reais das transações
• (Yandex.Metrika, Google Analytics) — , , ,
• (Facebook, Google Adwords, Yandex.Direct) — , -, ,
• Call-tracking/Event tracking — , -

, : , , CRM, . . :

, , . :

• ()
• ( !)
• ( )
• ( )

()

- . : : ? ( 3:13).

myBI Connect. - , -. , :

1.

( ), , ETL-.

, Facebook (Campaigns), (Adsets) (Ads), , , , , ..

Facebook myBI Connect

2. //

.? .Clickhouse . , , , ( ).

JSON-, ( cron), (requests), .. .

myBI Connect .

.

3. Webhook ,

• CRM? ?
• ! Webhook .

. inhouse-CRM, XML:

# get data from XML endpoint with curl utility
curl "https://www.internal-crm.ru/order-list.xml?date-from=01-12-2020&date-to=31-12-2020" -o export.xml

# convert XML to JSON with xq utility
xq . export.xml > export.json

# parse JSON doc with jq utility
jq '[."order-list".date[] | .order[]]' export.json > parsed.json

# post to myBI Connect Webhook endpoint with curl utility
curl --header "Content-Type: application/json" \
 --request POST \
 --data @parsed.json \
 https://app.mybi.ru/webhook/23576/xhsfcxmlyh/
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

XML, JSON, , myBI Connect Webhook. shell-, .

4.

• . 3 (late arriving data)? , .
• “ ”.
• API.

, . .

(Data Modeling), . , , () - .

DWH: , ,

DWH :

• (1) — , , CRM
• (2) — (, );
• (3) — -, ;

dbt. git-, .sql ( ) .yaml (). dbt : Data Build Tool .

- : BigQuery, Redshift, Snowflake, Postgres, Spark, Presto. Azure SQL Database (managed SQL Server). , ; , -, ( ).

: git- (.sql) (.yaml)

DWH :

: -> -> ->

1. (Sources)

- , myBI Connect. .

2. (Staging)

( views), . :

• , UTM-
• : , ..
• ( )

3. (Auxiliary)

Aux . () :

• — , , ( )
• : , ..

4. (Marts)

— , . , - . . :

• , ,
• -: ,
• : full join,

— ?

— , .

— . , API, , Github Action, .

. . , ().

, , . , . Slack.

— . , . .

:

• ,
• , , -
• (x-ray)

Open Source BI Metabase (!). Amazon Elastic Beanstalk, :

• Docker-
• Postgres (AWS RDS)
• (Load Balancing) -Healthcheck
• Metabase

BI Metabase AWS Elastic Beanstalk

? ! , , . , .

1.

, . , .. .

. - — . .

(1) : , , { }, UTM- ( !), , . , , . , .

(2) -. - - php-, .

(3) , . , Google Adwords aud-, kwd-, pla-.

(4) , . !

, .

2. CRM

CRM, , . , , - . , , .

— , CRM-, ?

— : .

? , .

CRM

3. ( )

, , . . : . - :

from costs c
   full join conversions cv on
           c.[] = cv.[]
       and c.[ ] = cv.[ ]
       and c.[  ] = cv.[  ]
       and c.[  ] = cv.[  ]   
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

NULL? ( NULL = NULL).

: , -:

--      
{%- set key_field_list = [
       '[]',
       '[ ]',
       '[  ]',
       '[  ]'
   ]
-%}

--  -     
select

     {{ concat_key(key_field_list) }} as concat_key
   , {{ surrogate_key(key_field_list) }} as hash_key

...

--    :
from costs c
   full join conversions cv on c.hash_key = cv.hash_key
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. .

- ;

4.

, full join. , , , : CRM, , ..

-, , ( ). -, .

-, - , . -:

• meta_is_row_match (true/false) — ?
• meta_row_origin — (././AmoCRM)?

- is_match, row_origin

, CRM - ? . , , . :

   select 
       ...
       , sum(1) as []
       , sum(CASE WHEN [] LIKE '%%' THEN 1 ELSE 0 END) as [ -  ]
       , sum(CASE WHEN [ ] LIKE '%%' THEN 1 ELSE 0 END) as [ -  ]
       , sum(CASE WHEN [ ] LIKE '%%' THEN [] ELSE 0 END) as [ -  ]
       ...
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

5.

. . , API .. , ym:s:<AttributionModel>UTMSource:

ym:s:lastsignUTMSource --   
ym:s:firstUTMSource --  
ym:s:lastUTMSource --  
ym:s:last_yandex_direct_clickUTMSource --    

      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

6. ()

- . , .

Github Action

Github Action kzzzr / mybi-dbt-action — , Docker-, , , , . , , .

. . , , . .

, . — . , . . .

— ? ?

— 1 5 .

— , ?

— .

, TODO:

• : , API, Webhook
• , dbt CORE . .
• + + + (- -)
• ( + ),
• ( ) Github Actions
• , , (DAG)
• : Git-, DEV PROD.
• : , -, .

, . :

• — Continuous Integration & Data Tests. , .
• (Data Quality): , . , Wheely : : QA .
• 1- . , http://spasibo-metabase.eu-central-1.elasticbeanstalk.com/
• Apache Superset Metabase. , .
• - . : , .
• self-service BI. . , , , .
• : , , .
• . . ! .

, ?

pet-project, . :

• , . , ?
• . Private, public Open Source.
• . , - .
• . — .

Vou publicar notícias relacionadas a este projeto no canal de telegramas https://t.me/enthusiastech .

Fique ligado nas atualizações e tire suas dúvidas, terei prazer em respondê-las.

Obrigado pela atenção.