Calculate One-Time Visitors from Google Analytics Session Data

[jlie]

I have a data table that contains single Sessions and the Dimension ga:userType, that states if the Session is the first session of the identified user or a subsequent one ("New Visitor" vs. "Returning Visitor"). The ga:clientId dimension tells me the actual user, how ga identifies them.

I am now trying to create a measure, that calculates the number of one-time visitors vs. the number of returning visitors: It should therefore aggregate the distinct Client Ids for all the Users that only have a "New Visitor" session and no subsequent "Returning Visitor" Sessions. 

How could I accomplish that? Thanks in advance!

[v-yuezhe-msft]

Hi @jlie,

Could you please share sample data of you table and post expected result here?

Assume that you have a data table as follows.


You can create two measures.
number of one-time visitors = CALCULATE(COUNTA('Session'[ClientID]),FILTER('Session','Session'[UserType]="New visitor"))
number of returning visitors = CALCULATE(COUNTA('Session'[ClientID]),FILTER('Session','Session'[UserType]="Returning visitor"))

Thanks,
Lydia Zhang

[jlie]

Of course, yes. So this is the relevant structure of my table. Of course, in reality the ClientIds are the GA Client Ids and the SessionID is calculated from Client-Id and the Session-Iterator. User Type is nothing more than a if [session count] = 1 then "new" else "returning"; wether I import it as a dimension from Google Analytics or calculate it myself.

My expected Result:

The "One Time Visitors" Measure counts Clients C and D, and therefore gives me a 2.

A "Recurring Visitors" Measure counts Clients A and B, and therefore also gives me the number 2.

I think I need some nested Calculations, because the formula needs to evaluate for every single Client-Id, if it is "New Visitor", if there are subsequent Sessions with "Returning Visitor" and only then make a DISTINCCOUNT on the [Client Id] Column.

[jlie · Accepted solution]

Ok, i figuered it out:

I need two measures on the table AnonymousSessions

Returning Visitors = 
CALCULATE(
    DISTINCTCOUNT(AnonymousSessions[Client Id]);
    AnonymousSessions[User Type]="Returning Visitor"
)

OneTimeVisitors = 
    DISTINCTCOUNT(AnonymousSessions[Client Id]) - [Returning Visitors]

This yields the correct data; I can now slice/dice the sessions by channel or date and see 100% accurate the returning and one-time visitors in the respective context.

[Andrian]

Hello,

I am using powerBI Desktop and using the GA connector provided by PBI. How do you get the client ID and the Session Id? I don't see them in the data. Thank you.

Fred