Transforming data Before Syncing with Census Datasets
Sylvain GiulianiMay 30, 2024
Syl is the Head of Growth & Operations at Census. He's a revenue leader and mentor with a decade of experience building go-to-market strategies for developer tools. San Francisco, California, United States
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
🤔 What are Census Datasets? Census Datasets are the way to model all your customer data in Census. Datasets expose your best data to business teams so they can explore and self-serve data.
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
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:
We create a dataset from an SQL query.
We select the Dataset Type. This makes certain fields mandatory, which makes it easier to set up segments, syncs, and more down the line.
We set up the relationships with other datasets so we don’t have to write SQL in the future.
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)!
💡 Calculated columns are a great way to empower users to transform data without writing code, but in a safe environment with trusted data.
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!
