יצירת צינור ELT לנתוני ניתוח שיווקי

במדריך הזה נסביר איך להגדיר תהליך עבודה של ELT שמחלץ, טוען ומשנה נתוני ניתוח שיווקי ב-BigQuery.

בתהליך עבודה טיפוסי של ELT, נתונים חדשים של לקוחות מחולצים ממקור הנתונים שלכם ונטענים ל-BigQuery. לאחר מכן, הנתונים הלא מובנים עוברים עיבוד והופכים למדדים בעלי משמעות. במדריך הזה תיצרו תהליך עבודה של ELT על ידי הגדרת העברת נתונים של ניתוח שיווקי באמצעות שירות העברת הנתונים ל-BigQuery. לאחר מכן, מתזמנים את Dataform להפעלת טרנספורמציות תקופתיות על הנתונים.

במדריך הזה משתמשים ב-Google Ads כמקור נתונים, אבל אפשר להשתמש בכל אחד ממקורות הנתונים שנתמכים על ידי שירות העברת הנתונים ל-BigQuery.

לפני שמתחילים

נכנסים לחשבון Google Cloud . אם אתם משתמשים חדשים ב- Google Cloud, צרו חשבון כדי שתוכלו להעריך את הביצועים של המוצרים שלנו בתרחישים מהעולם האמיתי. לקוחות חדשים מקבלים בחינם גם קרדיט בשווי 300$ להרצה, לבדיקה ולפריסה של עומסי העבודה.

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 role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

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 role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

Go to project selector

Verify that billing is enabled for your Google Cloud project.

התפקידים הנדרשים

כדי לקבל את ההרשאות שדרושות להשלמת המדריך הזה, צריך לבקש מהאדמין להקצות לכם את תפקידי ה-IAM הבאים בפרויקט:

• אדמין של BigQuery (roles/bigquery.admin)
• אדמין ב-Dataform (roles/dataform.admin)

להסבר על מתן תפקידים, ראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

יכול להיות שאפשר לקבל את ההרשאות הנדרשות גם באמצעות תפקידים בהתאמה אישית או תפקידים מוגדרים מראש.

תזמון העברות נתונים חוזרות

כדי שנתוני השיווק העדכניים ממקור הנתונים שלכם יהיו זמינים ב-BigQuery, אתם יכולים להגדיר העברות נתונים חוזרות באמצעות שירות העברת הנתונים ל-BigQuery. כך תוכלו לחלץ ולטעון נתונים לפי לוח זמנים.

במדריך הזה, Google Ads משמש כמקור הנתונים לדוגמה. רשימה מלאה של מקורות הנתונים שנתמכים על ידי שירות העברת הנתונים ל-BigQuery זמינה במאמר מקורות נתונים נתמכים.

1. עוברים לדף 'העברות נתונים' במסוף Google Cloud .

   מעבר אל 'העברות נתונים'

2. לוחצים על Create transfer (יצירת העברה).

3. בקטע Source type (סוג המקור), בוחרים באפשרות Google Ads בשדה Source (מקור).

4. בקטע פרטי מקור הנתונים:

   1. בשדה מספר לקוח, מזינים את מספר הלקוח ב-Google Ads.
   2. בקטע סוג הדוח, בוחרים באפשרות רגיל. הדוח הרגיל כולל את קבוצת הדוחות והשדות הרגילה, כפי שמפורט במאמר בנושא טרנספורמציה של דוחות Google Ads.
      • בשדה חלון רענון, מזינים 5.

5. בקטע הגדרות יעד, בשדה מערך נתונים, בוחרים את מערך הנתונים שיצרתם לאחסון הנתונים.

6. בקטע Transfer config name, בשדה Display name, מזינים Marketing tutorial.

7. בקטע אפשרויות תזמון:

   • בקטע תדירות חזרה, בוחרים באפשרות ימים.
   • בשדה At (בשעה), מזינים 08:00.

8. לוחצים על Save.

אחרי ששומרים את ההגדרה, שירות העברת הנתונים ל-BigQuery מתחיל את העברת הנתונים. בהתאם להגדרות בהגדרת ההעברה, העברת הנתונים מתבצעת פעם ביום בשעה 8:00 (שעון UTC) ומחלצת נתונים מ-Google Ads מ-5 הימים האחרונים.

אתם יכולים לעקוב אחרי משימות העברה שמתבצעות כדי לבדוק את הסטטוס של כל העברת נתונים.

שאילתות על נתונים בטבלה

כשהנתונים מועברים ל-BigQuery, הם נכתבים בטבלאות עם חלוקה למחיצות לפי זמן ההטמעה. מידע נוסף זמין במאמר מבוא לטבלאות עם מחיצות.

אם אתם שולחים שאילתות ישירות לטבלאות במקום להשתמש בתצוגות שנוצרו אוטומטית, אתם צריכים להשתמש בעמודה הווירטואלית _PARTITIONTIME בשאילתה. מידע נוסף זמין במאמר בנושא שליחת שאילתות לטבלאות מחולקות.

בקטעים הבאים מוצגות דוגמאות לשאילתות שאפשר להשתמש בהן כדי לבדוק את הנתונים שהועברו.

ביצועים ברמת הקמפיין

השאילתה לדוגמה הבאה מנתחת את ביצועי הקמפיין ב-Google Ads ב-30 הימים האחרונים.

המסוף

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'

מחליפים את מה שכתוב בשדות הבאים:

• ‫DATASET: השם של מערך הנתונים שיצרתם כדי לאחסן את הטבלה שהועברה
• ‫CUSTOMER_ID: מספר הלקוח שלכם ב-Google Ads.

מספר מילות המפתח

השאילתה לדוגמה הבאה מנתחת מילות מפתח לפי קמפיין, קבוצת מודעות וסטטוס של מילת מפתח. השאילתה הזו משתמשת בפונקציה KeywordMatchType. סוגי ההתאמה למילות מפתח עוזרים לכם לקבוע אילו חיפושים יכולים להקפיץ את המודעה שלכם. מידע נוסף על אפשרויות התאמה למילות מפתח זמין במאמר מידע על אפשרויות התאמה למילות מפתח.

המסוף

  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'

מחליפים את מה שכתוב בשדות הבאים:

• ‫DATASET: השם של מערך הנתונים שיצרתם כדי לאחסן את הטבלה שהועברה
• ‫CUSTOMER_ID: מספר הלקוח שלכם ב-Google Ads.

יצירת מאגר Dataform

אחרי שיוצרים את ההגדרה של העברת הנתונים כדי להעביר את הנתונים העדכניים מ-Google Ads, מגדירים את Dataform כך שיבצע באופן קבוע טרנספורמציה של נתוני ניתוח השיווק. בעזרת Dataform אפשר לתזמן טרנספורמציות קבועות של נתונים, ולהגדיר את הטרנספורמציות האלה באמצעות SQL, תוך שיתוף פעולה עם אנליסטים אחרים של נתונים.

יוצרים מאגר Dataform לאחסון שאילתות SQLX שמרכיבות את קוד הטרנספורמציה.

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר אל Dataform

2. לוחצים על Create repository (יצירת מאגר).

3. בדף Create repository, מבצעים את הפעולות הבאות:

   1. בשדה Repository ID (מזהה המאגר), מזינים marketing-tutorial-repository.
   2. ברשימה Region, בוחרים אזור.
   3. לוחצים על יצירה.

מאגר marketing-tutorial-repository המידע יופיע עכשיו ברשימת מאגרי המידע של Dataform.

מידע נוסף על מאגרי Dataform זמין במאמר מידע על מאגרי Dataform.

יצירה ואתחול של סביבת פיתוח ב-Dataform

יוצרים סביבת עבודה לפיתוח ב-Dataform כדי שתוכלו לעבוד על קוד השינוי במאגר לפני שתבצעו commit ותדחפו את השינויים למאגר.

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר אל Dataform

2. לחץ על marketing-tutorial-repository.

3. לוחצים על יצירת סביבת עבודה לפיתוח.

4. בחלון Create development workspace (יצירת סביבת עבודה לפיתוח):

   1. בשדה מזהה Workspace, מזינים את הערך marketing-tutorial-workspace.
   2. לוחצים על יצירה.

   מופיע הדף של סביבת הפיתוח.

5. לוחצים על Initialize workspace (הפעלת סביבת העבודה).

