Analyse de bout en bout Azure SQL + actions dbt + Github + métabase

Bonjour, Habr! Je m'appelle Artemy Kozyr.

Ces dernières années, j'ai accumulé une expérience assez étendue avec les données et ce que l'on appelle maintenant le Big Data .

Il n'y a pas si longtemps, l'intérêt pour le domaine du marketing Internet et de l'analyse de bout en bout a également explosé , et non à partir de zéro. Mon ami de l'agence par intérim m'a fourni des données et des cas de vrais clients, et iciignoréloin nous allons.

Cela s'avère assez intéressant: Azure SQL + dbt + Github Actions + Metabase.

La moitié du succès pour faire la bonne tâche

Alors, essayons sans louanges et droit au but. Portrait du client (c'est celui pour qui nous faisons le service): le propriétaire d'une boutique en ligne / réseau de vente au détail / application mobile / plateforme éducative. Il poursuit les objectifs suivants:

• Positionnement et promotion de votre produit; croissance des affaires
• Optimisation des canaux de promotion: concentrez-vous sur les domaines qui donnent le meilleur résultat
• Contrôle des dépenses et des indicateurs de rendement pour chaque rouble investi
• Segmentation des utilisateurs et renforcement de la communication avec eux

En moyenne, il utilise 4 groupes de services:

• CRM ( AmoCRM , Bitrix24 ) - prospects, entonnoirs et ventes; cycle de vie et attributs réels des transactions
• (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.
• . , - .
• . — .

Je publierai des nouvelles liées à ce projet dans la chaîne de télégramme https://t.me/enthusiastech .

Restez à l'affût des mises à jour et posez des questions, je serai ravi d'y répondre.

Merci de votre attention.