Insights

Email Google Analytics 4 Reports Automatically To Yourself Or A Group Of Users

by Paul Cote on Feb 24, 2023

Digital marketing analytics has come a long way in the last decade. Data is now more accessible than ever before and most companies are no longer accepting a monthly share of their prior months data. While there are many fully automated ways to access and visualize data, we're finding that sometimes our users just want the data as an email attachment.

In this post we'll show you how to query the native Google Analytics 4 dataset in Google BigQuery and set up an automated daily, weekly, monthly, etc email using a Launchpad email destination.

  • Configure the GA 4 / BQ linking - https://support.google.com/analytics/answer/9823238?hl=en
  • Create a scheduled query to build your report table each day
  • Connect Launchpad to your BigQuery table
  • Create an email destination in Launchpad and add your recipient email addresses
  • Configure your job and schedule for the report

Configure the GA 4 / BQ linking

You'll need to have the GA 4 / BQ linking setup before you can complete the remaining steps. If someone else has created the native GA 4 transfer and you cannot gain access to the BigQuery events table, you can connect using Launchpad and transfer the data via API instead.

Create a scheduled query to build your report table each day

Use the following query as a starting point to query as a starting point.

Dimensions

  • date
  • medium
  • source
  • device

Metrics

  • unique_users
  • sessions
    
    SELECT  
        PARSE_DATE("%Y%m%d", event_date) AS date,
        traffic_source.medium AS medium,
        traffic_source.source AS source,
        device.category AS device,
        COUNT(DISTINCT user_pseudo_id) AS unique_users,
        COUNT(DISTINCT(
          SELECT value.int_value
          FROM UNNEST(event_params)
          WHERE key = 'ga_session_id'
        )) AS sessions
      
    FROM `your-project-id.analytics_your-ga4-dataset.events_202*` /* Adjust wildcard * to match your needs */
      
    WHERE PARSE_DATE("%Y%m%d", event_date) BETWEEN CURRENT_DATE() - 30 AND CURRENT_DATE - 1 /* Adjust date settings to match your needs */
      
    GROUP BY
        date, 
        medium, 
        source,  
        device
      
    ORDER BY 
        date desc
    

Connect Launchpad to your BigQuery Table

  • Create a new BigQuery data source. Follow the instructions in the wizard to connect to your Google Cloud Console account.
  • Select the table you created in the previous step.

Create an email destination in Launchpad and add your recipient email addresses

  • Create an email destination.
  • Follow the setup instructions and add your list of email addresses.

Configure your job and schedule for the report

  • Select the BigQuery source and your email destination you created in the previous step.
  • Configure your job settings.
    • The date setting should match the number of days or date range you would like to include in the attached file.
    • The pull frequency indicates the time the job will run each day. The job should be configured to run AFTER your scheduled query job in BigQuery has finished.
  • Configure your job schema
    • Select the fields you would like to have included in your file output
    • Indicate your file type preferences in the output settings

Need Help?

If you're having any trouble managing your data, then the experts at Calibrate would be happy to help show you what's possible. From walk-throughs and interactive demos to custom solutions, we'll help you set up a healthy analytics stack that fuels smart business decisions.

Schedule A Demo

Share this post: