SSIS tutorial: How to import Google Analytics data into Excel

[daniel]

Introduction

Need to pull website performance data from Google Analytics and analyze it in Excel? You can automate this process without writing any code with SQL Server Integration Services (SSIS) and the ZappySys SSIS PowerPack.

In this tutorial, you’ll learn how to extract data from Google Analytics (GA4) using the Google Analytics Source and export it directly to Excel using the Excel Destination component.

Prerequisites

• Download and install the ZappySys SSIS PowerPack
• A Google account with access to a Google Analytics property
• Microsoft Excel installed
• SQL Server Data Tools (SSDT)

Steps

Step 1: Create or select a project in Google API console

1. Go to the Google API Console.

2. Click the project dropdown at the top bar, select an existing project, or click CREATE PROJECT to make a new one.
   

   

3. Once the project is set, click ENABLE APIS AND SERVICES.

4. Search for Google Analytics Admin API and enable it.
   

   

5. Go to the OAuth Consent Screen tab, fill in the required details, and save.

6. Navigate to the Credentials tab.

7. Click CREATE CREDENTIALS, choose OAuth Client ID, select Desktop App as the application type, and click Create to obtain your Client ID and Client Secret.
   

   

Step 2: Connect to Google Analytics in SSIS

1. Add a Data Flow task to your SSIS package.

2. Drag a Google Analytics Source component inside the Data Flow.

3. Click New Connection to configure the connection.

4. Choose the authentication type, select GA4 as the version, and enter your Client ID and Client Secret.

5. Generate the token, test the connection, and click OK to save.
   

   

6. Choose the views/profiles you want to use.

7. Select your query option.

   NOTE: Reporting templates have not yet been implemented for GA4.

8. Set parameters such as metrics, dimensions, filters, etc. Use the checkboxes to enable or disable parameters.

9. Preview the data and click OK to save the configuration.
   

   

Step 3: Set up the Excel Destination

1. Drag the Excel Destination from the toolbox into the Data Flow and connect it to the Google Analytics Source.

2. In the Connection Managers pane, right-click and select New Connection, then choose ZS-EXCEL.
   

   

3. Specify the file path of your Excel file.

4. Optionally configure the Excel format and protection settings (e.g., passwords).

5. Test the connection and click OK to save it.
   

   

6. Double-click the Excel Destination component and select the Excel connection.
   

   

7. In the Component Properties, choose the target worksheet and define options such as start cell or overwrite behavior.
   

   

8. Go to the Input Columns tab and select the fields you want to export.
   

   

9. Click OK to finalize the configuration.

Step 4: Run and verify

1. Execute the SSIS package.
   
   
2. Open the resulting Excel file to review the extracted Google Analytics data.

Conclusion

Using SSIS with ZappySys components like Google Analytics Source and Excel Destination, you can automate the extraction and reporting of analytics data without writing a single line of code. This is ideal for marketers, analysts, and developers who need scalable and repeatable ETL workflows.

References

• Google Analytics Source
• Google Analytics Source documentation
• Excel Destination
• Excel Destination documentation
• Blog articles
• SSIS PowerPack

Contact us

If you encounter any issues or have specific requirements, feel free to reach out to our support team via chat or support ticket.