1308971 - [Basket] passing referral/source url

Status: REOPENED

(Websites :: Basket, defect)

Description

[Eric Renaud [:erenaud]]

User Story prompting changes to Basket: As the Email program owner, I need to know when/how/where incoming email opt-ins are happening per email list, so that I can attribute opt-in numbers to particular campaigns.

Changes to basket: Source and Referring URL

Previous comments in email:

- We pass the *original* source URL through to SFDC. This is for the *first* time someone is in our system.

- We do *not* pass any more referring URLs in SFDC for subscriptions that happen later on.

Requirement - need to store info on conversion tracking of signup forms, and for ongoing segmentation.

Comment 1

[Eric Renaud [:erenaud]]

New Approach:

Referral URLs (in addition to the original Source URL)
For tracking referring URLs per additional subscriptions, we're going to store this in SFMC. 

Pmac will set up Basket to have all incoming subscription calls put this information there so that we can keep up with what newsletter subscriptions/interests were assigned to an email address, from what referring URL. (pending privacy approval).

(Since SFMC doesn't have storage issues, this will help us get the info so we can see if/how we use it and if it will make sense to put in SFDC at a different time in the future.)


Jess to create “NEWSLETTER_SOURCE_URLS” data extension in SFMC that will have the following fields (exact detail to be added in this bug):
 - email address
 - source URL (max out text)
 - SFDC Field name

Comment 2

[Jessilyn Davis]

Privacy has cleared via email with Marshall on 10/18!

"NEWSLETTER_SOURCE_URLS" data extension has been created in SFMC
(Shared Data Extensions > Source URLs)

The fields are below in this format:
Name
Type
Length
Nullable? (yes/no)


Email
EmailAddress
254
no

Signup_Source_URL__c
Text
255
yes

Newsletter_ID
Text
500
no


Please let me know if you have any questions.

Thanks!

Comment 3

[Paul [:pmac] McLanahan]

Just to be clear: these records are to be created in addition to, not instead of, the Source_URL field in SFDC right? And Newsletter_ID is to be the SFDC newsletter field name, not the newsletter slug from basket right?

Also I'm wondering why the length for the Newsletter_ID field is twice that of the Signup_Source_URL__c one? The Newsletter_ID will always be pretty short (less than 100 characters), where the source URL could well be very long (max URL length in browsers is around 2000 characters). I will truncate the data to fit into the fields, but if we can make the text field for the URL larger I think we should.

Lastly, is this new DE setup in the SFMC sandbox account?

Comment 4

[Paul [:pmac] McLanahan]

One more question: for subscriptions to a group newsletter ID (one that results in subscriptions to several fields in SFDC), do we want a record in this DE for each of those newsletter IDs, or just for the group ID? I guess this would inform the "SFDC field name vs. Basket slug" debate.

Comment 5

[Jessilyn Davis]

(In reply to Paul [:pmac] McLanahan from comment #3)
> Just to be clear: these records are to be created in addition to, not
> instead of, the Source_URL field in SFDC right? 

Correct! In addition to. They should be stored in both places. The SFDC record will continue to hold the *first* place someone enters our system.


>And Newsletter_ID is to be the SFDC newsletter field name, not the newsletter slug from basket right?

Correct!  I just updated the field name to be more accurate:

"Newsletter_Field_Name"

> 
> Also I'm wondering why the length for the Newsletter_ID field is twice that
> of the Signup_Source_URL__c one? The Newsletter_ID will always be pretty
> short (less than 100 characters), 

For most scenarios yes - until someone mass subscribes to allofthenewsletters from either the new /confirm page or the email preference center. Or until we get creative with newsletter groups to indicate interests.

> where the source URL could well be very
> long (max URL length in browsers is around 2000 characters). I will truncate
> the data to fit into the fields, but if we can make the text field for the
> URL larger I think we should.

The Source_URL__c field is a "URL type" field in SFDC (instead of a text field like it is in SFMC :/ ). URL type fields have an automatic limit of 255 characters. 

I've talked with Andrew about changing this - but there's not a good way to change the field type without losing all the current source URLS (or having to export and re-import them.... for the entire database).


> 
> Lastly, is this new DE setup in the SFMC sandbox account?

Yep! I just set 'er up.


(In reply to Paul [:pmac] McLanahan from comment #4)
> do we want a record in this DE for each of those newsletter IDs, or just for the group ID?

All of the newsletter field names that were passed through. That way if we change group IDs and newsletter IDs, we have what the actual field names were changed during that subscription call.

Thanks, Pmac!

Comment 6

[Paul [:pmac] McLanahan]

(In reply to Jessilyn Davis from comment #5)
> > Also I'm wondering why the length for the Newsletter_ID field is twice that
> > of the Signup_Source_URL__c one? The Newsletter_ID will always be pretty
> > short (less than 100 characters), 
> 
> For most scenarios yes - until someone mass subscribes to
> allofthenewsletters from either the new /confirm page or the email
> preference center. Or until we get creative with newsletter groups to
> indicate interests.

But we'll be storing a row per newsletter_field_name, so it'll never be longer than a single field name.
 
> > where the source URL could well be very
> > long (max URL length in browsers is around 2000 characters). I will truncate
> > the data to fit into the fields, but if we can make the text field for the
> > URL larger I think we should.
> 
> The Source_URL__c field is a "URL type" field in SFDC (instead of a text
> field like it is in SFMC :/ ). URL type fields have an automatic limit of
> 255 characters. 

True, but this is SFMC and we can store as much info as we like, which I think we should do. We can always truncate later if we need to move it to SFDC.

Comment 7

[Jessilyn Davis]

(In reply to Paul [:pmac] McLanahan from comment #6)

> But we'll be storing a row per newsletter_field_name, so it'll never be
> longer than a single field name.

Ah! Right! Smart. What's your recommended length? 50? 100?


> True, but this is SFMC and we can store as much info as we like, which I
> think we should do. We can always truncate later if we need to move it to
> SFDC.

Ah! Right! Smart again! What's your recommended length here? Should I try for the 2000?

Sidenote: What happens if we try to send a longer than 255 long Source URL field into SFDC? (Does it error, truncate, or default to blank?)

Comment 8

[Paul [:pmac] McLanahan]

(In reply to Jessilyn Davis from comment #7)
> Ah! Right! Smart. What's your recommended length? 50? 100?

100 should be fine.

> Ah! Right! Smart again! What's your recommended length here? Should I try
> for the 2000?

I don't see the harm in setting it as high as you can, but in reality I think something like 1000 should get nearly everything.

> Sidenote: What happens if we try to send a longer than 255 long Source URL
> field into SFDC? (Does it error, truncate, or default to blank?)

It is truncated. I have all of the max lengths in basket[0] and truncate anything that is over to avoid errors and record everything we can.

[0] https://github.com/mozmar/basket/blob/b8cd1e0347a0b31778adfc2e46001d2d0b4ecc2e/news/backends/sfdc.py#L80-L90

Comment 9

[Jessilyn Davis]

"NEWSLETTER_SOURCE_URLS" data extension has been updated in SFMC - both in Prod & Sandbox
(Shared Data Extensions > Source URLs)

The fields are below in this format:
Name
Type
Length
Nullable? (yes/no)


Email
EmailAddress
254
no

Signup_Source_URL__c
Text
1000
yes

Newsletter_Field_Name
Text
100
no



Happy to hear that it gets truncated and the call completes. \o/

Thanks pmac!

Comment 10

[Jessilyn Davis]

Pmac, is it too late to request an additional "Newsletter_Date" field to be the date when the API call was made?

Comment 11

[Paul [:pmac] McLanahan]

That's fine. Just like the dates we used to add for the newsletters in Master_Subscribers?

Comment 12

[Jessilyn Davis]

Ya! Except they're all in one field (vs a separate Date field for every newsletter FLG like the old days):

Newsletter_Field_Date

Sound good?

Comment 13

[Paul [:pmac] McLanahan]

Sure. I've already got the other name in the PR though. "Newsletter_Date".

https://github.com/mozmar/basket/pull/8/files#diff-51b8860a302aa6e150b7a2a32e8384b7R626

Comment 14

[Paul [:pmac] McLanahan]

Attachment: Link to Github pull-request: https://github.com/mozmar/basket/pull/8/files#diff-51b8860a302aa6e150b7a2a32e8384b7R626

Comment 15

[Jessilyn Davis]

Moving too fast today - I've updated it on my side too.

"Newsletter_Date" it is!

Thanks, Pmac!

Comment 16

[[github robot]]

Commits pushed to master at https://github.com/mozmar/basket

https://github.com/mozmar/basket/commit/81c85fbc561f0c2f2db8be7a083771988ab0cb62
Fix bug 1308971: Record all source_urls in SFMC

https://github.com/mozmar/basket/commit/459a61953773a9c937fde8f0d33ac444d382dac8
Merge pull request #8 from pmac/record-all-source-urls-1308971

Fix bug 1308971: Record all source_urls in SFMC

Comment 17

[Jessilyn Davis]

Hey pmac, when's this set to go live? 

(I checked the data extension in Prod SFMC and it has 0 records)

Comment 18

[Paul [:pmac] McLanahan]

It's testable now on dev. Let's try it there today and we can roll it out to prod once that's a success.

Comment 19

[Jessilyn Davis]

Hey pmac!

It looks like this data extension is filling up = Yay!

I just tested a FxAccount signup and the email address didn't come through to this data extension. Can you see what's up and make sure that we're documenting all subscribe calls from Accounts too? (I have this data extension able to accept null values as Source URL - just in case.)

Comment 20

[Paul [:pmac] McLanahan]

They've not yet pushed the fix for source_url to prod in FxA. I think you're CC'd on a bug about the push. Try again after that, but I can't see how those subscriptions would be in any way unique. Keep in mind though that it only records for new subscriptions. If you're subscribing to a newsletter to which you're already subscribed then it's a no-op and no records are updated in SFMC or SFDC.

Comment 21

[Jessilyn Davis]

10-4 on the source URL update to FxA - I'm on that bug and following it.

In my test, I was expecting my new FxAccount email (brand new & fresh into the system) would show up in this SFMC data extension with a blank source URL.  But it's not showing up. The email address is showing up in SFDC with the newsletter subscriptions (and the expected blank source URL).

Comment 22

[Paul [:pmac] McLanahan]

Why would we record blank source URLs in there? All of the other information is in the main contact record in SFDC.

Comment 23

[Jessilyn Davis]

Heyo - in the past week, these additional scenarios would be helpful to have to have on this Data extension so that we can better troubleshoot our systems:

1) *all* incoming subscriptions even if it's blank

2) incoming subscriptions from the confirm page

Is it possible to get these improvements in the next sprint (Sprint 4)?

Thank you, pmac. What's in this data extension so far as been incredibly helpful.

Comment 24

[Paul [:pmac] McLanahan]

(In reply to Jessilyn Davis from comment #23)
> Heyo - in the past week, these additional scenarios would be helpful to have
> to have on this Data extension so that we can better troubleshoot our
> systems:
> 
> 1) *all* incoming subscriptions even if it's blank

Okay. This is very redundant, but okay.

> 2) incoming subscriptions from the confirm page

Can you explain what you mean here? Are you asking for the email pref center to send source_url?

> Is it possible to get these improvements in the next sprint (Sprint 4)?

That's a question for Eric.

Comment 25

[Jessilyn Davis]

1) thank you.

2) Correct. If someone subscribes to a *new* newsletter from the email preference center - or from the /confirm page (that's now the email pref center with some modifications), please have that action send source_url.

3) I figured so - he's cc'ed ;)

Thanks!

Comment 26

[Jessilyn Davis]

Hey Eric & Pmac!

Can we get these last 2 improvements on the additional Source_URL work in the next sprint?

1) make sure to add *all* incoming subscriptions even if it's blank

2)  If someone subscribes to a *new* newsletter from the email preference center - or from the /confirm page (that's now the email pref center with some modifications), please have that action send source_url.

Comment 27

[Jessilyn Davis]

Additional improvement:

Source_URL is captured for *new* subscriptions to a newsletter, it's not for a repeat subscription. (ie if you signup on mozilla.org/en-US and again on mozilla.org/newsletter - the mozilla.org/newsletter subscription URL isn't passed through.

3) Please update code so that *all* subscriptions are passed through to Source_URL table, even if it's for an existing newsletter subscription.

Comment 28

[Eric Renaud [:erenaud]]

Paul - can you ballpark level of effort/require time here please?

Comment 29

[Paul [:pmac] McLanahan]

I'm gonna say that throwing all of those into a story would make it an 8. The one about the pref center recording source url may require changes on the bedrock side as well.