I've currently got an integration with the Google Analytics (GA) API which pulls data on a daily basis and stores it within a database for use within a PHP project.
I'm trying to get the "Average Session Duration" within my project to match the value of which is set within GA for a specific channel (Organic Search), on a specific day (Apr 1, 2018 - Apr 1, 2018).
As you can see from the below image, the total Average Session Duration for that specific filter within GA is returning "00:04:28", for use later, this in seconds is 268 (Calculated by multiplying the time by [24 * 60 * 60]).
NB: I've removed some key, personal, information from the above image.
If we perform an export of this view, and select the option "Excel (XLSX)", this will give us a spreadsheet with values in which replicate what's returned from the GA API.
The below image shows this dataset, and i've highlighted a few key things which are explained below the image:
The contents highlighted in blue is static data which is calculated by GA at export-time. This includes an average from them, which is the last row within the highlighted section (H7).
The contents highlighted in red is using the time to seconds calculations (Which was explained earlier), and this successfully replicates the same contents which is within GA. This means that the Average Session Duration coming from the API (In Seconds) converts correctly to the values which are shown (In Time) on the GA dashboard.
Now, here's where I'm having issues; The contents highlighted in pink is using the AVERAGE calculation within Excel, and takes values from H2:H6 and I2:I6 respectively. What I'm expecting is for these two averages to match the averages at the bottom of the blue and red sections, however, as you can see they don't.
Is there anyone here that's able to help with how GA are calculating that average, please?
