I need to answer the following question:
What is the average number of email sends it take before a customer opens an email? I know I need to count the number of rows between each Open_Date in order to get the number of email sends in between each open. I realize I need to add the row_number () function for that, but I can't seem to get to the desired column in my second screenshot.
Something to keep in mind, I will need to get one average "CounttilOpens" per email address. In my example below, I have two different email addresses and their respective Send and Open dates and I need the query to function indepdently for each email address.
My data is structured as follows:
I need a query that help me get me a new column like this that restarts for each email address and for each gap of open dates: