Google Sheets

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);
}