סביבת העבודה לפיתוח marketing-tutorial-workspace מופיעה עכשיו במאגר marketing-tutorial-repository בכרטיסייה Development Workspaces (סביבות עבודה לפיתוח), יחד עם שני קבצים לדוגמה בספרייה definitions שנקראים *first_view.sqlx ו-*second_view.sqlx.

מידע נוסף על סביבות פיתוח ב-Dataform זמין במאמר סקירה כללית של סביבות פיתוח.

הצהרה על הטבלה ב-Google Ads כמקור לטבלה

כדי לקשר את הטבלה החדשה שהועברה מ-Google Ads אל Dataform, צריך להגדיר אותה כמקור נתונים. לשם כך, פועלים לפי השלבים הבאים:

יצירת קובץ SQLX להצהרה על מקור נתונים

ב-Dataform, כדי להגדיר יעד למקור נתונים, יוצרים קובץ SQLX בספרייה definitions/:

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר לדף Dataform

2. בוחרים באפשרות marketing-tutorial-repository.

3. בוחרים באפשרות marketing-tutorial-workspace.

4. בחלונית קבצים, לצד definitions/, לוחצים על תפריט האפשרויות הנוספות.

5. לוחצים על יצירת קובץ.

6. בחלונית Create new file:

   1. בשדה הוספת נתיב קובץ, אחרי definitions/, מזינים את השם definitions/googleads-declaration.sqlx.
   2. לוחצים על יצירת קובץ.

הצהרה על מקור נתונים

עורכים את definitions/googleads-declaration.sqlx כדי להצהיר על טבלת Google Ads שהועברה כמקור נתונים. בדוגמה הזו, הטבלה ads_Campaign מוגדרת כמקור נתונים:

1. בסביבת העבודה לפיתוח, בחלונית Files, לוחצים על קובץ ה-SQLX להצהרה על מקור הנתונים.

2. מזינים את קטע הקוד הבא בקובץ:

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

הגדרת הטרנספורמציה

מגדירים את טרנספורמציות הנתונים על ידי יצירת קובץ SQLX בספרייה definitions/. במדריך הזה יוצרים טרנספורמציה יומית שמצטברת מדדים כמו קליקים, חשיפות, עלויות והמרות באמצעות קובץ בשם daily_performance.sqlx.

יצירת קובץ SQLX של טרנספורמציה

1. בחלונית קבצים, לצד definitions/, לוחצים על התפריט עוד ואז בוחרים באפשרות יצירת קובץ.
2. בשדה Add a file path (הוספת נתיב קובץ), מזינים את הערך definitions/daily_performance.sqlx.
3. לוחצים על יצירת קובץ.

הגדרת קובץ הטרנספורמציה SQLX

1. בחלונית Files, מרחיבים את הספרייה definitions/.

2. בוחרים באפשרות daily_performance.sqlx ומזינים את השאילתה הבאה:

       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 ולדחוף את השינויים האלה למאגר. כך עושים את זה:

1. בסביבת העבודה marketing-tutorial-workspace, לוחצים על Commit 1 change (ביצוע של שינוי אחד).
2. בחלונית New commit (קובץ חדש לביצוע), מזינים תיאור של קובץ לביצוע בשדה Add a commit message (הוספת הודעה על קובץ לביצוע).
3. לוחצים על אישור כל השינויים.
4. בסביבת העבודה marketing-tutorial-workspace, לוחצים על Push to default branch (העברה לענף ברירת המחדל).

אחרי שהשינויים יועברו בהצלחה למאגר, תוצג ההודעה Workspace is up to date (השינויים ב-Workspace עודכנו).

תזמון של טרנספורמציית הנתונים

אחרי שמגדירים את קובץ טרנספורמציית הנתונים, מתזמנים את טרנספורמציות הנתונים.

יצירת גרסה לסביבת ייצור

כשמבצעים פריסה לייצור ב-Dataform, מוודאים שהסביבה מתעדכנת באופן עקבי עם התוצאות של טרנספורמציות הנתונים. בשלבים הבאים מוסבר איך לציין את main הענף של מאגר marketing-tutorial-repository לאחסון של טרנספורמציות הנתונים:

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר לדף Dataform

2. בוחרים באפשרות marketing-tutorial-repository.

