Replacing Segment Computed & SQL Traits With dbt & RudderStack Reverse ETL | RudderStack

neub9
By neub9
5 Min Read

“`html



Migrating from Segment to RudderStack: A Step-by-Step Guide

Migrating from Segment to RudderStack

We recently helped a customer migrate from Segment to RudderStack and decided to document it in a series of blog posts because we thought it might be useful to data engineers considering making a similar migration. In our last post, we discussed the differences between Segment Personas and RudderStack. As a follow up, we wanted to take a deep dive into how data teams can use open source tools like dbt from dbt labs and RudderStack Reverse ETL to leverage the power of your data warehouse to sync enriched users, audiences and other data to downstream tools.

Laying the groundwork

It will be helpful to set the stage, as no two technical stacks are the same and not all data warehouse platforms provide the same functionality. It’s for the latter that we really like tools like dbt, and the sample files provided here should provide a good starting point for your specific use case. Our instance leverages the dbt cloud and connects to our Snowflake data warehouse, where data models output tables in a designated dbt schema.

Why Do This In The First Place?

There are two basic reasons. The first is because your data warehouse has important information about your users that comes from data sets from other parts of your stack, both internal and external. This could be something as simple as measuring lead engagement by counting the number of track calls for that particular user, or something complex like running that same user through a machine learning model workflow to calculate a likelihood of churning in the next 24 hours. The benefit of RudderStack is that by having all of this data in your warehouse, your options are limitless in terms of how you enhance your user profiles.

The second reason follows from the first. The more holistic a view you create of your customers, the better your results will be. Creating a custom Facebook audience derived from first time purchasers of a new product is great, but including data from customer complaint service tickets, warranty claims, and returns will significantly increase the efficacy of any campaigns that your marketing runs to that audience.

–Maximilian Werner, Obsessive Analytics

The Basics – Enhancing Users In Customer.io

Here at RudderStack, we use Customer.io to support email marketing initiatives, and we needed the ability to segment audiences based on where a user happens to be in the sales funnel as well as his or her engagement with the free version of our app. This requires aggregating certain data points collected from the RudderStack application as well as our Salesforce instance. Our application streams live events, like logins and connections created, and we leverage the RudderStack Cloud Extract pipeline to ingest various objects from Salesforce.

For our initial example, we want to create specific campaigns around whether or not a user has engaged with the application — have they logged into the app and created anything (source, destination, transformation, or connection)? We also want to differentiate the messaging based on whether the lead is the first person from their organization (or email domain) to convert on our website.

There are also additional properties we can pull in from the lead record in Salesforce, such as whether the lead has been flagged as a Marketing Qualified Lead (MQL), the Industry of the organization for targeting certain verticals, and the user’s first and last name as that may not have been submitted to Customer.io as of yet.

Lastly, for this example we want to calculate a lead score which takes elements from their app engagement and Salesforce lead profile and combines them into a composite number that sales and marketing can use to inform their activities.

The dbt Model

We added the following SQL to our customer_io_demo.sql model in dbt. Now that we have the dbt model created and executed, we can see the output table and results in our Snowflake schema. As a reminder, the model name becomes the name of the table in dbt.

Create a RudderStack Reverse ETL

The next step is to create a RudderStack Reverse ETL to send this data to Customer.io.



“`

Share This Article
Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *