Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Backfill Missing "Product ID" in fxa_pay_setup - 3ds_success events #12564

Closed
data-sync-user opened this issue Apr 19, 2022 · 16 comments
Closed

Backfill Missing "Product ID" in fxa_pay_setup - 3ds_success events #12564

data-sync-user opened this issue Apr 19, 2022 · 16 comments

Comments

@data-sync-user
Copy link
Collaborator

data-sync-user commented Apr 19, 2022 •
edited
Loading

Background

As a member of the MDN team, I have created a Looker dashboard to track MDN Plus subscriptions over time, by visualising fxa_pay_setup - 3ds_success events.

Problem

We would like to filter those events using the Product ID field, but unfortunately about half of the events have no product ID set, i.e. it is null for those events (see this dashboard for a distribution by Plan ID and Timestamp Date).

As a workaround, we are currently filtering via the Plan ID field:

  • price_1KeG02JNcmPzuWtRlrSiLTI6
  • price_1KeG02JNcmPzuWtR1oBrw8o6
  • price_1KeG02JNcmPzuWtRuAnIgNHh
  • price_1KeG02JNcmPzuWtRslZijhQu

While we only have 4 plans so far, we will add several new plans this week as part of the EU launch.

Acceptance criteria

All fxa_pay_setup - 3ds_success events for MDN Plus subscriptions (both old events and new events) should have the Product ID field set to prod_LKvr8fYGbBxcaZ.

┆Issue is synchronized with this Jira Bug
@data-sync-user
Copy link
Collaborator Author

➤ Barry Chen commented:

Claas https://mozilla-hub.atlassian.net/browse/FXA-4837 ( https://mozilla-hub.atlassian.net/browse/FXA-4837|smart-link ) is marked as a duplicate of this and that’s already done. Is that accurate?

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Barry Chen Thanks for asking:

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Now that new PayPal subscription events properly record the Product ID, the remainder of this ticket is about back-filling the Product ID (prod_LKvr8fYGbBxcaZ) of all old PayPal subscription events (fxa_pay_setup - 3ds_success) with the following Plan IDs that have no Product ID set yet:

  • price_1KeG02JNcmPzuWtR1oBrw8o6
  • price_1KeG02JNcmPzuWtRslZijhQu
  • price_1KeG02JNcmPzuWtRuAnIgNHh
  • price_1KeG02JNcmPzuWtRlrSiLTI6
  • price_1KqeX9JNcmPzuWtRJNelT86c
  • price_1KqeX9JNcmPzuWtR4dOStCqA
  • price_1KqeXAJNcmPzuWtRjvfOVIUP
  • price_1KqeXAJNcmPzuWtR5hwpwsUr
  • price_1KqeXBJNcmPzuWtR7opydXog
  • price_1KqeXBJNcmPzuWtRBSY6DGlv
  • price_1KqeXBJNcmPzuWtRNWzQ6IX4
  • price_1KqeXCJNcmPzuWtR5fl7C1tx
  • price_1KqeXCJNcmPzuWtR05tPDWqA
  • price_1KqeXCJNcmPzuWtR5yy0wlfO
  • price_1KqeXDJNcmPzuWtRJAstOkqA
  • price_1KqeXDJNcmPzuWtR8hPdxJiS
  • price_1KqeXDJNcmPzuWtRoL9NNeK4
  • price_1KqeXEJNcmPzuWtRTw1w8bX5
  • price_1KqeXEJNcmPzuWtR3WZLuJ6K
  • price_1KqeXEJNcmPzuWtR1kOfChz0
  • price_1KqeXFJNcmPzuWtRUBiVlTVX
  • price_1KqeXFJNcmPzuWtRjdDWnMU6
  • price_1KqeXFJNcmPzuWtR2UJ1TVSG
  • price_1KqeXGJNcmPzuWtR7cw3rh90
  • price_1KqeXGJNcmPzuWtRcjH3vbwC
  • price_1KqeXGJNcmPzuWtRbycawcNr
  • price_1KqeXHJNcmPzuWtRxV9W8tIQ
  • price_1KqeXHJNcmPzuWtR9IvbHgFI
  • price_1KqeXHJNcmPzuWtR4itTRF7Y
  • price_1KqeXIJNcmPzuWtRZJlZhXpW
  • price_1KqeXIJNcmPzuWtRKwoSofy2
  • price_1KqeXJJNcmPzuWtRDSDHSlAl
  • price_1KqeXJJNcmPzuWtRDFlszhVO
  • price_1KqeXKJNcmPzuWtREcYZdrje
  • price_1KqeXKJNcmPzuWtRExRB58k0
  • price_1KqeXLJNcmPzuWtRtypFouG4
  • price_1KqeXLJNcmPzuWtRyS2uTKyE
  • price_1KqeXLJNcmPzuWtRbH6wD6sm
  • price_1KqeXMJNcmPzuWtR46VuMNtb
  • price_1KqeXMJNcmPzuWtR3bNmxM4C
  • price_1KqeXMJNcmPzuWtRC7IJcwUD
  • price_1KqeXNJNcmPzuWtRSCJ2GbVn
  • price_1KqeXNJNcmPzuWtRRb5z07dQ
  • price_1KqeXOJNcmPzuWtRMBae7104

