Facebook Conversion Data Missing

[manavkohli]

Enviroment

• Airbyte version: 0.22.0-alpha
• OS Version / Instance: example macOS
• Deployment: Local
• Source Connector and version: Facebook Marketing (Latest)
• Destination Connector and version: N/A
• Severity: High
• Step where error happened: Sync job

Current Behavior

After the rollout of iOS 14.5, Facebook added many restrictions to its API. In particular, it restricted fetching conversion data when breakdowns were present (see here: "No support for breakdowns: For both app and web conversions, delivery and action breakdowns, such as age, gender, region, and placement will not be supported.", source). In our warehouse we're at least seeing missing data for conversions after April.

Expected Behavior

There should be a report that does not use breakdowns and can expose conversion events.

Logs

If applicable, please upload the logs from the failing operation.
For sync jobs, you can download the full logs from the UI by going to the sync attempt page and
clicking the download logs button at the top right of the logs display window.

LOG

replace this with
your long log
output here

Steps to Reproduce

1. Create a Facebook Marketing source
2. Create any destination
3. Create a connection between the two and sync data
4. Try and query conversion data from the database (sample query below)

SELECT
    date_start as date,
    SUM(value)
FROM facebook_ads_insights_actions a
LEFT JOIN facebook_ads_insights b ON b._airbyte_facebook_ads_insights_hashid = a._airbyte_facebook_ads_insights_hashid
WHERE action_type LIKE 'omni_purchase'
GROUP BY date
ORDER BY date DESC

Are you willing to submit a PR?

Yes

[sherifnada]

@manavkohli it seems like the issue is that when the FB connector queries for action_attribution_windows of 28days then empty data can be returned. I think the answer is to stop querying for 28day attribution windows altogether but I'm not 100% sure yet. needs more research. Adding this to our current sprint.

[sherifnada]

helpful context: https://developers.facebook.com/docs/marketing-api/insights#sample see the notice about iOS 14 in these docs

[manavkohli]

@sherifnada good to know - will try that as well

[manavkohli]

@sherifnada fyi running into a handful of errors with the Facebook Marketing connector as I dug deeper, what's the best way to proceed here? Namely:

1. All jobs are failing here, logs:

 ERROR () LineGobbler(voidCall):85 -     record["thumbnail_url"] = remove_params_from_url(thumbnail_url, ["_nc_hash", "d"])
2021-08-03 20:02:54 ERROR () LineGobbler(voidCall):85 -   File "/airbyte/integration_code/source_facebook_marketing/streams.py", line 53, in remove_params_from_url
2021-08-03 20:02:54 ERROR () LineGobbler(voidCall):85 -     key, value = q.split("=")
2021-08-03 20:02:54 ERROR () LineGobbler(voidCall):85 - ValueError: not enough values to unpack (expected 2, got 1)

2. Once fixed, seeing separate error in the normalization process

[sherifnada]

@manavkohli will prioritize this failure asap, can you share the full logs from the failling normalization job?

[manavkohli]

Awesome, thanks! Also I dug deeper, I think the normalization job was an issue with the generated catalog/actually received data for the report I built. So is not something affecting the source/production build, sorry about raising the alarm there. That all being said, attaching the logs if helpful.
logs-10-0.txt

[sherifnada]

thanks for the heads up, will look into the first issue also

[manavkohli]

Definitely! Also to clarify earlier comment, all the jobs are still failing in production, but the normalization process after fixing seemed due to my changes

[manavkohli]

@sherifnada do you have an idea of when the underlying data issue may be deployed? That's still blocking us from being able to use the connector

[vitaliizazmic]

@manavkohli hi, I'm working on task and try to reproduce issue. Could you please provide more details about ads for which data is missing.

[manavkohli]

@vitaliizazmic definitely, basically all conversion data after mid April. If you look at the *_actions or *_actions_values tables and filter on events where the action_type is omni_purchase (this is a conversion event) then you won't see any results after that.

[manavkohli]

@sherifnada @vitaliizazmic I opened a PR that addressed both issues. Was able to verify data loaded into my DB that included conversion data. Query below (note that the tables are prepended with (fb921_)

SELECT
    date_start as date,
    SUM(value)
FROM fb921_ads_insights_action_type_actions a
LEFT JOIN fb921_ads_insights_action_type b ON b._airbyte_fb921_ads_i__ts_action_type_hashid = a._airbyte_fb921_ads_i__ts_action_type_hashid
WHERE action_type LIKE 'omni_purchase'
GROUP BY date
ORDER BY date DESC