0

I'd like to create a script in my parent Business Unit that tells me everything about which mails has been sent to a subscriber from our parent Business Unit (automated sends) and from our child business units (general marketing sends).

Using the script below I'm not getting info for ie. JobID, EmailName, EmailSubject from sends from child business units, probably due to the fact that the _Job Data View is limited to a single business unit.

With a complicated Business Unit setup having 170 child Business Units, could I in any way alter my script or approach to succeed with collecting the info I'm trying to get using the script below?

Thank you.

SELECT
    s.AccountID,
    s.SubscriberKey,
    s.SubscriberID,
    a.PersonEmail AS EmailAddress,
    a.Name As PersonName,
    a.FirstName As FirstName,
    a.LastName As LastName,
    a.Gender__pc As Gender,
    a.Country__c As Country, 
    a.Preferred_store__c As preferredStore,
    s.BatchID,
    j.EmailName,
    j.EmailSubject,
    s.EventDate as SentDate,
    o.EventDate AS OpenDate,
    c.EventDate AS ClickDate,
    j.JobID,
    j.SalesForceTotalSubscriberCount
FROM 
    _Sent s
    LEFT JOIN _Job j ON s.JobID = j.JobID
    INNER JOIN Account_Salesforce a ON a.PersonContactId=s.SubscriberKey
    LEFT JOIN _Open o  ON s.SubscriberKey=o.SubscriberKey and o.JobID=s.JobID and s.ListID = o.ListID and s.BatchID = o.BatchID and o.IsUnique = 1
    LEFT JOIN _Click c ON o.SubscriberKey=c.SubscriberKey and c.JobID=s.JobID and s.ListID = c.ListID and s.BatchID = c.BatchID and c.IsUnique = 1
WHERE
    a.PersonEmail ='[email protected]'

missing information from query

Improve this question

1 Answer 1

Reset to default
1

We have a similar requirement on our instance. The approach we follow is as below:

  1. Use a script in conjunction with WS Proxy to create a query activity in all Business Units
  2. Create a shared DE(or a single DE in each BU - with same name and external key), that will store the result of this query activity.

Below is a sample script, that generates a query activity which queries the sent DV for each market and stores in a DE. The downside of this approach is that you cannot debug a lot, since an automation is not created, just a query activity which runs independently.

  <script language=javascript runat=server>
Platform.Load("core", "1.1.1");

// Configurations START

var debug = true;
var TARGET_DE_NAME = "ypur target DE";
var TARGET_DE_CUSTOMER_KEY = "yur target DE etxernal key";

function getControlGroupsQuery(marketCode) {
    //WRite the query here
    return "select * from _Sent where eventdate >= FORMAT(DATEADD(dd,-1,getdate()),'yyyy-MM-dd 00:00:00')";
}

//  Configurations END 

var FOLDER_NAME = "Generated";


var BU = [];
//All your MIDs fall here
//BU =  [ { marketCode: 'FI', mid: 123, marketName: 'Finland' }, { marketCode: 'BE', mid: 4657, marketName: 'Belgium' } ];



var prox = new Script.Util.WSProxy();

function getQueryName(queryName){
    return "Generated_" + queryName;// Dynamic queries can be named like this || guid;
}

function createQuery(queryName, queryText, categoryID){
 
    var guid = Platform.Function.GUID();
    var name = getQueryName(queryName);


    // var options = {SaveOptions: [{'PropertyName': '*', SaveAction: 'UpdateAdd'}]};

    var query = {
        Name: name
        , CustomerKey: guid
        , Description: "Generated queries for analysis"
        , QueryText: queryText
        , TargetType: "DE"
        , DataExtensionTarget: {
            Name: TARGET_DE_NAME
            , CustomerKey: TARGET_DE_CUSTOMER_KEY
        }
        , CategoryID: categoryID
        , TargetUpdateType: "Overwrite" //THe query overwrites
    }

    dump(query, "Query: ");
 
  try{
    var res = prox.createItem("QueryDefinition", query);
      
  }
  catch(e)
  {
    Write(Stringify(e));
  }
    return res;
  Write(Stringify(res));
}

function updateQuery(queryName, queryText, categoryID){
    var cols = ["ObjectID", "Name", "CustomerKey", "Description", "QueryText", "TargetType", "DataExtensionTarget.Name", "DataExtensionTarget.CustomerKey", "CategoryID", "TargetUpdateType"];
    var filter = {
        Property: "Name"
        , SimpleOperator: "equals"
        , Value: getQueryName(queryName)
    };
    var opts = null;
    var props = null;
    if(categoryID){
        filter = {
            LeftOperand: {
                Property: "CategoryID"
                , SimpleOperator: "equals"
                , Value: categoryID
            }
            , LogicalOperator:"AND"
            , RightOperand: filter
        };

    }
    filter = {
            LeftOperand: {
                Property: "Status"
                , SimpleOperator: "equals"
                , Value: "Active"
            }
            , LogicalOperator:"AND"
            , RightOperand: filter
        };

    try{
        var queryRetrieve = prox.retrieve("QueryDefinition", cols, filter, opts, props) ;

        if(queryRetrieve.Status == "OK" && queryRetrieve.Results.length > 0){
            var query =  queryRetrieve.Results[0];
        }
        if(query){
            query = {
                ObjectID: query.ObjectID
                , Name: query.Name
                , CustomerKey: query.CustomerKey
                , Description: query.Description
                , QueryText: queryText
                , TargetType: "DE"
                , DataExtensionTarget: {
                    Name: TARGET_DE_NAME
                    , CustomerKey: TARGET_DE_CUSTOMER_KEY
                }
                , CategoryID: categoryID
                , TargetUpdateType: query.TargetUpdateType
            }
            var res = prox.updateItem("QueryDefinition", query);
            dump(res,"Update Query: ");
            return res;
        }
    } catch(ex){
        dump(ex, "Exception: ");
    }
}

function createOrUpdateQuery(queryName, queryText, categoryID){
    var res = updateQuery(queryName, queryText, categoryID);
    if(res.Status == "OK" && res.Results.length > 0){
        return res.Results[0].Object.ObjectID;
    } else {
        res = createQuery(queryName, queryText, categoryID);
        if(res.Results && res.Results[0]){
            var objectID = res.Results[0].Object.ObjectID;
            var statusMessage = res.Results[0].StatusMessage;
            var statusCode = res.Results[0].StatusCode;
            if(statusCode == "OK" && objectID){
                return objectID;
            }
            dump(result, "Result: ");
        };
    }
}


function performQuery(objectID){
    var action = "Start";
    var props = { ObjectID: objectID };
    var opts = {};
    var res = prox.performItem("QueryDefinition", props, action, opts);
 Write("Test");
    dump(res, "Performing! ");
}


function createAndPerform(queryName, queryText, categoryID){
    var objectID = createOrUpdateQuery(queryName, queryText, categoryID);
    if(objectID){
        performQuery(objectID);
    }
}

function dump(variable, label) {
    if(debug){
  var label = (label == undefined) ? '' : '<b>' + label + ':</b><br>';
  Write("<br><pre>" + label + Stringify(variable) + "</pre><br>");
    }
}

function runQueriesOnBU(bu){
    prox.setClientId({ "ID": bu.mid });

    dump(bu, "BU: ");

    // Generated CategoryID
    var categoryID = getFolderID(FOLDER_NAME, "queryactivity");
    dump(categoryID, "CategoryID: ")
    if(categoryID){
        createAndPerform("YOUR_QUERY_NAME", getControlGroupsQuery(bu.marketCode), categoryID); //Enter your query name here
    }

    prox.resetClientIds();
}
//Execution begins
for(var b in BU){
    runQueriesOnBU(BU[b]);
}

function createFolderID(folderName, contentType){
    dump(folderName, "Creating: ");
    var complexfilter = {
        LeftOperand:{
            Property: "ContentType"
            , SimpleOperator: "equals"
            , Value: contentType
        }
        , LogicalOperator:"AND"
        , RightOperand:{
            Property: "Name"
            , SimpleOperator: "equals"
            , Value: "Query"
        }
    };
    var myQueriesFolder = prox.retrieve("DataFolder", ["Name", "ID", "ContentType", "ParentFolder.ID"], complexfilter);
    var myParentFolder = myQueriesFolder.Results[0].ID;

    var guid = Platform.Function.GUID();
    var newFolder = {
        "Name" : folderName,
        "CustomerKey" : guid,
        "Description" : folderName + " auto generated folder",
        "ContentType" : contentType,
        "IsActive" : "true",
        "IsEditable" : "true",
        "AllowChildren" : "false",
        "ParentFolder" : {
            ID: myParentFolder
        }
    };
    try{
        var res = prox.createItem("DataFolder", newFolder);
        if(res.Status == "OK"){
            return res.Results[0].NewID;
        }
    } catch (ex){
        Write("Error: " + Stringify(ex));
    }
}

function getFolderID(folderName, contentType) {
    var cols = ["Name", "ID", "ContentType"];
      
    var complexfilter = {
        LeftOperand:{
            Property: "ContentType"
            , SimpleOperator: "equals"
            , Value: contentType
        }
        , LogicalOperator:"AND"
        , RightOperand:{
            Property: "Name"
            , SimpleOperator: "equals"
            , Value: folderName
        }
    };

    var data = prox.retrieve("DataFolder", cols, complexfilter);
    if(data.Results.length === 1){
       return data.Results[0].ID;
    } else {
        return createFolderID(folderName, contentType);
    }
}

</script>
Improve this answer
1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .