טעינת נתונים מ-Campaign Manager ל-BigQuery

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

סקירה כללית על המחבר

שירות העברת הנתונים ל-BigQuery במחבר של Campaign Manager תומך באפשרויות הבאות להעברת נתונים.

במאמר טרנספורמציות של דוחות Campaign Manager מוסבר איך הדוחות של Campaign Manager הופכים לטבלאות ב-BigQuery ולתצוגות ב-BigQuery.

אפשרויות להעברת נתונים תמיכה
דוחות נתמכים המחבר של Campaign Manager תומך בהעברת נתונים מהדוחות הבאים:
תדירות החזרה המחבר של Campaign Manager תומך בהעברת נתונים כל 8 שעות.

כברירת מחדל, העברות הנתונים של Campaign Manager מתוזמנות למועד שבו נוצרה העברת הנתונים.
רענון החלון המחבר של Campaign Manager מאחזר נתונים מ-Campaign Manager מיומיים לכל היותר בזמן שהעברת הנתונים פועלת. אי אפשר להגדיר את חלון הרענון של המחבר הזה.

מידע נוסף זמין במאמר חלונות רענון.
זמינות הנתונים למילוי חוסרים להפעיל השלמת חוסר בנתונים (data backfill) כדי לאחזר נתונים מחוץ להעברת הנתונים המתוזמנת. אתם יכולים לאחזר נתונים עד למועד שמאפשרת מדיניות שמירת הנתונים במקור הנתונים שלכם.

מידע על מדיניות שמירת הנתונים של Display & Video 360 זמין במאמר אמצעי הבקרה למחיקה ולשמירה של נתונים.

העברת נתונים מהעברות של Campaign Manager

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

רענון החלונות

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

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

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

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

לפני שיוצרים העברת נתונים מ-Campaign Manager:

  • מוודאים שביצעתם את כל הפעולות הנדרשות כדי להפעיל את שירות העברת הנתונים ל-BigQuery.
  • יוצרים מערך נתונים ב-BigQuery כדי לאחסן את הנתונים מ-Campaign Manager.

  • מוודאים שלארגון יש גישה לקובצי Campaign Manager Data Transfer גרסה 2 (Campaign Manager DTv2). הקבצים האלה מועברים על ידי צוות Campaign Manager לקטגוריה של Cloud Storage. כדי לקבל גישה לקובצי Campaign Manager DTv2, השלב הבא תלוי בשאלה אם יש לכם חוזה ישיר עם Campaign Manager. בשני המקרים, יכול להיות שיחולו חיובים נוספים.

    • אם יש לכם חוזה עם Campaign Manager, פנו אל התמיכה של Campaign Manager כדי להגדיר קובצי Campaign Manager DTv2.
    • אם אין לכם חוזה עם Campaign Manager, יכול להיות שלסוכנות או למפיץ של Campaign Manager יש גישה לקובצי DTv2 של Campaign Manager. כדי לקבל גישה לקבצים האלה, פנו לסוכנות או למפיץ שלכם.

    אחרי שתשלימו את השלב הזה, תקבלו שם של קטגוריה ב-Cloud Storage, שדומה לשם הבא:

    dcdt_-dcm_account123456

    לצוות

  • אם אתם מתכוונים להגדיר התראות על הפעלת העברות ב-Pub/Sub, אתם צריכים את ההרשאות של pubsub.topics.setIamPolicy. מידע נוסף זמין במאמר התראות על הפעלות של שירות העברת הנתונים ל-BigQuery.

ההרשאות הנדרשות

מוודאים שנתתם את ההרשאות הבאות.

התפקידים הנדרשים ב-BigQuery

כדי לקבל את ההרשאות שדרושות ליצירת העברת נתונים באמצעות שירות העברת הנתונים ל-BigQuery, בקשו מהאדמין להקצות לכם את התפקיד אדמין ב-BigQuery (roles/bigquery.admin) ב-IAM בפרויקט. כדי לקרוא הסבר על מתן תפקידים, ראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

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

ההרשאות הנדרשות

כדי ליצור העברת נתונים באמצעות שירות העברת הנתונים ל-BigQuery, נדרשות ההרשאות הבאות:

  • הרשאות לשירות העברת נתונים ל-BigQuery:
    • bigquery.transfers.update
    • bigquery.transfers.get
  • הרשאות ב-BigQuery:
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

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

מידע נוסף זמין במאמר מתן גישה ל-bigquery.admin.

התפקידים הנדרשים ב-Campaign Manager

נותנים הרשאת קריאה לקובצי Campaign Manager DTv2 שמאוחסנים ב-Cloud Storage. הגישה מנוהלת על ידי הישות שממנה קיבלתם את הקטגוריה של Cloud Storage.

הגדרת העברה מ-Campaign Manager

כדי להגדיר העברת נתונים מ-Campaign Manager, צריך:

  • קטגוריה של Cloud Storage: ה-URI של הקטגוריה של Cloud Storage לקובצי Campaign Manager DTv2, כמו שמתואר בקטע לפני שמתחילים. שם הקטגוריה צריך להיראות כך:

    dcdt_-dcm_account123456

  • מזהה Campaign Manager: מזהה הרשת, המפרסם או Floodlight ב-Campaign Manager. מזהה הרשת הוא ההורה בהיררכיה.

איך מוצאים את המזהה ב-Campaign Manager

כדי לאחזר את מזהה Campaign Manager, אתם יכולים להשתמש במסוף Cloud Storage כדי לבדוק את הקבצים בקטגוריה של Campaign Manager Data Transfer ב-Cloud Storage. מזהה Campaign Manager משמש להתאמת קבצים בקטגוריה של Cloud Storage שסופקה. המזהה מוטמע בשם הקובץ, ולא בשם הקטגוריה של Cloud Storage.

לדוגמה:

  • בקובץ בשם dcm_account123456_activity_*, המזהה הוא 123456.
  • בקובץ בשם dcm_floodlight7890_activity_*, המזהה הוא 7890.
  • בקובץ בשם dcm_advertiser567_activity_*, המזהה הוא 567.

איך מוצאים את התחילית של שם הקובץ

במקרים נדירים, יכול להיות שלקבצים בקטגוריה של Cloud Storage יש שמות קבצים מותאמים אישית ולא סטנדרטיים, שהוגדרו על ידי צוות השירותים של Google Marketing Platform.

לדוגמה:

  • בקובץ בשם dcm_account123456custom_activity_*, הקידומת היא dcm_account123456custom – כל מה שמופיע לפני _activity.

אם אתם צריכים עזרה, פנו אל התמיכה של Campaign Manager.

יצירת העברת נתונים מ-Campaign Manager

המסוף

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

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

  2. ‫לוחצים על Create transfer.

  3. בדף Create Transfer:

    • בקטע Source type, בשדה Source בוחרים באפשרות Campaign Manager.

      מקור ההעברה

    • בקטע Transfer config name, בשדה השם המוצג מזינים שם להעברת הנתונים, למשל My Transfer. שם ההעברה יכול להיות כל ערך שמאפשר לכם לזהות את ההעברה אם תצטרכו לשנות אותה בהמשך.

      שם ההעברה

    • בקטע Schedule options, בשדה Schedule, משאירים את ערך ברירת המחדל (Start now) או לוחצים על Start at a set time.

      • בקטע Repeats, בוחרים באיזו תדירות להפעיל את ההעברה. אם בוחרים אפשרות אחרת מלבד Daily, מופיעות אפשרויות נוספות. לדוגמה, אם בוחרים באפשרות Weekly, מופיעה אפשרות לבחור את היום בשבוע.
      • בשדה Start date and run time, מזינים את התאריך והשעה שבהם רוצים להתחיל את העברת הנתונים. אם בוחרים באפשרות התחלה עכשיו, האפשרות הזו מושבתת.

    • בקטע Destination settings, בשדה Destination dataset בוחרים את מערך הנתונים שיצרתם לאחסון הנתונים.

      העברת מערך נתונים

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

      • בשדה Cloud Storage bucket, מזינים את השם של הקטגוריה של Cloud Storage שבה מאוחסנים קובצי העברת נתונים V2.0, או מחפשים אותו. כשמזינים את שם הקטגוריה, לא כוללים את gs://.
      • בשדה מזהה DoubleClick, ממלאים את המזהה המתאים של Campaign Manager.
      • (אופציונלי) אם לקבצים יש שמות סטנדרטיים כמו בדוגמאות האלה, משאירים את השדה File name prefix ריק. אם לקבצים בקטגוריה של Cloud Storage יש שמות קבצים בהתאמה אישית, ציינו תחילית של שם קובץ.

      פרטי המקור ב-Campaign Manager

    • (אופציונלי) בקטע Notification options:

      • לוחצים על המתג כדי להפעיל את ההתראות באימייל. כשמפעילים את האפשרות הזו, האדמין של ההעברה מקבל התראה באימייל אם הרצת ההעברה נכשלת.
      • בקטע Select a Pub/Sub topic, בוחרים את שם הנושא או לוחצים על Create a topic. האפשרות הזו מגדירה התראות על הפעלת Pub/Sub להעברה.

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

BQ

מזינים את הפקודה bq mk ומציינים את הדגל ליצירת העברה – --transfer_config. צריך גם את הדגלים הבאים:

  • --data_source
  • --target_dataset
  • --display_name
  • --params
bq mk --transfer_config \
--project_id=project_id \
--target_dataset=dataset \
--display_name=name \
--params='parameters' \
--data_source=data_source

כאשר:

  • ‫project_id הוא מזהה הפרויקט.
  • ‫dataset הוא מערך הנתונים של היעד להגדרות של העברת הנתונים.
  • ‫name הוא השם המוצג של הגדרת העברת הנתונים. שם ההעברה יכול להיות כל ערך שמאפשר לכם לזהות את ההעברה אם תצטרכו לשנות אותה בהמשך.
  • ‫parameters מכיל את הפרמטרים של הגדרת העברת הנתונים שנוצרה, בפורמט JSON. לדוגמה: --params='{"param":"param_value"}'. ב-Campaign Manager, צריך לספק את הפרמטרים bucket ו-network_id. ‫bucket היא הקטגוריה של Cloud Storage שמכילה את קובצי DTv2 של Campaign Manager. ‫network_id הוא המזהה של הרשת, של Floodlight או של המפרסם.
  • data_source הוא מקור הנתונים – dcm_dt (Campaign Manager).

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

לדוגמה, הפקודה הבאה יוצרת העברת נתונים של Campaign Manager בשם My Transfer באמצעות מזהה Campaign Manager‏ 123456, קטגוריה של Cloud Storage‏ dcdt_-dcm_account123456 ומערך נתונים של היעד mydataset. הפרמטר file_name_prefix הוא אופציונלי ומשמש רק לשמות קבצים נדירים בהתאמה אישית.

העברת הנתונים נוצרת בפרויקט ברירת המחדל:

bq mk --transfer_config \
--target_dataset=mydataset \
--display_name='My Transfer' \
--params='{"bucket": "dcdt_-dcm_account123456","network_id": "123456","file_name_prefix":"YYY"}' \
--data_source=dcm_dt

אחרי הרצת הפקודה, תתקבל הודעה כמו זו:

[URL omitted] Please copy and paste the above URL into your web browser and follow the instructions to retrieve an authentication code.

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

API

משתמשים ב-method‏ projects.locations.transferConfigs.create ומספקים מופע של המשאב TransferConfig.

Java

לפני שמנסים את הדוגמה הזו, צריך לפעול לפי הוראות ההגדרה של Java במדריך למתחילים של BigQuery באמצעות ספריות לקוח. למידע נוסף, קראו את מאמרי העזרה של BigQuery Java API.

כדי לבצע אימות ב-BigQuery, צריך להגדיר את Application Default Credentials. מידע נוסף זמין במאמר הגדרת אימות לספריות לקוח. 

import com.google.api.gax.rpc.ApiException;
import com.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;
import com.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;
import com.google.cloud.bigquery.datatransfer.v1.ProjectName;
import com.google.cloud.bigquery.datatransfer.v1.TransferConfig;
import com.google.protobuf.Struct;
import com.google.protobuf.Value;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

// Sample to create campaign manager transfer config
public class CreateCampaignmanagerTransfer {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    String bucket = "gs://cloud-sample-data";
    // the network_id only allows digits
    String networkId = "7878";
    String fileNamePrefix = "test_";
    Map<String, Value> params = new HashMap<>();
    params.put("bucket", Value.newBuilder().setStringValue(bucket).build());
    params.put("network_id", Value.newBuilder().setStringValue(networkId).build());
    params.put("file_name_prefix", Value.newBuilder().setStringValue(fileNamePrefix).build());
    TransferConfig transferConfig =
        TransferConfig.newBuilder()
            .setDestinationDatasetId(datasetId)
            .setDisplayName("Your Campaignmanager Config Name")
            .setDataSourceId("dcm_dt")
            .setParams(Struct.newBuilder().putAllFields(params).build())
            .build();
    createCampaignmanagerTransfer(projectId, transferConfig);
  }

  public static void createCampaignmanagerTransfer(String projectId, TransferConfig transferConfig)
      throws IOException {
    try (DataTransferServiceClient client = DataTransferServiceClient.create()) {
      ProjectName parent = ProjectName.of(projectId);
      CreateTransferConfigRequest request =
          CreateTransferConfigRequest.newBuilder()
              .setParent(parent.toString())
              .setTransferConfig(transferConfig)
              .build();
      TransferConfig config = client.createTransferConfig(request);
      System.out.println("Campaignmanager transfer created successfully :" + config.getName());
    } catch (ApiException ex) {
      System.out.print("Campaignmanager transfer was not created." + ex.toString());
    }
  }
}

פתרון בעיות בהגדרת ההעברה מ-Campaign Manager

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

שליחת שאילתות לגבי הנתונים

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

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

שאילתות לדוגמה ב-Campaign Manager

אתם יכולים להשתמש בשאילתות הבאות לדוגמה ב-Campaign Manager כדי לנתח את הנתונים שהועברו. אפשר גם להשתמש בשאילתות בכלי להמחשה חזותית כמו Data Studio. השאילתות האלה נועדו לעזור לכם להתחיל לשלוח שאילתות לגבי נתוני Campaign Manager באמצעות BigQuery. אם יש לכם שאלות נוספות לגבי מה שאפשר לעשות עם הדוחות האלה, פנו לנציג הטכני של Campaign Manager.

בכל אחת מהשאילתות הבאות, מחליפים את המשתנים כמו dataset בערכים שלכם.

קמפיינים אחרונים

שאילתת ה-SQL לדוגמה הבאה מאחזרת את הקמפיינים האחרונים.

SELECT
  Campaign,
  Campaign_ID
FROM
  `dataset.match_table_campaigns_campaign_manager_id`
WHERE
  _DATA_DATE = _LATEST_DATE

חשיפות ומשתמשים ייחודיים לפי קמפיין

שאילתת ה-SQL לדוגמה הבאה מנתחת את מספר החשיפות והמשתמשים הייחודיים לפי קמפיין ב-30 הימים האחרונים.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.impression_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN start_date
    AND end_date
  GROUP BY
    Campaign_ID,
    Date

הקמפיינים האחרונים, מסודרים לפי קמפיין ותאריך

השאילתה הבאה לדוגמה ב-SQL מנתחת את הקמפיינים האחרונים ב-30 הימים האחרונים, לפי קמפיין ותאריך.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  Campaign,
  Campaign_ID,
  Date
FROM (
  SELECT
    Campaign,
    Campaign_ID
  FROM
    `dataset.match_table_campaigns_campaign_manager_id`
  WHERE
    _DATA_DATE = _LATEST_DATE ),
  (
  SELECT
    date AS Date
  FROM
    `bigquery-public-data.utility_us.date_greg`
  WHERE
    Date BETWEEN start_date
    AND end_date )
ORDER BY
  Campaign_ID,
  Date

חשיפות ומשתמשים נפרדים לפי קמפיין בטווח תאריכים

