search

department-of-veterans-affairs / va.gov-team

Public resources for building on and in support of VA.gov. Visit complete Knowledge Hub:
https://depo-platform-documentation.scrollhelp.site/index.html
281 stars 196 forks source link

Analytics KPI dashboard request for [VANotify] #34550

Closed miabecker closed 2 years ago

miabecker commented 2 years ago

Request an Edge Case KPI Dashboard

What this form is for

Use this template to request a KPI dashboard on Domo for your product. This template is for products that fall outside of our standardized dashboards.

If you have another VA.gov product, please reach out to the Platform Analytics & Insights team on #vfs-platform-support.

Expected delivery timelines

Please note that these dashboards will require additional data and design discovery, and will take additional time to deliver.

Understood - we would ideally like this stood up by the end of January 2022. Open to feedback and insight on what sort of timelines this dashboard will take.

Instructions for the VFS team

https://docs.google.com/spreadsheets/d/1tYevYp34WT22b73pFp-fU9v_gNJEc5p1xWFNe5ZmNwQ/edit?usp=sharing

The form and content areas of the spreadsheet do not seem to be relevant to our ask but open to feedback. Our data on notification statuses will be coming from Big Query and exported into DOMO. See query below.

DECLARE STARTDATE DATE;
DECLARE ENDDATE DATE;
DECLARE SERVICENAME STRING;

SET STARTDATE =  '2022-01-31';
SET ENDDATE = '2022-02-05';
SET SERVICENAME = 'dmc';

select FORMAT_DATE('%A, %b %d', results.date) as Date, results.Requests, results.NotFoundInMPI, results.MarkedAsDeceasedInMPI, results.NoVAProfileIDInMPI, results.NoEmailVaProfile, results.NotFoundInVAProfile,
results.Requests - (results.NotFoundInMPI + results.MarkedAsDeceasedInMPI + results.NoVAProfileIDInMPI + results.NoEmailVaProfile + results.NotFoundInVAProfile) as SuccessfulEmailAddressRetrieval,
Concat(
    cast(
        round(
        (results.Requests - 
            (results.NotFoundInMPI + results.MarkedAsDeceasedInMPI + results.NoVAProfileIDInMPI + results.NoEmailVaProfile + results.NotFoundInVAProfile)
        ) / results.Requests * 100, 2) 
        as string), 
    '%') as SuccessfulEmailAddressRetrievalRate,
results.Delivered as SuccessfulEmailDelivery, 
Concat(
    cast(
        round(
            results.Delivered / (results.Requests - (results.NotFoundInMPI + results.MarkedAsDeceasedInMPI + results.NoVAProfileIDInMPI + results.NoEmailVaProfile + results.NotFoundInVAProfile)) * 100, 2) 
        as string), 
    '%') as SuccessEmailDeliveryRate
FROM (SELECT 
date, 
SUM(count) as Requests,
SUM(CASE WHEN status_reason = 'Mpi Profile not found for this identifier' THEN count ELSE 0 END) as NotFoundInMPI,
SUM(CASE WHEN status_reason = 'Beneficiary has deceased status in MPI' THEN count ELSE 0 END) as MarkedAsDeceasedInMPI,
SUM(CASE WHEN status_reason = 'Requested identifier not found in MPI correlation database' THEN count ELSE 0 END) as NoVAProfileIDInMPI,
SUM(CASE WHEN status_reason = 'No contact info found from VA Profile' THEN count ELSE 0 END) as NoEmailVaProfile,
SUM(CASE WHEN status_reason = 'Received Forbidden HTTP error (403) while making a request to obtain info from VA Profile' THEN count ELSE 0 END) as NotFoundInVAProfile,
SUM(CASE WHEN status = 'delivered' THEN count ELSE 0 END) as Delivered
FROM `vsp-analytics-and-insights.platform_vanotify.prod-statistics` where service_name=SERVICENAME and (date >= STARTDATE and date <= ENDDATE) 
group by date
order by date) as results;

N/A

VFS-provided information

Add links to product outlines, feature information, or any other resources you think would help us better understand your product and OKRs below. Feel free to also type background information into this section directly. The more context you're able to provide, the fewer questions we'll need to ask you before getting started!

Product resources

Other product info

(Add here as needed.) The open and click-thru numbers that we provide, through GA, for our business lines are listed below. We would like to have these added to the dashboard and have the dashboard calculate open/click-thru rates

Additional Resources from the Platform Analytics team

Please leave the following blank

VSP Analytics & Insights Acceptance Criteria

  1. Discovery & Design
  1. Build (See internal process document & how to)
  1. Q&A
  1. Share

For new VFS customers only:

Definition of Done

Acceptance Criteria has been completed

miabecker commented 2 years ago

@chloedotbrown just wanted to check in on this ticket. Thank you!

miabecker commented 2 years ago

@bevnobev tagging for transparency.

chloedotbrown commented 2 years ago

Hi @miabecker – thanks for reaching out! Analytics will discuss this in our sprint planning meeting tomorrow, now that our full team is back in the office.

It does look like this will take custom KPI-setting and design work, and may potentially require some new data pipelines. There are also a couple edge case requests in our queue ahead of this one, so getting this stood up by the end of the month may not be something we're able to do. We should be able to update with a better estimate after discussing tomorrow.

miabecker commented 2 years ago

Adding our data fields VA Notify Data.pdf

bevnobev commented 2 years ago

@michelle-dooley, Maria added the fields that we are planning to upload to BigQuery. We are awaiting @bsmartin-ep create a bucket for us in BigQuery so we can upload to S3

miabecker commented 2 years ago

@michelle-dooley wanted to check in and see if you needed anything else to identify where this data will be leaving and creating the edgecase dashboard?

michelle-dooley commented 2 years ago

@miabecker I think we have what we need for now, we just need to dig into it a bit and see where this fits in with our other work. Will let you know as soon as we have more to share.

obrienjulie commented 2 years ago

@miabecker - Hi Maria, I have created a new table in Big Query based on the data fields that you supplied earlier. The new table is vsp-analytics-and-insights:platform_vanotify.email_results.

obrienjulie commented 2 years ago

@miabecker - Hi Maria - Just wanted to follow up to see if you needed anything else from us to start getting the data into Big Query. Thanks!

miabecker commented 2 years ago

@obrienjulie we are good I think on pushing the data into big query - tomorrow might be our first day in production. We are waiting on an edge case dashboard and have a ticket in for that.

bevnobev commented 2 years ago

Adding status definitions - https://docs.notifications.service.gov.uk/rest-api.html#email-status-descriptions

chloedotbrown commented 2 years ago

Thanks so much for adding this, @bevnobev – and do let us know if you're able to get open rates out of AWS as well! It'll give us a more accurate count than the GA data, if it's available.

bevnobev commented 2 years ago

@jakehova @nathanbwright Are we able to get open rates from AWS? see above for context

jakehova commented 2 years ago

We will look into this.

mjones-oddball commented 2 years ago

Status Definitions:

mjones-oddball commented 2 years ago

@bevnobev posted status definitions in comments and added the query from Jacob into the ticket description. If I missed anything please let me know! Per our discussion, we won't be getting open rates from AWS.

chloedotbrown commented 2 years ago

Hi @bevnobev - wanted to follow up on our conversation in office hours about this ticket. Our team is working on the dashboard design, and we've set up pipelines to bring the Google Analytics and notification data into Domo (updated to reflect query above). However, after reviewing the data, we're not going to be able to create an accurate open-rate estimate using a combination of the two. If open-rates do become available through the AWS data, then we'd love to use those. But for now, we'll have to show those metrics as total open events.

We're looking forward to connecting with @mjones-oddball and the new VANotify team soon to sync up on data sources and make sure the MVP matches their needs. I believe @michelle-dooley will be reaching out soon!

michelle-dooley commented 2 years ago

https://docs.google.com/spreadsheets/d/1I_EQzqONXWBWBWczDkfj-zYoCB_06JBjJGZklJen1ME/edit#gid=2091635579

bevnobev commented 2 years ago

Thanks Michelle. I updated the spreadsheet with one more ask, I link it's line 14. It's the click-thru rate for va.gov/profile. cc: @mjones-oddball

chloedotbrown commented 2 years ago

Hi @bevnobev - here's some language for cautioning folks against trying to recreate open-rates using GA data. I've attached a screenshot as well. Hope this helps!

We recommend against trying to create "open rates" using totals from Google Analytics. Because this data captures users who clicked from a campaign sent at any time prior to the date range selected, these totals will almost always be much higher than the true number of users who clicked from campaigns sent during that period. This results in artificially high open rates, sometimes over 100%.

For example, in a comparison of GA and notification data in the same period (1/25-2/5), email opens were higher than notifications delivered in 5 out of 11 cases.

Open Rates
mjones-oddball commented 2 years ago

@michelle-dooley Just wanted to check in and see if we're on track for end of March still? Anything you need from us?

michelle-dooley commented 2 years ago

@mjones-oddball - yep we are still on track for end of March!!

michelle-dooley commented 2 years ago

@bevnobev and @mjones-oddball - we have completed the VANotify KPI dashboard as discussed, see link below. I hope you will find this information useful!! I think it provides a lot of good insights. We did notice the bounce rate for week of 3/13 and 3/20 dropped significantly (1% and 0% respectively). We dug into this to validate as it jumped out at us but it seems to be correct, was wondering if something changed to cause that? Let me know if we need to discuss. Thanks!! Michelle

https://va-gov.domo.com/page/2040841289

bevnobev commented 2 years ago

Thanks @michelle-dooley I just requested access to the board.

Can you clarify the definition of bounce rate? Since we are sending notifications, I'm assuming the bounce rate is the percentage of veterans clicking on the link in the notification to land on a page, then leaving the page. Is that correct?

michelle-dooley commented 2 years ago

@bevnobev - I see your access was granted. Are you able to see it now?

You are correct on bounce rate. If you ever have questions on metric definitions you can always click on the card title and it will take you to a view that allows you to see more information about how it was calculated (looks like a typo in this one, will get that fixed) and drill down into details.

image

bevnobev commented 2 years ago

@michelle-dooley Thanks! I have access now.

bevnobev commented 2 years ago

@michelle-dooley Seems like the stats are only until 3/26, but we write to big query nightly.

michelle-dooley commented 2 years ago

Hi @bevnobev that is correct. Domo dashboards default groupings and comparisons are week over week so we generally only load once per week so you are comparing apples to apples. For example if we included the week of 3/27 you would see a big drop in this chart b/c the week is not over (may not now since it is Friday but earlier in the week you would have). You can still see daily trends by changing the graph by to day at the top right but it will still only report through 3/26. This week's data will be loaded Sunday. If you would like us to change this to daily loads for you we can since this is a custom report. You just need to be aware of that when doing week over week comparisons.

bevnobev commented 2 years ago

Thanks @michelle-dooley Let me think on that. If we decide to change to a daily load, how do we go about doing that?

michelle-dooley commented 2 years ago

@bevnobev just let us know and we can make the change, it is very simple update to the query to remove the date limit.

bevnobev commented 2 years ago

Good Morning @michelle-dooley. I'd like to change to a daily load. When we do that, are we able to see daily stats in Domo? Currently some of the sections don't show daily stats.

michelle-dooley commented 2 years ago

Hi @bevnobev - we will make that change for you. You can change the filter at the top right to "graph by - day" to see daily stats. You can also click on any graph/card title to drill down into more details about the metrics.

image

bevnobev commented 2 years ago

@michelle-dooley I drilled down into "Top reasons emails not retrieved", but didn't see any daily data

image

mjones-oddball commented 2 years ago

@michelle-dooley Can we set up 30 minutes to review the dashboard with Bev and me so we understand how best to utilize this? We are comparing it to the query in this task description to figure out how the dashboard will replace that.

michelle-dooley commented 2 years ago

