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 to keep the conversation going.

We can’t wait to see you there!

I Want TRUE Monthly Reach, and I Want It Now!

_ben_easley_ben_easley SFOSYS_ADMIN IMG42
edited April 2019 in Show & Tell


Getting Monthly Reach Metrics in Facebook (and other platforms like Google Analytics)
So your client is demanding that monthly reach report for Facebook again.  Before the release of python actions in Datorama (learn more python actions), this would have typically required implementing a scheduled report within Facebook-- which is all fine and dandy if you don’t need to manage dozens upon dozens of reports.  In such situations, using the Facebook API is typically the preferred route. However, in order to get the data in the appropriate format via the API has been a challenge a challenge due to the following:

  1. Non-additive metric:  Reach is a non-additive metric.  This means you cannot add reach across multiple days as you can with additive metrics like clicks or impressions.  The logic behind this is such: a given user may show up in your campaign data across multiple days, but should only be counted once.  

  2. Sliding Window:  The sliding window needs to dynamically update to match the number of days in the month.

  3. Processing Period:  The period for which data is being retrieved needs to be dynamically updated according to the month being queried, like January (Jan-1 through Jan-31).

If you’re attempting to report on reach metrics in January, the sliding window should equal 31, but in February it needs to be set to 28 (assuming it’s not a leap year).  We also need to Process the data for appropriate date range, which for January corresponds to Jan-1 through Jan-31, and for February this would be Feb-1 through Feb-28. The good news is, we can make this occur programmatically with the help of a python script and a Datorama workflow.

A Quick Note about the solution:  The solution below runs once per month, and is triggered by a workflow on the first of the month.  When the workflow is triggered, a python script runs and retrieves the prior month’s Facebook reach data.  With this solution, you will not get January’s reach data until Feb 1. If you want to report on month-to-date reach metrics, scroll to the bottom of this article to obtain the instructions and modified python script.  Additionally, for this solution to work, you cannot include the date field in your query (in a Facebook custom API stream, the date resolution should be set to “Aggregated”). Typically, the “day” field in Datorama is mapped to the report start date (in Facebook this is fb.start_date).  

How It Works:

Setting It Up:

1.  You’ll need to ensure that all of your data streams are in place before implementing this solution.  The “Data Load Mode” for all of your streams should be set to “Replace,” and you’ll typically want to map fb.start_date to the day (this gives the date as the 1st of each month, as opposed to the last day of month if you mapped to fb.end_date).  Since we’ll be using a workflow to trigger the streams on the first of every month, you’ll need to set all of your data streams to  “Execute only as part of a Workflow.” This setting can be found under the “Advanced” section of the data stream’s settings.

2.  Once your streams are set up, file a ticket with Datorama support and request that the statistical span be set to 60 days across the entire group of data streams (you'll need to go through support since API users can't change this setting). Next, let’s verify that the statistical span has been appropriately set to 60 for all of your data streams. If the statistical span is not set to 60, you won’t get the data in the format required for monthly reach reports.  

Use the script below to verify that the statistical span has been set to 60 days for all of your data streams.  But first, you’ll need to make some minor modifications to the script before you run it:

- Add  your API user’s email on line 7
- Add your API user’s password on line 8
- Add your list of data stream IDs on line 16 (if 2 or more streams, separate each ID by a comma)

Link to Script:
*The script should  be run in the python console of your choice -- do not run it in Datorama.

