Clever Geek Handbook

AzureSQLエンドツーエンド分析+ dbt + Githubアクション+メタベース

題名







こんにちは、Habr！私の名前はArtemyKozyrです。







近年、私はデータと現在のビッグデータと呼ばれるものに関して非常に幅広い経験を積み重ねてきました。







少し前までは、インターネットマーケティングとエンドツーエンド分析の分野への関心も、ゼロからではなく、急上昇しました。現在の代理店の友人から、実際のクライアントのデータとケースが提供されました。スキップしました離れて行きます。

それは非常に興味深いことがわかりました：Azure SQL + dbt + Github Actions + Metabase。







タスクを正しく行うための成功の半分



だから、賞賛せずに、ポイントにまっすぐに試してみましょう。クライアントの肖像（これは私たちがサービスを行っているものです）：オンラインストア/小売ネットワーク/モバイルアプリケーション/教育プラットフォームの所有者。それは以下の目標を追求します：







  • 製品の位置付けと宣伝。ビジネスの成長
  • プロモーションチャネルの最適化：最良の結果が得られる領域に焦点を当てる
  • 投資された各ルーブルの費用と収益指標の管理
  • ユーザーのセグメンテーションとユーザーとのコミュニケーションの構築


平均して、彼は4つのサービスグル​​ープを使用しています。







  • CRM（AmoCRM、Bitrix24）-リード、ファネル、セールス。トランザクションのライフサイクルと実際の属性
  • (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 myBIConnectの詳細なレイヤー図

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 .







Yandex.Metricaからのカスタムアップロード

.







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.









, . .









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







基本的なDWHブロック：ソース、詳細レイヤー、データマート

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). , ; , -, ( ).







プロジェクト構造：コード（.sql）と構成（.yaml）を含むgitリポジトリ

: 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


AWS ElasticBeanstalkクラウドにBIメタベースを生産的に展開する

BI Metabase AWS Elastic Beanstalk









? ! , , . , .







1.







, . , .. .

. - — . .







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







(2) -. - - php-, .







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







(4) , . !







その後の識別子の解析のためにマークアップの特性を考慮に入れる例







, .







2. CRM







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は、問題の原因を見つけるのに役立ちます

- 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アクションによるデータマートの自動化

Github Action







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









. . , , . .







, . — . , . . .







— ? ?

— 1 5 .

— , ?

— .

, TODO:







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




, . :









, ?



pet-project, . :







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


このプロジェクトに関連するニュースを電報チャンネルhttps://t.me/enthusiastechで公開します。







最新情報をお楽しみに、質問をしてください。喜んでお答えします。







清聴ありがとうございました。







More articles:


All Articles