How can I export goal conversions from Piwik PRO to Microsoft Ads (Bing Ads)?

If you want to export goal conversions from Piwik PRO to Microsoft Ads (Bing 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:

• You’ll need to create a custom dimension that will store Microsoft Click ID. Piwik PRO doesn’t store such data by default yet. The ID will help to identify campaigns and other ad click attributes.
• A conversion tracking in Microsoft Ads needs to have the same name as a goal in Piwik PRO.
• A conversion in Microsoft Ads needs to be set as Offline conversion (imported from clicks). For more, see this article (part: How do I set up offline conversion tracking?).
• Auto-tagging of Microsoft Click ID is automatically turned on when you create your first offline conversion goal in Microsoft Ads. You can always check your auto-tagging status of the click ID in Microsoft Ads > All campaigns > Settings > Account level options.

Create a custom dimension with Microsoft Click ID

First, you need to create a session custom dimension that will hold the Microsoft Click ID.

To create a custom dimension, follow these steps:

1. Go to Menu > Analytics.
2. Navigate to Settings.
3. On the left, click Custom dimensions.
4. Click Add a session dimension.
5. Name the dimension msclkid.
6. Click Save.
   

   

Now it’s time to set up a variable that will catch the value of msclkid (Microsoft Click ID).

To set up a variable, follow these steps:

1. Go to Menu > Tag Manager.
2. Navigate to Variables.
3. On the left, click Add a variable.
4. Name the variable Microsoft Click ID.
5. Choose the following variable: URL.
6. In Variable settings, choose GET Parameter.
7. In Get Parameter add: msclkid.
   

   

8. Click Add a variable.

After creating the variable, we are going to use it in a trigger to check if it has any value (if it has that means the visitor came from Microsoft Ads). 

To add a trigger, follow these steps:

1. Navigate to Triggers.
2. On the left, click Add a trigger.
3. Name the trigger: msclkid is not empty.
4. In Event type, choose a type: Page view.
5. Click Next.
6. In Event conditions, choose: When all these conditions are met. 
7. Enter your condition: Microsoft Click ID is not empty.
   

   

8. When you’re done, click Add a trigger.

Once we have the trigger, we can create a tag that will send the Microsoft Click ID value to the custom dimension we’ve created in the first step.

To add a custom dimension tag, follow these steps: 

1. Navigate to Tags.
2. Click Add a tag.
3. Name the tag Microsoft Click Id is not empty.
4. Choose the following type: Custom dimension.
5. In Dimension, choose msclkid.
6. In Dimension value, type Microsoft Click ID.
   

   

7. Click Add a tag.
8. Test your tag in debug mode.
9. When you’re happy with how the tag works, click Publish.

Prepare a template file

Now 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 XLSX 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.
   • Chenge this time zone to the one supported by Microsoft Ads.
3. Save the file.
   

   

Export goal conversions from Piwik PRO

As a next step, 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: msclkid, 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 XLSX or 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:
   • msclkid > Microsoft Click ID
   • Goal name > Conversion Name
   • Goal revenue > Conversion Value
4. Format dates.
5. Next to the Conversion Name column, insert a new column and name it Conversion Time.
6. 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 Microsoft 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.

7. Apply this formula to the remaining rows.
8. Now we need to replace the formula with values. Copy all cells from the Conversion Time column.
9. 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.
10. Delete the following columns: Date (group by day), Date (group by hour of day).

Prepare your file for upload to Microsoft 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 Microsoft Ads.

To prepare your file for upload to Microsoft 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.