Halaman ini diterjemahkan oleh Cloud Translation API.

Membangun pipeline ELT untuk data analisis pemasaran

Tutorial ini menunjukkan cara menyiapkan alur kerja ELT yang mengekstrak, memuat, dan mentransformasi data analisis pemasaran di BigQuery.

Alur kerja ELT standar secara berkala mengekstrak data pelanggan baru dari sumber data Anda dan memuatnya ke BigQuery. Data tidak terstruktur kemudian diproses menjadi metrik yang bermakna. Dalam tutorial ini, Anda akan membuat alur kerja ELT dengan menyiapkan transfer data analisis pemasaran menggunakan BigQuery Data Transfer Service. Kemudian, Anda menjadwalkan Dataform untuk menjalankan transformasi berkala pada data.

Dalam tutorial ini, Anda menggunakan Google Ads sebagai sumber data, tetapi Anda dapat menggunakan salah satu sumber data yang didukung oleh BigQuery Data Transfer Service.

Sebelum memulai

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Roles required to select or create a project

Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
Create a project: To create a project, you need the Project Creator (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

Peran yang diperlukan

Untuk mendapatkan izin yang Anda perlukan untuk menyelesaikan tutorial ini, minta administrator Anda untuk memberi Anda peran IAM berikut di project:

Admin BigQuery (roles/bigquery.admin)
Dataform Admin (roles/dataform.admin)

Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, baca artikel Mengelola akses ke project, folder, dan organisasi.

Anda mungkin juga bisa mendapatkan izin yang diperlukan melalui peran khusus atau peran bawaan lainnya.

Menjadwalkan transfer data berulang

Agar BigQuery tetap mendapatkan data pemasaran terbaru dari sumber data Anda, siapkan transfer data berulang menggunakan BigQuery Data Transfer Service untuk mengekstrak dan memuat data sesuai jadwal.

Dalam tutorial ini, Anda akan menggunakan Google Ads sebagai contoh sumber data. Untuk mengetahui daftar lengkap sumber data yang didukung oleh BigQuery Data Transfer Service, lihat Sumber data yang didukung.

Buka halaman Transfer data di konsol Google Cloud .

Buka Data transfers
Klik Create transfer.
Di bagian Source type, untuk Source, pilih Google Ads.
Di bagian Data source details:
1. Untuk Customer ID, masukkan ID pelanggan Google Ads Anda.
2. Untuk Jenis laporan, pilih Standar. Laporan standar mencakup kumpulan laporan dan kolom standar seperti yang dijelaskan dalam transformasi laporan Google Ads.
  - Untuk Refresh window, masukkan 5.
Di bagian Destination settings, untuk Dataset, pilih set data yang Anda buat untuk menyimpan data Anda.
Di bagian Transfer config name, untuk Display name, masukkan Marketing tutorial.
Di bagian Schedule options:
- Untuk Frekuensi berulang, pilih Hari.
- Untuk At, masukkan 08:00.
Klik Simpan.

Setelah Anda menyimpan konfigurasi, BigQuery Data Transfer Service akan memulai transfer data. Berdasarkan setelan dalam konfigurasi transfer, transfer data berjalan sekali setiap hari pada pukul 08.00 UTC dan mengekstrak data dari Google Ads selama lima hari terakhir.

Anda dapat memantau tugas transfer yang sedang berlangsung untuk memeriksa status setiap transfer data.

Membuat kueri data tabel

Saat data Anda ditransfer ke BigQuery, data tersebut akan ditulis ke tabel berpartisi berdasarkan waktu penyerapan. Untuk informasi selengkapnya, lihat Pengantar tabel berpartisi.

Jika membuat kueri tabel secara langsung, bukan menggunakan tabel virtual yang dihasilkan secara otomatis, Anda harus menggunakan kolom semu _PARTITIONTIME dalam kueri. Untuk mengetahui informasi selengkapnya, lihat Membuat kueri tabel berpartisi.

Bagian berikut menunjukkan contoh kueri yang dapat Anda gunakan untuk memeriksa data yang ditransfer.

Performa kampanye

Contoh kueri berikut menganalisis performa kampanye Google Ads selama 30 hari terakhir.

Konsol

SELECT
  c.customer_id,
  c.campaign_name,
  c.campaign_status,
  SUM(cs.metrics_impressions) AS Impressions,
  SUM(cs.metrics_interactions) AS Interactions,
  (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
FROM
  `DATASET.ads_Campaign_CUSTOMER_ID` c
LEFT JOIN
  `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
ON
  (c.campaign_id = cs.campaign_id
  AND cs._DATA_DATE BETWEEN
  DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3
ORDER BY
  Impressions DESC

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.customer_id,
    c.campaign_name,
    c.campaign_status,
    SUM(cs.metrics_impressions) AS Impressions,
    SUM(cs.metrics_interactions) AS Interactions,
    (SUM(cs.metrics_cost_micros) / 1000000) AS Cost
  FROM
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  LEFT JOIN
    `DATASET.ads_CampaignBasicStats_CUSTOMER_ID` cs
  ON
    (c.campaign_id = cs.campaign_id
    AND cs._DATA_DATE BETWEEN
    DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
  WHERE
    c._DATA_DATE = c._LATEST_DATE
  GROUP BY
    1, 2, 3
  ORDER BY
    Impressions DESC'

Ganti kode berikut:

DATASET: nama set data yang Anda buat untuk menyimpan tabel yang ditransfer
CUSTOMER_ID: ID Pelanggan Google Ads Anda.

Jumlah kata kunci

Contoh kueri berikut menganalisis kata kunci berdasarkan kampanye, grup iklan, dan status kata kunci. Kueri ini menggunakan fungsi KeywordMatchType. Jenis pencocokan kata kunci membantu menentukan penelusuran mana yang dapat memicu iklan Anda. Untuk informasi selengkapnya tentang opsi pencocokan kata kunci, lihat Tentang opsi pencocokan kata kunci.

Konsol

  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
    JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4

bq

  bq query --use_legacy_sql=false '
  SELECT
    c.campaign_status AS CampaignStatus,
    a.ad_group_status AS AdGroupStatus,
    k.ad_group_criterion_status AS KeywordStatus,
    k.ad_group_criterion_keyword_match_type AS KeywordMatchType,
    COUNT(*) AS count
  FROM
    `DATASET.ads_Keyword_CUSTOMER_ID` k
  JOIN
    `DATASET.ads_Campaign_CUSTOMER_ID` c
  ON
    (k.campaign_id = c.campaign_id AND k._DATA_DATE = c._DATA_DATE)
  JOIN
    `DATASET.ads_AdGroup_CUSTOMER_ID` a
  ON
    (k.ad_group_id = a.ad_group_id AND k._DATA_DATE = a._DATA_DATE)
  WHERE
    k._DATA_DATE = k._LATEST_DATE
  GROUP BY
    1, 2, 3, 4'

Ganti kode berikut:

DATASET: nama set data yang Anda buat untuk menyimpan tabel yang ditransfer
CUSTOMER_ID: ID Pelanggan Google Ads Anda.

Membuat repositori Dataform

Setelah membuat konfigurasi transfer data untuk mentransfer data terbaru dari Google Ads, siapkan Dataform untuk mentransformasi data analisis pemasaran Anda secara rutin. Dataform memungkinkan Anda menjadwalkan transformasi data reguler, dan memungkinkan Anda menentukan transformasi ini dengan SQL sambil berkolaborasi dengan analis data lain.

Buat repositori Dataform untuk menyimpan kueri SQLX yang membentuk kode transformasi Anda.

Di konsol Google Cloud , buka halaman Dataform.

Buka Dataform
Klik Buat repositori.
Di halaman Create repository, lakukan hal berikut:
1. Di kolom Repository ID, masukkan marketing-tutorial-repository.
2. Di daftar Region, pilih region.
3. Klik Buat.

Repositori marketing-tutorial-repository kini muncul di daftar repositori Dataform Anda.

Untuk mengetahui informasi selengkapnya tentang repositori Dataform, lihat Tentang repositori Dataform.

Membuat dan melakukan inisialisasi ruang kerja pengembangan Dataform

Buat ruang kerja pengembangan Dataform agar Anda dapat mengerjakan kode transformasi dalam repositori sebelum Anda melakukan commit dan mengirimkan perubahan ke repositori.

Di konsol Google Cloud , buka halaman Dataform.

Buka Dataform
Klik marketing-tutorial-repository.
Klik Buat ruang kerja pengembangan.
Di jendela Create development workspace, lakukan hal berikut:
1. Di kolom Workspace ID, masukkan marketing-tutorial-workspace.
2. Klik Buat.
Halaman ruang kerja pengembangan akan muncul.
Klik Initialize workspace.

Ruang kerja pengembangan marketing-tutorial-workspace kini muncul di repositori marketing-tutorial-repository Anda di tab Ruang Kerja Pengembangan, beserta dua contoh file di direktori definitions yang disebut *first_view.sqlx dan *second_view.sqlx.

Untuk mengetahui informasi selengkapnya tentang ruang kerja pengembangan Dataform, lihat Ringkasan ruang kerja pengembangan.

Mendeklarasikan tabel Google Ads sebagai sumber tabel

Hubungkan tabel Google Ads yang baru ditransfer ke Dataform dengan mendeklarasikannya sebagai sumber data dengan mengikuti langkah-langkah berikut:

Membuat file SQLX untuk deklarasi sumber data

Di Dataform, Anda mendeklarasikan tujuan sumber data dengan membuat file SQLX di direktori definitions/:

Di konsol Google Cloud , buka halaman Dataform.

Buka halaman Dataform
Pilih marketing-tutorial-repository.
Pilih marketing-tutorial-workspace.
Di panel Files, di samping definitions/, klik menu More.
Klik Create file.
Di panel Create new file, lakukan hal berikut:
1. Di kolom Add a file path, setelah definitions/, masukkan nama definitions/googleads-declaration.sqlx.
2. Klik Create file.

Mendeklarasikan sumber data

Edit definitions/googleads-declaration.sqlx untuk mendeklarasikan tabel Google Ads yang ditransfer sebagai sumber data. Contoh ini mendeklarasikan tabel ads_Campaign sebagai sumber data:

Di ruang kerja pengembangan Anda, di panel Files, klik file SQLX Anda untuk deklarasi sumber data.

Di file tersebut, masukkan cuplikan kode berikut:

    config {
        type: "declaration",
        database: "PROJECT_ID",
        schema: "DATASET",
        name: "ads_Campaign_CUSTOMER_ID",
    }

Tentukan transformasi Anda

Tentukan transformasi data Anda dengan membuat file SQLX di direktori definitions/. Dalam tutorial ini, Anda akan membuat transformasi harian yang menggabungkan metrik seperti klik, tayangan iklan, biaya, dan konversi menggunakan file bernama daily_performance.sqlx.

Buat file SQLX transformasi

Di panel Files, di samping definitions/, klik menu More, lalu pilih Create file.
Di kolom Add a file path, masukkan definitions/daily_performance.sqlx.
Klik Create file.

Tentukan file SQLX transformasi

Di panel Files, luaskan direktori definitions/.

Pilih daily_performance.sqlx, lalu masukkan kueri berikut:

    config {
        type: "table",
        schema: "reporting",
        tags: ["daily", "google_ads"]
    }
    SELECT
        date,
        campaign_id,
        campaign_name,
    SUM(clicks) AS total_clicks
    FROM
        `ads_Campaign_CUSTOMER_ID`
    GROUP BY
        date,
        campaign_id,
        campaign_name
        ORDER BY
        date DESC

Commit dan kirim perubahan Anda

Setelah melakukan perubahan di ruang kerja pengembangan, Anda dapat melakukan commit dan mengirim perubahan ini ke repositori dengan mengikuti langkah-langkah berikut:

Di ruang kerja marketing-tutorial-workspace, klik Terapkan 1 perubahan.
Di panel New commit, masukkan deskripsi commit di kolom Add a commit message.
Klik Commit all changes.
Di ruang kerja marketing-tutorial-workspace, klik Push to default branch.

Setelah perubahan berhasil di-push ke repositori Anda, pesan Workspace is up to date akan muncul.

Menjadwalkan transformasi data

Setelah menentukan file transformasi data, jadwalkan transformasi data.

Membuat rilis produksi

Rilis produksi di Dataform memastikan lingkungan Anda diperbarui secara konsisten dengan hasil transformasi data. Langkah-langkah berikut menunjukkan cara menentukan cabang main dari repositori marketing-tutorial-repository untuk menyimpan transformasi data Anda:

Di konsol Google Cloud , buka halaman Dataform.

Buka halaman Dataform
Pilih marketing-tutorial-repository.
Klik tab Rilis & penjadwalan.
Klik Buat rilis produksi.
Di panel Create release configuration, konfigurasi setelan berikut:
1. Di kolom Release ID, masukkan transformations.
2. Di kolom Git commitish, gunakan nilai default main .
3. Di bagian Frekuensi jadwal, pilih Sesuai permintaan.
Klik Buat.

Membuat konfigurasi alur kerja

Setelah membuat rilis produksi, Anda dapat membuat konfigurasi alur kerja yang menjalankan transformasi data sesuai jadwal yang ditentukan di repositori Anda. Langkah-langkah berikut menunjukkan cara menjadwalkan transformasi harian dari file transformations:

Di konsol Google Cloud , buka halaman Dataform.

Buka halaman Dataform
Pilih marketing-tutorial-repository.
Klik tab Rilis & penjadwalan.
Di bagian Konfigurasi alur kerja, klik Buat.
Di panel Create workflow configuration, di kolom Configuration ID, masukkan transformations.
Di menu Konfigurasi rilis, pilih transformations.
Di bagian Authentication, pilih Execute with user credentials

Di bagian Frekuensi jadwal, lakukan tindakan berikut:

1. Select **Repeat**.
1. For **Repeats**, select `Daily`.
1. For **At time**, enter `10:00 AM`.
1. For **Timezone**, select `Coordinated Universal Time (UTC)`.

Klik Selection of tags.
Di kolom Pilih tag untuk dieksekusi, pilih Harian.
Klik Buat.

Konfigurasi alur kerja yang telah Anda buat menjalankan seluruh hasil kompilasi terbaru yang dibuat oleh konfigurasi rilis transformations.

Pembersihan

Agar akun Google Cloud Anda tidak dikenai biaya untuk resource yang digunakan di halaman ini, ikuti langkah-langkah berikut.

Hapus set data yang dibuat di BigQuery

Untuk menghindari biaya aset BigQuery, hapus set data yang disebut dataform.

Di konsol Google Cloud , buka halaman BigQuery.

Buka BigQuery
Di panel Explorer, luaskan project Anda dan pilih dataform.
Klik menu Tindakan , lalu pilih Hapus.
Pada dialog Delete dataset, masukkan delete ke dalam kolom, lalu klik Delete.

Menghapus ruang kerja dan konfigurasi pengembangan Dataform

Pembuatan ruang kerja pengembangan Dataform tidak dikenai biaya, tetapi untuk menghapus ruang kerja pengembangan, Anda dapat mengikuti langkah-langkah berikut:

Di konsol Google Cloud , buka halaman Dataform.

Buka Dataform
Klik quickstart-repository.
Klik tab Rilis & penjadwalan.
Di bagian Konfigurasi rilis, klik menu Lainnya di samping konfigurasi production, lalu klik Hapus.
Di bagian Konfigurasi alur kerja, klik menu Lainnya di samping konfigurasi transformations, lalu klik Hapus.
Di tab Ruang kerja pengembangan, klik menu Lainnya dengan quickstart-workspace, lalu pilih Hapus.
Untuk mengonfirmasi, klik Hapus.

Menghapus repositori Dataform

Pembuatan repositori Dataform tidak menimbulkan biaya, tetapi untuk menghapus repositori, Anda dapat mengikuti langkah-langkah berikut:

Di konsol Google Cloud , buka halaman Dataform.

Buka Dataform
Dengan quickstart-repository, klik menu Lainnya, lalu pilih Hapus.
Di jendela Hapus repositori, masukkan nama repositori untuk mengonfirmasi penghapusan.
Untuk mengonfirmasi, klik Hapus.