Yes, I'll get something on our calendars.

On Mon, Apr 4, 2022 at 2:02 PM Melanie Jones @.***> wrote:

@michelle-dooley https://github.com/michelle-dooley Can we set up 30 minutes to review the dashboard with Bev and me so we understand how best to utilize this? We are comparing it to the query in this task description to figure out how the dashboard will replace that.

— Reply to this email directly, view it on GitHub https://github.com/department-of-veterans-affairs/va.gov-team/issues/34550#issuecomment-1087907036, or unsubscribe https://github.com/notifications/unsubscribe-auth/AWKC7W4VLI5OACIVA527BSLVDM4DXANCNFSM5K2UR7RQ . You are receiving this because you were mentioned.Message ID: @.*** .com>

jerinb-sdi commented 2 years ago

Ease of use

Chart title Assets Pass / Fail
Total users who opened emails Custom segment :green_circle: PASS
Avg. Campaign opens per user Custom segment / Filtered report :green_circle: PASS

Findability

Chart title Assets Pass / Fail
Claims & Appeals - VANotify users Custom segment :green_circle: PASS
Manage VA Debt - VANotify users Custom segment :green_circle: PASS
VA Profile - VANotify users Custom segment :green_circle: PASS
Avg. session per user for VANotify source BigQuery metric :green_circle: PASS
Bounce rate for VANotify sessions Custom segment :green_circle: PASS
jonathan-epstein13 commented 2 years ago

Hello @mjones-oddball and @bevnobev the VANotify dashboard has been updated with all the requested changes and has been QA'd. Let us know what you think. If you have any more questions or concerns, feel free to reach out.

michelle-dooley commented 2 years ago

Hi @mjones-oddball and @bevnobev I am going to close this ticket as this work is complete. If you need anything else please reach out and we will be happy to help!!

mjones-oddball commented 2 years ago

Hi @michelle-dooley Thank you so much for the domo updates! It looks great. 2 questions we haven't figured out:

  1. How can we share this out? Looks like it's locked down to an access list.
  2. We recently launched an email notification and can see that the clicks/referrals are being tracked in Google Analytics. However I'm not seeing it show up under the Landing Page section of our dashboard. The page itself is http://www.va.gov/view-change-dependents/add-remove-form-21-686c/resume/

Will new notifications/campaigns/landing pages automatically show up in the domo dashboard?

CC @bevnobev

michelle-dooley commented 2 years ago

Hi @mjones-oddball Glad everything is looking good!! As far as your questions....

  1. We updated the access level for you, you should be able to share now. If not let me know.
  2. We will automatically get new click events and notification totals for any new campaigns that go out. You can see that the 686c notifications are already filterable on the dashboard level and in the landing page section for the urls we're already tracking (see 1st screenshot). However, as far as what urls we're looking at for the landing page section, there's no way to automate those updates - you will have to let us know when there's another you'd like added. These urls manually added into the landing page BQ view (2nd screenshot). If you have another you would like included just let us know and we can get it added. image image
bevnobev commented 2 years ago

Thanks @michelle-dooley. How do we make that request?
One other item. I'm noticing that the campaign landing page numbers for Claims & Appeals seems off between GA and Domo. GA shows ~74K and Domo shows 8k for the same time period. GA shows events and Domo states users, but that's a huge difference. Am I missing something? cc: @mjones-oddball @megansiddle

michelle-dooley commented 2 years ago

Hi @bevnobev please complete the update request ticket below.

https://github.com/department-of-veterans-affairs/va.gov-team/issues/new?assignees=michelle-dooley&labels=analytics-insights%2C+analytics-request%2C+kpi-dashboard&template=analytics-request-update-kpi-dashboard.md&title=Analytics+KPI+dashboard+update+request+for+%5BTeam%5D

As far the campaign landing page numbers I believe you are using the wrong GA report. You are using the Pages report which is showing ALL pageviews while the Domo version is meant to recreate the Landing page report which only counts when the user started their session with that url.
cc: @mjones-oddball @megansiddle