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!
I Want TRUE Monthly Reach, and I Want It Now!
>> THIS SOLUTION REQUIRES A LICENSED API USER <<
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:
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.
Sliding Window: The sliding window needs to dynamically update to match the number of days in the month.
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: print_statisticalspan.py
*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): process_dynamic_monthly_data.py
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)