BRAZE TUTORIAL — SQL — How To Exports Users Who Opened/Clicked A Specific Email Campaign

BRAZE TUTORIAL — SQL — How To Exports Users Who Opened/Clicked A Specific Email Campaign

Buckle up for some SQL and the Query Builder!

It’s true that Braze has limited capability when it comes to user-level reporting. Most of the tools under the Analytics page provide Campaign or Canvas-level data, but there are 2 places in Braze that provide user-level data, so let’s check those out!

Segment Filters

The first place we’ll look at is the Segment Builder.

Even though it might feel a bit weird to be using the Segment Builder to create a report, it’s actually one of the few places in Braze where we can actually export user-level data.

And Braze also offers a handful of different ways to filter and segment our users, so this tool can be really helpful, especially for this use case.

Navigate to Audience -> Segments -> Create Segment, click the “Search filter” dropdown, and search for the keyword “opened”.

You should see a filter called “Clicked/Opened Campaign”.

By the way, we are operating on the new Segment Builder which does offer a bit more capability, so if you haven’t already adjusted to this new UI, now might be your chance!

Click the filter, and on the new dropdown, choose “clicked email”.

Lastly, choose the desired Email Campaign.

If you’re also interested in users who opened the email, we’ll just add the exact same filter again, except choose a different interaction this time.

Click “Search filter” dropdown again, and this time, choose any of the three “opened email” filter. Keep in mind the subtle differences among these three options, which we may cover in a future video.

Last thing to note is that if you’re looking for users who EITHER clicked OR opened an email, you want to choose the “OR” logic in between the filters.

If you are, in fact, looking for users who both clicked AND opened an email, then you can switch that “OR” dropdown to “AND”.

Click “Save” on the bottom right corner, scroll up to the top, click “User Data”, then click “CSV Export User Data” to export a CSV of users who fall under this Segment.

This CSV will be sent via email shortly.

Query Builder

The second way to export a list of users who clicked or opened an email is through the Query Builder.

When Braze finally launched the Query Builder and gave us, the Technical Marketers, access to its internal Snowflake tables, this unlocked a ton of new capabilities for Braze users within the dashboard without having to set up an external data pipeline.

Navigate to Analytics -> Query Builder -> Create SQL Query -> SQL Editor.

Before we write our query, let’s take a look at all the different data tables. The two tables we’re interested in are Email Clicks and Email Opens.

Search for “click” in the table search bar, and you’ll find a table called USERS_MESSAGES_EMAIL_CLICK_SHARED. This is a table that collects all instances of any clicks on any email, performed by any user, gathered at the user-level, and here are all the available columns under this Email-Click table.

Similarly, if you search “open”, you’ll find a table called USERS_MESSAGES_EMAIL_OPEN_SHARED which has all instances of any email opens.

Now, thanks to the AI Query Builder feature, you actually don’t need to know any SQL to write these queries. Yes, you do need a foundational understanding of how SQL works to confirm your query, but let’s try writing this query using the AI Query Builder.

Click the “AI Query Builder” feature, and for our prompt, let’s insert this:

“All clicks or opens of a specific email campaign”

Click “Generate”, and Braze spits out a nice query with some instructions.

We’ll need to grab the desired Campaign API ID which can be found at the bottom of your Campaign Analytics page.

Grab that ID, and replace the placeholder text with your actual Campaign ID.

Now, in our For Now Marketing dashboard, we unfortunately don’t have enough message data to display a meaningful result.

However, when you run this in your dashboard with meaningful email volume, you should see a bunch of rows that are broken out by click data and open data, and additional columns for each of those rows.

Lastly, click “Export”, and you should get a full list of these users who have clicked or opened the specific email.

Thank you!

Thank you for reading, and please reach out with any questions!

fornowmarketing.com

allan@fornowmarketing.com