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:
Some additional insight may come up:
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:
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.
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:
Fill in the details for the GA Account / Property / View access (remember use only views with ecommerce enabled (goals may work as well)):
Add the required metrics and dimensions and build the queris for mcf and core API:
1st query is a MCF API call which uses:
2nd query is for CORE API (standard reports) where we pull product info:
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:
The result of running the reports will be two additional Sheets which hold the data for further tasks.
Add a new empty sheet (one of the ways):
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):
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).
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:
The formula for First channel is:
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.
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:
Formula used to tie product to channel grouping:
=IFERROR(VLOOKUP(A2,’Dummy Standard’!$A$16:C$10015,2,FALSE),”No Product Info”)
And your data set is ready for pivoting (some reformatting may be required).
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).
Select the data range > Create the pivot table and explore: