search

fivetran / dbt_iterable_source

Fivetran data source models for Iterable using dbt.
https://fivetran.github.io/dbt_iterable_source/
Apache License 2.0
2 stars 5 forks source link

[Feature] Adding user attribute `userListIds` #33

Open syed-a-ali opened 2 months ago

syed-a-ali commented 2 months ago

Is there an existing issue for this?

Describe the issue

Our Iterable project is based on userId as described here.

We're using the Fivetran Iterable connector to sync the data from our Iterable account to our Snowflake database.

This is followed by using Fivetran's iterable_source and iterable dbt packages to transform the data into final models.

As per the docs, the LIST_USER_HISTORY source is not used and the iterable__list_user_history model recreates this source. However, we're unable to obtain the userListIds in this model.

Issue was raised with Fivetran's connector team as well but we were referred to the dbt team for help.

Relevant error log or model output

+------------------+---------------------------------+
|UNIQUE_USER_KEY   |60086da43eddcfa7c5b73a3456a129be |
|_FIVETRAN_USER_ID |d7Asje8xBwO6PHqOkfQSib6M3oo=     |
|USER_ID           |789b068d8ea24d9e83c032394b3423d2 |
|USER_EMAIL        |<redacted>                       |
|USER_FIRST_NAME   |null                             |
|USER_LAST_NAME    |null                             |
|USER_SIGNUP_DATE  |2024-03-05 12:54:07.000000 -08:00|
|USER_SIGNUP_SOURCE|API                              |
|USER_UPDATED_AT   |2024-04-06 05:09:50.000000 -07:00|
|LIST_ID           |null                             |
|IS_CURRENT        |true                             |
|LIST_NAME         |null                             |
|LIST_TYPE         |null                             |
|LIST_CREATED_AT   |null                             |
+------------------+---------------------------------+

Expected behavior

As seen in the screenshot from Iterable, we'd like to see the userListIds in this model. Screenshot 2024-09-12 at 4 43 10 PM

Possible solution

In the USER_HISTORY source there is a field named additional_properties that contains the userListId key which is empty. Below is the field for the above mentioned user. Possibly this needs to be populated and sourced.