השאילתה הבאה לדוגמה ב-SQL מנתחת את מספר החשיפות והמשתמשים הייחודיים לפי קמפיין בין start_date ל-end_date.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `dataset.match_table_campaigns_campaign_manager_id`
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.utility_us.date_greg`
    WHERE
      Date BETWEEN start_date
      AND end_date ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.impression_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN start_date
    AND end_date
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
WHERE
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
ORDER BY
  base.Campaign_ID,
  base.Date

חשיפות, קליקים, פעילויות ומשתמשים נפרדים לפי קמפיין

שאילתת ה-SQL לדוגמה הבאה מנתחת את מספר החשיפות, הקליקים, הפעילויות והמשתמשים הייחודיים לפי קמפיין ב-30 הימים האחרונים. בשאילתה הזו, מחליפים את המשתנים כמו campaign_list בערכים שלכם. לדוגמה, מחליפים את campaign_list ברשימה מופרדת בפסיקים של כל הקמפיינים ב-Campaign Manager שרלוונטיים לשאילתה.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  imp.count AS imp_count,
  imp.du AS imp_du,
  click.count AS click_count,
  click.du AS click_du,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `dataset.match_table_campaigns_campaign_manager_id`
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.utility_us.date_greg`
    WHERE
      Date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
      AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.impression_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS imp
ON
  base.Campaign_ID = imp.Campaign_ID
  AND base.Date = imp.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.click_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS click
ON
  base.Campaign_ID = click.Campaign_ID
  AND base.Date = click.Date
LEFT JOIN (
  SELECT
    Campaign_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.activity_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN campaign_list
  AND (base.Date = imp.Date
    OR base.Date = click.Date
    OR base.Date = activity.Date)
ORDER BY
  base.Campaign_ID,
  base.Date

פעילות בקמפיין

השאילתה הבאה לדוגמה ב-SQL מנתחת את הפעילות בקמפיין ב-30 הימים האחרונים. בשאילתה הזו, מחליפים את המשתנים כמו campaign_list בערכים שלכם. לדוגמה, מחליפים את campaign_list ברשימה מופרדת בפסיקים של כל הקמפיינים ב-Campaign Manager שרלוונטיים לשאילתה.

# START_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
# END_DATE = DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
SELECT
  base.*,
  activity.count AS activity_count,
  activity.du AS activity_du
FROM (
  SELECT
    *
  FROM (
    SELECT
      Campaign,
      Campaign_ID
    FROM
      `dataset.match_table_campaigns_campaign_manager_id`
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      mt_at.Activity_Group,
      mt_ac.Activity,
      mt_ac.Activity_Type,
      mt_ac.Activity_Sub_Type,
      mt_ac.Activity_ID,
      mt_ac.Activity_Group_ID
    FROM
      `dataset.match_table_activity_cats_campaign_manager_id` AS mt_ac
    JOIN (
      SELECT
        Activity_Group,
        Activity_Group_ID
      FROM
        `dataset.match_table_activity_types_campaign_manager_id`
      WHERE
        _DATA_DATE = _LATEST_DATE ) AS mt_at
    ON
      mt_at.Activity_Group_ID = mt_ac.Activity_Group_ID
    WHERE
      _DATA_DATE = _LATEST_DATE ),
    (
    SELECT
      date AS Date
    FROM
      `bigquery-public-data.utility_us.date_greg`
    WHERE
      Date BETWEEN start_date
      AND end_date ) ) AS base
LEFT JOIN (
  SELECT
    Campaign_ID,
    Activity_ID,
    _DATA_DATE AS Date,
    COUNT(*) AS count,
    COUNT(DISTINCT User_ID) AS du
  FROM
    `dataset.activity_campaign_manager_id`
  WHERE
    _DATA_DATE BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -31 DAY)
    AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
  GROUP BY
    Campaign_ID,
    Activity_ID,
    Date ) AS activity
ON
  base.Campaign_ID = activity.Campaign_ID
  AND base.Activity_ID = activity.Activity_ID
  AND base.Date = activity.Date
WHERE
  base.Campaign_ID IN campaign_list
  AND base.Activity_ID = activity.Activity_ID
ORDER BY
  base.Campaign_ID,
  base.Activity_Group_ID,
  base.Activity_ID,
  base.Date