Custom Reporting Solutions with Customer.io

A new customer recently made the switch away from Salesforce Marketing Cloud. In Customer.io, it is much easier for them to build campaigns and send newsletters, but they missed one thing from SFMC. They used to have the ability to schedule a daily campaign and newsletter metrics overview email. In Customer.io, performance data is visible inside the platform and available through our API, but we do not have an out-of-the-box option for a daily reporting email that exactly matched their needs. That is where Professional Services came in. We worked with the customer to build a custom solution for delivering campaign and newsletter metrics directly to their inboxes every day, using a Google Cloud Function and an API-Triggered Broadcast. 

You could imagine a monster of a Customer.io workflow with enough webhooks to call the metrics endpoint for each campaign, store the metrics in a dummy profile, then trigger an email to internal stakeholders with all the metrics prettily formatted. Given the variable number of campaigns and newsletters, we decided not to go down this route.

Instead, we opted to create an HTTP-triggered Google Cloud Function, written in Python. The function first grabs all the necessary metrics for either newsletters or campaigns based on an entity parameter. Then it sets off an API-Triggered broadcast in Customer.io, passing along all the metrics data. Using Google Cloud Scheduler, the function is called twice each morning at 5 AM – once for newsletters, once for campaigns – and then an email is sent to the internal stakeholders with all of the daily metrics. 

Here is a step-by-step breakdown of what happens when the function is called:

1. Retrieve and store the entity parameter.

if request_json:
    if 'entity' in request_json:
        entity = request_json['entity']
    else:
        entity = "newsletters"
elif request_args:
    if 'entity' in request_args:
        entity = request_args['entity']
    else:
        entity = 'newsletters'
else:
    entity = 'newsletters'

2. Call that entity’s endpoint to retrieve a list of names, ids, and sent_ats (for newsletters) or updateds (for campaigns).
3. For newsletters, calculate the days since sent for each newsletter. If the newsletter was sent within the reporting window (we chose the past 7 days, but it could be any length of time), it should be included in the report.
4. Loop through the list of ids, calling the metrics endpoint for each. Store the response, which includes the entity's metrics. In this case, we stored:
   1. sent_count
   2. sent_datetime (for newsletters)
   3. series
   4. sent
   5. delivered
   6. clicked
   7. bounced
   8. spammed
   9. unsubscribed
   10. opened
5. For campaigns, which are sent on an ongoing basis, an additional call is necessary to calculate the metrics totals over the last 24 hours (or whatever period you like). Newsletters, which are sent all at once, can use the overall metrics totals.
6. Calculate the rates for each of the metrics. (e.g. email open rates, click-through rates, etc)

if opened > 0:
    opened_rate = round((opened/delivered)*100, 2)
else:
    opened_rate = 0

7. Add each resulting metrics to an array called to_sort and then sort the data. Newsletters are sorted by the date they were sent; campaigns are sorted by the total number of messages sent in the past 24 hours.

to_sort.append({
    "id": id,
    "ui_overview_link": ui_overview_link,
    "name": name,
    "sent_at": sent_at,
    "sent_datetime": str(sent_datetime.strftime("%Y-%m-%d")),
    "sent": sent,
    "bounced": bounced,
    "bounced_rate": bounced_rate,
    "delivered": delivered,
    "delivered_rate": delivered_rate,
    "opened": opened,
    "opened_rate": opened_rate,
    "clicked": clicked,
    "clicked_rate": clicked_rate,
    "unsubscribed": unsubscribed,
    "unsubscribed_rate": unsubscribed_rate,
    "spammed": spammed,
    "spammed_rate": spammed_rate
})

8. Lastly, send the sorted entity_data to Customer.io as an API-Triggered Broadcast. We include the entity for use in the template and the date_range for the report. For security purposes, we avoid storing our customer’s API tokens in the script by using Google Cloud Functions’ environment variables (os.environ.get("keyName")). 

url3 = f'https://api.customer.io/v1/campaigns/{apitbID}/triggers'
    apitbHeaders = {'Content-Type':'application/json'}

    apitbData = {
        "recipients":
            {"segment":
                {"id": InternalSegmentID}
            },
        "data":{
            "entity":entity,
            "date_range": yesterday_dt if entity=="campaigns" else "the last 7 days",
            "entity_data":to_sort
        }
    }
    
    r3 = requests.post(url3, auth=(f'{os.environ.get("InternalAPITBAuthUser")}',f'{os.environ.get("InternalAPITBAuthPass")}'), headers=apitbHeaders, data=json.dumps(apitbData))
    res3 = r3.json()

With the data nicely formatted and sent into Customer.io, we built a custom template for these emails. Here’s what they look like:

We hope you find this helpful as you build out your own custom reporting solutions with Customer.io!