3.  Now that you’ve confirmed the statistical span is set accordingly, it’s time to create the python action that will trigger with the help of a workflow.  Go to the “Analyze & Act” tab, then select “Activation Center” from the left sidebar, and finally, click “Actions.” Give your action a descriptive name like “Run Monthly FB Reach Query (python)”.  Before pasting the script into the code section, you’ll need to modify the script with your API credentials and list of data stream IDs (follow the same steps as provided in step #2).

Now that you’ve modified the script accordingly, paste it into the code section and click save.  

Link to script (save as a .py file):

4.  Now we’re ready to create the workflow that will trigger your python action.  Click on the “Connect & Mix” tab and then select workflows from the sidebar. Click Add new and give your workflow a descriptive name.  Set the frequency to “monthly” and the day of month to “1st.” Set the delivery hour to the hour of your choice. We recommend setting it later in the day in order to give the platform the opportunity to reconcile the reach metrics for the last day in the month. 

Navigate to the Workflow canvas, which is also labeled “Flow”.  DoubleClick a blank area of the canvas, set Type to “Action”, and from Entities, select the python action that you created and click “Add,” then save your Workflow.  

5.  We recommend that you also text your action to confirm that it’s working.  To do this, go back to the “Activation Center” and then click on “Actions.”  Find the python action that you created and click run. The logic within the python script will recognize the current month (regardless of the day of month), and retrieve the prior month’s data.  Once you run the action, check your data streams, which should be processing. Once they’ve completed processing, check the data stream’s log to confirm the data was queried as expected. You should see a single job that starts on the 1st of the month and ends on the last day of the month.  So if the current month is March, the script will retrieve February’s data, where the start day should be Feb-1 and the end day should be Feb-28.

Modified Version:  Getting Month-to-date reach metrics
(instructions have been given in the replies section)


  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    edited March 2019

    Instructions for Getting Month-to-date reach metrics
    If you don’t want to wait until the first of each month to get your monthly reach metrics, it’s possible to obtain month-to-date reach metrics.  In order to achieve this, you’ll need to follow the above process, but with a couple of modifications.

    1. Set your workflow to trigger on a daily basis.
    2. Make sure all of your data streams have “Data Load Mode” set to “Replace.”
    3. Map the field fb.start_date to the day field in Datorama
    4. Next use this modified script, which will retrieve the current month’s reach metrics, as opposed to the prior month’s reach metrics.  

    **We recommend setting the workflow to run at hour 23.  If you were to set this to another time, such as 6am, on the last day of the month, you’ll get the entire months’ reach metrics through 6am of the final day.  If you set it to hour 23, you only miss out on 1 hour of reach data per month.

    Script for Month-to-date reach:

  • YaelYael SYS_ADMIN IMG42
    Hi @_ben_easley!
    Thanks for this super detailed and useful post!
    Is it possible to use this solution with other platforms as well?
  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    edited April 2019
    Can I use this solution with other platforms?

    Hey Yael,

    That's a great question and the above process can be adopted to other platforms, but there is one caveat. The sliding window for the API connector in question must have the same flexibility as the Facebook API connector, which allows for the sliding window to be set to any integer from 1 to 31 days.  Check with your success manager to inquire about the sliding window settings for a given API connector.   

       Datorama now supports getting true monthly reach data (users/visitors) with Google Analytics as of April 2019.  
  • MarissbcMarissbc New YorkSYS_ADMIN IMG42
    Hey Ben! Quick question -- so I am looking at our mapping and it doesn't look like we have fb.start_date in the Facebook data we have but we do have 'date_id' and 'campaign_start_time (start time)' -- could we use either of these? And should we map this to day?
  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    Hey @Marissbc

    In order to get aggregated reach metrics from Facebook, you need to use the FB Custom Connector.  This is because the standard connector doesn't have the option to set the date resolution (which should be set to "aggregated).  The custom connector will give you access to the field fb.start_date.  By mapping the date to fb.start_date, we're assured that the date field lines up with the month that we're reporting reach for.  You'll also see the date_id field in the log files, but if you've set up the data resolution to aggregated, this field should remain null (which is what we want, otherwise it would break the setup).  

  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    edited March 2019
    Hi @MartaHaus

    Glad you liked the post.  Regarding the Type Error above, when you created the streams variable, did you create it as a list wrapped in square brackets?   

    It should look something like this:

    If that doesn't resolve the issue, it's possible you may be using an old or invalid API token.  You may try to use the print function to print the response of the line producing the error.  

  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    Hi @sfsolistic

    When setting up your FB API stream, you should be able to use the same set of credentials (email + pw) that you use to access/manage your FB campaigns. 

    >> Please note, this solution does require that you have a licensed Datorama API user.  
  • Khushboo_NairKhushboo_Nair LondonSYS_ADMIN IMG42
    Hey @_ben_easley thanks for this article, it's really helpful. I have a question on this- The way we upload the reach data is by creating a report on FB and then uploading it via TC, for instance, the date is set as today-1 and the latest value is taken. We get data every day but it's only till last 28 days (due to FB's window) and this particular value will be shown across a campaign on Datorama. For example, if a campaign ran for 30 or 35 days- Is there a way that we can show the reach of the entire campaign?  
    your advice would be helpful :) thanks!
  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    Hey @Khushboo_Nair

    I'm not super familiar with FB's in-console reporting in terms of reporting windows.  If the date setting is 28d (as opposed to month to date or previous month), I'm not sure how you'd be able to get monthly reach from the platform.  However, I believe one of our peers has implemented this for a client with scheduled reports.  Let's connect offline and discuss, and I can point you to the the CS person working on that account.  
  • adriendebargueadriendebargue ParisSYS_ADMIN IMG42
    Hi @_ben_easley,

    Thanks for this really interesting article. I was wondering if we could replicate a kind of similar solution to get the accurate reach with the Instagram by Facebook API connector (on this use case, it would get an accurate reach on the last 28 days). As there is no custom connector available and the Instagram UI not contains a reporting analytics tool to get a report for a TotalConnect (from what I read), do you know if that's something doable? Thanks!
  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    The only requirement for this to work with any stream is that it must have the flexibility to set the sliding value between 1 and 31.  Many of the streams in the past were or still are limited to 28d or less, preventing you from getting true monthly reach.  If you're getting the Instagram data from a FB api connector, you should be fine since the FB API connector has a sliding window of up to 31d.  
  • _ben_easley_ben_easley SFOSYS_ADMIN IMG42
    It can be used with any frequency or timeframe as long as you can set the sliding window appropriately.  In the case of weekly reach, you don't even need the python script and action center.  Instead, just have support set the sliding window to 7d and the statistic span needs to be 7+ days.  The stream will run on a daily basis, and for any given day, it will contain the last 7d of reach data.  

    If you're unclear on how to do this, please connect with Global Support or your CSM/SA, as this is a relatively easy task to implement for weekly reach reporting.  
Sign In or Register to comment.