Anna Sobiepanek You mentioned ( https://mozilla.slack.com/archives/CG0AJ6E77/p1649952649911179?thread_ts=1649950742.583339&cid=CG0AJ6E77 ) that this needs to go through SRE. Do I need to file a separate ticket for this?

@data-sync-user data-sync-user changed the title Missing "Product ID" in fxa_pay_setup - 3ds_success events Backfill Missing "Product ID" in fxa_pay_setup - 3ds_success events May 9, 2022
@data-sync-user data-sync-user reopened this May 9, 2022
@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Dustin Lactin Jon Buckley Please don’t hesitate to ping me on Slack if you need any additional details about this. We would like to analyze the subscriptions of our launches in US/CA and EU via Looker, but these incomplete PayPal subscription events are effectively blocking us from doing so.

@data-sync-user
Copy link
Collaborator Author

➤ Jon Buckley commented:

Claas I looked into the Looker data set you’re using for this dashboard and it’s using mozdata.firefox_accounts.fxa_content_auth_stdout_events - this is not a table that Dustin Lactin and I have access to update. I am asking around to see who can assist you in backfilling this data.

@data-sync-user
Copy link
Collaborator Author

➤ Jon Buckley commented:

Daniel Thorn Anna Scholtz Would either of you be able to assist in running this backfill?

@data-sync-user
Copy link
Collaborator Author

➤ Anna Scholtz commented:

How far back should we backfill the data?

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Anna Scholtz We launched in March’22, so the oldest affected event should be from 2022-03-24.

@data-sync-user
Copy link
Collaborator Author

➤ Anna Scholtz commented:

The backfill is running and should be done in an hour or so.

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Anna Scholtz Thank you. How long does it take until the backfill will be visible in Looker ( https://mozilla.cloud.looker.com/dashboards/596 )?

@data-sync-user
Copy link
Collaborator Author

➤ Anna Scholtz commented:

It should be up-to-date

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Hm, either Looker is not yet up-to-date or the backfill did not work as expected. 😕

PS: Since this issue is synced with GitHub, I cannot share any screenshots here.

@data-sync-user
Copy link
Collaborator Author

➤ Jon Buckley commented:

Sorry, I goofed here - I asked for a backfill before actually doing the update on the BQ tables in the fxa-prod project. Let me get than done, then we should be in position to run the backfill again and then Looker should be updated correctly

@data-sync-user
Copy link
Collaborator Author

➤ Claas commented:

Jon Buckley No worries! Have you already finished the update so that the backfill could be run again?

@data-sync-user
Copy link
Collaborator Author

➤ Jon Buckley commented:

Here’s the query I ran to get all of the rows missing the product ID and generate a new event_properties JSON string

SELECT
jsonPayload.fields.event_type,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.plan_id') AS plan_id,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.product_id') AS product_id,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.payment_provider') AS payment_provider,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.source_country') AS source_country,
jsonPayload.fields.event_properties AS old_event_properties,
TO_JSON_STRING(STRUCT(JSON_VALUE(jsonPayload.fields.event_properties,
'$.plan_id') AS plan_id,
'prod_LKvr8fYGbBxcaZ' AS product_id,
'paypal' AS payment_provider,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.source_country') AS source_country)) AS new_event_properties
FROM
moz-fx-fxa-prod-0712.fxa_prod_logs.stdout_2022*
WHERE
jsonPayload.fields.event_type = 'fxa_pay_setup - 3ds_success'
AND JSON_VALUE(jsonPayload.fields.event_properties,
'$.plan_id') IN ( 'price_1KeG02JNcmPzuWtR1oBrw8o6',
'price_1KeG02JNcmPzuWtRlrSiLTI6',
'price_1KeG02JNcmPzuWtRslZijhQu',
'price_1KeG02JNcmPzuWtRuAnIgNHh',
'price_1KqeX9JNcmPzuWtR4dOStCqA',
'price_1KqeX9JNcmPzuWtRJNelT86c',
'price_1KqeXAJNcmPzuWtR5hwpwsUr',
'price_1KqeXAJNcmPzuWtRjvfOVIUP',
'price_1KqeXBJNcmPzuWtR7opydXog',
'price_1KqeXBJNcmPzuWtRBSY6DGlv',
'price_1KqeXCJNcmPzuWtR5fl7C1tx',
'price_1KqeXEJNcmPzuWtR1kOfChz0',
'price_1KqeXEJNcmPzuWtR3WZLuJ6K',
'price_1KqeXFJNcmPzuWtRUBiVlTVX',
'price_1KqeXGJNcmPzuWtRbycawcNr',
'price_1KqeXGJNcmPzuWtRcjH3vbwC',
'price_1KqeXHJNcmPzuWtR4itTRF7Y',
'price_1KqeXIJNcmPzuWtRKwoSofy2',
'price_1KqeXIJNcmPzuWtRZJlZhXpW' )
AND JSON_VALUE(jsonPayload.fields.event_properties,
'$.product_id') IS NULL;I ran the following query on each day between March 24 and May 4 to update the rows:

UPDATE
moz-fx-fxa-prod-0712.fxa_prod_logs.stdout_20220504
SET
jsonPayload.fields.event_properties = TO_JSON_STRING(STRUCT(JSON_VALUE(jsonPayload.fields.event_properties,
'$.plan_id') AS plan_id,
'prod_LKvr8fYGbBxcaZ' AS product_id,
'paypal' AS payment_provider,
JSON_VALUE(jsonPayload.fields.event_properties,
'$.source_country') AS source_country))
WHERE
jsonPayload.fields.event_type = 'fxa_pay_setup - 3ds_success'
AND JSON_VALUE(jsonPayload.fields.event_properties,
'$.plan_id') IN ( 'price_1KeG02JNcmPzuWtR1oBrw8o6',
'price_1KeG02JNcmPzuWtRlrSiLTI6',
'price_1KeG02JNcmPzuWtRslZijhQu',
'price_1KeG02JNcmPzuWtRuAnIgNHh',
'price_1KqeX9JNcmPzuWtR4dOStCqA',
'price_1KqeX9JNcmPzuWtRJNelT86c',
'price_1KqeXAJNcmPzuWtR5hwpwsUr',
'price_1KqeXAJNcmPzuWtRjvfOVIUP',
'price_1KqeXBJNcmPzuWtR7opydXog',
'price_1KqeXBJNcmPzuWtRBSY6DGlv',
'price_1KqeXCJNcmPzuWtR5fl7C1tx',
'price_1KqeXEJNcmPzuWtR1kOfChz0',
'price_1KqeXEJNcmPzuWtR3WZLuJ6K',
'price_1KqeXFJNcmPzuWtRUBiVlTVX',
'price_1KqeXGJNcmPzuWtRbycawcNr',
'price_1KqeXGJNcmPzuWtRcjH3vbwC',
'price_1KqeXHJNcmPzuWtR4itTRF7Y',
'price_1KqeXIJNcmPzuWtRKwoSofy2',
'price_1KqeXIJNcmPzuWtRZJlZhXpW' )
AND JSON_VALUE(jsonPayload.fields.event_properties,
'$.product_id') IS NULL;Anna Scholtz this is ready for back filling now 🙂

@cknowles-admin
Copy link

FxA is no longer synchronizing all issues between Jira and Github. We are closing open issues and will selectively synchronize in the future.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@data-sync-user @cknowles-admin