It's been an amazing 3 years coming together in this forum to collaborate, innovate, support, and inspire each other about our shared usage of Datorama. While this is not quite a goodbye, we are excited to announce that we are getting a fresh start in our new home within the Salesforce Trailblazer Community. We have a ton of fun new content planned and you may even see the revival of some of our most popular posts from the past few years.
We’ll be keeping this group around for a bit for you to peruse, but as of November 15, we will no longer be allowing new posts or comments. Be sure to join our new group at https://www.salesforce.com/success-datorama to keep the conversation going.
We can’t wait to see you there!
Best Of
New custom widget: Quick Trends
I made a new custom widget. It's free and easy to set up. It's similar to status bars, but all in one widget. This means it's also compatible with switch measurements and dimensions.
Set up instructions are here: https://app.process.st/workflows/Data-Canvas-V2-Advanced-Visualizations-Use-Cases-e-Custom-Widget-Data-Canvas-rRS9GH7D6JhwnWOZOb9NFg/view/tasks/iZICs9yZR-NTjGaxZAJLjA
Let me know any thoughts and feedback!
Thanks,
Josh
jharris
Re: Admin Advanced - Media Transparency Center to track campaign
This is a program that you want us to use right?
The main issue I have had is that the notes are out of date (UI / UX has changed in Datorama) or that the examples given are not sufficient.
A lot of the tools have clearly been written by someone who assumes knowledge.
This makes no sense.
Particularly as the way that Datorama uses elements of SQL is not at all consistent with typical SQL in the formula mapping.
Overall I like what Datorma achieves as a product, but you really need to spend more money on updating the training manuals / provide clear, more detailed examples.
william_mollers
Generate your own Public/Private key pair for SFTP
As we push for more secure data transfers SFTP is used more and more.
Setting up a SFTP connection is quite simple but needs some specific parameters to have in mind. Datorama has some specificities and in the process of setting up the connection, information could be tricky to find. We’ll discuss here how you can generate yourselves a pair of public and private key to be used for this connection.
As a reminder, you have two authentication options when setting up a SFTP retrieval or exporting your reports through SFTP :
- User / Password authentication
- Private Key
What’s Private Key Authentication
This method works with the generation of a pair of keys :- Public Key that the IT will place in the right folder for SFTP authentication method
- Private Key which will be kept secret by the user and will be used by Datorama
It’s a common methods used around IT systems and is considered a bit safer than user password as the key are randomly generated and encrypted.
What’s specific with Private Key at Datorama
Today (04/10/2021), Datorama is compatible with OpenSSH RSA method keys. Therefore, those keys need to be generated using the relevant algorithms.- Old OpenSSH RSA format
- Private key must not be password protected
Generate you key pair
To do so, please follow this article. It’s really easy. Once you’ve saved your private and public key, send the public key to your IT department to be put in the right folder. Keep the private key and copy/paste the content in the “Private Key” space in Datorama.If this is not sufficient, the problem does not come from the keys and need to be investigated elsewhere.
Please find here a list of items to verify in order for your connection to work :
- User needs access to the right folder
- Keys must be stored in places with appropriate restrictions on server side
- Make sure to write the whole file path, not just the last folder
- Verify user and host for typo
Error guide
Datorama Log indicates “Auth Fail“ → We reach the server but the Auth Fail without any other detailPotential issues:
- User needs access to the right folders
- Convert the file to a ppk to pem format https://www.tbs-certificates.co.uk/FAQ/en/putty-ppk-vers-openssl-openssh.html
- File permission in the ssh directory https://serverfault.com/questions/871240/jsch-exception-auth-fail-with-private-key
fchanssard
Datorama Visualisation Training
"A combination of Art & Science is how Data Visualisation is realised!"
Art is concerned with creativity and innovation. Designing communications that appeal on an aesthetic level and are memorable.
Science is aimed at understanding the way our eyes and brains process information most effectively and efficiently.
At Datorama we recognise that not only understanding the theory of Data Visualisation but possessing the ability to produce visually appealing dashboard/custom widgets is vitally important to effective Data Storytelling.
So we have created a (paid for) custom training program that does exactly this
This training course is aimed at Datorama Admins/Power Users who are looking to develop a deep understanding of Data Visualisation, through theory and a practical introduction to coding (CSS/HTML):
Learn and apply the core principles of great Data Visualisation, while empowering teams to amend basic code and utilise/tailor Datorama custom widgets.
Combination of Theory and Practical sessions, with takeaway materials to continue and reinforce learning after course completion.
Datorama Platform focused training around Data Visualisation.
If you have any questions then feel free to leave a comment or contact me directly: K[email protected]
Kamlesh_Mistry
Activation Center Use Case Library: Benchmarking Against a Main KPI
For a general overview on Action Rules and Actions, please use the resources below. For this and all examples, I will just be showcasing the Action Rule in use.
Creating an Action Rule
For an overview of Action Rules, check out the Knowledge Base article here.
Creating an Action
For an overview of Actions, check out the Knowledge Base article here.
We have also introduced new Activations. You can read about those here.
And with our new connector on your dashboard pages, be sure to check out how Slack can be used for actions, too!
Our first use case: Benchmarking Against a Main KPI
Use Case: if you have a main KPI that you monitor, you can set up an alert to know whenever performance falls outside your expected values/benchmark.
Implementation:
- Define the KPI: select the KPI you are trying to monitor
- Define the Operator: are you trying to monitor if the measurement is above, below, equal to your benchmark? Choose the right operator accordingly.
- Define the Benchmark: input your KPI benchmark
- Define the Dimension (optional): is your benchmark at the Placement level? The Campaign? Overall? If overall, leave this section blank. Otherwise, input the relevant dimensional value.
- Define the Time Period: is your KPI monitored on a weekly basis? Is it for the entire flight period? Define that period here.
You can also use the Filter to refine the results that are returned. This is helpful if your benchmarks are different per Entity (e.g. Campaign A has Benchmark A, while Campaign B has Benchmark 
.
ajs1218
Custom Widget for Top Performing Facebook Creative
Locate and Isolate the Creative Image Data
1. Find the unique field in the Facebook data streams for the creative image (ex. csv['ad_creative_image'] )
2. Create a calculated dimension to produce high res images of the creative.
var str = [Creative_Image];<br>var end_pos = '';<br>var url = '';if (str contains '%2F')<br>{<br>end_pos = FIND('&url=',str) + 5;<br>url = SUBSTRING(str,end_pos,LEN(str));<br>url = REPLACE(url,'%2F','/');<br>url = REPLACE(url,'%3A',':');<br>url = REPLACE(url,'%3F','?');<br>url = REPLACE(url,'%3D','=');<br>url = REPLACE(url,'%26','&');<br>return url;<br>}<br>else<br>{<br>[Creative_Image]<br>}1. Create a Custom Widget and use 'add data' to add the Creative Image dimension/calculated dimension made above.
2. HTML in Custom Widget Editor (Note that the HTML below includes a line for number of clicks which could be brought in with another query if desired).
<p><img id="creative-image" height="350" src="about:blank"></img></p><p><h4>Total Clicks:</h4></p><p><p id="numberClicks"></p></p>
3. Javascript in Custom Widget Editor:
<p>var queryResult = DA.query.getQueryResult();</p><p>var result = queryResult["rows"][0][0]['value'];</p><p>var clicks = queryResult["rows"][0][1]['value'];</p><p>console.log(JSON.stringify(queryResult));<br>console.log(DA.query.getQueryResult());<br>console.log(clicks);</p><p>document.getElementById('creative-image').src = result;</p><p>document.getElementById('numberClicks').innerHTML = clicks;</p>4. Save your custom widget
5. Sort Method: by the KPI that is most important in determining the creative's success:
7. Test the widget to ensure you are seeing a creative image (Note that you may need to open up the console to see your creative image data)
8. If you cannot gather your unique creative image, or are having trouble with it, try changing the index values (in relation to the console log values) to query the value:
Emily
Custom Widget for Top Performing Instagram Creative
Locate and Isolate the Creative Image data
1. Find the unique field in the Instagram data streams that has the creative image data link. It could be: csv['post_link']
2. Prepare the link for usage: Isolate and format the link using the two Calculated Dimensions below
Dimension 1:
<p>var instaLink = EXTRACT([Social_Post_Link],'/',5);</p><p>if (([Data_Stream] contains 'Instagram'){<br>return instaLink;<br>}else {<br>return null;<br>}</p>Dimension 2:
var link = 'https://www.instagram.com/p/'+<b>[The Top Calculated Dimension Above]</b>+'/embed';
**Note the bold in dimension 2 indicates that it references the first calculated dimension
1. In the Customer Widget Editor construct this HTML (Note that the HTML below includes a line for number of clicks which could be brought in with another query if desired).
<p><h1>#1 Post</h1></p><p><br><div id="frame" style="margin-left: 0%;"></p><p><br><iframe style="height: 100%; overflow: hidden; width: 100%;"<br>id="instaFrame" scrolling="no" border="0" frameborder="0"<br>src="about:blank"<br>sandbox="allow-scripts allow-forms allow-top-navigation allow-popups allow-same-origin"></iframe></p><p><br></div></p>
2. Create a Custom Widget and in the add data section query the Creative Image dimension/calculated dimension made above.
3. JavaScript (the highlighted code is required, but the non-highlighted code is useful for troubleshooting the resulting query from within the console):
<p>var queryResult = DA.query.getQueryResult();</p><p><br>var result = queryResult["rows"][0][0]['value'];</p><p><br>console.log(JSON.stringify(queryResult));</p><p><br>console.log(result);</p><p><br>document.getElementById('instaFrame').src = result;</p>**Note that by changing the '0' in the code below to '1' you can get the second row in the query and thus have the next row of best performing creatives based on clicks; then the third, etc., each in their own custom widget which would allow for multiple creatives on a dashboard page.
var result = queryResult["rows"][1][0]['value'];
4. Save your custom widget
5. Set the sort method by the KPI that is most important to determine the creative's success.
Emily
What’s your idea of a perfect date?
and I’m pretty sure that we’ve all run into some bad date formats! 
But on Datorama... it can be solved... eventually! (don’t quote me anywhere else)
Enjoy the cartoon
and let’s dive into the world of dates in Datorama.
Datorama recognizes and supports all kinds of common date formats that you can also choose from if needed.
However, sometimes you may face different data stream processing errors due to dates and their unique formats so I will give you some ideas on how to tackle the exceptions below.
It’s best to make amends in the mapping section of your Day dimension field.
1) Unparsable date: “A value does not match its declared type.” or “Invalid date format”
This can be solved using a PARSEDATE formula.
- Eg1: var date = EXTRACT(csv['Date'],' ',1); PARSEDATE(date,'dd-MMM-yy')
- Eg2: PARSEDATE((FORMATDATE(csv['Date'],'MM/dd/yyyy')),'dd/MM/yyyy')
- Eg3: PARSEDATE(FORMATDATE_DEFAULT(csv['Date']), 'yyyy-dd-MM HH:mm:ss')
2) Un-identifiable format: Perhaps you have files with some blank cells in the date column? You can add this formula to the Day dimension:
if(!ISEMPTY(csv['Date'])){
PARSEDATE(LEFT(csv['Date'],10),'yyyy-MM-dd');
}
else {
null;
}
3) When there is no date in your file, you can add TODAY(); to the Day dimension or use a DATEADD function.
4) Inconsistent date format:
- Using the latest TotalConnect version, the date could be recognized by the system as a text (preview section), and you can manipulate it in the mapping section to parse the date.
- Eg: In this case PARSEDATE(EXTRACT(csv['date'],'-',0),'d.M.yy') will parse the below date format.
Some useful success center articles on date formats and functions:
Now let’s look at some useful hacks that you could do with dates!
1) If you need to Extract a date from file name:
Eg: Filename: 1475016970372_Ross 2016 - On Target F18-54_v3.1-Daily Auto Delivery_2_340 27-SEP-16 Upi
You can use the parsedate formula and extract from the right hand side.
PARSEDATE(RIGHT(env['filename'],9),'dd-MMM-yy')
2) ‘Manual’ data load rule:
This allows you to apply a syntax in the Day field of the data stream and upon a certain condition, the formula returns null, meaning that these rows are not uploaded into the platform. It is, of course, more complex, and for simple use cases, we would recommend the regular Data Load Rule so that you can keep track of it and not get syntax fever!
Eg: IF(csv['impressions']>100, csv['date'], null)
Here’s a few blasts from the past around dates from other community users!
Goodluck!
Nidene
Nidene_Pillay
Become a Datorama Master!
Over 4,000 users have successfully completed the Datorama Fundamentals Exam to date!
The Datorama Fundamentals Exam allows you to:
- Get familiar with Datorama’s latest features
- Gain hands-on experience of the various aspects of the platform -from data ingestion to visualization
- Learn in your language - the exam is available in English, Japanese, Spanish, German & French!
- Self score your exam - score your exam workspaces quickly and easily and receive feedback on errors in your exam
So what are you waiting for? Become a Datorama Master!!
Tal_Erlich
Re: Turning your LIFETIME metrics into Daily values
I'm trying to use this method, but because of my data set, I'm unable to send the full data set from the beginning. As such, I'm sending aggregate metrics for the prior 7 days, but because the earliest date doesn't have a "previous day" the calculations result in the aggregate number flowing through. Is there a way to modify the formula to skip the calculation if there isn't a previous date present?
Ian