How to do aggregations in Salesforce Marketing Cloud?

How to do aggregations in Salesforce Marketing Cloud?

Would you like to be able to find the number of orders each customer has made in a certain period of time? Or calculate the average order value for each customer? Or maybe you want to count the number of emails sent to each subscriber in the last 30 days? All that and more is possible with the aggregation functionality in Salesforce Marketing Cloud.

In this article, we will explain how to use aggregations in Salesforce Marketing Cloud (SFMC) using SQL and DESelect.

How to use aggregations in SFMC using SQL

SQL is great at aggregating data the way you might in a pivot table in Excel. The functions are similar to what you’ll find in Excel or certain analytical tools. For instance, The GROUP BY statement groups rows that have the same values as summary rows, like “find the number of customers in each country”.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()), to group the result-set by one or more columns. Here you can find a brief explanation of what each function means:

  • AVG – calculates the average of a set of values
  • COUNT – counts rows in a specified table or view
  • MIN – gets the minimum value in a set of values
  • MAX – gets the maximum value in a set of values
  • SUM – calculates the sum of values.

In this article, we’re going to dig deeper into the COUNT aggregation that allows you to do a count on a data extension or data view and store the results in your target data extension.

In the scenario we’re going to demonstrate, we’d like to know the number of times each subscriber has opened an email in the last 30 days.  On top of that, we’d also like to limit the result to the first 20% of subscribers.

Here’s how the SQL query for the following scenario will look.

Aggregations SQL that you can do in DESelect

(SQL query using Aggregations)

In our example, we’re using the Customers data extension along with the data view _Open. Normally, an aggregation is only a part of the segmentation a marketer has to perform. However, for non-technical marketers it can sound like a challenge. Fortunately, there are alternatives to writing SQL in SFMC if you want to use aggregations… like DESelect!

How to use aggregations in SFMC using DESelect

In the video below you can find a step-by-step guide to using aggregations in DESelect.

For convenience, DESelect offers different screens depending on the level of the marketer’s maturity. For instance, if you don’t know how your data model is set up exactly, you can use the ‘Basic’ screen, where the relations between your data extensions are already pre-defined. In the video, for example we’re using this screen to create an aggregation. However, if you are more confident with your data model, you can use the advanced screen. You can read more about how to create a segment using the advanced aggregation screen here.

If you’d like to book a live demo to see how DESelect avoids the need for code in your advanced segmentation, one of our experts would love to help you.

Conclusion

In this article, we explained how to use aggregations in Salesforce Marketing Cloud using SQL and the no-code alternative, DESelect. If you want to see DESelect live and leverage aggregations with an easy drag-and-drop, book a demo with one of our experts.

Latest Articles

  • Unveiling New Reporting Features in DESelect Engage

    Webinar Transcript Time-Smart Marketing: Import Journey & Automate Retries with Engage Don’t miss the next one Read the release notes from this feature: here Written transcript from this webinar will be available soon. In the meantime, please watch a recording of the webinar on Youtube (above).     Don’t miss an update       […]
  • Harnessing the Power of Relative Dates in Salesforce Marketing Cloud (SFMC)

    The ability to adapt and react with timely precision is key in the marketing world. Marketing Cloud Engagement (MCE), also often referred to as Salesforce Marketing Cloud (SFMC), offers robust tools to facilitate such agility, particularly through the use of relative dates. This feature enables marketers and SFMC admins to automate campaigns with a precision […]
  • 14 Data & AI Challenges Collected from 57 Leaders in MOPs (Transcript)

    Webinar Transcript 14 Data & AI Challenges Collected from 57 Leaders in MOPs Don’t miss the next one Download the report mentioned in this webinar: here Anthony Lamot: Hello! And welcome to a brand new webinar by DESelect. I’m here today, joined by my co-founder, Jonathan, and I’m Anthony, and we are DESelect for Salesforce. […]
  • Kyra Constam, Creative & Web Manager

    Company Culture Meet Our Team: Creative & Web Manager, Kyra Constam In the dynamic world of design, Kyra Constam stands as a definition of talent and innovation. Get her take on her DESelect journey →   Her journey at DESelect began with the title of Senior Graphic Designer, where her talent and hard work shined […]

Join our newsletter to receive updates and helpful SFMC guides.

Reach the most targeted<br/> audiences in half the time

Reach the most targeted
audiences in half the time

Deselect icon
Subscribe to the Newsletter
Receive Salesforce Marketing Cloud tips,
tricks, and DESelect updates

By clicking the “Subscribe now” button, you agree to the DESelect Terms of Use and Privacy Policy.