{
  "LastTransferCountry": "MEX",
  "devices.appBuild": "[\"946\"]",
  "StateRegistration": "IL",
  "devices.firebaseCompatible": "[true]",
  "FirstTransferTerminated": "2024-08-16T00:44:03.547+00:00",
  "devices.endpointEnabled": "[true]",
  "devices.platform": "[\"GCM\"]",
  "AcquisitionSource": "WhatsApp",
  "IsDeleted": "false",
  "devices.appVersion": "[\"9.39.0\"]",
  "ip": "",
  "HasActiveSchedule": "false",
  "itblInternal.emailDomain": "gmail.com",
  "timeZone": "",
  "devices.tokenRegistrationType": "[\"FCM\"]",
  "LastLpEmailOpen": "",
  "LeanplumSentMsgs": "",
  "devices.token": "[\"eRxl01CLS7eXoyzacQCl5k:APA91bFKNJgmy2vckoeDABSnk1INLY6os-O3vOp7pO2rSpXt56PYyjrMhzPEnHO-TPsahfqBKZwqn0i05SLtAPd12BalnAR2coQU4y2EnLyyetBPNXuPKK6ZqJdWHbbLNGPHdSSpZm8U\"]",
  "FXCountryPresentationES": "México",
  "FXCountryPresentationEN": "Mexico",
  "TransferCountCard": "3",
  "city": "",
  "devices.applicationName": "[\"com.pangea.android\"]",
  "ReferralCodeActivation": "true",
  "itblInternal.phoneCountry": "US",
  "subscribedMessageTypeIds": "[125840]",
  "itblInternal.regionCode": "US",
  "LastTransferReceiverFirstName": "<redacted>",
  "devices.identifierForVendor": "",
  "devices.appPackageName": "[\"com.pangea.android\"]",
  "devices.manufacturer": "[\"Wingtech\"]",
  "userListId": "",
  "receivedSMSDisclaimer": "true",
  "Sep2024PrmCode": "",
  "FirstName": "<redacted>",
  "ActiveScheduleCadence": "",
  "Sep2024PrmAmnt": "",
  "devices.userInterfaceIdiom": "",
  "PromoCodeCount": "1",
  "devices.sdkVersion": "[32]",
  "Aug2024PrmCode": "",
  "TerminationBrandsUsed": "Elektra",
  "Frequency": "7.5",
  "Aug2024PrmAmnt": "",
  "PromoRetentionCodeCount": "0",
  "NativeAppInstalled": "Android",
  "PrimaryTransferCorridor": "",
  "country": "",
  "brandAffinityLabel": "negative",
  "ReferralCode": "<redacted>",
  "devices.platformEndpoint": "[\"arn:aws:sns:us-east-1:203113165317:endpoint/GCM/prod-project-21160-com.pangea.android/3d9595f7-2578-37f4-846d-949956b8b4fb\"]",
  "ItblActClassTime": "",
  "Last90DayVolume": "285.0",
  "EmailOptIn": "true",
  "devices.brand": "[\"Dish\"]",
  "ReceiverCountries": "MEX",
  "DestinationExchangeRef": "USD-MXN|US-MX",
  "April24PromoTier": "",
  "itblInternal.phoneType": "FIXED_LINE_OR_MOBILE",
  "predictionId": "",
  "LastActiveDate": "2024-09-07T13:17:04.000+00:00",
  "IntrofxEligible": "false",
  "ReferralCountEffective": "0",
  "Jun2024PrmAmnt": "",
  "LapsedPromoTier": "",
  "TerminationMethodsUsed": "Cash, DirectDeposit",
  "devices.notificationsEnabled": "[true]",
  "24MDayAmount": "",
  "TransferCountCash": "1",
  "devices.iterableSdkVersion": "[\"3.5.1\"]",
  "Jun2024PrmCode": "",
  "SmsOptIn": "true",
  "PrimaryTerminationMethod": "",
  "24MDayCode": "",
  "AverageSendingAmount": "95",
  "ItblActClass": "",
  "RewardsBalance": "",
  "LastName": "<redacted>",
  "region": "",
  "DestinationCurrency": "mx",
  "July2024PrmAmnt": "",
  "TransferCount": "3",
  "locale": "es",
  "July2024PrmCode": "",
  "FxCountrySelected": "Mexico",
  "Language": "",
  "ReceiverCount": "2",
  "TransferCountAch": "0",
  "devices.deviceId": "[\"<redacted>\"]",
  "devices.systemVersion": "[\"12\"]",
  "LastLeanplumEmailOpenDate": "",
  "IsBlocked": "false",
  "LastPaymentMethodSelected": "Card",
  "PromoCodesUsed": "<redacted>",
  "devices.model": "[\"Celero5G+\"]",
  "devices.localizedModel": "",
  "devices.systemName": "[\"DSH88501\"]",
  "SeedGroup": "",
  "AttributionSurveyResponse": ""
}

dbt Project configurations

vars:
    iterable__event_metrics:
    - "emailClick"
    - "emailUnSubscribe"
    - "emailComplaint"
    - "customEvent"
    - "emailSubscribe"
    - "emailOpen"
    - "pushSend"
    - "smsBounce"
    - "pushBounce"
    - "inAppSendSkip"
    - "smsSend"
    - "inAppSend"
    - "pushOpen"
    - "emailSend"
    - "pushSendSkip"
    - "inAppOpen"
    - "emailSendSkip"
    - "emailBounce"
    - "inAppClick"
    - "pushUninstall"

Package versions

Did not directly source the Fivetran package due to a special use case where we combine three Iterable projects and union them.

What database are you using dbt with?

snowflake

How are you running this dbt package?

dbt Core™

dbt Version

root@a41253d07c31:/app/data_marts# dbt --version Core:

Plugins:

Additional Context

No response

Are you willing to open a PR to help address this issue?

fivetran-avinash commented 2 months ago

Hi @syed-a-ali, thanks for reporting this issue and bringing it to our attention. After chatting with our team, I've reclassified this as a feature rather than a bug. Our models were not built to bring in userListIds. Also, at the moment, userListIds isn't available in the Iterable ERD for user_history. We would need that field to be available to do work on that and bring it into our data models.

Once that field is available, I think we can scope out and investigate the work it would take to bring in user list ids in the way we currently bring in email list ids. But for now I'd recommend your best path forward is to work closely with the Fivetran support team to see if you can find a way to get the userListId values available to you within either the additional_properties value in user_history, or populate the userListIds field somehow.

Sorry we couldn't be of more immediate help here! Once that field is available in the ERD, we can potentially start scoping out bring that in. Let us know if you have any additional questions or concerns about the Iterable package!

syed-a-ali commented 2 months ago

Thanks for the update. Per advise from the connector support team I've raised this feature request there.