Transforming data Before Syncing with Census Datasets

The Problem: Your good data is always one request away.

Your data team built some great data models in your warehouse; it could be with dbt, or could be plain ol’ SQL — the typical Accounts, Contacts, and Teams golden models. Now you work with another vendor. Maybe a third-party enrichment provider writes open job listings and description data for your warehouse, like Sumble.com. Or perhaps you have an enriched target accounts list generated in another marketing tool.

Now, you want to join all of that data together to:

• Create new enriched targeting audiences in LinkedIn ads
• Sync denormalized company info on the contact objects in Salesforce

You could write a ticket for your data team to update their dbt model, which can take days. Or, you can use Census Datasets to do it in 30 minutes. Read on!

Setting up Census

In this guide, we will:

• Set up Census datasets using SQL
• Assign relationships between our datasets in Census
• Build a segmented audience using the four datasets we created
• Sync our audience to LinkedIn to target them with our ads
• Create a sync to Salesforce to empower our sales team with better data

Let’s get going! 🎉

BTW, I assume you already connected your warehouse to Census.

Step 1 - Create Our Datasets

This is as simple as selecting a table with select * from your_table, or you can do some transformation and join in SQL right there, too. We will create four datasets.

The steps are straightforward, as you can see in this short animation:

1. We create a dataset from an SQL query.
2. We select the Dataset Type. This makes certain fields mandatory, which makes it easier to set up segments, syncs, and more down the line.
3. We set up the relationships with other datasets so we don’t have to write SQL in the future.
4. That’s it, we’re done!

We now have four datasets created, ready to be activated 👨‍🚀:

• Users: Our app users
• Accounts: Companies our users work at
• Events: Behavioral events users are taking
• Subscriptions: Subscription plans attached to the accounts
• Job Intents: Job post listings that accounts are currently hiring for

Step 2 - Building Segments using data from many datasets

You rarely sync the whole kitchen sink dataset. Usually, you want to create a LinkedIn audience of certain types of accounts, for example:

• High-paying customers with many open jobs to promote our latest product to help them hire faster
• Low-paying, highly active customers that could be ripe for an upsell
• Or worse, high-paying customers with low usage that are at risk of churn 😱

The Shape of Our Datasets

Let’s use Census Audience Builder to do just that across our datasets. In our case, we can see the subscription has the following interesting fields:

• Plan name
• Monthly Recurring Revenue (mrr)
• Status

This will make it easy for us to identify our high and low-paying customers.

Similarly, our Events dataset has all the information we need to see which users and accounts are most active.

Calculated columns with no code

Let’s go and create new columns on our dataset to help build our audience even more easily.

We will create three new columns on the accounts:

• Running Total Number of Events
• Number of invites sent
• Number of open jobs in the last 30 days

Let’s head to Census Audience Hub and set up these columns in a few clicks. Calculated columns are a great way to do roll-up functions such as counting associated records (with a filter!), the most frequent, average value of a one-to-many relationship, and much more without writing any code (see docs here)!

Now that we have our dataset and new columns let’s build some audiences 💪.

Putting it all together, building our audience

We will build the following audience together:

• Ex-customers with high MRR and many open jobs to promote our latest product to help them hire faster and win them back.

We define a high-paying customer as an account with a canceled subscription, an MRR of $100, and “many open jobs” as 2 open jobs in the last 30 days.

As with computed columns, you can click your way to success to build this audience.

This is just the tip of what an audience builder offers. You can run experiments against audiences, see their size over time, prioritize audiences, and more!

Step 3 - We have audiences, let’s sync them to LinkedIn

We can now create a sync to LinkedIn Ads by going to the audience and following these four steps:

• Click Destination at the top navbar and select all Destinations.
• Follow the simple sync creation wizard and pick the right identifier, which, in our case, is the website domain.
• If needed, we can even create a new LinkedIn audience from here.
• Set up a sync schedule.
• And we’re done!

Now, whenever our job, subscriptions, or account datasets change, we will always have the freshest audience on LinkedIn to run our campaigns on!

Syncing to Salesforce to empower our sales team

Finally, we want to sync our transformed and trusted data to Salesforce so our teams can build automations, such as Slack notifications and emails to the account owner when there is a surge of activity on the account or when new jobs are open at a customer account.

To enable all of this, we will create a sync to the Salesforce Contact Object and:

• Use the lookup to copy the associated company name to the contact
• Copy the subscription plan name from the subscription (attached to the account) to the contact
• Sync the remaining data from our Users dataset
• [Coming soon, stay tuned 😉] Sync the three calculated columns that we created on the accounts dataset

All of that again without writing code!

It’s a wrap!

Hopefully, by following along, you’ll see how easy it was to go from raw data in your warehouse to activating your data. In this guide, we covered:

• Setting up multiple datasets
• Establishing their relationships
• Building calculated columns on the account dataset
• Using the calculated columns and relationships to build a target audience
• Syncing that audience to LinkedIn to ensure it always uses the freshest data
• Syncing our users to Salesforce to equip our sales team with the best data to manage our users proactively

All of this was achieved with minimal code, leveraging the power of your data warehouse and the best data available. Census offers many more features, such as versioning, support for Python, dbt, audience comparisons, mirror syncs, and much more!

Book a demo with our experts today to explore the full potential of transforming, exploring, and activating your data!

Happy data transforming! 🚀