From developing new campaigns to content planning and creation, marketing involves a wide range of activities. However, tracking the success of your marketing strategy can be just as complex as developing and implementing them. Search engine marketing provides excellent data-rich and cost-effective opportunities to advertise your products and services. Nevertheless, data from your online advertising can be time-consuming to review and overbearing to analyze without having the data available through other data analyzing software or automating the retrieval of information. Pulling Bing Ads reports and storing it in a database on a timely basis would be essential for your marketing and advertising needs. Bing Ads offers multiple types of reports like for Ads, Account Performance, Audience, Geographic and much more which provides numerous opportunities for analysis and extrapolation. In such cases, it would be utmost essential to have an SSIS package that can be scheduled to run in a timely manner, to extract the Bing Ads reports specifying a start and end date, or a for a specific duration.
With our Bing Ads components from KingswaySoft, this could be easily done. In this blog post, we demonstrate how a Bing Ads Source component can be used to read from Reports and extract data in a tabular format, and how it can be utilized to write to a database. Although our demonstration focuses on writing the Bing Ads data to a SQL server, KingswaySoft SSIS Integration components can be used to integrate Bing Ads data with virtually any application or database system including Redshift, HubSpot, Salesforce Pardot and much more. To get started, you will need:
- The Bing Ads Source component, to read the data from Bing Ads Reports and,
- The Premium ADO.NET Destination component, that can be used to write data into a database, with additional options to Update, Delete and even Upsert data.
Both components are available in KingswaySoft’s SSIS Productivity Pack, a collection of premium SSIS components to enable greater developer productivity and increase the power of SSIS.
Connecting to Bing Ads from SSIS
Connecting to Bing Ads from your SSIS package is a straightforward method. Right-click on the Connection Managers click New Connection and choose Bing Ads. Choose the instance type (Sandbox or Production) and use OAuth2 authentication type to generate the token file, which can be stored in a path. For more information on how to set up the connection, visit our Help Manual on Using the Bing Ads Connection Manager.
Once you have successfully connected to your Bing Ads instance, start building the package.
Retrieving Data from Bing Ads using SSIS
There are multiple services supported in KingswaySoft Bing Ads Source component including Ad Insights and Campaign Management, to name a few, but we are going to work with the Reporting Services. Among the various Report Types available, we are working with Account Performance, as an example. Selecting Account Performance populates the grid at the bottom for Input Parameters with the applicable input parameters that are acceptable by Bing Ads API.
When working with Reporting Service, even Report Type may have certain input parameters that are mandatory. In this case, for Account Performance, The AccountPerformanceReportRequest.Scope.AccountIds is a mandatory parameter, which is automatically provided by the component. Its an array type parameter, so if you have more Account Ids, list them as delimited “,”. When working with other report types, the applicable parameters may change.
Next, chose the Report Time Zone from the list provided, and configure the Report Date, that is, the date range for which the report should be generated. Once the General page is configured, click on Columns page, and then select the columns that are allowed. With Bing Ads, there are a few field types that can be sent in the request while others cannot be sent. For the Account Performance report, this API documentation will help identify the required fields. The Columns page allows you to deselect all fields and choose the ones that are required.
To help simplify the display of the required Bing Ads Fields, click Hide Unselected to hide the un-required fields. In our case, we selected AccountId, AccountName, AccountNumber, Clicks, LowQualityClicks, and LowQualityClicksPercent.
Writing Bing Ads Report data to SQL Server using SSIS
With the Source component defined to fetch the data, let us configure the Premium ADO.NET Destination component to write the data to a SQL server database. With KingswaySoft’s SSIS Integration components, you can write the data to virtually any application or database system. For our case, we chose to write the data to a database. First, set up the connection to the SQL server by creating a new ADO.NET Connection Manager. With the connection established, drag-and-drop the Premium ADO.NET Destination component and connect the Bing Ads Source component to it.
Double-click on the Premium ADO.NET Destination component to configure its settings. Select the recently created Connection Manager and choose the Upsert action. Upsert is beneficial when working with new data and old data that needs to be updated. Upsert will check if a row already exists based on a specified matching criterion. If it does exist, the component will update that row. If it does not find a match, it will create a new row. The action is handled within a single destination.
Next, select the appropriate table the data from Bing Ads should be written to. The Premium ADO.NET Destination component provides a useful feature called Create Table. With this feature, if you have a new data segment from Bing Ads Source while designing, instead of having to build the table beforehand, you can quickly create it right within SSIS when updating the package. The Create Table option automatically generates the command for based on the input data, though you can modify the command, as needed, before executing.
On the Columns page, select the Input column that needs to be mapped to the Destination Table column. Since we chose Upsert action on the previous page, a Key column appears. You must select the key field that will determine if there are any matches with the data currently available on the SQL server. This column would not be available if a different action were chosen, such as Insert.
Once all the required fields have been mapped and the Error Handling page has been configured, click on OK button to save, and close the Destination Editor. Enable a Data Flow Task and execute the task to see the data flowing from the Source to the Destination.
Closing Notes
Using the same approach, you can extract and store other data segments as well from the Reporting service in Bing Ads. The requirements for input parameters and columns that are to be included in the request would need to be verified and configured accordingly. You can have different Data Flow Tasks in the same package connected in a series to extract multiple reports. The Precedence Constraints in the Control Flow can help in the flow of control between the data flows.
