Supermetrics Support Forum

Solved

Facebook engagements figure per post (Supermetrics for Data Studio)

Hi.


I'm trying to arrive at an 'Engagements' figure for each post. While this is available in Facebook, I can't find a way to access it in Data Studio. Here are the steps I've taken:

  1. Created a table in Data Studio
  2. Added the Facebook Insights data source
  3. Added some dimensions (thumbnail, message, link to post, post type, date)
  4. Added some metrics (Reactions, Comments, Shares, Total Reach, Engagement Rate)
  5. I tried creating a custom field using 'Total reach*Engagement rate (% of reached people who clicked)' but the figure this gives is lower than the one given in Facebook

What am I missing? Is this data available? Is there a way for me to calculate it to arrive at the same figure provided by Facebook?


Any help gratefully received.


Thanks

James

  • *Update*


    The closest I have got so far to calculating a similar figure to Facebook is with the following formula:

    Total post reactions+Shares on post+Comments on post+Content clicks+Comments on post shares+Shares on post shares+Likes on post shares

     The figure I get from this is of the same order of magnitude to Facebook. But, weirdly, it is slightly higher. I've tried many permutations of fiddling around with it to get the data to match (maybe FB doesn't count Likes on post shares?, etc) but to no avail. 


    The variance I'm seeing between my figure and Facebook's is around 0.3% on a post with c.90k engagements, which rises to 0.9% on a post with c.20k  engagements, 1.2% on a post with c.10k engagements and then up to 6.9% on a post with c.1.5k engagements. 


    Any bright ideas?


    James

  • Hi James,


    Thanks for your question. Facebook API does unfortunately not have a pure "Post Engagements" metric, and this is why you need to create a calculation for it. When you click on the "Engagements" link you can find underneath any of your posts, you should see which metrics are included in the Engagements figure.


    I think you're pretty close with the formula you used, but "Content Clicks" is likely not supposed to be part of it, as it is defined "Page consumptions - The number of times people clicked on any of your content.". 

    My hunch is that Facebook uses "Likes" for the Engagements figure instead of Reactions, so I would replace "Total post reactions" with "Likes on post". Also, you should add "Post link clicks", "Post other clicks" and "Post photo views".


    My suggestion for the formula would be:


    NARY_MAX(Post link clicks,0) + NARY_MAX(Post other clicks,0) + NARY_MAX(Post photo views,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)


    Note that all involved fields need to return a number, so use for example NARY_MAX, as above, to avoid null values.


    Let us know if any issues remain, and would be great if you can confirm whether the formula works for you. Thanks!


    Best,

    Heidi / Supermetrics


    1 person likes this
  • Hi Heidi.


    Thanks so much, that's super helpful! I find it weird that Facebook would only count likes in the engagement figure and not other reaction types!.


    Your formula has worked really well, so I'm seeing pretty much the same figures in Data Studio and Facebook now (yay!). However, video clicks are counted differently so I've made a slight adjustment to try to make it work for videos too. But it's not quite there yet. Can you see where I'm going wrong? There doesn't appear to be a metric called 'clicks to play'.


    NARY_MAX(Post link clicks,0) + NARY_MAX(Post other clicks,0) + NARY_MAX(Post photo views,0) + NARY_MAX(Clicked to play video views,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)


    Kind regards

    James

  • Hi James,


    glad to hear it looks better!


    Regarding videos, I think the metric "Clicked to play video views" is the same as clicks to play. There's also another one called "Clicked to play 10s video views" and "Clicked to play 30s video views", but I think those are not needed here. Any tips behind the "Engagements" link on a video post? I don't have video posts in our test account, so am unfortunately not able to check.


    Thanks,

    Heidi / Supermetrics


    1 person likes this
  • Yes, see attached. 


    The help text for Clicks to Play reads: 'The number of times your video started playing after a person clicked to play it'


    image


    Thanks again for your help!

    James

  • Hi James,


    thanks for the screenshot!


    Looks like it is the same field, although the API field has a playtime limit on it. The description for "Clicked to play video views" is "The number of times your videos played for at least 3 seconds, or for nearly their total length if they're shorter than 3 seconds, after people clicked play".


    Does it match "Clicks to play" if you fetch that field individually?


    Best,

    Heidi


    1 person likes this
  • Hmm, no. It shows 787 for the example in the screenshot above (Facebook says 1138).

  • Aha!

    The 'Content Clicks' figure for video posts in Data Studio is the same figure as for 'Post Clicks' for those posts in Facebook!

    Some examples:

    • DS 9,164 / FB 9,161
    • DS 3,544 / FB 3,544
    • DS 2.036 / FB 2,036
    • DS 2,693 / FB 2,693

    This formula gives me an accurate engagements figure for videos (hooray!):

    NARY_MAX(Content clicks,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)

    So now for the final piece of the puzzle: is it possible to add conditional logic so that the field uses one formula for non-video content types and this one for videos? (I appreciate this is veering into the realms of Google Data Studio support rather than Supermetrics support, but I'm sure more of your customers will want to know how to solve this)

    With continued thanks!
    James

  • **Update**


    So I now have two fields for engagements:

    1. JAS NonVideo Engagements (which calculates the correct value for non-video posts)
    2. JAS Video Engagements (which works for video posts)

    So I tried creating a CASE statement to add the necessary logic to load the appropriate field:

    CASE 
        WHEN Post Type IN ("photo","link","status")THEN JAS NonVideo Engagements
        WHEN Post Type IN ("video")THEN JAS Video Engagements
        ELSE "Borked" 
    END

    But Post Type is a dimension and JAS SM Engagements  / JAS video engagements are metrics and you can't cross the streams. So I get this error message and I can't save the field: 
    'Sorry, calculated fields can't mix metrics (aggregated values) and dimensions (non-aggregated values). Please check the aggregation types of the fields used in this formula. Learn more.'

    Could really use some help at this point! :(

    Thanks again
    James

  • My last post may seem out of context because my previous one wasn't posted for some reason. Basically, I discovered that  engagements are calculated differently for videos than non-video posts. The two fields referenced in my previous post (which give accurate engagement figures) are:


    JAS NonVideo Engagements

    NARY_MAX(Post link clicks,0) + NARY_MAX(Post other clicks,0) + NARY_MAX(Post photo views,0) + NARY_MAX(Clicked to play video views,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)


    JAS Video Engagements

    NARY_MAX(Content clicks,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)

  • @James - Sorry first post got caught in spam moderation! It should be present now.


    -Supermetrics Team

  • Hey James,


    Amazing groundwork, looks like you've found all the correct fields.


    However, I'm not sure if there is a way to have the formula automatically change depending on Post Type, as it will give an error like you said, because of the dimension-metric conflict. The easiest way would probably be to create two engagement fields (each formula) and then have two separate graphs, one for videos and one for other posts, filtering by post type in the graph. Do you think that will work for you?


    Best,

    Heidi / Supermetrics

  • Hi Heidi.


    Actually, after I spent a sunny Sunday digging into this, it suddenly dawned on me that my JAS Video Engagements field was showing the exact same figure for photos, links and status post as JAS NonVideoEngagements *facepalm*.


    So there's no need for conditional logic or for two separate fields!


    For anyone interested in calculating Facebook engagements on a per-post level, this is how you do it:


    NARY_MAX(Content clicks,0) + NARY_MAX(Likes on post,0) + NARY_MAX(Shares on post,0) + NARY_MAX(Comments on post,0) + NARY_MAX(Comments on post shares,0) + NARY_MAX(Shares on post shares,0) + NARY_MAX(Likes on post shares,0)


    Many thanks for all your help. This was a really useful learning exercise for me, and not just in terms of solving the original problem!

     

    James

Login to post a comment