Snowflake Secure Data Sharing
Snowflake is a purpose-built SQL cloud data warehouse for all of your data and all of your users. With Snowflake’s unique and patented architecture it’s easy to amass all of your data, enable rapid analytics, and derive data-driven insights for all of your users.
Braze leverages Snowflake’s Data Exchange to build a presence, find new customers, and expand reach through the ever-growing Snowflake customer base.
Learn more about this partnership here!
What is Data Sharing?
Data Sharing allows Braze to allow you secure access to data on our Snowflake portal without worrying about workflow friction or slowdown, failure points, and unnecessary costs that come with typical data provider relationships.
If you’re interested in this integration, reach out to your Braze Account or Customer Success Manager and ask them to consult Braze Data Strategy Services on Secure Data Sharing with Snowflake. This will get the cogs going inside Braze and we’ll have your views set up in no time!
You must have an account with Snowflake to use the Data Sharing Services.
Usage & Visualization
Similar to Currents, you can use your Secure Snowflake Data Sharing to…
- Create complex reports,
- Perform attribution modeling,
- Secure sharing within your own company,
- Map raw event or user data to a CRM (like Salesforce)…
And so much more!
After Braze sets up your views on Snowflake and grants you the access you need, we will send you some views. This data is similar to what you’d see in Currents.
You’ll have access to historical, current, and future data dating from the beginning of when that data was sent to Snowflake up until you choose not to utilize Snowflake anymore.
You can do whatever you want with the data, but we recommend using it in conjunction with our Looker Blocks.
Below, you can see query samples for some possible use cases.
You can use this Push Funnel query to aggregate push sends raw event data, through to deliveries raw event data, through to opens raw event data. This query shows how all the tables should be joined, since each raw event typically has a separate table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT COUNT(DISTINCT users_messages_pushnotification_send."ID" ) AS "users_messages_pushnotification_send.push_sent", COALESCE((COUNT(DISTINCT users_messages_pushnotification_send."ID" )),0)-COALESCE((COUNT(DISTINCT users_messages_pushnotification_bounce."ID" )),0) AS "users_messages_pushnotification_send.push_delivered", COUNT(DISTINCT users_messages_pushnotification_open."ID" ) AS "users_messages_pushnotification_open.push_opens" FROM USERS_MESSAGES_PUSHNOTIFICATION_SEND AS users_messages_pushnotification_send LEFT JOIN USERS_MESSAGES_PUSHNOTIFICATION_OPEN AS users_messages_pushnotification_open ON (users_messages_pushnotification_send."USER_ID")=(users_messages_pushnotification_open."USER_ID") AND (users_messages_pushnotification_send."DEVICE_ID")=(users_messages_pushnotification_open."DEVICE_ID") AND ((users_messages_pushnotification_send."MESSAGE_VARIATION_ID")=(users_messages_pushnotification_open."MESSAGE_VARIATION_ID") OR (users_messages_pushnotification_send."CANVAS_STEP_ID")=(users_messages_pushnotification_open."CANVAS_STEP_ID")) LEFT JOIN USERS_MESSAGES_PUSHNOTIFICATION_BOUNCE AS users_messages_pushnotification_bounce ON (users_messages_pushnotification_send."USER_ID")=(users_messages_pushnotification_bounce."USER_ID") AND (users_messages_pushnotification_send."DEVICE_ID")=(users_messages_pushnotification_bounce."DEVICE_ID") AND ((users_messages_pushnotification_send."MESSAGE_VARIATION_ID")=(users_messages_pushnotification_bounce."MESSAGE_VARIATION_ID") OR (users_messages_pushnotification_send."CANVAS_STEP_ID")=(users_messages_pushnotification_bounce."CANVAS_STEP_ID")) LIMIT 500
You can use this daily Email Messaging Cadence query to analyze the time between emails that a user receives.
For example, if a user received two emails in one day, they would fall under
0 “days since last received”. If they received one email on Monday and one on Tuesday, they would fall into the
1 “days since last received” cohort.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 WITH email_messaging_cadence AS (with deliveries as (select TO_TIMESTAMP(time) AS delivered_timestamp, email_address AS delivered_address, message_variation_id as d_message_variation_id, canvas_step_id as d_canvas_step_id, campaign_name as d_campaign_name, canvas_name as d_canvas_name, id as delivered_id, rank() over (partition by delivered_address order by delivered_timestamp asc) as delivery_event, min(delivered_timestamp) over (partition by delivered_address order by delivered_timestamp asc) as first_delivered, datediff(day, lag(delivered_timestamp) over (partition by delivered_address order by delivered_timestamp asc), delivered_timestamp) as diff_days, datediff(week, lag(delivered_timestamp) over (partition by delivered_address order by delivered_timestamp asc), delivered_timestamp) as diff_weeks from PUBLIC.USERS_MESSAGES_EMAIL_DELIVERY group by 1,2,3,4,5,6,7), opens as (select distinct email_address as open_address, message_variation_id as o_message_variation_id, canvas_step_id as o_canvas_step_id FROM PUBLIC.USERS_MESSAGES_EMAIL_OPEN), clicks as (select distinct email_address as click_address, message_variation_id as c_message_variation_id, canvas_step_id as c_canvas_step_id FROM PUBLIC.USERS_MESSAGES_EMAIL_CLICK) SELECT * FROM deliveries LEFT JOIN opens ON (deliveries.delivered_address)=(opens.open_address) AND ((deliveries.d_message_variation_id)=(opens.o_message_variation_id) OR (deliveries.d_canvas_step_id)=(opens.o_canvas_step_id)) LEFT JOIN clicks ON (deliveries.delivered_address)=(clicks.click_address) AND ((deliveries.d_message_variation_id)=(clicks.c_message_variation_id) OR (deliveries.d_canvas_step_id)=(clicks.c_canvas_step_id)) ) SELECT email_messaging_cadence."DIFF_DAYS" AS "email_messaging_cadence.days_since_last_received", (count(distinct email_messaging_cadence."OPEN_ADDRESS", email_messaging_cadence."O_MESSAGE_VARIATION_ID") +count(distinct email_messaging_cadence."OPEN_ADDRESS", email_messaging_cadence."O_CANVAS_STEP_ID"))/(COUNT(DISTINCT email_messaging_cadence."DELIVERED_ID" )) AS "email_messaging_cadence.unique_open_rate" FROM email_messaging_cadence GROUP BY 1 ORDER BY 1 LIMIT 500
You can use this Email Clicks query to analyze the interactions with specific emails in your Braze Campaigns and Canvases.
Set Up this Query
Create a database for
BRAZE, then create database if none exists for
1 2 3 4 5 6 7 8 9 10 11 use schema BRAZE_CURRENTS.public; create or replace stage braze_currents.public.braze_data url='s3://tl-braze/' credentials = (AWS_KEY_ID = 'XXXXXXXXX' AWS_SECRET_KEY = 'YYYYYY' ); create file format braze_currents.public.currents_avro type = 'avro' compression = 'auto'; alter stage braze_currents.public.braze_data set file_format = braze_currents.public.currents_avro; show stages;
Use the following command to create your table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE TABLE braze_currents.public.users_messages_email_click ( id STRING, user_id STRING, external_user_id STRING, time INT, timezone STRING, campaign_id STRING, campaign_name STRING, message_variation_id STRING, canvas_id STRING, canvas_name STRING, canvas_variation_id STRING, canvas_step_id STRING, send_id STRING, dispatch_id STRING, email_address STRING, url STRING, sending_ip STRING, user_agent STRING );
Use the following command to create or replace your pipe:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 CREATE OR REPLACE PIPE pipe_users_messages_email_click auto_ingest=true AS COPY INTO braze_currents.public.users_messages_email_click FROM (select $1:id::STRING, $1:user_id::STRING, $1:external_user_id::STRING, $1:time::INT, $1:timezone::STRING, $1:campaign_id::STRING, $1:campaign_name::STRING, $1:message_variation_id::STRING, $1:canvas_id::STRING, $1:canvas_name::STRING, $1:canvas_variation_id::STRING, $1:canvas_step_id::STRING, $1:send_id::STRING, $1:dispatch_id::STRING, $1:email_address::STRING, $1:url::STRING, $1:sending_ip::STRING, $1:user_agent::STRING FROM @braze_currents.public.braze_data/currents/dataexport.prod-03.S3.integration.YOUR_INTEGRATION_ID_HERE/event_type=users.messages.email.click/); show pipes;
Do More with this Query Example
notification_channel from the output of the command above and use that when configuring S3 bucket notifications.
Manually sync from S3 to Snowflake for the pipe name given below:
1 2 3 ALTER PIPE pipe_users_messages_email_click refresh ;
Check the pipe status, which will show when the message was forwarded from S3 into Snowflake.
1 2 3 4 SELECT SYSTEM$PIPE_STATUS( 'pipe_users_messages_email_click' )
Finally, show the copy history for the table by selecting
1 table(braze_currents.information_schema.copy_history(table_name=>'users_messages_email_click', start_time=> dateadd(hours, -1, current_timestamp())));
Braze Benchmarks will allow Braze prospects and customers alike to see how they compare to top players in their industry by comparing their metrics against Braze’s industry benchmarks.
We created a Data Tool for Braze Benchmarks so you can view a selection of the data, outside the Snowflake interface.
The initial industries are: Delivery Services, Ecommerce, Education, Entertainment, Finance, Gaming, Health, Lifestyle, Restaurants, Retail, Technology, Transportation, and Travel.
Our benchmarking data will also be available directly in the Snowflake Data Exchange.