Expose actions and action_values for Facebook Ads?

[andrewstautz]

Hi -
First of all, this is great work! I think lots of people are going to get some good use out of it.

I've spent the day trying to modify your code to expose the "actions" and "action_values" fields on the "insights" edge of the Facebook Marketing API.
(I use Facebooks "Offline Conversions" tool, and those results are stored in actions/action_values)

The trouble I'm having is that those fields return a list (or an array? in any case, not a single value) that doesn't play nice with the navigation steps you have happening after the main request loop. If I comment out those navigation steps, and just return the "list" variable, things look all right for a second, but then the query throws a "400: Bad Request" error if I try to filter on any of the expanded action_values items.

A github user named Hugoberry has a repository with a simpler Facebook Ads connector that just spits out a JSON - which works better (insofar as it tries to do less) with the actions and action_values responses. But his script can't handle big requests because it doesn't have the pagination handling that yours does.

Unfortunately, I don't know enough M to tinker with the main list.combine/list.accumulate step that you use to handle big requests, so I haven't come up with a way to combine the two approaches.

Long story short, if this is still a project in active development, I think it'd be really cool if you could add the functionality needed to expose "actions" and "action_values" in the Facebook Ads Insights connector.

Or, if you've got any hints for me, I can keep working toward it as well, and I can share my code with you/other users.

Thanks,
Andrew

[mbilling]

Hi Andrew, This repo is definitely in active development. I’m currently on holiday but i’m back at my desk next week. The philosophy behind and goals for all this repos connectors is to make it really user friendly for less technical people but yet add tables enough table output to satisfy guys like you too. I hope this ambition is achievable :o) I’ll add the parts you need as soon as I come back. If you need other connectors please let me know. Maybe we can built the next one in a co-op :) Next, i’m planning to build connectors for - HubSpot - WooCommerce Subscriptions. Cheers, Michael fre. 30. mar. 2018 kl. 01.20 skrev Andrew Stautz <notifications@github.com>:

…

Hi - First of all, this is great work! I think lots of people are going to get some good use out of it. I've spent the day trying to modify your code to expose the "actions" and "action_values" fields on the "insights" edge of the Facebook Marketing API. (I use Facebooks "Offline Conversions" tool, and those results are stored in actions/action_values) The trouble I'm having is that those fields return a list (or an array? in any case, not a single value) that doesn't play nice with the navigation steps you have happening after the main request loop. If I comment out those navigation steps, and just return the "list" variable, things look all right for a second, but then the query throws a "400: Bad Request" error if I try to filter on any of the expanded action_values items. A github user named Hugoberry has a repository with a simpler Facebook Ads connector that just spits out a JSON - which works better (insofar as it tries to do less) with the actions and action_values responses. But his script can't handle big requests because it doesn't have the pagination handling that yours does. Unfortunately, I don't know enough M to tinker with the main list.combine/list.accumulate step that you use to handle big requests, so I haven't come up with a way to combine the two approaches. Long story short, if this is still a project in active development, I think it'd be really cool if you could add the functionality needed to expose "actions" and "action_values" in the Facebook Ads Insights connector. Or, if you've got any hints for me, I can keep working toward it as well, and I can share my code with you/other users. Thanks, Andrew — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA7b3my1prDX-0ny_KjQif9FAPwbXueVks5tjSXygaJpZM4TAxQw> .

[mbilling]

I took a look at the changes you need.
If was easy for me to add the actions column list (and expanding, pivoting etc. in to separate columns for each action). But it became apparent to me when adding the action_values list column that when these are empty they are difficult to handle without cluttering up the M steps afterwards.
I think it's a better approach to take out these lists in separate tables since PBI is excellent at combining them. The only hurdle here is that PBI doesn't do multicolumn key relations. So we need to add a composite text column containing date, ad_id, adset_id and campaign_id.
Now PBI will automatically relate these tables (if not disabled in the PBI settings).
One advantage with this method is it that other users of the connector take out the level of detail they need. One can skip taking out actions and action_values if you don't need them - taking less time to extract from FB.

I can't exactly break-down in my head if my proposed solution should be cause to some obstacles when making calculated measres/columns in DAX.
Any thoughts?

[andrewstautz]

Hi - Thanks for taking the time to look at this! My thoughts: 1. I agree that it makes sense to break out the actions/action_values into a separate table if that simplifies handling. 2. It does seem that the composite text column approach is the way to handle the table relationship, because: 3. Regarding side-effects: there are two ways to go about relating the tables. First would be to merge them in the query editor; second would be to link them with a one-to-one relationship in the relationship pane. I think approach #2 is more flexible. I see it like this: right now you have two functions, one for "Active Ad Insights" and one for "All Ad Insights" - what if you treated actions/action_values as a third function "Get Action Insights," or some such, that is specified by the user in a separate query and that returns a separate table (with the unique ID column pre-calculated). Then if the user fetches both "All Ad Insights" and "Get Action Insights," PBI will pick up on the unique ID columns and automatically create the relationship - but the user still has the freedom to merge the tables instead, if that's his/her preference. Merging the tables puts all the columns in one table, which might be seen as an advantage to some users. But I don't think the related-tables approach is any more complicated. Because it'd be one-to-one, you could access values from either table (for the purposes of measures/calculated columns) with only the judicious use of the RELATED() and RELATED_TABLE() functions in DAX. I think most users would be able to handle that with a minimum of trouble.

…

-AS

On Sun, Apr 1, 2018 at 7:14 AM, Michael Frost Billing < ***@***.***> wrote: I took a look at the changes you need. If was easy for me to add the actions column list )and expanding, pivoting etc. in to separate columns for each action). But it became apparent to me when adding the action_values list column that when these are empty they are difficult to handle without cluttering up the M steps afterwards. I think it's a better approach to take out these lists in separate tables since PBI is excellent at combining them. The only hurdle here is that PBI doesn't do multicolumn key relations. So we need to add a composite text column containing date, ad_id, adset_id and campaign_id. Now PBI will automatically relate these tables (if not disabled in the PBI settings). One advantage with this method is it that other users of the connector take out the level of detail they need. One can skip taking out actions and action_values if you don't need them - taking less time to extract from FB. I can't exactly break-down in my head if my proposed solution should be cause to some obstacles when making calculated measres/columns in DAX. Any thoughts? — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#1 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AgobKtMBA87BhKXI5UQljATZX4wQIzCcks5tkLaGgaJpZM4TAxQw> .

[mbilling]

Thanks, sounds like we're on the same page.
I'll add the new tables and the key binding them all.

[mbilling]

Let me hear what you think :)

[andrewstautz]

Hi -

Wanted to let you know that I've seen this!
Thanks for doing it!

Right now I'm hitting rate limits with the actions and action values queries - I'm still troubleshooting to see if it's a local problem with the ad accounts I'm trying to look at, or if it's something that's going to affect users more generally.

The original two queries - ad insights and active ad insights - both seem to function well. Maybe I'm just under my limits with those queries and the trouble starts because the actions/action values return more data per call? Maybe I'll filter to the specific actions I need?
Another thing I'm trying is reducing the date preset from lifetime to 90 days, 14 days, etc, to see if that helps.

Ideally I can get this figured out without resorting to batched or asynchronous requests. I'll post here with what I can come up with.

-AS

[mbilling]

Thanks for the feedback! Much appreciated. What kind of FB Marketing API access do you have? Sandbox, Basic or Standard? I can build the connector and deploy it to our pre packaged downloadpage. https://try.scitylana.com/pbi-connector-gallery We currently have Basic API access. I’ve just applied for standard access but FB has suspended the review process for the moment due to the “misuse situation”. So next tier API access might be a month or more in the future for us. Regarding history lookback, lifetime, 90days etc. I can add tables as options. if you have a quota issue, then other will to. Looking at the API quota limits, maybe I should bump the days returned per query. One can make 200 API calls to certain endpoints per user per hour. Do you know how many rows your action queries contains total? As you suugest a filtered view might also be a good idea. Partitioning the action tables on action type would make sense. Cheers, 😁 Michael man. 9. apr. 2018 kl. 20.23 skrev Andrew Stautz <notifications@github.com>:

…

Hi - Wanted to let you know that I've seen this! Thanks for doing it! Right now I'm hitting rate limits with the actions and action values queries - I'm still troubleshooting to see if it's a local problem with the ad accounts I'm trying to look at, or if it's something that's going to affect users more generally. The original two queries - ad insights and active ad insights - both seem to function well. Maybe I'm just under my limits with those queries and the trouble starts because the actions/action values return more data per call? Maybe I'll filter to the specific actions I need? Another thing I'm trying is reducing the date preset from lifetime to 90 days, 14 days, etc, to see if that helps. Ideally I can get this figured out without resorting to batched or asynchronous requests. I'll post here with what I can come up with. -AS — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#1 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA7b3jp5latYU5IAKKhGIu7sRdehmCx1ks5tm6cFgaJpZM4TAxQw> .