Marketing Automation: Sending Data From MySQL to Salesforce Marketing Cloud using Python
Salesforce Marketing Cloud (SFMC) is one of the leading platforms for e-mail and mobile marketing automation and analytics. In a marketplace context, emails and push notifications are usually sent on a daily basis for users having recent navigation data (impressions, clicks, searches,etc).
Data Extensions are objects in the SFMC environment which generally store user data and can be worked just like database tables (actually SMFC uses SQLServer under the hood). And we can even perform SQL queries with these Data Extensions using the QueryStudio environment.
What can be done if one wants to send a specific message to a group of users? Say we want to send a weekly mail message to sellers with late orders (which could lead to an order cancellation) asking them what is happening. We could just run a SQL query locally and export it to an csv file to a newly created Data Extension. But this process is quite cumbersome, as queries can take a long time to run and we could even lose track of our contacts with these setup.
Setting up an automated ETL process which captures internal data (say, a MySQL transaction database) to a Data Extension could make a great solution to this processes improving the overall performance of the marketing team . We can access and transform Data Extension objects through Salesforce SDK for Python and its wrapper, which adds further funcionality to the API.
In this article, a simple ETL process will be illustrated. Suposse we want to select both the ids and e-mails from a given MySQL table. Using Pandas and mysql.connector we could store these data in a dataframe, which we call here user_data_df :
Inspecting user_data dataframe, we see that there are 3 records containing some dummydata (which I manually inserted to a local database to make this example).
Now it is possible to insert data from this dataframe into a Salesforce Data Extension using its External Key, a random code generated after the creation of the Data Extension Object.
In order to update a Data Extension with the new data collected, firstly a DataExtension_Row object is instantiated (de) and two attributes are set: an authentication parameter with the SMFC keys, (which are previously loaded in a variable called de.stubObj ) and the associated External Key of the Data Extension in use (de.auth_stub) . Finally, for each row in the DataFrame loaded, a third attribute (de.props) is set as a dictionary, with the Data Extension field name as a key and then the de.post() method is invoked, populating the Data Extension.
Now, a populated Data Extension arises and the marketing team can kindly communicate with their users :-)
This process can be further improved with the aid of an orchestration tool such as Apache Airflow or Prefect.
