first i query :

ET_OpenEvent oe = new ET_OpenEvent();
oe.AuthStub = myclient;
oe.SearchFilter = new SimpleFilterPart() { Property = "EventDate", SimpleOperator = SimpleOperators.greaterThan, DateValue = new DateTime[] { filterDate } };
oe.Props = new string[] { "SendID", "SubscriberKey", "EventDate", "Client.ID", "EventType"};
GetReturn oeGet = oe.Get();

I take sendID and subsciber key in dict (SubscriberKey, sendid)

foreach SendID, I query :

ET_Send oe_etsent = new ET_Send();
oe_etsent.AuthStub = myclient;
oe_etsent.SearchFilter = new SimpleFilterPart() { Property = "ID", SimpleOperator = SimpleOperators.equals, Value = new string[] { etopenevendictDisplay.Value } };
oe_etsent.Props = new string[] { "SentDate", "NumberSent", "NumberDelivered", "Duplicates", "EmailName", "Subject", "ID", "Unsubscribes", "UniqueOpens", "UniqueClicks" };
GetReturn oeGet_etsent = oe_etsent.Get();

but calling the API in foreachloop again and again seems not a good ... Is there a way to retrieve data at once . Am using Fuel SDK and c# for this.


So, first i need to have the Query + system views enabled for the account.right? I just created a test DE(queryDE) to hold the query result in ET and tried this code against DE(DE_TEST2) :

QueryDefinition qd = new QueryDefinition();

        qd.Name = "queryActivityTest1";
        qd.CustomerKey = "queryActivityTest1";
        qd.Description = "Some test Description1";
        qd.TargetUpdateType = "Overwrite";
        qd.TargetType = "DE";
        qd.QueryText = "Select EmailAddress as EMAIL from DE_TEST2";

        InteractionBaseObject ibo = new InteractionBaseObject();
        ibo.CustomerKey = "queryDE";
        ibo.Name = "queryDE";
        qd.DataExtensionTarget = ibo;

and the API gets back to me with this : Status OKRequest ID c0a5b8e9-121d-4203-afef-185b86498684 msgresult:QueryDefinition created

But i dont see the the result in the target DE (queryDE)?? is it coz the account doesn't have the QueryActivity enabled yet?


I agree, a for loop each time does not seem like the way to go. I would suggest:

  1. Write a QueryActivity (either within the Marketing Cloud App or via API but preferably within the Marketing Cloud App) and do some kind of join query. This will segment the data as you need, and put the results in a new DataExtension. (have a look at this)
  2. Call that query via API
  3. Do a Get on that new Data Extension.

Hope this helps!

| improve this answer | |
  • Am getting tracking info like opens from ET_OpenEvent and other fields from ET_Send (Coz i can't get subsciberkey and subject, email name directly) to local database . Also,Customer do have an Advance Account and i see QueryActivities are not enabled under interactions. I doubt they will pay if it costs to enable it . So, i left with trying via API. – Jack85 Oct 14 '14 at 17:00
  • 1
    It might be worth your customer's phone call... Querying is so standard right now its tough to imagine it costing very much. They would need to enable the system views for you too. – Timothy Oct 14 '14 at 17:39
  • They said Query Activities are part of AIM package and it cost 5000/yr and so it didn't go through with customer approval . So end up trying other-ways...Can you suggest any alternatives? – Jack85 Nov 19 '14 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.