Page MenuHomePhabricator

High Unique Clicks for nlNL RML program
Open, LowPublic



We're seeing some unusually high unique clicks for one of our nlNL RML tracks. The specific track utm_source is RML2021_CadenceTestnlNL_T1_E1 and you can see it in this spreadsheet:

I retrieved that count from mysql pgehres, using this query:
SELECT utm_source, count(*) FROM pgehres.landingpageimpression_raw WHERE utm_source LIKE ‘RML2021_CadenceTestnlNL_%’ AND timestamp > ‘20200708000000’ GROUP BY utm_source;

I'm tagging all people from email team and analytics who might be able to chime in on this!

Event Timeline

KHaggard created this task.Jul 29 2020, 7:33 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 29 2020, 7:33 PM
KHaggard triaged this task as Medium priority.Jul 29 2020, 7:34 PM

Could someone from email team drop in the full email URL for that track?

DStrine added a subscriber: DStrine.Aug 3 2020, 8:06 PM

As discussed in civi fortnightly this could very easily be an email client registering click. Would Trillogy have any info on this? Fr-tech might not be able to help.

I'll flag this with our Trilogy rep this week, thanks!

KHaggard lowered the priority of this task from Medium to Low.Aug 6 2020, 9:10 PM

Just updating that we have a Trilogy rep looking into this, but I have some questions for fr-tech:

Is landingpageimpression_raw table pulling unique or gross?

  • How can we tell? I'm not very familiar with pgehres SQL, could fr-tech help me figure out the SQL to figure this out?
  • Is there a query to find unique records?

I can also mention this in a future meeting sometime.

EYener added a subscriber: Ejegg.Sep 4 2020, 10:11 PM

@KHaggard and I discussed this last week (or so) and I wanted to post some highlights here.

  • Katie's objective was to identify if there was a particular user causing a high rate of clicks to donate wiki
  • The query she was originally using was
SELECT utm_source, count(*) FROM pgehres.landingpageimpression_raw WHERE utm_source LIKE ‘RML2021_CadenceTestnlNL_%’ AND timestamp > ‘20200708000000’ GROUP BY utm_source;
  • We identified a few things about the overall ask, as well as RML click tracking: (1) It seems RML donors can't be tracked individually, as there is no hashed identifier associated with the RML being passed during this exchange, as there is with other email exchanges. Please correct me if I've stated this incorrectly - @Ejegg might be able to clarify that statement about when a unique user can be identified in pgehres.donatewiki_unique. (2) Other email clicks, which are sourced from pgehres.donatewiki_counts, are not unique clicks but gross clicks. Clicks are summarized and grabbed in sum by utm_source from this table by the Email team / by the data cube for reporting, so there is no identification of a 1:1 relationship between the click and the user.

It seems like this task can be closed out based on our conversations, correct? As I understand it, there is currently no way to drill down further into RML clicks for this campaign / time range, so this might be the furthest we can get right now. Of course, please add / correct me if this is incorrect!