Join our Community in its new Home - The Datorama Trailblazer Community Group!

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!

How Many Campaigns do I have? - DISTINCTCOUNT()

Howdy!

So here's a pretty Rich Text widget - adapted entirely from the wonderful work of Jake Ichikawa. Code at the end of this post:

There's an interesting but I think under-utilised function called DISTINCTCOUNT(). It's really simple to use, but I think has some very nice applications.

Here's how it looks in a Calculated Measurement:


Now what that is saying, is based on the settings in this particular widget (e.g. filters, other dimensions) - how many different Campaigns do I have? You don't need to set granularity or aggregation functions - it just kindof works.

Now in this example, we're using it for Media Forecasting - a large Media Plan type document is uploaded, and this tells us how many campaigns will be in market at that time, but I think there are a few good uses for this:

Organic Social - How many Posts?
Using Social Element Key, this is good to see how many Social Posts you have. Combine with a filter to only show posts created in the last 30 days - now we know how many posts were created in the past month, and calculated Average Fans per Post or similar metrics.

Advertiser Growth over Time

Consider a very large Media Agency or Trade Desk. They might have hundreds of Advertisers at any given time. Sometimes growing, sometimes shrinking - we can count these advertisers at each point in time (e.g. on a Monthly granularity) and see how advertiser growth has been over the past year or so.

And now here is the code for that pretty Rich Text:

HTML Code:

<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
<div class="container">
  <div class="title" id="rep">Campaigns</div>
  <div style="background-color:white; margin-top:10px; height:2px; width:25%;"></div>
  <table style="width:95%">
    <tr>
      <td class="subtitle" width="40%">Campaigns This Year</td>
      <td class="value" width="25%">{Query.Campaign Count}</td>

    </tr>
    <tr>
      <td class="subtitle">Campaigns Complete</td>
      <td class="value">{Query.Campaign Count Complete}</td>

    </tr>
    <tr>
      <td class="subtitle">Campaigns in Progress</td>
      <td class="value">{Query.Campaign Count In Progress}</td>

    </tr>  
        <tr>
      <td class="subtitle">Campaigns Planned</td>
      <td class="value">{Query.Campaign Count Planned}</td>

    </tr>  

  </table>

</div>

CSS:

@import url('https://fonts.googleapis.com/css?family=Montserrat:100,200,300,400,600');

body{overflow:hidden; height:100%; font-family: 'Montserrat'; padding:0; margin:0;}

.container{background:linear-gradient(to bottom right, #F26912, #FFCC00); margin:0px; box-shadow: 0px 0px 0px rgba(0,0,0,0.1); padding:35px;  padding-top: 20px; height:100%}

.title{font-weight:200; font-size:34px; color:white;}

.subtitle{padding-top:15px; font-weight:400; font-size:18px;  color:white; }

.value{padding-top:12px; font-weight:800; font-size:21px; color:white; }

.button{font-weight:400; font-size:18px; margin-top:25px; color:white; border:2px solid white; transition:0.2s; width:150px; text-align:center; padding:15px; border-radius:50px; cursor:pointer;}

.button:hover{background-color:white; border-radius:3px; color: #1eccdd;}

Comments

  • mishomisho SofiaRND_ADMIN IMG42
  • AlexAlex SYS_ADMIN IMG42
    edited May 2018

    Hi @Nakulb ! There are a few ways to go about this depending on your set up - but assuming you have campaign start and end dates mapped, you might be able to write an expression like this:

    if (FORMATDATE([Campaign_Start_Date],'yyyy')=='2018') {return DISTINCTCOUNT([Campaign_Key]);}

    You can also make it more dynamic by including an additional condition using TODAY() as a reference for the current year.

Sign In or Register to comment.