MIS homework
Max's Distinctive, Impressive BizTech Student Blog
S u n d a y , S e p t e m b e r 2 5 , 2 0 1 6
DataMatic: It Slices, It Dices, Visualizes & Analyzes
Ok, so what IS a database, really? Despite being a vegetarian (mostly), I’m getting it this way—a database is to data as KFC is to chicken. It slices, it dices, things whirl around and bing...out comes just the parts you want, done to perfection. Some people want certain parts, done a certain way. Others want different parts, done another way. Databases are no different, really, except you don’t need a wet‐nap afterwards (one hopes).
This is good because Riley raved on my app (yessssssssss! crowd goes wild, $200 added to checking, yay). But when she explained more about everything she has to deal with to make this VC/angel stuff work, it got me thinking. The more I thought, the more sure I got—I could get the app to be way more helpful to what she’s trying to do if I could just figure out how to add some data slicing & dicing. I brain‐dumped with her at Starbucks the next day, sketching my ideas on a napkin in the classic Silicon Valley style, and then the quest for Version 2.0 (and $300 this time!) was on.
BTW—Here’s the splash screen I put together for my little app‐making service, working with the SJSU Spartan theme.
Totally optional, of course, but if you’re highly motivated, you can try to figure out how to embed my pic (or your own) so it flashes for a sec every time when you first open your mobile app. Hint: Start with Mobile Administration (under Administer, at the top, on the lefthand side menu).
Copyright © 2017 The Max Labs Project
So, my fan‐people, after talking with Riley, I cooked up a new, improved version of my app!
From what she told me, I realized she needed a new field, Closability, a percentage (0‐100%). For
each pitch, Closability tells how likely it is that she will close the deal with that VC or angel. Like if
there’s a VC company she’s pretty sure will sign the contract, then she might set the Closability for
that pitch to like 90%. Or if one of the angels is a long shot, she might set the Closability for that
pitch to only 5% (1 in 20). For every pitch, it’s just an estimate, for sure, based on her intuition
from dealing with that firm but it helps her track what to do. You’ll see.
So, to save you time (& steps), I made what SF calls a “package” containing my new Pitch structure
(with Closability added) along with some other stuff I added for later on. Packages are how you
give somebody whatever objects, layouts, apps, programming code, etc that you want to share—
you save it all in a package and SF puts it on their cloud servers at a link you give out for people to
install your package of stuff into their own SF account.
Now, if you’re thinking what I was thinking, you’re right—you don't have to share packages for free.
Packages are how you can sell the apps & such you’ve created to others who want to use them!
When you install a package, it creates the structures that were put into it—in this case, it creates
the new Pitch object and the associated tab, layouts, apps, code, etc. but NOT the data. You have
to export/import that separately, but it’s easy. I’ll show you in a sec. But before we do that, I’ll
show you how to get rid of your Pitch object (and data). Don’t worry—I made up 10 records you’ll
be able to download and import in a flash. (They’re not Riley’s real data, of course.) Start by
deleting your Pitch object:
1. Go to Setup > Build > Create > Objects > Pitch
2. Click the Delete button in the center
3. In the popup window that appears, confirm you want to delete by checking the box that says
“Yes, I want to delete the custom object.”
4. Then click the Delete button at the bottom of the popup window (you might have to scroll
down to find it)
5. A few seconds later you should see Deleted Objects (1), confirming you deleted it. (Note
you can click there to undelete for up to 15 days after deleting!)
Ok so here’s the part where I’m trying to “monetize my IP” (my blog) by getting my readers to chip
in–a paltry one‐time gratuity of $19.99! Riley says this is how blog writers cuts back work hours so
they can keep writing instead. We shall see, but for now:
6. To get the Pitch package that you need (and to see a selfie with my bff, Rocky) just click this
link or type it into your browser:
http://tiny.cc/maxnrocky
7. Now, on the “Max’s Pitch Package Shop” page, click the Buy now with PayPal button (any
credit or debit card works)
8. Add your deets & finish off the buy by clicking Pay Now
9. Check your email & find a PayPal receipt–the top should like one of these below (but with your name, of course)
10. Now, back on the “Max’s Pitch Package Shop” PayPal confirmation web page, follow the steps it shows by entering the email address you used when you got your SF account (the one SF emailed your account verification to), etc & then on the Thank You page, click Install
Now back in SF, you should see an Install Max’s Pitch Package page appear:
11. Click the Install for All Users panel in the middle
12. Click the blue Install button at the right
13. It’ll say “installing and granting access to all users” for a bit & then Installation Complete!
14. Just click Done underneath that, on the right, & you’ll see the one new package on the list
Now you should have the new, improved Pitch object all set up, including the new Closability field. All you need to do is get some data. I made some up and used Excel to put it into a plain text file (.csv) which is a format SF likes for importing. Prof gave me some space on his Google Drive to upload the file so you guys could download it & try this out.
15. Just open a new tab with the link below and Google Drive should open a preview of the file and just use the down arrow on the menu bar at the top of the page to download the file to somewhere on your computer (eg. your desktop) where you can find it later in Step 21
http://tiny.cc/MaxsPitchData
(If you just entered your card info to pay.) (If you logged into your PayPal account to pay.)
16. Then, close the download tab and, back in SF, go to Administer > Data Management > Data
Import Wizard
17. Scroll down and find the big green‐ish Launch Wizard buttons and click it
18. Under “What kind of data are you importing?” click the Custom Objects tab. That should
show up Pitches below
19. Click on Pitches. That will fill in the middle box labelled What do you want to do?
20. In that middle box, click on Add new records
21. Then in the right hand column of the page, you’ll see where you can drag that .csv file you
just downloaded onto the panel labeled Drag CSV file here to upload. SF will fill in the File,
Character Code and Values Separated By fields automatically–just leave them as is.
Or click CSV just below, with the little page icon by it. That should open a panel
below and then under File, click on Choose File which should bring up a popup
window for you to choose a file from your computer’s hard drive. Just navigate to the
.csv file you just downloaded and double‐click it or single‐click to select it and then
click Choose.
(Windows users note: If you can’t find the file, it might be because your file search is set to show Excel files only, so you might need to change that setting to All Files to be able to find the .csv file you just downloaded.)
22. Once you have the file selected properly, the green Next button that was dimmed until now
should be bright, inviting you to click it, so go ahead and click Next
23. The next page that appears, Edit Field Mapping: Pitches, should show a perfect match
between the SF Pitch object’s field names and the column headers in the .csv file and it
even shows you samples of the data that will be going in
24. Click the green Next button
25. The next page that appears should say you have 12 mapped fields and then you can just click
the green Start Import button
26. A popup window should appear with Congratulations (a little weird) and you can just
click OK
27. You’ll get an email saying your Pitches import is complete
28. Then you should just be back in your normal SF environment with a page of stats you can
ignore about the import
Now you’ve got all the data but how can you see it? When you deleted the Pitch object, it trashed your Pitches tab. When you installed the package, it recreated the Pitch object AND the tab, but you need to add the tab back onto the tab bar at the top in order to see it. Here’s how:
29. Click on the plus sign at the end of the tab bar
30. That’ll show you all your existing tabs and you can just click the orange button that says Customize My Tabs on the upper right
31. Find Pitches in the list under Available Tabs on the left and select it
32. Then, click the Add button in the center to move Pitches over to the Selected Tabs list
33. Just click Save and your new Pitches tab should show up now
Now click on your Pitches tab to see the new data. Yikes! Where is it?! (Sorry I couldn’t help it.)
No, you won’t see anything just yet, even though the data really is there. Why? Well, when you go to the Pitches tab, by default, it only shows you records you recently touched. Last time, you had touched them all recently by creating them. This time, you imported mine and haven’t touched them yet so this view shows nothing!
No worries. It’s the perfect opportunity to show you how I learned to customize views, slicing & dicing the data so it’s easier for Riley to get the stuff she needs out of it:
34. First click on Create New View near the center
35. For View Name enter “Pitch Everything”
36. Tab to get View Unique Name to autofill
37. Scroll down to Step 3. Select Fields to Display
38. Under Available Fields, click Address so that it’s selected
39. Hold down the Shift key while you click on Type (lower down on the list) & that should highlight everything from Address all the way down to Type
40. Now click the Add button in the middle to move all twelve (12) fields over to the Selected Fields list
41. Now at the bottom, under Step 4. Restrict Visibility, select Visible to all users (Includes partner and customer portal users)
42. Click Save
Now you should see all ten (10) records you imported from my file with all the custom fields showing but you’ll have to scroll sideways to see everything. See? I told you it was all in there.
And here’s a nifty feature:
43. Click on the column header for the Business Name field (near the left, by Address)
Notice that all the records (rows) automatically reordered themselves alphabetically by Business Name!
44. Now do the same thing to the Closability field by clicking on that header.
Notice the records are now sorted by Closability from lowest to highest.
45. Click on the Closability header again to see it reorder from highest down to lowest.
See how this gives Riley a lot of power to browse her data? But wait, there’s more!
46. Click on Create New View again
47. For View Name enter “Angels Only” & tab to let View Unique Name autofill below
48. Scroll down if you need to to find Step 2. Specify Filter Criteria
49. Under Filter By Additional Fields (Optional):, below the Field column, click ‐‐None‐‐ to open the pull‐down menu of fields to choose from
50. Click on Type
51. The menu immediately to the right, under Operator, will automatically switch to equals
52. Next find and click the little magnifying glass icon immediately to the right of the Value column slot next to the equals operator
53. That should open a popup window containing the two possible values of Type to choose from
54. Click the checkbox for Angel and then click Insert Selected. That should make the popup disappear and place “Angel” into the Value slot next to equals back on the form
55. Below, under Step 3. Select Fields to Display, move these fields, one at a time, over to the Selected Fields list: Business Name, Investment Amount, Equity Percentage, Closability and Comments, in that order. (If you already did it in a different order, it’s ok—you can select any one of them in the Selected Fields list and use the Top, Bottom, Up and Down buttons immediately to the right to move them into the right order)
56. Like before, under Step 4. Restrict Visibility, select Visible to all users (Includes partner and customer portal users)
57. Click Save
See the new listing? Cool slicing, eh? Now for some dicing.
58. Notice the menu of available views up above the list, showing Angels Only as the current view. You can pull this down to select a different view at any time
59. For now, stay on Angels Only but click on Edit, immediately to the right
60. Now clear out “Angels_Only” from View Unique Name so it’s empty & then clear View Name too & type in “Loaded & Likely Angels Only” & tab to fill in View Unique Name
61. Scroll down to where you had already set the filter to Type equals Angel
62. In the 2nd filter row, set the Field menu to Investment Amount, set the Operator to greater than and enter “200000” (5 zeroes, no commas) into the Value slot
63. In the 3rd filter row, set the Field to Closability, set the Operator to greater or equal and enter “10” into the Value slot (be sure you pick the one with “equal” too, not just >)
64. Scroll down and click Save
Now, you’re seeing only those five custom fields for the just the two (2) pitches where it’s angels talking about more than $200,000 AND there’s at least a 10% chance of closing the deal.
This is how I set up a bunch of views for certain slices & dices that Riley wants to see all the time. She just logs in, hits the Pitches tab and then picks whatever views she wants to see. And all the views are always automatically updated—whenever new Pitch records are added, they automatically show up in whatever views Riley’s viewing (assuming they pass through the filters).
Riley’s super busy so she loves this b/c it helps her see how to target certain types of investors and avoid wasting time with others. She thinks I’m like some kind of elite techno‐wizard! But check out the rest...
So how about seeing a couple of different slices at the same time, so you can compare them, etc?
And how about getting stats on those slices, like average Investment Amount for VCs versus angels?
No problemo:
1
65. Click the Reports tab & if a yellow tour window pops up just click No Thanks to get rid of it
66. Click the New Report button and you’ll get a page with a bunch of folders listed
67. Click on the plus sign (+) next to the Other Reports folder, at the bottom, to open it up and see Pitches listed
68. Click on Pitches then click the Create button at the bottom right corner
69. Kill the annoying yellow window again if it pops up and then you should see a report Preview pane in the center with a Pitch ID column already there and on the left, a column of Pitch fields with little icons to tell what type of data they hold, eg. text, numbers, dates, etc
70. Click on Business Name, in that lefthand column list and hold down on it to drag it over to the right of the Pitch ID column in the Preview pane:
71. You’ll see a narrow blue line appear—that’s where the new column will appear in your report. Go ahead and drop it there to the right of Pitch ID. You’ll see the Business Names fill in.
72. Now drag in Investment Amount and Equity Percentage, one at a time, in that order, further to the right side. (If you make a mistake you can just drag a column again to where you want it or drag it back off of the report into the list of fields again & start over!)
73. Now find the Tabular Format menu, just above Business Name in your Preview pane. Pull it down and select Summary
74. Next, drag Type and drop it on the blue bar, just below the column headers, where it says Drop a field here to create a grouping.
75. Now touch the Investment Amount header and you’ll see a down‐arrow appear, inviting you to pull down a menu
76. Click the arrow make the menu appear and then select Summarize this Field
77. In the Summarize popup window that appears, check the box under Average and click the Apply button
78. Now you’ll see the average Investment Amount for VCs and for Angels and you’ll notice that the Angels tend to be thinking in higher dollar terms than the VCs as a group
79. Repeat Steps 75‐77 but for Equity Percentage to get those averages too & then note that those greedy Angels want a lot more equity, as a group, than their VC counterparts, too. Makes sense, though—they’re risking more up front, they expect bigger shares of the payoff later
80. Now find the Save button on the upper left and click it to save your report
81. In the Save Report popup, enter the Report Name as “Average Amount & Equity by Type”
82. Tab to autofill the Report Unique Name
83. Pull down the Report Folder menu and select Unfiled Public Reports
84. Click Save and Run Report
Now you’ve got a nice drag & dropped report that actually shows something meaningful. At least Riley loved it because it helps her analyze what’s going on with her pitches. And here’s a another nifty feature:
85. In the upper center, you’ll see a Time Frame box with Date Field already set to Follow Up and Range set to Custom
86. Click on the From box and set the date to 11/1/2016
87. Click on the To box and set the date to 11/30/2016
88. Click the Run Report button on the left, above the report
Now, only the records with Follow Up dates in November are included and you can see that the differences between the angel and VC groups is even more exaggerated for the pitches that she’s following up with in that month. Maybe interesting strategy implications for Riley? Don’t know— she’s the boss, but as least she can get the data, served to perfection, if/when she needs it.
Visualizing data really helps sometimes and SF can help you do that too:
89. Click on your Reports tab and then New Report again, like before
90. Open the Other Reports folder again and click on Pitches and then the Create button on the bottom right, like before
91. Drag in Investment Amount and drop it next to Pitch ID
92. Touch the Investment Amount header see the down‐arrow, like before, and drop down that menu & click Summarize this Field again
93. Check the box under Average and click Apply, just like before
94. Now, find the Tabular Format menu again, just above the Investment Amount header, pull it down and click on Summary
95. Now drag Equity Percentage from the lefthand column onto the blue bar at the top of the report that says Drop a field here to create grouping.
2
96. Now click the Add Chart button, just at the top of the Preview pane, near the center, and a Chart Editor popup window will appear
97. Near the top of the popup window, find the Select Type row with depictions of different types of charts and click on the third one, the Line Chart
98. Then click OK at the bottom of the popup window and you’ll see your line chart, plotting Average Investment Amount against Equity Percentage
99. To save your chart/report, click Save on the upper left
100. Then in the popup window, enter “Equity vs Investment Chart” for the Report Name, tab to autofill the Report Unique Name and select Unfiled Public Reports for the Report Folder
101. Click Save and Run Report
What does it all mean?
3
Well, as Riley explained it, the “mountains” are good deals, relatively speaking. Those are the points where the pitched firms are offering higher levels of investment in return for lower levels of equity (ownership) they’re demanding. The “valleys” are bad deals, by comparison, because those firms tend to be demanding more ownership (equity) for the level of investment they’re offering her.
If you drew an imaginary line through the middle of the data, that was like the smooth version of the jagged line, then the deals above the line would be looking good and the ones below would be looking worse, by comparison.
Whenever Riley’s pitching a new firm and negotiating a deal, she knows if they’re falling below that line, they’re greedy or she’s not negotiating hard enough, etc. Visualization rules!
One more nifty thing to go for now—talking to Riley I got this idea. She was talking about gauging her progress. It’s not easy b/c she hasn’t closed any deals yet so if you ask how much capital she’s raised the answer is zero. But she’s made a lot of pitches and each one has some chance that it will close and bring $ in. That’s the closability factor. So really, to estimate how much she’s LIKELY to have raised, you multiply the amount of the deal times the probability it will close, the closability, right? So if there’s a 10% chance of a $500,000 deal closing, then that’s .10 X $500,000 = $50,000 of expected value in that pitch. Turns out you can add calculated fields like that to a record in SF:
102. Go to Setup > Build > Create > Objects > Pitch
103. Then, scroll back down to the Custom Fields and Relationships section and click New
104. This time, select Formula for the Data Type and click Next
105. For Field Label, enter “Expected Value” and tab to autofill Field Name
106. For Formula Return Type select Currency and click Next
107. Now on the Step 3. Enter formula page, be sure the Simple Formula tab is selected instead of the Advanced Formula tab and then find the ‐‐ Insert Merge Field ‐‐ menu
108. Click on the ‐‐ Insert Merge Field ‐‐ menu to open it up and then select Investment Amount
109. SF will insert the internal field name “Investment_Amount__c” into the formula definition box for you
110. Next click on the Insert Operator menu to open it up and then select * Multiply
111. SF will insert the multiplication operator into your formula definition box for you, after Investment_Amount__c
112. Click on the ‐‐ Insert Merge Field ‐‐ menu again to open it up and this time, select Closability
113. SF inserts the internal field name “Closability__c” into the formula definition box for you
114. Now just click Next, then Next again and then Save
Now, to see data in your new field:
115. Click your Pitches tab
116. Click Create New View
117. For View Name, enter “Deal numbers & expected values”
118. Then tab to autofill View Unique Name
119. Scroll down and move Business Name, Investment Amount, Equity Percentage, Closability and Expected Value over from the Available Fields list to the Selected Fields list, by highlighting each and clicking the Add button, as before
120. Click on Visible to all users (Includes partner and customer portal users)
121. Click Save
Now you can see all the records with the Expected Values appearing as if they were just part of the stored data but they’re not. They’re calculated when they’re needed, like when you go to this view, so they’re always up to date, even when someone changes the value of Closability or Investment Amount for a record. Nice, eh?
So how can Riley see at a glance how close she is to her goal of raising $1.5M? Let’s calculate & visualize it:
122. Click the Reports tab
123. Click New Report
124. Open the Other Reports folder
125. Click on Pitches
126. Click the Create button
127. Drag Expected Value onto the report Preview pane, to the right of Pitch ID
128. Touch the Expected Value header and click the little down arrow to open the menu
129. Select Summarize this Field
130. Check the box under Sum and click the Apply button
131. Click on the Tabular Format menu, just above the Expected Value header and select Summary
132. Drag Pitch: Pitch ID (NOT Pitch: ID) to the blue bar labelled Drop a field here to create a grouping.
133. Click Save at the upper left corner
134. For Report Name enter “Total Expected Value of Pitches So Far”
135. Tab to autofill Report Unique Name
136. Set Report Folder to Unfiled Public Reports
137. Click Save
138. Click Run Report
139. Now click the Dashboards tab
140. Click New Dashboard
141. Click‐hold the gauge component on the left (the one that looks like a rainbow) and drag it onto the leftmost of the three columns in the middle of the page
142. Then click the Data Sources tab, just above the chart components icons
143. Click the plus sign (+) next to Reports to open that folder
144. Click the plus sign (+) next to Unfiled Public Reports to open that folder
145. Drag Total Expected Value of Pitches So Far to the gauge icon in the leftmost column of the middle area and drop it there
146. Click the little wrench icon above and to the right of the Gauge you created
147. For Maximum, enter 1500000 (b/c Riley has figured that anything between $1,250,000 and $1,500,000 is a go and anything beyond that is really overkill)
148. For Breakpoint 2, enter 1250000 (b/c Riley thinks launching with between $750,000 and $1,250,000 would make it doable, but not really enough to build the business efficiently)
149. For Breakpoint 1, enter 750000 (b/c if she doesn’t raise at least that much, she’ll bag it)
150. For Data Labels, just below Maximum, check the box for Show % and click OK
151. Click on Edit Title, just above your gauge
152. Enter “Fundraising Goal Progress (EV)”
153. Click Save near the upper left corner
154. For the dashboard Title enter “Fundraising Progress Gauge”
Older PostHome
155. Tab to autofill Dashboard Unique Name
156. Leave the Save to set to My Personal Dashboards and click Save
157. Click Close near the upper left corner and then Close in the popup window
Now you see the what Riley sees when she clicks her Dashboard tab to check on how it’s going.
Of course, I’m not showing you her real data but like this gauge, hers shows there’s a lot of work yet to do to get the capital she needs. But at least she has the data and the tools to slice, dice, visualize & analyze now, so that’s gotta help.
Oh, and I reached my fundraising goal of $200 more income for Spartapps in the Cloud by Max. 100% growth rate. Yay! Just got to keep it up!
Powered by Blogger.
4