🤰🏼 🚑 🖌️ Azure SQL Analisis Ujung-ke-Ujung + dbt + Tindakan Github + Metabase #⃣ 🍀 💒

Judul

Halo, Habr! Nama saya Artemy Kozyr.

Dalam beberapa tahun terakhir, saya telah mengumpulkan pengalaman yang cukup luas dengan data dan apa yang sekarang disebut Big Data .

Belum lama berselang, minat pada bidang pemasaran Internet dan Analisis End-to-End juga meningkat , dan bukan dari awal. Teman saya dari agen akting memberi saya data dan kasus klien nyata, dan di sini~~dilewati~~kita pergi.

Ternyata cukup menarik: Azure SQL + dbt + Github Actions + Metabase.

Setengah dari keberhasilan menyelesaikan tugas dengan benar

Jadi, mari kita coba tanpa pujian dan langsung ke intinya. Potret klien (ini yang akan kami layani): pemilik toko online / jaringan ritel / aplikasi seluler / platform pendidikan. Itu mengejar tujuan berikut:

Memposisikan dan mempromosikan produk Anda; pertumbuhan bisnis
Optimalisasi saluran promosi: fokus pada area yang memberikan hasil terbaik
Kontrol pengeluaran dan indikator pengembalian untuk setiap rubel yang diinvestasikan
Segmentasi pengguna dan membangun komunikasi dengan mereka

Rata-rata, dia menggunakan 4 kelompok layanan:

CRM ( AmoCRM , Bitrix24 ) - prospek, saluran, dan penjualan; siklus hidup dan atribut transaksi yang sebenarnya
(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.
. , - .
. — .

Saya akan mempublikasikan berita terkait proyek ini di saluran telegram https://t.me/enthusiastech .

Pantau terus untuk pembaruan dan ajukan pertanyaan, saya akan dengan senang hati menjawabnya.

Terima kasih atas perhatian Anda.

Azure SQL Analisis Ujung-ke-Ujung + dbt + Tindakan Github + Metabase

Setengah dari keberhasilan menyelesaikan tugas dengan benar

()

, ?

More articles: