1

I have an idea I hope you'll help me set up. I want to count the number of records in a data extension and write the count into a new data extension called DataExtensionCount. I want to count records for more than one data extension and write each count into the DataExtensionCount. It could be helpful if I somehow could distinguish each count from the others.

Something like:
Subscribers Denmark - 50.000
Inactive subscribers Denmark - 12.000

Or:
Subscribers Denmark
50.000
Inactive subscribers Denmark
12.000

I'd like to have a query do this for me every day, so I know how our subscriber / inactive subscriber ratio develop without having to take a note of these numbers every day.

I guess I need at least a query and a data extension to do this.

1) How do I count the number of records in a data extension using a query? 2) What type of data should my data extension be ready for handle?

Hope this makes sense :) Thank you for helping

4
  • What do you mean by DataExtension?
    – d_k
    Apr 26, 2017 at 11:50
  • @d_k I import my subscribers every night from Navision and filter them into several data extensions. I. e. I have a data extension for all swedish newsletter subscribers and I have a data extensions for all the inactive swedish newsletter subscribers. I want to know the number of records in each of these and I think using a query is the best way to registrer these numbers / collect this data. So my idea was to save each number daily into a data extension. Does it make more sense now? Thank you Apr 26, 2017 at 12:11
  • In which Object are you saving all the data in salesforce? If I understood your requirement correctly. Your problem can be solved using a batch class
    – d_k
    Apr 26, 2017 at 12:21
  • @d_k I have a Navision to Marketing Cloud solution only, I don't have a Salesforce account. Apr 26, 2017 at 12:30

3 Answers 3

4

If you have a relatively fixed list of Data Extensions you're interested in, you could create a Query Activity and place it in an Automation. It would target a 'report' data extension with Overwrite and have maybe two fields - DataExtension (Text(128)) and RecordCount(Number). Your Query could look something like this:

SELECT 'MyFirstDE' AS DataExtension, COUNT(1) AS RecordCount
FROM MyFirstDE
UNION ALL
SELECT 'MySecondDE' AS DataExtension, COUNT(1) AS RecordCount
FROM MySecondDE

... and so on.

If you want something more dynamic in nature where you have a variable number of DEs to get counts on, consider an SSJS Script Activity that runs immediately before the query above and builds the SQL for this query dynamically based on an API retrieve on the DataExtension object and then updates the Query Activity by API.

5
  • Queries are notoriously hard to make dynamic without scripts - at which point, why not just finish it via the script instead of running a query? After retrieving the DE names, you just need to do a DataExtensionRowCount() and then an Upsert. This seems quicker than building, updating and running a Query Apr 26, 2017 at 13:31
  • Can you substantiate "notoriously" in any way? You build a string and set the text to the query to that string. Please explain.
    – Macca
    Apr 26, 2017 at 13:34
  • The only way to make a variable query is through a script - SFMC does not allow the SQL variable (help.marketingcloud.com/en/documentation/exacttarget/…) which means inheritly it is not dynamic like scripting is Apr 26, 2017 at 13:45
  • 1
    OK. Sounds pretty notorious to me. Agree to disagree.
    – Macca
    Apr 26, 2017 at 13:46
  • @AndrewMcLeod - Thank you. This was exactly what I needed. I know I might be able to make this setup more smooth and dynamic, but being a email marketeer with zero programming skills, I'm very happy with what you've have helped me accomplish. So, I'll look into doing a more advanced version of this later on. For now, this is what I ended up with: SELECT 'CH' AS DataExtension, COUNT(1) AS RecordCount, DATEADD(hour, + 8, GETDATE()) AS TheDate FROM CH UNION ALL SELECT 'CH-Unengaged' AS DataExtension, COUNT(1) AS RecordCount, DATEADD(hour, + 8, GETDATE()) AS TheDate FROM [CH-Unengaged] Apr 27, 2017 at 11:25
1

In order to keep this as fully dynamic, this would likely require a Retrieve Request SOAP call followed by a For loop in AMPscript.

I would recommend putting this inside of a script activity (SSJS only) which calls a content area housing your AMPScript and having it run in a scheduled automation daily. As time goes on, and your number of Data Extensions increases, you may need to find a different solution as the SOAP call could time out due to the size of data retrieved.

Breakdown the AMPscript content Area:

  1. Do a Retrieve Request on the DataExtension Object in SOAP API to gather the names of all of your DEs (See sample of Retrieve Request of Subscriber object in AMPscript). I would recommend adding a filter of some kind, as with AMPscript, you have a limit of 2000 rows I believe. You can get around this with the continue request (not available in AMPScript) if you want to write it inside SSJS instead.
  2. This will create a rowset to house your DE names
  3. You can then create a For loop using the Rowcount() of this rowset
  4. Inside this For loop, you will do a DataExtensionRowCount() on each DE name.
  5. You will then use the UpsertData() function to insert or update this info inside a row in the DE.

Breakdown of the Automation and Interactions:

  1. Create a Data Extension with the required fields (DE_Name, Rowcount, etc)
  2. Create a Content Area inside of Classic builder housing your AMPscript from above
  3. Create a Script Activity with SSJS that calls the Content Area above.
  4. Put This Script Activity inside a scheduled automation that runs daily.
0
0

I know this is old but thought I'd respond as I had to do something pretty similar (the SSJS try/catch will show errors in CloudPages):

<script runat=server>
 Platform.Load("Core","1");
</script>

<script runat=server>
 try{
</script>

%%[

    SET @folderID = "5137|5138"
    SET @folderIDRowSet = buildrowsetfromstring(@folderID, '|')

    /* Retrieve Dataextensions lists */
    SET @rr = CreateObject("RetrieveRequest")  
    SetObjectProperty(@rr, "ObjectType", "DataExtension")  
    AddObjectArrayItem(@rr,"Properties","CustomerKey")  
    AddObjectArrayItem(@rr,"Properties","Name")  
    AddObjectArrayItem(@rr,"Properties","DataExtension.ObjectID")

    /* IN filter requires at least 2 folderids above */
    SET @sfp = CreateObject("SimpleFilterPart")  
    SetObjectProperty(@sfp, "Property", "CategoryID")  
    SetObjectProperty(@sfp, "SimpleOperator", "IN")  

    for @b= 1 to rowcount(@folderIDRowSet) do
        SET @folder = field(row(@folderIDRowSet, @b),1)
        AddObjectArrayItem(@sfp, "Value", @folder)
    next @b

    SetObjectProperty(@rr, "Filter", @sfp)  
    SET @retrieve = InvokeRetrieve(@rr,@status)  

    /* Retrieve DE name and count */
    for @a= 1 to rowcount(@retrieve) do
        SET @retrieveRow = row(@retrieve, @a)
        SET @DEName = field(@retrieveRow, 'Name')
        SET @DECount = DataExtensionRowCount(@DENAME)

        OUTPUTLINE(v(@DEName))
        OUTPUTLINE(v(@DECount))

    next @a
]%%


<script runat=server>
}catch (ex){
    Write(Stringify(ex));
}

You must log in to answer this question.

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