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

Analytics

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

Before you start

Here are some things to know before you set up the export:

  • In Piwik PRO, you’ll find the Gclid dimension, short from Google Click ID, that shows a parameter passed in the URL with ad clicks in Google Ads. It helps to identify the campaign and other ad click attributes.
  • A conversion action in Google Ads needs to have the same name as a goal in Piwik PRO.
  • A conversion action in Google Ads needs to be set 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 to 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 Websites & apps.
    • Choose the site or app from which you export goal conversions.
    • In General settings, look at the Time zone.
    • Translate 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 model, remove this parameter.
  3. Save the file.

Export goal conversions from Piwik PRO

Now you need to export goal conversions from Piwik PRO to the 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), 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.

Format exported data in a spreadsheet

After exporting goal conversions from Piwik PRO, you need to tweak the file so that it fits the template file. You can do it either in a spreadsheet or a text editor.

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

  1. Open the exported CSV file in a spreadsheet.
  2. Delete the following columns: Goal ID, Goal conversion. 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

    Format dates

  4. Next to the Conversion Name column, insert a new column and name it Conversion Time.
  5. Create a formula for the date and time concatenation. For example =CONCAT(E2;" ";F2;":59:59") where the E2 cell contains the date and the F2 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 in 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), Date (group by hour of day).

    External attribution model

  10. If you’re using an external attribution model, add a column for attributed credit next to the Conversion Time column. The final shape of the sheet is: Google Click ID, Conversion Name, Conversion Time, Attributed Credit, Conversion Value.
  11. Fill the column in with appropriate credit values. It should be between 0 and 1.

    No external attribution model

  12. If you’re not using an external attribution model, the final shape of the sheet is: Google Click ID, Conversion Name, Conversion Time, Conversion Value.

Format exported data in a text editor

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

To format 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. Turn on Regular expressions.
  3. Replace the following expressions.

    External attribution model

  4. 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

  5. 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.

    No external attribution model

  6. 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

  7. 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.

Prepare your file for upload to Google Ads

As the last step, you need to upload the modified CSV file to the template file. After that step, the file will be ready to upload it to Google Ads.

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

  1. Copy the formatted data and paste it to the template file.
  2. Optionally, fill in the Conversion currency column. You need to fill it in when you use multiple currencies or you combine data from multiple sites with different currencies.
  3. Save the file. Now it’s ready to be uploaded.

Was this article helpful?

Technical support

If you still have some questions, visit our community.
There’s always someone ready to help!

Back to help center