The goal of this is story is to create a method of extracting Key usage data for export and graphical representation in Salesforce. The first output is data to form a graph that looks like this (below) that can be emailed monthly to the customer with personalized info. It serves two purposes, encourage regular contact and acceptance of emails and also to drive contact to our site, particularly the available web apps and encourage their usage.
The graph above represent the number of keys the company owns and the monthly day average over the the past year. A monthly average over 20 days represents keys being used thoroughly and potential opportunities to sell more. It's necessary to represent the total keys on the graph as well because new purchases tend to dilute numbers if they aren't done at the beginning of a month.
For instance, the graph above shows TopGrade starting with two keys in 2017, buying four more in July, another in October, and finally two more in January of 2018. The supporting spreadsheet data that generates this graph looks like this:
| #keys 2017 | 2017 avg key days | #keys 2018 | 2018 avg key days | |
| Jan | 2 | 19.5 | 9 | 18.4 |
| Feb | 2 | 25.5 | 9 | 18.2 |
| Mar | 2 | 28 | 9 | 21.9 |
| April | 2 | 22 | 9 | 19.4 |
| May | 2 | 25.5 | ||
| June | 2 | 24.5 | ||
| July | 6 | 13.7 | ||
| Aug | 6 | 19.2 | ||
| Sept | 6 | 17.5 | ||
| Oct | 7 | 15.9 | ||
| Nov | 7 | 19.4 | ||
| Dec | 7 | 19 |
The actual spreadsheet in Google sheets is available here: https://docs.google.com/spreadsheets/d/1wpYmuhTi1NCTWrMNuwEonuveZiPXDvSkkSZDAPaUDrY/edit?usp=sharing
Data Anomalies Seen
To put together the mockup using real data there were a couple of anomalies that showed up:
- George Reed/Modesto showed 32 Days in the Usage Data when April only has 30 Days.
- The key count when pulling for April 2017 on GoodFellow - Top Grade Construction shows nine although they actually only owned 2 keys at at the time
Proposed Export Format
This data is intended for import into Salesforce (could also be a spreadsheet) so a CSV file is probably the simplest for Salesforce to import. Required fields are:
| CompanyID | month - yr | key # | Avg Key Days |
Example
CA4495764,July 2017,6,13.7 CA4495764,August 2017,6,19.2 CA5219761,July 2017,2,26 CA5219761,August 2017,2,31 ... (all customers in July and August)
From a data perspective my instinct was to separate month and year but the Salesforce display is apparently easier with them combined.
Proposed Export Method
To initially populate Salesforce Allen would like to pick a month range (Jan 17 - last month 18') for all customers and generate the CSV file. After that pulling just the last month (say May 18') for all customers and adding the data in would be sufficient. New Report Created: “Monthly Key Day Average Export” for all customers in CSV format
Potential Future Story
There's been no decision made yet on exposing this information to the customer on the website reporting. Currently only Enterprise customers are allowed to use reporting for just two reports. The process of sending these graphs out to customers as part of an email is likely to generate interest. Marketing and Sales need to decide what we want to expose in the future and we'll write the story from there when they make a decision.
Another potential option is to automatically email the monthly report in CSV to Allen/Marketing but for now just allowing them to pull the report manually is probably sufficient. I could also see us creating reporting opt/ins as part of the User Profile page we're working on.