How can I export goal conversions from Piwik PRO to Google Ads?

If you want to export goal conversions from Piwik PRO to Google Ads to see how your ads are performing, you need to set up the export. In this article, we’ll show you how.

Before you start

Here are some things to know before you start:

• In Piwik PRO, you’ll come across the Gclid dimension, short for Google Click ID. It’s a URL parameter associated with ad clicks in Google Ads, helping identify campaign and other click details.
• Make sure that a conversion action in Google Ads has the same name as a goal in Piwik PRO.
• Remember to set the conversion action in Google Ads as Import from clicks. For more, see this article (Part 1: Create one or more import conversion actions).

Prepare a template file

First, you’ll need to prepare a template file into which you can import data with goal conversions.

To prepare a template file, follow these steps:

1. Download the XLS file or CSV file.
2. In the file, add the following parameters:
   • TimeZone: your website’s time zone

   Tip: To find your website’s time zone, follow these steps:

   • Log in to Piwik PRO.
   • Go to Menu > Administration.
   • Navigate to Sites & apps.
   • Choose the site or app from which you want to export goal conversions.
   • In General settings, look for Time zone.
   • Convert this time zone to the one supported by Google Ads.
   • Attribution model: If you’re using an external attribution model, add its name. The name is defined in a conversion action in Google Ads. If you’re not using such a model, remove this parameter.
3. Save the file.
4. Done!

Export goal conversions from Piwik PRO

Now you need to export goal conversions from Piwik PRO to a CSV file.

To export goal conversions, follow these steps:

1. In Piwik PRO, create a custom report with the following setup:
   • Report type: Flat table
   • Dimensions: Gclid, Goal name, Goal revenue, Date (group by day) and Date (group by hour of day)
   • Metrics: Goal conversions (We need to use at least one metric to create a report.)
2. Export your report to a CSV file.

   

3. That’s done!

Format the exported data in a spreadsheet

After exporting goal conversions from Piwik PRO, you’ll need to adjust the file to match the template. You can do this using either a spreadsheet or a text editor.

To format the exported data in a spreadsheet, follow these steps:

1. Open the exported CSV file in a spreadsheet.
2. Delete the following columns: Goal name ID and Goal conversions. You won’t need them.
3. Rename columns so that they fit the template:
   • Gclid > Google Click ID
   • Goal name > Conversion Name
   • Goal revenue > Conversion Value
4. Format dates. Next to the Conversion Name column, insert a new column and name it Conversion Time.
5. Create a date and time concatenation formula. Example: =CONCATENATE(TEXT(F2, "yyyy-mm-dd")," ",TEXT(G2, "00"),":59:59") where the E2 cell contains the date and the G2 cell an hour of the day.

   Note: We add minutes and seconds at the end of the date and time because Google requires precision in seconds. The export from Piwik PRO has precision in hours.  We set it to the last minute and second of the hour :59:59.

6. Apply this formula to the remaining rows.
7. Now we need to replace the formula with values. Copy all cells from the Conversion Time column.
8. Paste the cells as a text into the same column. Right-click on the cell below the header row in the column and choose Paste Special > Text.
9. Delete the following columns: Date (group by day) and Date (group by hour of day).
10. If you’re using an external attribution model, add an Attributed Credit column next to the Conversion Time column. So, your spreadsheet should have the following columns: Google Click ID, Conversion Name, Conversion Time, Attributed Credit and Conversion Value.
11. Fill in the column with appropriate credit values. It should be between 0 and 1.
12. If you’re not using an external attribution model, your spreadsheet should have the following columns: Google Click ID, Conversion Name, Conversion Time and Conversion Value.
13. Done!

Format the exported data in a text editor

You can also format the exported data using a text editor instead of a spreadsheet.

To format the exported data in a text editor, follow these steps:

1. Open the exported CSV file in a text editor. It needs to support regular expressions.
2. Run the Find & Replace command and turn on Regular expressions.
3. Check what attribution model you are using and replace the appropriate regular expressions.
4. If you’re using an external attribution model, format the header of the CSV file:

   Find: ([Gg]clid|campaign_gclid).*
   Replace with: Google Click ID,Conversion Name,Conversion Time,Attributed Credit,Conversion Value,Conversion Currency

   And format dates:

   Find: \d+,(\d{4}-\d{2}-\d{2}),(\d{1,2}),([\d.]+),\d
   Replace with: $1 $2:59:59,,$3,

   Note: We add minutes and seconds at the end of the date and time because Google requires precision in seconds. The export from Piwik PRO has precision in hours.  We set it to the last minute and second of the hour :59:59.

5. If you’re not using an external attribution model, format the header of the CSV file:

   Find: ([Gg]clid|campaign_gclid).*
   Replace with: Google Click ID,Conversion Name,Conversion Time,Conversion Value,Conversion Currency

   And format dates:

   Find: \d+,(\d{4}-\d{2}-\d{2}),(\d{1,2}),([\d.]+),\d
   Replace with: $1 $2:59:59,$3,

   Note: We add minutes and seconds at the end of the date and time because Google requires precision in seconds. The export from Piwik PRO has precision in hours.  We set it to the last minute and second of the hour :59:59.

6. That’s done!

Prepare your file for upload to Google Ads

As the final step, you need to upload the modified CSV file into the template file. Once you’ve completed this step, the file will be ready to upload to Google Ads.

To prepare your file for upload to Google Ads, follow these steps:

1. Copy the formatted data and paste it into the template file.
2. (Optional) Fill in the Conversion currency column. You should fill it in if you use multiple currencies or combine data from different sites with various currencies.
3. Save the file.
4. Done! Now your file is ready to be uploaded to Google Ads.

   Note: Read more about importing conversions into Google Ads.