👨🏾‍🎓 👰 👨‍💻 Análisis de extremo a extremo de Azure SQL + dbt + acciones de Github + metabase 🚊 👨‍👨‍👦 🤟🏾

Título

¡Hola, Habr! Mi nombre es Artemy Kozyr.

En los últimos años, he acumulado una experiencia bastante amplia con los datos y lo que ahora se llama Big Data .

No hace mucho tiempo, el interés en el campo del marketing en Internet y el análisis de extremo a extremo también se ha disparado , y no desde cero. Mi amigo de la agencia de actuación me facilitó datos y casos de clientes reales, y aquí~~omitido~~Vamos lejos.

Resulta bastante interesante: Azure SQL + dbt + Github Actions + Metabase.

La mitad del éxito en hacer bien la tarea

Entonces, intentemos sin elogios y directo al grano. Retrato del cliente (este es para quien estamos prestando el servicio): el propietario de una tienda en línea / red minorista / aplicación móvil / plataforma educativa. Persigue los siguientes objetivos:

Posicionamiento y promoción de su producto; el crecimiento del negocio
Optimización de canales de promoción: foco en áreas que den el mejor resultado
Control de indicadores de gastos y rentabilidad por cada rublo invertido
Segmentación de usuarios y construcción de comunicación con ellos

En promedio, utiliza 4 grupos de servicios:

CRM ( AmoCRM , Bitrix24 ): clientes potenciales, embudos y ventas; ciclo de vida y atributos reales de las transacciones
(Yandex.Metrika, Google Analytics) — , , ,
(Facebook, Google Adwords, Yandex.Direct) — , -, ,
Call-tracking/Event tracking — , -

, : , , CRM, . . :

, , . :

()
( !)
( )
( )

()

- . : : ? ( 3:13).

myBI Connect. - , -. , :

( ), , ETL-.

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

Facebook myBI Connect

2. //

.? .Clickhouse . , , , ( ).

API .

https://api-metrika.yandex.net/stat/v1/data.csv
   ?ids=55254416
   &dimensions=ym:s:date, ym:s:UTMSource, ym:s:UTMMedium, ym:s:UTMCampaign
   &metrics=ym:s:visits, ym:s:ecommercePurchases, ym:s:ecommerceRevenue
   &date1=2020-12-01
   &date2=2020-12-31
   &group=day
   &lang=en
   &accuracy=full
   &sort=ym:s:date
   &limit=100000
   &pretty=true

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-, .

. 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) : , , { }, 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

. .

- ;

, 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 [ -  ]
       ...

. . , 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.
. , - .
. — .

Publicaré noticias relacionadas con este proyecto en el canal de telegramas https://t.me/enthusiastech .

Esté atento a las actualizaciones y haga preguntas, estaré encantado de responderlas.

Gracias por su atención.

Análisis de extremo a extremo de Azure SQL + dbt + acciones de Github + metabase

La mitad del éxito en hacer bien la tarea

()

, ?

More articles: