Anyone who manages paid keyword search knows it is hard work! You can look at vast reports of raw statistics and quickly get lost in trivia. At Altova we designed a better way to analyze and manage the performance data for our Google Adwords campaigns. We can creatively query the numbers to: · Quickly aggregate results for subcategories of campaigns, for instance by product, geographical region, or any other grouping · Easily identify trends over time The chart below illustrates these advantages by collecting data for a single Altova product – SemanticWorks – from multiple campaigns over six individual months. Starting Out Like many keyword advertisers, we were viewing statistics in Adwords, downloading CSV files, then spending hours massaging and manipulating the data in spreadsheets to identify and format the information we required. We wanted more immediate and in-depth reporting of keyword performance while retaining full control of the process and managing everything internally. SQL queries of a database of keyword statistics offer a powerful and flexible alternative. In the remainder of this post we explain how the database design, data mapping, and reporting features of the Altova MissionKit can be applied to create an architecture to efficiently track paid keyword performance. Database Design Our choices were to implement a keywords database on an existing database platform already running in the company, an express edition of a commercial database, or an open-source database, since the Altova MissionKit works with SQL Server®, MySQL®, Oracle®, IBM DB2®, PostgreSQL®, Sybase®, and Microsoft® Access®. We chose SQL Server for our database platform. We connected with DatabaseSpy and used the graphical database Design Editor to create the table shown below. Most columns correspond to fields in a keywords report. In order to store multiple rows for each individual keyword – one row for every month of statistics – the table also includes columns for the month and year. Populating the Table The Google Adwords online interface lets users create reports of keyword statistics of specific date ranges and download them as CSV files. We downloaded individual CSV files containing our performance data for each unique month. We used MapForce to map values from the CSV files to columns in the database table and insert the month and year data for each row. The string functions at the bottom center of the mapping diagram remove percent signs and commas from fields we want to treat as numerical data. By doing this in the mapping, we don’t have to massage the columns of data in the CSV files before importing them. Since the CSV files for each month all have the same structure, the mapping needs only minor revisions to import each new month’s data: update the constants at the top that define the starting row id, month, and year. MapForce processes the mapping with its built-in execution engine, reading the CSV input and generating SQL INSERT statements for each row of data. MapForce then allows users to execute the entire generated SQL script by clicking a toolbar icon or from a selection in the Output menu: Querying the Database Back in DatabaseSpy, we can query the database from the SQL Editor window. This query reports the top ten performing keywords for SemanticWorks in October 2011. For data privacy, some fields in the Results chart are hidden. To get additional interesting results, the SQL statement can be easily modified. For instance, the ORDER BY line can sort for highest cost, most clicks, or any other characteristic. The WHERE statement combines data from multiple campaigns. The LIKE keyword treats the percent signs around SemanticWorks as wildcard characters to match any campaign with SemanticWorks anywhere in its name. Other queries could add a geographic identifier such as US or EU, or match on an entirely different column such as adgroup. Of course, all these options depend on a consistent and predictable campaign and adgroup naming system. We created a DatabaseSpy Project to collect all our favorite SQL queries for sharing and convenient reuse. Here is the query we used to generate the chart right in DatabaseSpy that appears at the top of this post: This query goes beyond simple SQL reporting to perform calculations on a subset of the data and format the results. Database Reports We designed reports for the executive team using Altova StyleVision, based on the queries and charts we had already designed in DatabaseSpy. We simply copied our queries from the DatabaseSpy SQL Editor window and added them as sources in the StyleVision Design Overview window. Saving our report design in a StyleVision SPS stylesheet makes it is easy to regenerate an updated version every month. Here is the HTML output for a SemanticWorks Keyword Trends report based on the query above, displayed in the StyleVision Preview window: If you follow the conventional wisdom for building your own paid keyword campaigns, you will develop segmented campaigns with many small, highly specialized ad groups, and you may also find yourself overwhelmed by the data in Adwords reports. If you’d like to try managing your own keywords the way we describe here, a fully functional trial of the Altova MissionKit is available.
Tag Archive for: database reports
Tags: Altova XMLSpy, Blog, BPMN, data mapping, database charts, database reports, diff merge tool, DiffDog, file comparison, MapForce, MissionKit, reporting tool, software tools, SQL Server, UML, v2011r2, XML charts, XML Editor
The industry is abuzz with the latest news announcing our release of the MissionKit Version 2011 Release 2. The release is loaded with new features for chart and report creation, enhanced data mapping capabilities, new XML Schema editing functionality, support for the latest version of BPMN, and a really cool new feature for comparing and merging Microsoft® Word documents. Dr Dobb’s and SQL Server magazine are just a few of the industry publications and blogs that covered the launch. Read what the industry is buzzing about and then download a free 30-day trial of the MissionKit and check out for yourself all the powerful new features now available in our suite of XML, database, and UML tools!
Tags: database charts, database reports, StyleVision, v2011, Version 2011, XBRL charts, XML charts, xml reports
On September 8, Altova released v2011 of the MissionKit with the addition of powerful reporting functionality across many of the products. Specifically, StyleVision has been supercharged with a multitude of new features, securing its place as an advanced and versatile business intelligence application, priced for today’s market. Let’s take a closer look at the exciting new features in StyleVision 2011 including:
- Charts as a new design element
- Chart wizard for XBRL files
- Explicit support for HTML/CALS tables
- Ability to import existing XSLT files
- Scripting & toolbar editor for Authentic®
Charts as a new design element StyleVision joins other MissionKit 2011 tools with support for charting, adding to its already advanced general purpose reporting capabilities to create a highly scalable decision support tool for XML, database, and XBRL content. As with all StyleVision designs, charts can be easily rendered for multi-channel output in HTML, RTF, PDF, Word 2007+, and electronic forms. The following types of charts are available:
- 2D & 3D pie charts
- 2D & 3D bar charts
- Category line graphs
- Value line graphs
- Gauge charts
The chart configuration dialog lets you use XPath to select data for your charts. This can be as simple or as complex as you want, running the gamut from database data that is already laid out in a tabular format to XML files where the nodes you need to select are spread out over hundreds of lines of code. Pssst… if you’re thinking that this would be great for the unique demands of XBRL, read on because we created something special with that in mind ;). Here are a couple of examples of charts that you can build in StyleVision: Yup, you can even chart completely different sets of data on the same graph. How creative you want to get with your visual analysis reports is really up to you. You can even create interactive charts for use with Authentic – allowing end users to manipulate eForms to view the desired result. For example, the screenshot below shows the Authentic view of a pie chart where a drop-down menu (combo box) selection dictates the subset of data that is represented. Charts are easily integrated into your StyleVision report templates at any point by simply dragging the relevant node onto the design pane and choosing Insert Chart from the context menu. The Chart Configuration dialog can then be used for chart settings (choosing chart type and style) and data selection (populating your chart axes with the relevant data from the source). You can even use Dynamic XPath Settings to apply transactional data to your chart. Of course, if you sometimes have trouble telling your .s from your /s, you can always get some help with more complex expressions from StyleVision’s XPath Builder. Chart wizard for XBRL files If you are familiar with XBRL, you are intimately aware of the complexities associated with navigating XBRL taxonomies. And, if you’re impressed with what you’ve just read about StyleVision’s charting capabilities, you are probably trying to remember where you put that old XPath reference guide. Not to worry! StyleVision has added to its XBRL rendering support with an XBRL Chart Wizard that lets you easily select data and define presentation settings for your XBRL reports. Concept and Period Properties dialogs are included to let you specify which elements should be included and how periods (instants or intervals of time) should be handled in your chart. Explicit support for HTML/CALS tables StyleVision now provides direct support for HTML/CALS tables, meaning that it will automatically recognize values dictating table structure (column number, row height, etc.) and apply them to rendered output. (v2011 adds support for rendering HTML/CALS tables in HTML, RTF, PDF, AND Word 2007+ – previous releases have supported output to Authentic eForms.) You can also easily assign additional presentation styles to HTML/CALS tables using the Edit CALS/HTML dialog. Ability to import existing XSLT files Well, I’m sure a few of you were hoping that this one was coming soon… You can now base your StyleVision template designs on existing XSLT files that were designed for HTML output or XSLT files with XSL:FO commands that were designed for output to PDF. Simply choose the New from XSLT File option and presto change-o, your design will be fully manifested in the design pane. Now you can edit your template using StyleVision’s graphical interface and output to even more formats (HTML, RTF, PDF, Word 2007+, and Authentic eForms) with just the click of a button. Scripting & toolbar editor for Authentic This feature is just way too cool not to devote an entire post to it, so keep your eyes peeled for a full description coming up on this blog. In the meantime, check out the Authentic scripting page for a brief description and examples. Better yet, just download a free 30-day trial of StyleVision today to test drive all of these new features for yourself!
Tags: database charts, database reports, Version 2011, XML charts, xml reports
The Altova team is excited to announce the release of Version 2011 of our MissionKit tool suite and entire product line. This release delivers a multitude of innovative features, including robust chart and reporting functionality for analyzing and communicating XML, database, XBRL, EDI – virtually any type of data – in a meaningful, eye-catching way. Charts are created with a few clicks inside MissionKit tools and can be immediately shared via copy/paste or saved as image files – that’s right, no more exporting to Excel – or integrated in reports or data entry applications designed in Altova StyleVision. Of course, you can also get the XSLT or XQuery code for generating the chart for use in your own apps. The MissionKit 2011 includes a wide range of other new features– like SOAP validation, schema flattening and subset creation, database UML diagrams, and more – and we’ll cover all of those subsequent blog posts. For now let’s focus on the new charting and reporting features across the Version 2011 MissionKit.
Charts can now be generated and used in the MissionKit 2011 XML and database tools in a variety of ways. The following types of charts and graphs are available for providing a graphical representation of numerical data:
- 2D & 3D pie charts
- 2D & 3D bar charts
- Category line graphs
- Value line graphs
- Gauge charts
Charts are fully dynamic and can be automatically or manually regenerated when backend data is updated. XMLSpy You can create a new chart directly in the XML editor by simply highlighting a range of data in either Text View or Grid View and selecting New Chart from the right-click context menu. The chart’s appearance, labels, and so on, are highly customizable, and your finished chart can be printed, copied to the clipboard, saved as an image, or – and this is truly unique – exported as XSLT or XQuery code for use in your own stylesheets, reports, or apps. XMLSpy is the first and only XML editor on the market to support this powerful functionality, giving our users a distinct advantage when it comes to analyzing and interpreting XML data. You can also create a chart to visualize the results delivered by the XMLSpy XSLT Profiler and XQuery Profiler, making it easy to interpret and communicate performance data. StyleVision Charting and graphing support in StyleVision 2011, the stylesheet and report design tool, allows you to present data in a powerful, visual way, so that it can be easily analyzed from a variety of different angles. When you’re creating an XML, database, or XBRL report (or even a data entry form for use in Altova Authentic), it’s easy to specify your chart parameters via XPath and then customize the appearance of your chart or graph. You can even utilize dynamic XPath settings to apply dynamic data from your source to the settings of your chart. For example, if you have a chart that shows regional data, you may wish to reflect the name of each region in the title of your chart. If there are several Region elements, the data for the chart title can be selected dynamically via an XPath expression depending on which set of data is being presented in the chart. Charts are integrated in multi-channel StyleVision reports, described further below. DatabaseSpy Relational database data often lends itself perfectly to a graphical representation, but it’s not always easy to create charts to analyze relationships locked away in database tables. Now, with DatabaseSpy 2011, it’s as simple as selecting the column(s) you wish to chart. You can even generate and save charts based on calculations performed in a SQL query – such as averages, percentages, etc. As with XMLSpy, charts may be saved as image files, printed, and copied to your clipboard. DatabaseSpy supports all major relational databases, and even with this charting functionality and its other advanced tools, it’s ridiculously affordable.
StyleVision The new chart creation functionality described above adds a whole new level of sophistication to Altova’s general purpose reporting tool, StyleVision. While you’ve been able to design attractive XML, database, and XBRL reports in previous versions of StyleVision, now you can also include dynamic charts for full featured, multi-channel reporting in digital formats like HTML and e-Forms, and print media including Word and PDF. Advanced options such as drag-and-drop design, absolute positioning, modular designs, conditional templates, support for importing and/or reusing existing XSLT stylesheets, dynamic data selection, and more, combine to make StyleVision robust solution for business reporting on any scale. In fact, StyleVision now integrates with MapForce – so you can design reports for virtually any type of data. MapForce Starting with Version 2011, MapForce supports integration with StyleVision, allowing MapForce users to automatically render results of their XML and XBRL mappings using an associated StyleVision report design. This very powerful feature essentially combines MapForce’s any-to-any data mapping capabilities with a sophisticated rendering engine, meaning that you can now automate chart and report generation from virtually any data format – including databases, flat files, Excel, EDI, and more – that can be mapped to an XML Schema or XBRL taxonomy.
DOWNLOAD VERSION 2011
Whether you need to generate charts while working with XML or databases, or you need to design full-featured reports with dynamic, multi-channel output, we hope you’ll check out the new MissionKit 2011. Then, post a comment here on the blog to let us know what you think! Upgrade or Download a Free Trial See more details and screenshots, or download Version 2011 now. Current customers with active support and maintenance can download the new version for free. A fully functional 30-day trial is also available.
More details about all the new features added in Version 2011 will be posted in the coming days.