Facebook Ads Tables: join parent with nested table

Hi all,

When extract my FB Ads data with stitch and push them into Panoply, Stitch automatically creates a subtable/nested table.

I am having difficulties joining the subtable ads_insights_actions to its parent table ads_insights.

I did a left join on campaign_id, adset_it, ad_id and date but that’s not it. I think I should be using the _sdc_level_0_id key as well, but I don’t know how.

Anyone experienced the same problem?

Here is the table schema (a bit outdated I believe) and I read the info on nested table from Stitch but doesn’t really help in this case.

Quentin, what’s the SQL query you’re trying to run? That might help us figure out what’s going on.

basically I would like to get all the actions (post, likes, shares, etc) joined to the parent table. However I am starting to think this is not possible.

here is my query

select *
from ads_insights
join ads_insights__actions actions
on ads_insights.ad_id = actions._sdc_source_key_ad_id
and ads_insights.adset_id = actions._sdc_source_key_adset_id
and ads_insights.campaign_id = actions._sdc_source_key_campaign_id
and ads_insights.date_start = actions._sdc_source_key_date_start

I know I should be using _sdc_level_0_id key as described in the doc but I don’t know how.

ths a lot for your help !

Hey there, Quentin.

I ran this query on some sample FB Ads data, and it yielded the results I’d expect to see. Can you tell me a little bit about what you’re seeing in your results that’s making you question things?

If you were joining a subtable below ads_insights__actions, then sdc_level_0_id would be useful since it forms a composite key for the ads_insights__actions table. It’s not needed as part of this join, since the parent ads_insights doesn’t contain any column that’s equivalent. Basically: Any _sdc_level column is only useful for joining tables that are below the current table.

Let’s say there was a subtable below ads_insights__actions called ads_insights__actions__values. It’d contain these columns:

  • _sdc_source_key_ad_id
  • _sdc_source_key_adset_id
  • _sdc_source_key_campaign_id
  • _sdc_source_key_date_start
  • _sdc_level_0_id (because subtables automatically include all Primary Key columns from the table above them)
  • _sdc_level_1_id (the level key for this table)

If you wanted to join ads_insights__actions__values to ads_insights__actions:

SELECT *
  FROM ads_insights__actions actions
  JOIN ads_insights__actions__values values
    ON actions._sdc_source_key_ad_id = values._sdc_source_key_ad_id
   AND actions._sdc_source_key_adset_id = values._sdc_source_key_adset_id
   AND actions._sdc_source_key_campaign_id = values._sdc_source_key_campaign_id
   AND actions._sdc_source_key_date_start = values._sdc_source_key_date_start
   AND actions._sdc_level_0_id = values._sdc_source_level_0_id
1 Like

Hi Erin,

Thanks a lot for your extensive answer.

Yes that works but then I end up with multiple instances of the same row of my parent table, one for each action types (like, share, etc). Then if I want to sum the cost or the clicks I got the number of clicks * 7 (if 7 different type of actions).

Is there a workaround ? I mean something that will allow me to calculate my cost per action types for instance.

Thanks a lot for the info on joining subtable with sub-subtable, that’s clear and I will make sure to try it later on.

Quentin