Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Not sure if anyone has had experience with extracting multi-channel funnel data from google Analytics. I can see that it's not possible through the connector but I was hoping this would be feasible through the blank query connector. However, I don't have experience developing these queries.
First question would be whether this is possible and secondly I would appreciate if anyone could refer me to a good guide or examples where I can start my investigation 🙂
Many thanks!
Raul.
Solved! Go to Solution.
Hi Arthur,
I sort of did. Not through any connectors though. In the end I built a script that connects to the Google API that controls the MCF reports. First, I had to get my service account key to be included on my script (in a json file format). I got some sample code from the documentation site and adjusted it to pull the data I needed. I'm including it here below (in python). I'm not an expert programmer, actually this was my first script so maybe there's a more optimized way to code it.
from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials from datetime import date, timedelta,datetime import psycopg2 def get_service(api_name, api_version, scopes, key_file_location): credentials = ServiceAccountCredentials.from_json_keyfile_name( key_file_location, scopes=scopes) # Build the service object. service = build(api_name, api_version, credentials=credentials) return service def get_first_profile_id(service): # Use the Analytics service object to get the first profile id. # Get a list of all Google Analytics accounts for this user accounts = service.management().accounts().list().execute() if accounts.get('items'): # Get the first Google Analytics account. account = accounts.get('items')[0].get('id') # Get a list of all the properties for the first account. properties = service.management().webproperties().list( accountId=account).execute() if properties.get('items'): # Get the first property id. In my case it was 9 property = properties.get('items')[9].get('id') # Get a list of all views (profiles) for the first property. profiles = service.management().profiles().list( accountId=account, webPropertyId=property).execute() if profiles.get('items'): # return the first view (profile) id. return profiles.get('items')[0].get('id') return None def get_results(service, profile_id): # Use the Analytics Service Object to query the Core Reporting API # for the number of sessions within the past seven days. return service.data().mcf().get( ids='ga:' + profile_id, #start_date='7daysAgo', start_date='3daysAgo', end_date='today', metrics='mcf:firstInteractionConversions', dimensions='mcf:conversionDate,mcf:source,mcf:sourceMedium,mcf:campaignName,mcf:adwordsAdContent,mcf:basicChannelGrouping', sort='mcf:conversionDate', filters='mcf:conversionGoalNumber==013', max_results='5000').execute() def print_results(results): if results: #connecting with database connection = psycopg2.connect(database = 'DATABASE_NAME', user = 'DB_USER', password = 'XXXXXX', host = 'DB_SERVER', port = 'XXXXX') cursor = connection.cursor() #parsing the query if results.get('rows', []): for row in results.get('rows'): conversiondate = row[0].get('primitiveValue') conversiondate = conversiondate[0:4]+'-'+conversiondate[4:6]+'-'+conversiondate[6:8] source_ = row[1].get('primitiveValue') sourcemedium = row[2].get('primitiveValue') campaign = row[3].get('primitiveValue') adcontent = row[4].get('primitiveValue') channel = row[5].get('primitiveValue') conversions = row[6].get('primitiveValue') #Query the data from my DB cursor.execute("SELECT * from ga_mcf where conversiondate = %s AND sourcemedium = %s AND campaign = %s and adcontent = %s", (str(conversiondate),str(sourcemedium),str(campaign),str(adcontent))) a=len(cursor.fetchall()) if a>0: #update old entries """ print("update") """ cursor.execute("UPDATE ga_mcf set conversions = %s where conversiondate = %s AND sourcemedium = %s AND campaign = %s AND adcontent = %s", (str(conversions),str(conversiondate),str(sourcemedium),str(campaign),str(adcontent))) connection.commit() else: #Insert new rows """ print("insert") """ cursor.execute("INSERT INTO ga_mcf (conversiondate,source,sourcemedium,campaign,adcontent,channel,conversions) VALUES (%s,%s,%s,%s,%s,%s,%s)", (conversiondate,source_,sourcemedium,campaign,adcontent,channel,conversions)) connection.commit() connection.close() else: print ('No results found') def main(): # Define the auth scopes to request. scope = 'https://www.googleapis.com/auth/analytics.readonly' key_file_location = '.\JSON FILE HERE' # Authenticate and construct service. service = get_service( api_name='analytics', api_version='v3', scopes=[scope], key_file_location=key_file_location) profile_id = get_first_profile_id(service) print_results(get_results(service, profile_id)) if __name__ == '__main__': main()
Here are a couple of links for reference:
https://developers.google.com/analytics/devguides/reporting/mcf/dimsmets/
https://developers.google.com/analytics/devguides/reporting/mcf/v3/
I hope this helps
Hi guys!
Just a follow up... did you had any success with getting MCF data into PowerBI?
Hi Arthur,
I sort of did. Not through any connectors though. In the end I built a script that connects to the Google API that controls the MCF reports. First, I had to get my service account key to be included on my script (in a json file format). I got some sample code from the documentation site and adjusted it to pull the data I needed. I'm including it here below (in python). I'm not an expert programmer, actually this was my first script so maybe there's a more optimized way to code it.
from apiclient.discovery import build from oauth2client.service_account import ServiceAccountCredentials from datetime import date, timedelta,datetime import psycopg2 def get_service(api_name, api_version, scopes, key_file_location): credentials = ServiceAccountCredentials.from_json_keyfile_name( key_file_location, scopes=scopes) # Build the service object. service = build(api_name, api_version, credentials=credentials) return service def get_first_profile_id(service): # Use the Analytics service object to get the first profile id. # Get a list of all Google Analytics accounts for this user accounts = service.management().accounts().list().execute() if accounts.get('items'): # Get the first Google Analytics account. account = accounts.get('items')[0].get('id') # Get a list of all the properties for the first account. properties = service.management().webproperties().list( accountId=account).execute() if properties.get('items'): # Get the first property id. In my case it was 9 property = properties.get('items')[9].get('id') # Get a list of all views (profiles) for the first property. profiles = service.management().profiles().list( accountId=account, webPropertyId=property).execute() if profiles.get('items'): # return the first view (profile) id. return profiles.get('items')[0].get('id') return None def get_results(service, profile_id): # Use the Analytics Service Object to query the Core Reporting API # for the number of sessions within the past seven days. return service.data().mcf().get( ids='ga:' + profile_id, #start_date='7daysAgo', start_date='3daysAgo', end_date='today', metrics='mcf:firstInteractionConversions', dimensions='mcf:conversionDate,mcf:source,mcf:sourceMedium,mcf:campaignName,mcf:adwordsAdContent,mcf:basicChannelGrouping', sort='mcf:conversionDate', filters='mcf:conversionGoalNumber==013', max_results='5000').execute() def print_results(results): if results: #connecting with database connection = psycopg2.connect(database = 'DATABASE_NAME', user = 'DB_USER', password = 'XXXXXX', host = 'DB_SERVER', port = 'XXXXX') cursor = connection.cursor() #parsing the query if results.get('rows', []): for row in results.get('rows'): conversiondate = row[0].get('primitiveValue') conversiondate = conversiondate[0:4]+'-'+conversiondate[4:6]+'-'+conversiondate[6:8] source_ = row[1].get('primitiveValue') sourcemedium = row[2].get('primitiveValue') campaign = row[3].get('primitiveValue') adcontent = row[4].get('primitiveValue') channel = row[5].get('primitiveValue') conversions = row[6].get('primitiveValue') #Query the data from my DB cursor.execute("SELECT * from ga_mcf where conversiondate = %s AND sourcemedium = %s AND campaign = %s and adcontent = %s", (str(conversiondate),str(sourcemedium),str(campaign),str(adcontent))) a=len(cursor.fetchall()) if a>0: #update old entries """ print("update") """ cursor.execute("UPDATE ga_mcf set conversions = %s where conversiondate = %s AND sourcemedium = %s AND campaign = %s AND adcontent = %s", (str(conversions),str(conversiondate),str(sourcemedium),str(campaign),str(adcontent))) connection.commit() else: #Insert new rows """ print("insert") """ cursor.execute("INSERT INTO ga_mcf (conversiondate,source,sourcemedium,campaign,adcontent,channel,conversions) VALUES (%s,%s,%s,%s,%s,%s,%s)", (conversiondate,source_,sourcemedium,campaign,adcontent,channel,conversions)) connection.commit() connection.close() else: print ('No results found') def main(): # Define the auth scopes to request. scope = 'https://www.googleapis.com/auth/analytics.readonly' key_file_location = '.\JSON FILE HERE' # Authenticate and construct service. service = get_service( api_name='analytics', api_version='v3', scopes=[scope], key_file_location=key_file_location) profile_id = get_first_profile_id(service) print_results(get_results(service, profile_id)) if __name__ == '__main__': main()
Here are a couple of links for reference:
https://developers.google.com/analytics/devguides/reporting/mcf/dimsmets/
https://developers.google.com/analytics/devguides/reporting/mcf/v3/
I hope this helps
Hi @Anonymous
After searched, i can't find any documents about connecting Google analytics Multi-channel Funnels Reporting API via Power Query
Here is an similar idea you may vote for
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |