Visualize Data Extension Rows With Chart.js

Display Aggregated Data Extension Data Using CloudPages With Chart.js

Jason Hanshaw
May 29th, 2020 – 25 minute read

When developing for Salesforce Marketing Cloud, a common request that you might get from clients (or fellow developers) is a solution to visualize data that isn't easily represented with the native functionality provided by Marketing Cloud. Whether you want to impress your business department with sleek engagement charts or create an interactive user-experience for your customers, having the capability to display aggregated data in an understandable way is important.

In this blog post, I'll highlight one such method using CloudPages, Code Resources and the Chart.js Javascript library.

Setup:

Obviously before we can display our data in a meaningful way, we'll need to gather and place it into a coherent structure. For this example, we're going to create a data extension with some simple engagement metrics for the last five days, and then populate that table with some random values for demonstration. Our data extension will look something like this:

Column	DataType
eventDate	Date
sent	Number
open	Number
click	Number
bounce	Number

While this post will cover the implementation and formatting necessary for displaying our data extension values with Chart.js, it will not serve as a comprehensive guide on chart customization available with that library. For more information on this subject, please visit the documentation link below.

Chart.js Introduction

Create JSON Code Resource:

Next, we'll create a JSON Code Resource that will serve as the back-end for our implementation. All that we need to do is to initialize our data extension, using the customerKey, and then output the results in a JSON format for use within our example.

<script runat=server>
Platform.Load("Core", "1");
// User Filter For Limited Response Size Or Pagination
var data = DataExtension.Init("Simple Engagement Table").Rows.Retrieve(filter);
Write(Stringify(data));
</script>

Create CloudPage For Displaying Chart:

Then, we'll need to create a CloudPage that will display the actual Chart.js output that we want to show our users. To do this, we'll add some HTML that will provide the following:

A helpful title and subheader to describe the data we are displaying
A series of buttons that we can toggle to show different types of data based on user-selection
The canvas element that will render our chart data

Let's take a look and see what that markup looks like:

<div class="wrapper">
  <div class="main-panel">
    <div class="content">
      <div class="row">
        <div class="col-12">
          <div class="card card-chart">
            <div class="card-header ">
              <div class="row">
                <div class="col-sm-6 text-left">
                  <h5 class="card-category">EMAIL</h5>
                  <h2 class="card-title">Performance</h2>
                </div>
                <div class="col-sm-6">
                  <div class="btn-group btn-group-toggle float-right" data-toggle="buttons">
                    <label class="btn btn-sm btn-primary btn-simple active" id="0">
                    <input type="radio" name="options" checked>
                    <span class="d-none d-sm-block d-md-block d-lg-block d-xl-block">Sent</span>
                    <span class="d-block d-sm-none">
                    <i class="tim-icons icon-single-02"></i>
                    </span>
                    </label>
                    <label class="btn btn-sm btn-primary btn-simple" id="1">
                    <input type="radio" class="d-none d-sm-none" name="options">
                    <span class="d-none d-sm-block d-md-block d-lg-block d-xl-block">Opens</span>
                    <span class="d-block d-sm-none">
                    <i class="tim-icons icon-gift-2"></i>
                    </span>
                    </label>
                    <label class="btn btn-sm btn-primary btn-simple" id="2">
                    <input type="radio" class="d-none" name="options">
                    <span class="d-none d-sm-block d-md-block d-lg-block d-xl-block">Clicks</span>
                    <span class="d-block d-sm-none">
                    <i class="tim-icons icon-tap-02"></i>
                    </span>
                    </label>
                    <label class="btn btn-sm btn-primary btn-simple" id="3">
                    <input type="radio" class="d-none" name="options">
                    <span class="d-none d-sm-block d-md-block d-lg-block d-xl-block">Bounces</span>
                    <span class="d-block d-sm-none">
                    <i class="tim-icons icon-tap-02"></i>
                    </span>
                    </label>
                  </div>
                </div>
              </div>
            </div>
            <div class="card-body">
              <div class="chart-area">
                <canvas id="email_stats"></canvas>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>

As you can see, we're using a card-based layout that specifies our chart type as Email Performance. We're also using radio inputs, that we'll style as buttons, in order to offer our users the ability to alter chart data based on the engagement types present in our data extension. Note that each radio input is assigned a unique Id, which is what we'll key off of in our script to determine selection.

Finally, we'll declare our canvas node and assign it a unique Id value that we can use to bind our chart to that element.

Formatting Data Extension Data:

With our markup established, and our Code Resource page serving our Data Extension data, we're ready to format our JSON and prepare it for display. To begin, let's make an asynchronous call to our Code Resource using the getJSON() Jquery method. For the point of demonstration, we'll forgo a promise-based approach or error-handling and make this a dead-simple implementation:

$.getJSON("YOUR JSON RESOURCE PAGE", function(json) {

});

Now that we've got a method for bringing in our JSON data, let's do some simple date formatting of our eventDate values for presentation and then assign these to the extended_timeline variable which will be the x-axis of our chart implementation.

$.getJSON("YOUR JSON RESOURCE PAGE", function(json) {
    daily_obj_arr = json;
    var extended_timeline = [];

    for (var j = 0; j < daily_obj_arr.length; ++j) {
        var getDate = daily_obj_arr[j].eventDate;
        var formatter = new Intl.DateTimeFormat("en", {
                month: "short",
                day: "numeric"
            }),
            newDate = formatter.format(new Date(getDate));
        extended_timeline.push(newDate.toUpperCase());
});

We can then use the map() method to isolate our data into individual arrays based on the engagement type so that we can give our users the ability to select between them.

$.getJSON("YOUR JSON RESOURCE PAGE", function(json) {
    daily_obj_arr = json;
    var extended_timeline = [];

    for (var j = 0; j < daily_obj_arr.length; ++j) {
        var getDate = daily_obj_arr[j].eventDate;
        var formatter = new Intl.DateTimeFormat("en", {
                month: "short",
                day: "numeric"
            }),
            newDate = formatter.format(new Date(getDate));
        extended_timeline.push(newDate.toUpperCase());

    var sent = daily_obj_arr.map(function(a) {
        return parseInt(a.sent);
    }); 
    var clicks = daily_obj_arr.map(function(a) {
        return parseInt(a.click);
    });
    var opens = daily_obj_arr.map(function(a) {
        return parseInt(a.open);
    });
    var bounces = daily_obj_arr.map(function(a) {
        return parseInt(a.bounce);
    });
});

Our data is now bucketed and formatted correctly for use within our Chart.js implementation. Let's move on to the final piece of this post, which will deal with the actual formatting and creation of the chart.

Chart Configuration:

For our chart configuration, there are a few functions that we'll need to setup in order for our data to display and to be configurable by the end-user. First, let's write a simple function that will selectively highlight which engagement type the user has selected from our radio inputs in our markup.

initPickColor: function() {
    $('.pick-class-label').click(function() {
        var new_class = $(this).attr('new-class');
        var old_class = $('#display-buttons').attr('data-class');
        var display_div = $('#display-buttons');
        if (display_div.length) {
            var display_buttons = display_div.find('.btn');
            display_buttons.removeClass(old_class);
            display_buttons.addClass(new_class);
            display_div.attr('data-class', new_class);
        }
    });
}

Now comes the important piece of actually configuring what type of chart we will show, how the data should be mapped and any styling options that we'll want to apply to ensure out chart is visually appealing as well as functional.

First, let's go ahead and create a function that will house our configuration and initialize some styling options for our chart.

initDashboardPageCharts: function() {
    gradientChartOptionsConfigurationWithTooltipPurple = {
        maintainAspectRatio: false,
        legend: {
            display: false
        },
        tooltips: {
            backgroundColor: '#f5f5f5',
            titleFontColor: '#333',
            bodyFontColor: '#666',
            bodySpacing: 4,
            xPadding: 12,
            mode: "nearest",
            intersect: 0,
            position: "nearest"
        },
        responsive: true,
        scales: {
            yAxes: [{
                barPercentage: 1.6,
                gridLines: {
                    drawBorder: false,
                    color: 'rgba(29,140,248,0.0)',
                    zeroLineColor: "transparent",
                },
                ticks: {
                    suggestedMin: 60,
                    suggestedMax: 125,
                    padding: 20,
                    fontColor: "#9a9a9a"
                }
            }],
            xAxes: [{
                barPercentage: 1.6,
                gridLines: {
                    drawBorder: false,
                    color: 'rgba(225,78,202,0.1)',
                    zeroLineColor: "transparent",
                },
                ticks: {
                    padding: 20,
                    fontColor: "#9a9a9a"
                }
            }]
        }
    };
    var chart_labels = extended_timeline;
    var chart_data = sent;
    var ctx = document.getElementById("email_stats").getContext('2d');
    var gradientStroke = ctx.createLinearGradient(0, 230, 0, 50);
    gradientStroke.addColorStop(1, 'rgba(72,72,176,0.1)');
    gradientStroke.addColorStop(0.4, 'rgba(108,102,251,0.3)');
    gradientStroke.addColorStop(0, 'rgba(119,52,169,0)'); //purple colors
};

As you can see, we're using the object gradientChartOptionsConfigurationWithTooltipPurple to provide some basic items that will make our chart responsive, hide the legend (in favor of our selectable radio input approach), format spacing and styles, etc... Next we'll initialize our variables used for our x-axis timeseries and selected label. Then we'll instantiate our chart and bind it to the email_stats Id that we specified on our canvas element in our markup. Finally, we'll add a gradientStroke to our chart data for additional styling.

With our chart instantiated, and our base-styling configurations set, we'll now move towards implementing the config object for our chart that will specify the key items.

var config = {
    type: 'line',
    data: {
        labels: chart_labels,
        datasets: [{
            label: "Sent",
            fill: true,
            backgroundColor: gradientStroke,
            borderColor: '#6C66FB',
            borderWidth: 2,
            borderDash: [],
            borderDashOffset: 0.0,
            pointBackgroundColor: '#6C66FB',
            pointBorderColor: 'rgba(255,255,255,0)',
            pointHoverBackgroundColor: '#6C66FB',
            pointBorderWidth: 20,
            pointHoverRadius: 4,
            pointHoverBorderWidth: 15,
            pointRadius: 4,
            data: chart_data,
        }]
    },
    options: gradientChartOptionsConfigurationWithTooltipPurple,
    scales: {
        yAxes: [{
            ticks: {
                min: Math.min.apply(this, chart_data),
                max: Math.max.apply(this, chart_data),
                stepSize: 5
            }
        }]
    }
};

While there are several moving parts to this configuration, let's highlight the most important ones that impact the basic functionality of our chart.

type: 'line'

This specifies the type of chart that we want to display on our CloudPage. For a full listing of all the available chart types, visit the Chart.js documentation page linked earlier in this post.

data: chart_data

This configuration tells Chart.js what data we would like to display on our chart. Note in the implementation above, the initial configuration is set to display our sent array that was mapped in our initial formatting of the data extension output. By default, the chart will display this dataset first.

min: Math.min.apply(this, chart_data),
max: Math.max.apply(this, chart_data),

This piece sets the range of values for our y-axes. We could declare explicit ranges if we wanted to keep this axis consistent across our engagement types but, for our example, lets set these values to scale with the minimum and maximum values in our data set.

Lastly, we'll need a function that will update our chart configuration above whenever a user selects a different engagement type. This function should update both the data and label properties of our config object so that the chart responds accordingly. To do this, we'll key off of the unique Id's we've assigned to our radio inputs in the markup and then re-instantiate our chart with the new data set that maps to that Id selection.

var myChartData = new Chart(ctx, config);
$("#0").click(function() {
    var data = myChartData.config.data;
    data.datasets[0].data = sent;
    data.datasets[0].label = "Sent";
    data.labels = chart_labels;
    myChartData.update();
});
$("#1").click(function() {
    var chart_data = opens;
    var data = myChartData.config.data;
    data.datasets[0].data = chart_data;
    data.datasets[0].label = "Opens";
    data.labels = chart_labels;
    myChartData.update();
});

$("#2").click(function() {
    var chart_data = clicks;
    var data = myChartData.config.data;
    data.datasets[0].data = chart_data;
    data.datasets[0].label = "Clicks";
    data.labels = chart_labels;
    myChartData.update();
});

$("#3").click(function() {
    var chart_data = bounces;
    var data = myChartData.config.data;
    data.datasets[0].data = chart_data;
    data.datasets[0].label = "Bounces";
    data.labels = chart_labels;
    myChartData.update();
});

Conclusion:

chartDemo

That's it! Now we've got a dynamic solution for visualizing data extension data on a CloudPage in a way that's visually appealing and helpful for our users. Following the above guide, this solution could be easily configured to use different data structures, chart types and custom styles to provide an experience tailored to your specific use-case.

In addition to this blog post, you can find the code for this example in this github repository.

chartjs

cloudpage