Read on Twitter
Right now @SubstackInc offers basic analytics.
I thought I would start giving them an upgrade with my recently acquired Python skills.
The first step is going to the bottom of the settings page of your publication and exporting your settings.
A thread
I thought I would start giving them an upgrade with my recently acquired Python skills.
The first step is going to the bottom of the settings page of your publication and exporting your settings.
A thread
Your folder will contain 4 files for each newsletter you've sent, in addition to some main files plus a couple of other files.
Not all of them are needed.
Not all of them are needed.
I load up the main "posts" file which gives a summary information of all the newsletter posts which you've sent out.
Here I want to isolate the "post_id"
Here I want to isolate the "post_id"
I don't want all posts. Just the ones which I've actually sent.
So I need to filter out posts without a subtitle (which are likely default posts when you make a newsletter) as well as anything which hasn't been published.
This leaves me with my 7 sent emails.
So I need to filter out posts without a subtitle (which are likely default posts when you make a newsletter) as well as anything which hasn't been published.
This leaves me with my 7 sent emails.
With the correct post_id's, I can then access all of the files with that post_id which also include the word "opens".
But there's a problem. As the logs show multiple email opens, sometimes, very close together from the same person...which probably should count as just one view
But there's a problem. As the logs show multiple email opens, sometimes, very close together from the same person...which probably should count as just one view
I want to modify my data to only include the first open from a user (for now). I could simply strip out the email list from this list and clear duplicates, but then I lose out on meta data like timestamps of the open.
In this case, I'm actually saving the time (in a time format)
In this case, I'm actually saving the time (in a time format)
I then loop over all the "opens" files and save all of that data to the same table/sheet (or Dataframe in python).
This now gives me a single sheet of everyone who has opened at least 1 of my emails, as well as which specific email they opened and when.
This now gives me a single sheet of everyone who has opened at least 1 of my emails, as well as which specific email they opened and when.
Now that I have all the "open" info. I have to see who actually got which emails.
Maybe the person who opened 5 emails, only has received 5 emails.
Given everyone only gets sent 1 email. All I have to do is loop over all the "delivered" files and record emails.
Maybe the person who opened 5 emails, only has received 5 emails.
Given everyone only gets sent 1 email. All I have to do is loop over all the "delivered" files and record emails.
From there, I can merge the "delivered" table with the "opened" table.
And reformat that into a simple 4 column table of, posts opens, sent, and perform a quick calculation to get open rate.
And reformat that into a simple 4 column table of, posts opens, sent, and perform a quick calculation to get open rate.
I can do another reformat and see how the different openrates per reader.
Finding out that 63 readers have read all the issues that they have received.
...somewhat shockingly 57 have never opened one they have received.
Finding out that 63 readers have read all the issues that they have received.
...somewhat shockingly 57 have never opened one they have received.
Right now, the cohort which is performing best all signed up in week 2 of the newsletter, and have an average open rate of 76%...but most likely because it's only 5 readers!
I'm excited to see how the cohort from two week ago performs as it's my largest cohort.
I'm excited to see how the cohort from two week ago performs as it's my largest cohort.
Just scratching the surface on this. Have some good thoughts which I'll continue on over the next few days.
I have a lot of info, now my job is to keep tracking and improving. Reach out to customers who haven't opened...thank those that read a lot etc etc...
I have a lot of info, now my job is to keep tracking and improving. Reach out to customers who haven't opened...thank those that read a lot etc etc...
