Connecting products and categories with Google Analytics MCF API Intro

 

How to track specific product purchased by name, id, category or similar and tie the sales to the MCF API data (Multi Channel Funnel)? – as an example:

  • Black T-shirt was bought with this purchase path Organic Search > Paid Search > Social Network inside 5 days with total conversion value of $5 (Like this will ever happen:))

Some additional insight may come up:

  • Track how much time, on average, is needed for a purchase of specific product or a group of products (currently limited to max default lookback window of 30 days – shame – feature request to Google – increase to 90 days or give the ability to set it manually for the MCF API – please).
  • Track if expensive products need a longer conversion path in terms of days and touch points.
  • Track channels which create awareness for a particular group of products and find ideas for scaling sales.

You will hopefully find some delicious insights while you explore this pretty useful connection between CORE and MCF API – just by browsing the dimensions and metrics available from the APIs you’ll pile up some exploration ideas:

  •  CORE API dimensions and metrics – https://developers.google.com/analytics/devguides/reporting/core/dimsmets – please note this page includes the Reporting API v4 dimensions and metrics as well
  • MCF API dimensions and metrics – https://developers.google.com/analytics/devguides/reporting/mcf/dimsmets/

Before getting into details we will be working with CORE API v3 as it is supported in Google Sheets Google Analytics add on. This is an intro article so the idea is to enable access to this data without unneeded tech hassle.

Prerequisites

  1. Working ecommerce implementation
  2. At least some historic data
  3. Access to Google Analytics account / property / view
  4. Some basic Excel / Google Sheets experience

Step 1 Google Sheets setup

Using Google Analytics add on for Google Sheets create the following 2 queries using this procedure …

After you open a new Google Sheets document and installed the GA add on create a new report:

1 create new report GA sheets

Fill in the details for the GA Account / Property / View access (remember use only views with ecommerce enabled (goals may work as well)):

2 define account property view for pulling data

Add the required metrics and dimensions and build the queris for mcf and core API:

3 mcf and core queries

1st query is a MCF API call which uses:
Metrics:

  • mcf:totalConversionValue

Dimensions:

  • mcf:transactionId
  • mcf:conversionDate
  • mcf:basicChannelGroupingPath
  • mcf:pathLengthInInteractionsHistogram
  • mcf:timeLagInDaysHistogram

2nd query is for CORE API (standard reports) where we pull product info:
Metrics:

  • ga:itemRevenue

Dimensions:

  • ga:transactionId
  • ga:productName

Both queries can of course be customised extensively based on the questions you are trying to find answers for.

Note that the max count of results returned from the API is 10 000 if you have more than that you will have to create additional queries yet you will use the Start Index filed in the query set to 10001 and so on – there are existing plugins such as Analytics Edge for Excel which can help with that.

If a transaction can contain multiple products the entire process is a bit different so your main metric becomes item revenue instead of total conversion value. You can in that case add an additional calculation where you would basically do item revenue / transaction revenue and the attribution would be used only if the result would be > 50%. 

Once all is set you can proceed with running the report:

4 run report

The result of running the reports will be two additional Sheets which hold the data for further tasks.

5 reports in sheets

2 Google Sheets String manipulation (adding fields for more granular analysis and pivot options)

Add a new empty sheet (one of the ways):

6 add new sheet

Copy data from MCF report (Dummy MCF sheet) to the empty sheet (the new sheet should be named Reporting) (If transactions contain multiple products you will have to revert the process in terms of first copying data from Dummy Standard):

7 referencing fields in Reporting sheet

You basically reference the cell from a different sheet so it will always refresh once you pull a different data set.

Next sub step is adding some more columns for pivoting purposes (just as ideas – you can always come up with your own columns).

The idea is to come up with the First channel responsible for the sale and then the Penultimate channel. You can also do last and what not yet penultimate is something we do not often analyse but can prove to be valuable (better say the channel before the decision itself).

Case:

  • Conversion path contains the following string …
  • Display > Direct > Paid Search > Organic Search
  • We want to extract Display (First channel) in one cell and Paid Search (Penultimate channel) in a different cell.

We need to create an extraction formula which will go through all the purchase paths (regardless which channels these contain and the number of touch points). Note that the formula will be different based on which dimensions you use (channels, vs adwords campaigns vs source / medium etc.). In your newly created Reporting sheet after you referenced the Dummy MCF sheet cells successfully click on the next empty cell and enter this formula:

8 extract string

The formula for First channel is:
=IFERROR(TRIM(REGEXEXTRACT(C2,”^[a-zA-Z()]+”)),”-“)

The formula for Penultimate channel is:
=IFERROR(TRIM(REGEXEXTRACT(TRIM(REGEXEXTRACT(C2,”[a-zA-Z() ]+ > [a-zA-Z() ]+$”)),”^[a-zA-Z() ]+”)),”-“)

Copy / paste the formulas throughout the rows (remember max rows is by default 10 000). These formulas will not work in Excel though.

3 Tie the Dummy Standard data with the new reporting tab based on transaction Id

In our Reporting tag where we did all our string manipulation we need to fill in on more column – product name in this case (it can be whatever you queried from CORE API – just remember you always need the transaction ID as it serves as a primary key).

Use the free column on the right and name it like Product Name and in the first data row enter the formula which will tie the product name based on transaction Id:

9 vlookup

Formula used to tie product to channel grouping:
=IFERROR(VLOOKUP(A2,’Dummy Standard’!$A$16:C$10015,2,FALSE),”No Product Info”)

The formula:

  • takes the value from the current sheet set in 1st column starting at A2 and
  • finds the same transaction Id value in Dummy Standard sheet
  • in the cell range from A16:C10015 and
  • returns value from the 2nd column from that sheet to our Reporting sheet.

And your data set is ready for pivoting (some reformatting may be required).

4 Pivoting

My advice, as Google Sheets pivoting is a bit limiting, is to export the Reporting sheet to CSV and import all to Excel (much easier to group and what not).

Screenshot_61

Select the data range > Create the pivot table and explore:

10 Pivot start

 

Leave a Reply

Your email address will not be published. Required fields are marked *