I thought I had covered everything that needed to be covered in my matching BigQuery to Google analytics series until one day someone asked me to recreate the assisted conversions report, so here we are.

In the past, I have covered:

• Audience overview

• Channel Acquisition

• Landing pages

• Product Performance

• Product list Performance

For this report we need:

• The transactions

• The past sessions

• Tie the two together

The transactions

Recreating this report isn’t that hard. The first step is to get all the transactions from the period. Note this doesn’t work with the sample GA dataset because there is no client ID. In the query below, everything is pretty straightforward. The only bit that I haven’t covered before is the isTrueDirect. You may know that Google reports on the last non-direct attribution by default in other reports. In assisted conversions, you need the proper direct attribution.

The past sessions

Now you have the transactions. You need to see if they came to the site previously. Though, we only want the sessions since the last transaction, not all previous sessions. So this goes through all sessions, and if a user converts copy that transaction id to all the previous sessions going back, another transaction id is listed. Thanks to Corinne Brooker for that tip. You will also notice that my date range here goes one month more than the first query, so I have a 30-day lookback window. You should adjust this to what you want your assisted conversions to be within.

Tie the two together

Now we have the two bits of data, we tie together the client id and generate the table with the data we need. You could join on client id and transaction id, but I filter out those that don’t match. That way, if they want to look further back, it is pretty straightforward.

In the end, I make sure the assisted conversion was before or the same visit as the transaction. I also check that it was within my 30-day lookback window. You can change the 30 there to whatever look back you want. Visitstarttime is in seconds, so the calculation converts days to seconds.

That is how it is done. Any questions or comments, please leave them below and call out if there is anything else you might want to see in BQ.