In this recipe, you’ll learn how to be automatically notified with the top activity happening on your website and app. In this example, we will be using top products (product viewed or page viewed event). However, this same recipe can be applied to other elements that receive traction throughout the day such as articles or popular posts. The members of your sales team will be able to quickly react to products that are high in demand.
Step 1: Create an incoming Webhook URL in Slack
In your Slack custom integration settings, create a new Incoming Webhook URL by selecting a Slack channel associated with your account.
Step 2: Set up Snowflake as a source for Reverse ETL (rETL)
In your Segment workspace, navigate to Reverse ETL -> Sources and click Add Source.
Select Snowflake and then name your source on the Basic Settings tab.
Have your Snowflake Account ID, Database Name, Warehouse, Username, and Password availableand follow the instructions on the Connection Settings tab to connect your Snowflake source.
Step 3: Build a model to pull data from your Snowflake table
In your Segment workspace, under Reverse ETL -> Sources, click into your newly created Snowflake source and select Add Model -> SQL Editor.
Create your SQL query to pull the data from your Snowflake table. An example query for this model would look like this:
SELECT PRODUCT, COUNT(*) VIEWS, URL, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS ID FROM ACME_MDWHO.ACME_PRODUCTION.POPULAR_PRODUCTS GROUP BY PRODUCT, URL LIMIT 3
Enter a unique identifier column. In this example,
ROW_NUMBERis being used named
IDso use ID as the unique identifier column in the box below the query.
Preview the results and make sure there are no errors.
Enter a name for the model, and set how frequently you would like it to be performed. If you want to receive the top 3 products being viewed every 2 hours, select ‘every 2 hours’.
Step 4: Setup Slack as a destination and map the table data as Post Message.
In your Segment workspace, navigate to Reverse ETL -> Destinations and select Add Destination.
Select Slack and connect it to the Snowflake source.
Name the destination and click Create Destination.
Enable the destination and Save Changes.
Navigate to the Mappings tab of the Slack destination and select Add Mapping.
Select your model at the top, and then select the Post Message mapping.
Add the webhook URL from Slack in the top left box and map it to Webhook URL.
Customize your Slack Message by inserting properties from the payload and using Slack text formatting to help make your message highly readable in the second left box that maps to Message.
Insert the Slack channel name in the next box that maps to Channel.
To have the message come from a Slack user, add the Slack user handle to the next left box that maps to User.
To add an icon, insert the url of the icon in the next box that maps to Icon URL.
Once mapping is complete in sections 1-3, click Test Mapping in section 4. If there are any issues with your mapping, you will receive the information you need to correct the issues within section 4. Once your test is successful, click Create Mapping.
Test the mapping and once you confirm it’s working, click Save.
Toggle Mapping State to Enabled to enable the mapping.
Go to your Slack Channel and find the messages automatically created by rETL:
In this recipe, we showed how to connect to our table in Snowflake and map data from our table to a Post Message Slack Destination.