API

Adventures in Marketing: The Highlights

A few years ago, in a foray outside my normal sphere of influence, I spent a few months working cross-functionally to build out an elaborate Executive Marketing Dashboard for the Marketing team at Seagate. 

First, some context: In the past, the Marketing department's leadership has driven their monthly meetings through PowerPoint presentations.  Much of the normal work for analysts across the department would grind to a halt while data was pulled, groomed, graphed, and compiled into slide decks.  Leadership was looking to move to Tableau to stabilize the subject matter, track to consistent goals, and drive the business through an automated set of instruments.

A recent copy of the Executive Marketing Dashboard

I was lucky enough to have some spare time at work around the holiday season of 2016, and while browsing the jobs list for a friend, I noticed an intriguing job description for a Marketing Analyst.  The post's technical needs were consistent with my capabilities, but the experience within marketing was lacking.  That, and I really enjoy my current job.   On a lark, I reached out to see if I could share how my team does what it does, in exchange for some hands on experience into the marketing world.  And that's how I found myself as the technical owner of Seagate's Executive Marketing Dashboard. 

Know your Audience, NOt just your Data!

Whenever you're building a dashboard, it's crucial to understand both your data and your audience.  There's a relationship there, between the two, and it will emerge in a properly designed dashboard. 

In my experience, executive management generally needs to stay at a high level view of their business.  In the case of the EMD, the need to stay high level was emphasized by the sheer number of topics getting presented within a relatively short forum. 

So rather than designing the dashboard as a drill-into-detail dashboard, this was serving to smoothly transition management from a static, PowerPoint presentation into a new world of interactive analytics.  The requirements I was given included some strict guidelines: No filters, no drilling.   Management wanted it to essentially be a one-pager, with 10 different visualizations based on 10 different data sets, all on the same page.  Right off the bat, this means every viz has to be crafted with an attention to dramatic spacial constraints: each one was going to get only about 300 x 400 px worth of room.  Fortunately, since filters and parameters take up space, these requirements weren't at odds with one another. 

Do not adjust your screen

This causes text to scale differently.

For better or worse, management tends to skew towards both towards farsighted folks and owners of fancier Windows computers with higher resolutions, which tends to mean they use a higher DPI scaling setting.

Enter: the Long Form View.  Each viz in the main dashboard is reproduced on a longer form dashboard, and thus are given ample room to breathe, solving for both Window's scaled up fonts and the 50+ crowd that forgot to bring their reading glasses to the meeting.

EMD's Long View, with buffer and goal parameters visible, giving mgmt the flexibility to tell their own story.

Choose your own ending: Buffers

One benefit of presenting the vizes in two different ways, I was able to sneak in a bit of clever instrumentation that I call buffers.  If you build a set of calculations that finds the minimum and maximum values on a chart, and then add a "buffer" constant to them, you can sneak in a hidden reference line which has the subtle effect of re-calibrating the scale of the axis it is built upon. 

buffer implementation explained.png

So, if normally your line chart looks as jagged as the Rockies, you can alter a parameter  that drives the buffer constant (I guess it's a variable now) to scale out the axis such that the peaks and valleys are no more thrilling than the rolling hills of Ohio.  Now, I know this isn't scientifically sound, tinkering with the axis like this, but remember, we're working for Marketing today, not Engineering!

Like I said, you gotta know your Audience!

Show a lot of data in a little space

The biggest visualization challenge I had was how to display all the data for the Amazon Marketing vizes.  I had two categories of advertising, AMS and AMG, which had their own vastly different scales, their own goals, spend, revenue, and the relationship between revenue and goal. So right off, they need to be separated. 

green and red in tooltip.png

Because there was so much to track, I needed to find ways of keeping the rev-to-goal obvious, without being overwhelming.  Since the most important factor is "did we make goal", that point is emphasized in redundant ways.  With the color scheme implemented in three ways, combined with the check/x symbols, it is crystal clear which quarters met goal. 

At that point I still hadn't shown spend relative to goal beyond a pass/fail, so I added goal lines based on a monetized goal.  The goals are multiples of spend, so I built a calculation based on parameters.  Then I drew goalposts using reference lines.  In this way, viewers can also easily see how well we did relative to goal.

 

 

 

Getting to know Marketing data

I spent the overwhelming majority of my time getting to know - and automate - the data sets involved in this dashboard.  The data sets are diverse enough in origin and type that most merit their own blog posts.  I'm quite proud of my work on this project because not only did I accomplish the primary goals of building a viz tailored for my audience, but the data sources are automated and in all cases, such automation had never been achieved within Seagate.  No one in Marketing, to my knowledge, had automated Google Analytics into Tableau, and no one had ever automated Sysomos and iPerception data into Tableau using their respective APIs.  This aspect - blazing a trail, proving out and sharing these huge quality of life improvements for my fellow analysts, that has been immensely satisfying to me.  The weeks and months since have been dedicated to training up my fellow analysts on how to take these lessons and expand them to all their day-to-day reporting.

A few highlights from that adventure:

For the entire dashboard, the goal was an automated dashboard that pulls nine different metrics from six very different data sources:

  • Click Thru Rate via Google Analytics

  • Time on Site via Google Analytics

  • Sessions via Google Analytics

  • Amazon AMS Spend, Goal, & Revenue via Google Sheets

  • Amazon AMG Spend, Goal, & Revenue via Google Sheets

  • Master Marketing Budget via Excel on a shared network drive

  • Social Media Followers via iPerception

  • Internet Community Brand Sentiment via Sysomos API.

  • Email Marketing Engagement and Purchasing via SFDC and Business Objects

Scripting Google Sheets Filters...

This one was way, way harder than I would have figured, but I should come to expect no less from Google's App team.

So, Filters are a great feature in a spreadsheet.  I love them.  You love them.   We all love them. 

For some reason, Alteryx's Google Sheets Output Tool is honoring filters.  That is, if you have a row filtered out, and You ask the Google Sheets Output Tool to "Overwrite Sheet (Drop)" which sounds an awful lot like "nuke that shit from orbit", well, it won't nuke row 2, because it's filtered.

Yeah, that's kinda bad. One upside to this whole "honor thy mother and filter" concept is that it does keep the formatting on the sheet, which is, probably good?  I dunno.

So, I needed to script the "clearing" but probably not the "removal" of every filter in the spreadsheet.  Seems simple; I've done all kinds of crazy formatting, so this seemed like a trivial task.  Nope.  You can do it though.  You just have to go through some crazy hoops.  Here we go:

You have to "Enable Advanced Services"  What follows is a lot of annotation, which will quickly become obsolete, because Google hates me^tm.

To use an advanced Google service, follow these instructions:

  1. In the script editor, select Resources > Advanced Google services....
  2. In the dialog that appears, click the on/off switch next to the service you want to use. 
  3. At the bottom of the dialog, click the link for the Google API Console.
  4. In the console, click into the filter box and type part of the name of the API (for example, "Google Sheets API")
  5. On the next screen, click Enable API.
  6. Close the API Console and return to the script editor. Click OK in the dialog. The advanced service you enabled is now available in autocomplete.

Step 2.

Step 3

Step 4a

Step 4b

Step 5

Bad news, too: You have to do this for  every "project" (spreadsheet) that you want to script these sorts of filter clearing behaviors on, and find a way to run the script before Alteryx outputs its data.  Not especially cool...  Hopefully Alteryx can come up with a better solution.

Anyway, once you've got all that done.  You can run this script:

function clearFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetId = ss.getActiveSheet().getSheetId();
  var requests = [{
    "clearBasicFilter": {
      "sheetId": sheetId
    }
  }];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}

 

Adventures in Marketing: Connecting to an API and the Download Tool

This is one of the cooler workflows I've built in Alteryx:

A finger on the pulse of the internet...  Can you spot the month with no Reddit data?

When working with the Download tool, to start, imagine you're building one or more URLs. In total, the URL will likely: identify who you are, what you want, and for what time periods you want it.

In this case, I want a count of mentions of Seagate that are positive, negative, neutral, for various forums and social platforms on the internet, for 6 complete historical months and the current month.

The Sysomos API has great documentation on how I write the various API calls that, when combined, will give me all of these details without hitting a row limit on their responses.

The green boxes on the left are built to quickly reconfigure the API requests.

For ease of maintenance, I choose to break out the various API inputs such that I only ever need to edit one cell to make a given change to the API call.  In total, I create 21 distinct API calls to get all the data I need for this workflow.  I make requests based on three sentiment types, across seven calendar months: (positive, negative, neutral) x ( -6 thru 0 month ages).

 

These are all the "common" payload parameters for the Sysomos API

These are common inputs.  You should always expect to have a root URL.  You'd usually also expect to have at least one or two API Keys that uniquely identify you as the requester.  It's possible that publicly available APIs don't require a Key. Notice how I don't use all of these arguments.  I filter the rows out if they resolve to null.

Humans like many rows, machines prefer columns...

You might notice I enter each API parameter on a unique row and transform them using the crosstab tool.  I do this because rows of data are very friendly to read and manage in Alteryx, and that's not true if I have more than a couple columns.  So I crosstab the columns in order to turn the variableNames into headers for columns, with a total of 1 row of data (the variableNames).

You'll find that if you have one row of data with all common data, then you can append the varying data in order to get all of the unique combinations you're going to need for a complete data set.  I do this twice, once for the 3 sentiments, and then again for the 7 months.

Now, for a simple GET request, it's been my experience that configuring the download tool is pretty straightforward. I have had success leaving most settings defaulted, but I'd review your unique use case (and the API manual) to ensure you're complying with their requirements.

Here's how I'm set up.  First, I tell it where to find the URL and I do tell it to encode the URL Text.  That converts weird characters into URL compatible characters.  That's including the "colon" I use to delimit the various forum types.

Configuring the payload based on the parameters I decided to use.

Now, in this case, all my headers are static; they never change, so I just pre-compile them into  my URL.  It may be that you  need to vary your headers, in which case that tab applies to you, (Headers are just the parts of your URL separated by "/").  So, skipping to the Payload tab, I have the following attributes that I call, each of which is a unique column in my data.

You'll notice this is also where you would change the API type from GET (or FTP) to POST, etc.

You can also just take it all from one field or just in a big text input box, I think, as an alternative to checking multiple values by field.  I could see that being useful in more strange APIs that require enhanced customization.

 

So far, every API I've worked with has returned JSON, XML or csv.  Most only offer the first two, so check out the JSON Parse and XML Parse tools to wrap up your workflow.  Once out the other side, it is likely that you will need to further refine the data, particularly if you get JSON outputs.  Oftentimes the data is not structured in a tabular way because it's nested into groups of records.  If that's the case, the good news is, it's still programatically structured and you can programatically restructure it!

For my part, if I can, I've tended to use a csv output, writing the data to as many temporary files as I need, one for each API call, and then I'll use a dynamic input tool in order to pick up those files and continue my work.  The reason why I prefer csv is that the data is already given in columnar format, obviating quite a few steps, particularly when compared to JSON, which requires a handful of transforms before heading to Tableau for visualization.