Census is a low-code reverse ETL tool for marketers allowing to easily move data sitting in databases into other tools where you need it. 

If you'd like to send data sitting in Snowflake or BigQuery, we recommend using our direct Snowflake and BigQuery integration. 

You can use Census to send data from other databases to MadKudu into an S3 bucket 

Pre-requisites

• You have a Census account connected to your database (Redshift, Postgres...)

• You have an AWS account to create a S3 bucket 

• You have added this S3 bucket as a destination in Census 

Step 1: Create a Model in Census to format your data

The Models feature in Census allows you to transform the data from your database into the format expected by MadKudu with a simple SQL query. 

1. Log into your Census account and go to Models and click Create new model 

2. Select your database for the source and name the model.
   The naming is up to you but we can recommend: 

   • User Events - MadKudu format : if you are sending product events, web visits.. 

   • Users - MadKudu format : if you are sending contact properties 

   • Accounts - MadKudu format: if you are sending account properties 

   • Account Signals - MadKudu format:  if you are sending account usage properties (like intent)
     Tip: For events, we recommend creating 2 models: 

     • one for the 9 months of historical data you'll sync in one shot, once 

     • the other one that would send on a daily basis your most recent data to the S3  

3. Write a SQL query to get the data from your database and transform into the expected format (please refer to this article detailing the format depending on the data you are sending)

   • Not a SQL expert? We've got you! Our Support team is and can do this live with you, no biggy! 

4. Test your model by clicking Preview results

Here is an example: 

Step 2: Create a Sync in Census to send this model to your S3 

In Census, in the Syncs tab, create a sync 

• Source: select the model you just created

• Destination: S3 for MadKudu. If you have not connected a S3 bucket for MadKudu you'll need to do that first in Connections  

  • File path: path_to_file/data_%m-%d-%y.json 

    
    

    

• In Select a Sync Behavior section, choose Update or Create

  • in Advanced configuration, select the JSON format

    
    

    

    
    

• Append key, select event_id 
  

• S3 field mapping 

  • Select

    • Sync all properties

    • Mapping order 

  • Map your columns to the MadKudu format. The model you create should already have the expected column names. If you had different ones make sure you have them mapped to 

    • event_timestamp

    • event_text

    • email
      + any other relevant properties
      
       
      

• Click Run Test then Next

• You should have a review like this
  

• Click Create

• You'll arrive on the Sync page. in the Alert tab, make sure you'll receive an alert if the connection is broken
   

• Run the Sync 

Step 3: Give MadKudu access to your S3 bucket

MadKudu connects to your S3 bucket via an IAM role. Please follow the instruction starting step 2 in this documentation: Giving MadKudu access to your S3 bucket

Once you're all set, let us know here and we'll start pulling the data from S3