3. לוחצים על הכרטיסייה גרסאות ותזמון.

4. לוחצים על יצירת גרסה לסביבת ייצור.

5. בחלונית Create release configuration, קובעים את ההגדרות הבאות:

   1. בשדה Release ID (מזהה פריט תוכן), מזינים את הערך transformations.
   2. בשדה Git commitish, משאירים את ערך ברירת המחדל main .
   3. בקטע תדירות התזמון, בוחרים באפשרות לפי דרישה.

6. לוחצים על יצירה.

יצירת הגדרה של תהליך עבודה

אחרי שיוצרים גרסת ייצור, אפשר ליצור הגדרת תהליך עבודה שמריצה את טרנספורמציות הנתונים לפי לוח זמנים שצוין במאגר. בשלבים הבאים מוסבר איך לתזמן טרנספורמציות יומיות מקובץ transformations:

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר לדף Dataform

2. בוחרים באפשרות marketing-tutorial-repository.

3. לוחצים על הכרטיסייה גרסאות ותזמון.

4. בקטע הגדרות של תהליכי עבודה, לוחצים על יצירה.

5. בחלונית Create workflow configuration (יצירת הגדרת תהליך עבודה), בשדה Configuration ID (מזהה הגדרה), מזינים transformations.

6. בתפריט Release configuration (הגדרות הפצה), בוחרים באפשרות transformations.

7. בקטע אימות, בוחרים באפשרות הפעלה באמצעות פרטי כניסה של משתמש.

8. בקטע Schedule frequency:

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

9. לוחצים על בחירת תגים.

10. בשדה Select tags to execute (בחירת תגים להפעלה), בוחרים באפשרות Daily (יומי).

11. לוחצים על יצירה.

הגדרת תהליך העבודה שיצרתם מריצה את כל תוצאות הקומפילציה העדכניות שנוצרו על ידי הגדרת ההפצה transformations.

הסרת המשאבים

כדי לא לצבור חיובים בחשבון Google Cloud על המשאבים שבהם השתמשתם בדף הזה, פועלים לפי השלבים הבאים.

מחיקת מערך הנתונים שנוצר ב-BigQuery

כדי להימנע מחיובים על נכסי BigQuery, צריך למחוק את מערך הנתונים שנקרא dataform.

1. במסוף Google Cloud , עוברים לדף BigQuery.

   כניסה ל-BigQuery

2. בחלונית Explorer מרחיבים את הפרויקט ובוחרים באפשרות dataform.

3. לוחצים על התפריט פעולות ואז על מחיקה.

4. בתיבת הדו-שיח מחיקת מערך נתונים, מזינים delete בשדה ולוחצים על מחיקה.

מחיקת סביבת הפיתוח וההגדרות ב-Dataform

יצירת סביבת פיתוח ב-Dataform לא כרוכה בעלויות, אבל כדי למחוק את סביבת הפיתוח אפשר לבצע את הפעולות הבאות:

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר אל Dataform

2. לחץ על quickstart-repository.

3. לוחצים על הכרטיסייה פרסום ותזמון.

4. בקטע הגדרות הפצה, לוחצים על סמל התפריט אפשרויות נוספות לצד ההגדרה production ואז על מחיקה.

5. בקטע הגדרות של תהליכי עבודה, לוחצים על התפריט אפשרויות נוספות לצד ההגדרה transformations ואז על מחיקה.

6. בכרטיסייה סביבות עבודה לפיתוח, לוחצים על תפריט אפשרויות נוספות לצד quickstart-workspace ואז בוחרים באפשרות מחיקה.

7. כדי לאשר, לוחצים על מחיקה.

מחיקת מאגר Dataform

יצירת מאגר Dataform לא כרוכה בעלויות, אבל כדי למחוק את המאגר אפשר לפעול לפי השלבים הבאים:

1. נכנסים לדף Dataform במסוף Google Cloud .

   מעבר אל Dataform

2. ליד quickstart-repository, לוחצים על תפריט האפשרויות הנוספות ואז בוחרים באפשרות מחיקה.

3. בחלון Delete repository (מחיקת מאגר), מזינים את שם המאגר כדי לאשר את המחיקה.

4. כדי לאשר, לוחצים על מחיקה.