How to download SFMC data as a file via Slack
For many SFMC architects and consultants copying and pasting data from the SFMC UI in a translatable way can be a painful process. The way data is formatted and structured is often hard to manage and time consuming to rebuild into clean, easy to read structures (like a table in a document).
What if there was an easy to use alternative to scanning network traffic, copying, pasting json objects and parsing them in a browser application?
In this article I’ll expand on my previous post and explain how to download data from SFMC using slack as a structured csv file.
This specific example outlines the process to download Data Extension field information. This pattern can be used to download any data from the SFMC SOAP Objects with some minor tweaks to the code provided.
Architecture
This is a continuation from my previous post so much is the same, the only difference in this process is that we are using our GCP micro service to create a file and upload it to Slack’s server rather than creating an object in SFMC and then returning a message to slack.
Cloud Functions
As per my previous article you will need to update the routing or subscriber function that slack is posting to and subsequently build a new function to handle the dedicated request.
1. Subscriber Function:
This is a continuation from the previous article, this function is the HTTPS triggered function that is listening from the slack message and will then route the message into the relevant bucket as a .json file.
import json
import functions_framework
import requests
from flask import Flask, request, jsonify
from datetime import timedelta
from google.cloud import storage
import datetime
#Slack Functions#
#create Data Extension - create a DE with rows
#get Data Extension - will return DE fields
# Register an HTTP function with the Functions Framework
@functions_framework.http
def input_request(request):
body = request.get_json("body")
date = str(datetime.datetime.utcnow())
print(body)
strbody = str(body)
bucketname = "null bucket"
strMessage = body["event"]["text"]
print(strMessage)
# this is for deployment 1 to confirm the URL with slack
"""
challenge_answer = body["challenge"]
return {
'statusCode': 200,
'body': challenge_answer
}
"""
if "create Data Extension" in strMessage:
bucketname = "sfmc-de-sink"
elif "get Data Extension" in strMessage:
bucketname = "new bucket"
if "file" in strMessage or "Data Extension" in strMessage:
bucket = bucketname
storage_client = storage.Client()
bucket = storage_client.get_bucket(bucket)
exportFileName = f"{date}_Slack.json"
blob = bucket.blob(exportFileName)
blob.upload_from_string(str(body),content_type="application/json")
print(exportFileName)2. Handler Function
This function like my other examples is written in Python, It’s triggered by a file landing in a dedicated bucket (the file is basically the message from slack) and will then be processed to understand the BU and the DE Key to then pull the relevant data, create a temp file and then upload it to the slack channel.
In this section of code we are expecting a message that the key phase of “get Data Extension “ followed by the external API Key of the DE (the first Id is the Slackbot member Id that you will need to identify and replace).
if "text" in str(body):
text = body["event"]["text"]
text = text.replace("<@U03VCBQLFQV> get Data Extension ","")
deKey = textIf you want to add additional SOAP attributes to the csv file you can and you would need to update them here in the fields section and the columns sections respectively:
if len(fields) > 0:
fieldsArray = []
for item in fields:
json = { "FieldName": item["Name"],
"FieldType": item["FieldType"],
"IsRequired": item["IsRequired"],
"DefaultValue" : item["DefaultValue"],
"IsPrimaryKey": item["IsPrimaryKey"]
}
fieldsArray.append(json)
df = pd.DataFrame(fieldsArray,
columns=[
"FieldName",
"FieldType",
"IsRequired",
"DefaultValue",
"IsPrimaryKey"
])You will also need to decide which channel to post back to, this may be to the same channel the app is deployed or a sub channel. There’s also an option for a comment too!
files = {
'file': open(path, 'rb'),
'initial_comment': (None, f'Field schema file for Data Extension: {deKey}'),
'channels': (None, 'C03VCA5RTTP'),
}Full Code
def sfmc_api(event,context):
import requests
import json
from google.cloud import storage
import pandas as pd
import ast
import xmltodict
import sys
from os import environ
# find config data
client_id = "Your client ID here"
client_secret = "Your client secrent here"
baseURL = "your unique SFMC API url string here"
#add your token into Key Manager and then user within your cloud function
token = environ["SlackBot_Token"]
token = token.replace("\n","")
triggerfilename = format(event["name"])
bucket = format(event["bucket"])
storage_client = storage.Client()
bucket = storage_client.get_bucket(bucket)
blob = bucket.blob(triggerfilename)
blob =blob.download_as_string()
body = ast.literal_eval(blob.decode("utf-8"))
if "text" in str(body):
text = body["event"]["text"]
text = text.replace("<@U03VCBQLFQV> get Data Extension ","")
deKey = text
else:
sys.exit("no text in request")
SOAPEndpoint = "https://"+baseURL+".soap.marketingcloudapis.com/Service.asmx"
#get token
url = "https://"+baseURL+".auth.marketingcloudapis.com/v2/Token"
data = {"grant_type":"client_credentials",
"client_id":client_id,
"client_secret":client_secret
}
r = requests.post(url, data=data)
print("posted")
body = json.loads(r.content)
token = body["access_token"]
TokenStatus = r.status_code
if TokenStatus != 200:
Failure = 1
StatusMessage = "token request invalid"
else:
Failure = 0
XML = ("""<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
<s:Header>
<a:Action s:mustUnderstand="1">Retrieve</a:Action>
<a:To s:mustUnderstand="1">"""+SOAPEndpoint+"""</a:To>
<fueloauth xmlns="http://exacttarget.com">"""+token+"""</fueloauth>
</s:Header>
<s:Body xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
<RetrieveRequest>
<ObjectType>DataExtensionField</ObjectType>
<Properties>DefaultValue</Properties>
<Properties>FieldType</Properties>
<Properties>IsPrimaryKey</Properties>
<Properties>IsRequired</Properties>
<Properties>MaxLength</Properties>
<Properties>Name</Properties>
<Properties>Scale</Properties>
<Filter xsi:type="SimpleFilterPart">
<Property>DataExtension.CustomerKey</Property>
<SimpleOperator>equals</SimpleOperator>
<Value>"""+deKey+"""</Value>
</Filter>
<QueryAllAccounts>true</QueryAllAccounts>
<Retrieves/>
</RetrieveRequest>
</RetrieveRequestMsg>
</s:Body>
</s:Envelope>""")
XML = bytes(XML,"utf-8")
header = {"Content-Type": "text/xml" }
post = requests.post(SOAPEndpoint, data = XML ,headers=header)
print("posted")
body = post.content
xml_dict = xmltodict.parse(body)
xml_string = str(xml_dict)
xml_clean = ast.literal_eval(xml_string)
fields = xml_clean["soap:Envelope"]["soap:Body"]["RetrieveResponseMsg"]["Results"]
if len(fields) > 0:
fieldsArray = []
for item in fields:
json = { "FieldName": item["Name"],
"FieldType": item["FieldType"],
"IsRequired": item["IsRequired"],
"DefaultValue" : item["DefaultValue"],
"IsPrimaryKey": item["IsPrimaryKey"]
}
fieldsArray.append(json)
df = pd.DataFrame(fieldsArray,
columns=[
"FieldName",
"FieldType",
"IsRequired",
"DefaultValue",
"IsPrimaryKey"
])
path = f"/tmp/{deKey}.csv"
df.to_csv(path, encoding="utf-8",index=False)
headers = {
'Authorization': f'Bearer {token}',
}
files = {
'file': open(path, 'rb'),
'initial_comment': (None, f'Field schema file for Data Extension: {deKey}'),
'channels': (None, 'C03VCA5RTTP'),
}
response = requests.post('https://slack.com/api/files.upload', headers=headers, files=files)
print(response.status_code)
print(response.content)Conclusion
And there you have it, a really simple and nice way to pull structured data from SFMC.
You may also want to consider running a SOAP request to retrieve the External Key of the DE by name before making the current request. That way you will only need to post the DE Name rather than the External key.
