@Salesforce Data Management: Picklist-Generated Country & States/Regions/Provinces

Triumpant: Salesforce Counry & State/Region/Prefecture by Pickli
Triumpant: Salesforce Counry & State/Region/Prefecture by Pickli

A New Business Intelligence Hub for CBS Alumni

The user experience is calculatedly thus:

Enticing hints of what’s contained within – along with realistic expectation setting for something published so publicly.

So, after the movie explains why our ‘chaperoned’ romp through partially disguised data allows an alum in networking or job search mode to identify some prospects/targets, indicate their choices to a club administrator who balances privacy concerns against a stronger, more robust network by asking the target alums for specific permission to share (fortunately it’s almost always given)

Then on to a couple of reports & visualizations, all the while exhorting the alum to register for an access-controlled site for supervised exploration.

Salesforce Contacts, Employer Accounts and Non-Profit Edition Relationships carry the majority of the payload — supplemented by visualization tools like Google Maps Engine and SharePoint 2013’s robust security settings.

Click on the image to take a look at the public offerings.

The New CBS Business Intelligence Hub
The New CBS Business Intelligence Hub



Spot-Checking Record Type Agreement against Expectations

When we last left ZoHo’s cloud-based Business Intelligence tool

it was with the promise –or was it a threat? — of coming back to discuss matters honestly.  To jar your memory, here is the screenshot of the counts of record types:

Annotated ZoHo Reporting
red needs further investigation
green is exactly or almost exactly as one would expect
yellow is pretty good
oh, provided you’re using a non-free account, it’s basically one-click publishing of your data. Take that, SharePoint 2013!


the power of summary functions
the power of summary functions


So, going row by row:

  • University Alum Club has slightly more contacts than organizations — and that’s fine.  From Columbia Business School (henceforth “CBS”) Club’s perspective, anyone in the file who’s an alum of another club is likely either a programming partner (in which case they should more likely be affiliated with that club’s Board – but that’s not universally true) or if not a subject matter expert helping us with programming development, s/he attended one of our events, we got some money from them, and aggregating those (relatively small) sums at their club level is as defensible a way as any — and might help us track who to be extra solicitous towards.
  • Alumni Club Head Hierarchies are a placeholder mechanism meant to unify relationships among clubs as is seen here.  These records don’t represent anything real in the world — they’re just a convenient way to encode a hierarchical relationship in the database.
  • The other MBA clubs seem sound enough — (though truth be told there are a surprisingly large number of clubs, so I’ll have to go double check that with some troubleshooting).  But logically those MBA club’s members/public would be the most likely of any alumni to attend my programming, so it would only stand to reason that they would be accumulating many more members with attendance and/or revenue association with them for each organization I tracked.
  • The CBS Alumni case is actually the weakest – and that phenomenon has two causes. But I’ll return to that explanation, which is lengthier, after I finish up the other lines.

Business Intelligence in the Cloud: Zoho Reports

I discovered today in the chrome webstore a nifty little business intelligence offering from Zoho.

Zoho Reports

So I uploaded some exports from Salesforce.  First we’ll take a look at the login activity data, which begins to point towards how one audits things in the multidimensional space that is a database in the cloud, towards which lots of web services are making calls.

Salesforce Login Log Export - in Zoho Report (Table)
Salesforce Login Log Export – in Zoho Report (Table)

The summary function reporting of Zoho’s BI Tool is just like a SQL/MS Access GroupOn[Value] query.  It enables us to take this table of 1,691 rows and look at the clustering of values.  To tmake this interesting, I choose to Group On (and thereby collapse around) the LoginType field. And count the records to produce the following distribution histogram:

 Distribution Histogram of Login Type:  Humans aren't even close!

Distribution Histogram of Login Type: Humans aren’t even close!

Absolute Automation is the name of an app by IHance, and it’s an email matching app that takes all email to my address and tries to find a Salesforce record to attach them to — it makes for a very thorough approach to CRM, which is rather exactly what we’d expect from Salesforce.com

Cirrus Insight is an app that syncs Google Apps contact data with Salesforce — and enables creating new accounts & contacts & leads from within the Gmail interface..  Those 175 entries via the browser — that’s me as the admin: a living, breathing mortal who is a mere  piker in comparison to the hard working apps  Such is the beauty and power of Cloud computing


Record Type Agreement between Salesforce.ACCOUNT object and Salesforce.CONTACT.

SF.CONTACT fields are shaed in Gray; SF.ACCOUNT fields are in Blue; Dark blue is redaction of Alumni personal data
SF.CONTACT fields are shaed in Gray; SF.ACCOUNT fields are in Blue; Dark blue is redaction of Alumni personal data

Record type agreement — after all my bellyaching about the importance of a record type schema that can handle the complexity of the milieu in which an Ivy League Alum Club operates. Record type agreement is one way to track if one’s practice lives up to one’s theory.   Furthermore, this little exercise is providng an awfully convenient excuse to dig deeper in Zoho Reports.  Pretty nifty the  way it’s  just a few short clicks until you can make some interesting discoveries.

The image below shows a portion of the 1700 plus rows in the table.  The grey shaded portion are SF.CONTACT object fields; the light blue are SF.ACCOUNT object fields.  And the dark blue are redaction on my part to safeguard my alumni data.

When I was enthusing earlier about dataloader.io, this is why:  if you don’t pull over related records’ actual fields, to look at a Salesforce export — well for a human, it’s often not an easy read: long strings of digits in which upper v. lowercase actually counts!

One nice diagnostic test to run is to compare counts of Salesforce CONTACT records, by record type, against the number of organizational ACCOUNT records, by record type.  The logic of the nature of the relatioships that are to be expected helps one to ascertain how well the coding schema is working.  So, again, using GroupOn ACCOUNT.Organization Record type:  what inferences can we make about the SF.CONTACT records by type?

Take a look at the entires in the report and, as Linda Richmond would say: “discuss amongst yourselves.”

I’ll use non-Linda-Richmond diction by noting that I’ll return to this anon.

red needs further investigation green is exactly or almost exactly as one would expect yellow is pretty good oh, provided you’re using a non-free account, it’s basically one-click publishing of your data. Take that, SharePoint 2013!
red needs further investigation
green is exactly or almost exactly as one would expect
yellow is pretty good
oh, provided you’re using a non-free account, it’s basically one-click publishing of your data. Take that, SharePoint 2013!


More Walking that Walk: Salesforce.com Coding Schema for an Cloud-Based Alum Club

So, I had been regaling you the way the person, me:

Darren Scott Kowitt, a new yorker by birth but a washingtonian by circumstance  got to new york first by way of new haven (isn’t that coy!) and in new york I studied marketing at Columbia Business School before coming to DC for love

Now Salesforce.com is  a robust platform.  It likely could accomodate stories like that without too much difficult int the hands of the appropriate database & platform administrator.  but all that broadband personhood i suggestively sketched above — well, it has to be shoe-horned into an unmovable and unforgiving fact about Salesforce.com.

it’s staring you in the face with the name: it’s got sales in its DNA. and sales means nothing without [ACCOUNTS]..  Where there are [ACCOUNTS] there may be people.  so in terms of the Salesforce.com instance for Columbia Business School Alumni of MetroDC , this is how it plays out concretely:

I, Darren Kowitt, who graduated from Columbia in 1997, I Darren am represented in the database when first loaded/created as:

[ACCOUNT]=MBA’97: Kowitt, Darren

[ACCOUNT].[CONTACT] = Mr. Darren S. Kowitt, residing at…born on…

with as much detail as External Relations in New York cared to provide me with

note how the [ACCOUNT] record is functioning/quacking like an Alum Household — to which a subsidiary partner or spouse might be easily attached — and, provided the coding schema is rigorously applied, and attached in such a way as to be acknowledged and included where appropriate — but not gratuitously and carelessly.  this is not an accident.  It’s important to remember that coding is a choice of how to represent reality (in all its potential complexity) in the database.

running an ivy league alumni club in a major metro is stimulating and at its best, fun.  but it takes a lot of partnering.  the people i deal with, wheedling, cajoling, begging, borrowing — oh and let’s not forget: drinking with, conversing expansively with, but also receiving upon their arrival at Union Station,. these personages, some august, others less so — well there’s no easy way to categorize them all: so here’s an attempt at conveying to you the breadth of their diversity:

  • Alumni (they’re easy: that MBA’YY: LName, FName sees to that)
  • Spouses do show up, but not as often as you might think
  • Professors visit from new york
  • Admissions stages its dog & pony show each fall
  • Applicants paw at us, rending our garments in their over-eager enthusiasm to simply put their nose up against the iorn fence. But I digress
  • the Columbia Business School Alumni Board of course has much traffic with the board of the Columbia University Club of Washington, DC (and in truth, my alums are truly, fully Columbian in both senses)
  • but we don’t always keep in the family: sometimes we even collaborate on programming with those people from Cambridge, Philadelphia, Chicago, and Palo Alto (the dirty secret of competitive MBA populations is: in the long run, excellence converges)
  • i’d be remiss in not mentioning congress members, their staff – and the economics/policy beat journalists  we run with
  • Compass Strategic Guidance. Stronger Non-Profits,
  • and then there is a wonderful organization called CompassDC whose purpose is to harness all that pan-ivy-league intellectual firepower towards helping regional non-profits change for the better and indeed even thrive.  every fall they recruit pro bono volunteers for a 7 month collaborative consulting project.

Some of the relationships are essentially/practically permanent:  for better or worse, I shall remain myself until I die.  thus MBA’97: Kowitt, Darren is quite strongly tied to Mr. Darren S. Kowitt.  not all relationships are quite so durable, however.  and this is not merely truth spoken from a broken heart.  an Alum Club Board has terms of office — or at least in theory good governance principles somehow suggest that, even if  such evasions of the unspoken still amount to quite the opposite of permanence,  and those Compass projects are by their nature fixed in term.

So the data I want to capture in my CRM system is, to be polite, heterogeneous.  And thus every time I set about creating a new Alum in the database, am I faced with this  choice

Ar the moment of creation: what a choice!
Ar the moment of creation: what a choice!

–somewhat paralyzing to the unititiated, I’m afraid.   and i’ll leave you to mull over that for now.

Walking the Walk: the Mass Update Wizard


I’m Darren Kowitt, a data geek with an MBA in marketing from Columbia Business School.  in 2009 I came to realize that the local chapter of Columbia’s alumni club was eligible for free licenses of Salesforce.com enterprise edition from Salesforce.com Foundation.  And thus my manager’s love-affair with cloud computing was born.

if wisdom comes from recognizing one’s limits, i’m the first to admit that I’m no developer or engineer.  i self-taught myself MS Access with VBA way back in ’98, and over the years, I’ve been quite pleased with what once can do in Access as a modeling environment – especially with multiply nested queries and dynamic scoring.

for years i’ve had all these extra Salesforce.com licenses — i almost couldn’t give them away. but this fall, our board is expanding a bit, and next week the new board is going to be let in (notice how I used the passive voice, hinting not so subtly as my anxiety as I think about strangers romping through my data!)  and i looked around and realized that even I who take some care not to generate codes unnecessarily, had let the number of record types (for accounts) and page layouts proliferate.

so: I turned to my trusty, if unwieldy friend, the Mass Update Wizard

Ungainly by Useful
Ungainly but Useful

And by holding Account.RecordType constant, and doing a quick visual scan of whether it makes sense to quickly ad an attribute to a few records here — and then do the update based upon that attribute being NULL in the target records to be updated, or by noticing which two or three letters or letter combination will give me the most bang for the buck:  well, I whittled it down rather nicely

my club has about 1000 alumni in the database.   the account records use the following naming convention

I’m Darren Kowitt, MBA Class of ’97

so my AccountName becomes:  MBA’97: Kowitt, Darren

and the affiliated Contact is Mr. Darren S. Kowitt

So far, so good. that covers the AccountRecordType=Alumnus case.  but we have all kinds of people and organizations that we interact with.  not surprisingly for an alumni club, my constituents want to know about who’s working where.  So naturally, we have Accounts of the RecordsType=Employer variety — not that we ever expect to earn any revenue from those organizations whatsoever.  (but then again: that’w where having an employer-specific page layout makes sense — keep any related items related to sales & revenue visually suppressed on the page!)

wow. it’s getting late. but i’ll write more as soon as i can.