Importing CRM Data into Google Analytics

In Chapter 16, “Integrating Google Analytics with CRM Data,” we recorded GA campaign information directly in our CRM (Salesforce) so we could correlate our medium, source, and campaign values with the lead status that our sales team assigned in the CRM. We also recorded a common visitor ID in both GA and our CRM to enable additional joins between the two data sets within the CRM, within a separate environment, or within GA.

We can take advantage of the GA data import feature to accomplish the join within GA. (Since the imported data would need to apply retroactively, this scenario would require query-time import available in Analytics 360. The content, product, and geo imports that appear later in the chapter would be suitable as processing-time or query-time import and would therefore also be applicable to GA Standard.) In this example, we’ll take the following steps to import lead status from the CRM for further correlation with GA data. Note that you need Edit access at the property level to perform the following procedure:

1. Create a Lead Status custom dimension in GA.

   The data that we’re pulling in from Salesforce is the qualification status that our sales team assigned to each lead. Since there is not currently a slot for this data in GA, we’ll need to create a custom dimension as in Figure 17.1 to populate as the objective of the data import.

   

   Let’s say that we had previously created five other custom dimensions in the GA property and that this is the sixth. When we’re importing the CRM data into GA, we’ll add ga:dimension6 as a heading to the lead status column to match it to the lead status custom dimension.

2. Create the data set schema in GA.

   Before we can import our CRM data into GA, we need to create a data set to receive the CRM data and define a schema to map the data import. In most cases, you’ll designate a single key field that will serve as the join between the data sets and one or more target fields to populate in GA from the imported data.

   1. In the Property Admin, click Data Import.
   2. For Import Behavior, select Query Time.
   3. For Data Set Type, select User, and click Next Step.
   4. On the following screen, name the data set Lead Status, and select one or more views to import the customer relationship management (CRM) data into. (You’re advised to import first into a test view, verify import in the test view, and then import into one or more working views.)

   5. In the next step, designate the common field/dimension to be used as the join in the import and, under Imported Data, one or more target dimensions to copy the new data into, as shown in Figures 17.2 and 17.3.

      

      

   6. For Overwrite Hit Data, you can select No, since there will be no lead status associated with any of the visitor IDs at this point. For future imports in which Lead Status may have changed for some visitorIDs, you can select Yes for Overwrite Hit Data.

   7. Click Get Schema to confirm the heading names that we’ll need to add to our exported CRM data in step 3. In the lead status example, you’ll configure the schema as either ga:dimension4, ga:dimension6, or ga:userId, ga:dimension6, depending on where you stored your visitor ID from the CRM (and assuming that you created the lead status custom field in the sixth custom dimension slot as in the example). If you previously stored the CRM visitor ID as both the User ID (for cross-device) and your Visitor ID custom dimension for CRM integration, you can use either as the key in your schema.

3. Export CRM data.

   In Salesforce, as an example, we can create a report that contains one of two field sets, following the schema in either Figure 17.2 or 17.3.

   • Visitor ID and lead status. Again, if the common key that we stored in both GA and Salesforce was a client-generated ID (such as the client ID value of the GA _ga cookie), you need to export the visitor ID custom field that you populated into the Salesforce lead through a hidden field on the memory chip lead form.

   • Lead ID/Customer ID and lead status. If, instead of passing a client-generated visitor ID to your CRM when a lead or purchase form was initially submitted, you retrieved the lead or customer ID from your new CRM record and recorded it in GA as the visitor ID custom dimension or the cross-device user ID, export this lead/customer ID and lead status.

     When you export your CRM report as comma-separated values (CSV), you’ll generate a file similar to Listing 17.1. For our example, we’ll call this file sf-export-lead-status.csv.

     Note that you must add the heading row yourself. This schema example corresponds to Figure 17.2.

4. Upload the .csv file.
   1. In the property admin column, click Data Import.
   2. For the Lead Status data set listed in Figure 17.4 that we defined previously, click Manage uploads.
   3. Click Upload file, select sf-export-lead-status.csv, and click Upload.

Once the import is completed, you’ll see a confirmation, as in Figure 17.5.

Uploading through the Management API

Note that you can also import data through the GA Management API instead of through the GA user interface. The management API would be a better option in scenarios where automation is desired. If data is being uploaded to GA on an ad hoc or infrequent basis, then the interface method works perfectly. If the upload is frequent or if a hands-off approach is desired, then the capabilities facilitated by the management API become much more attractive.

Using Imported Data in Google Analytics Reports

In many cases, such as the lead status example outlined in the previous section, the new data is imported into one or more custom dimensions. While custom dimensions do not appear by default in any of the built-in GA reports, you can apply custom dimensions as secondary dimensions in the built-in reports and use custom dimension to define a custom segment or custom report as discussed in Chapter 10, “Segments,” and Chapter 11, “Dashboards, Custom Reports, and Intelligence Alerts.”

We could apply our imported Lead Status data as a custom dimension in the Campaigns report as displayed in Figure 17.6. This report displays some of the same data as Table 15.4, but here the merge between CRM and GA data has occurred within GA and not the CRM or a separate environment.

Importing Content Data into Google Analytics

It was in Chapter 12, “Implementation Customizations,” that we first discussed the classic examples of content dimensions that GA can’t record without help from you: author and category on blog or article pages. GA has no way to capture this data by default, so we populated the data layer with author and category variables from your content management system (CMS) since they did not already appear on the page, and we recorded author and category as custom dimensions with the pageview hit.

If, for any reason, your developers were not in a position to write that back-end data to the data layer so you could record it as a custom dimension with each hit, you could, instead, perform a data import for author and category against the page.

As mentioned above, GA Standard supports only processing-time imports other than Ecommerce refunds and cost data, so it would be beneficial to import this data as early as possible if you’re using GA Standard. (With Analytics 360, you can take advantage of query-time import to add data to hits that have already been processed.)

The procedure for importing content data is similar to importing user data as seen in the previous CRM example, but instead of matching on a visitor ID custom dimension or user ID, we’ll match our content on the Page dimension, or a portion of it.

To perform the author and category import, take the following steps:

1. Create two new custom dimensions, one for author and one for category, with the scope set to Hit as shown in Figure 17.7.

2. Analyze the CMS key value relative to the GA Page dimension.

   Before we create a data set and associated schema in GA, let’s consider the CMS export, especially the key, and how the key is represented in the page URL.

   Your CMS is essentially a database that injects content into the page. Each page on your website exists as a record in your CMS. When a Web visitor requests a page on your website, the CMS (1) reads the page key from the URL, (2) pulls the corresponding fields from the CMS into a page template, and (3) sends the resulting HTML for the page to the requesting browser.

   As a simple example, let’s say that three URLs in the news section of your meteorology website appear in the following format:

   /news/article.php?articleId=3293
   /news/article.php?articleId=4588
   /news/article.php?articleId=5214

   The corresponding row in your CMS might be structured as in Table 17.1, with the articleId parameter in the URL serving as the unique key in the CMS.

3. Define the data set and associated schema in GA.
   1. In the Property column of the Admin screen, click Data Import.
   2. For Data set type, select Content Data.
   3. Select one or more views to import the data into.

   4. For Key, select Page. In many cases, the key in the imported data set will correspond only to a portion of the Page value rather the full Page value as detailed in Table 17.2.

   5. Under Imported Data, set the newly defined Author and Category custom dimensions as the import targets. In this case, Author and Category were the fourth and fifth custom dimensions that you defined in the property, so you’ll specify ga:dimension4 and ga:dimension5 in the header row of the .csv import file.

   6. Keep the overwrite option set to No unless you’re performing an additional import and any previous author or category values that may have changed in the interim.

      Since the key values previously listed in Table 17.1 correspond only with the value of the articleId query parameter in the Page dimension, we can specify articleId as the query refinement. This refinement applies only when your CMS key appears as the value in a name=value pair. The key in the data set configured in Figure 17.8 uses a query refinement on articleId.

      If, instead, your CMS records were identified with a text key such as typhoon, and that text was incorporated into the Page value but not with the name=value format, you could instead use regex refinement to isolate the key within the URL pattern. In the regex refinement example in Table 17.2, /news/ identifies the static pattern of Page value, and ([^/]+) represents the dynamic portion of the Page value that corresponds to the CMS key.

      You could also use a regex refinement to match a single CMS key to multiple page variations, such as /news/article.php?articleId=5214 and /news/article.php?articleId=5214&sessionId=12374, but, ideally, you should instead make every effort to consolidate your Page values by using the Exclude URL Query Parameters view setting as discussed in Chapter 9, “View Settings, View Filters, and Access Rights.”

4. Export the article ID, author, and category columns from your CMS, and add a header row to match the schema.

5. Upload the .csv file shown in Listing 17.2 as in the lead status example above.

This data import populates author and category just as if we were recording custom dimensions with each pageview on the meteorology website, and we can now create a custom report to display performance by author and category.

Importing Campaign Data into Google Analytics

In Chapter 7, “Acquisition Reports,” we discussed the extreme importance of using the utm_medium, utm_source, and utm_campaign URL parameters to more accurately record our traffic sources and to populate the campaigns report with accurate, structured campaign data as in Listing 17.3.

There is, however, another option for populating campaign parameters into GA: you can pass a single utm_id parameter in the URL, as shown in Listing 17.4, and then import the campaign parameters—and custom dimensions as an option—against this ID as the key, as shown in Listing 17.4.

Why might you use the simplified campaign parameter format in Listing 17.4? For one thing, it can be a bit compromising to explicitly display campaign parameters for your website visitors to plainly see. In most instances, direct indications of marketing descriptions can only be a distraction from the user and brand experience that you aim to provide. In other cases, you might be dealing with advertising platforms that allow a single campaign ID only.

To import campaign data:

1. Create a new data set and select Campaign Data as the Data set type.

2. Define the schema as in Figure 17.9. Note that we have created a Campaign Group custom dimension, which you can use (in a custom report or segment, or as a secondary dimension) to distinguish between product campaigns and informational resource campaigns. Also, as mentioned in Chapter 7, campaign name is not actually mandatory for campaign tracking, but it’s always recommended as best practice.

3. Populate a .csv file as in Table 17.3.

4. Import the .csv file as in the lead status and author/category examples earlier in the chapter.

Importing Cost Data into Google Analytics

By linking your Google AdWords and GA and enabling AdWords auto-tagging as described in Chapter 14, “Google Analytics Integrations—The Power of Together,” you can view AdWords cost data within GA. While GA does not offer direct integrations with other paid advertising, you can manually import cost data from other platforms such as Bing or Facebook.

Importing cost data is fairly similar to the data imports we reviewed previously in this chapter, but a few special considerations are detailed in the following procedure.

1. Create a new data set and select Cost Data as the Data set type.
2. Define the schema as in Figure 17.10.
   • Note the key is automatically defined as the combination of medium and source dimensions. In most cases, however, campaign will serve as a more specific de facto key as described in the figure caption.
   • You must import one of the following three metrics: impressions, clicks, and cost. In many cases, such as Bing Ads, it makes sense to import all three. You can use this schema, however, even if the data you’re importing does not contain all three values.
3. Export a .csv file from the ad platform as shown for Bing in Figure 17.11.
   • Although the date does not appear in the schema, you’ll need to make sure that ga:date appears as the first column in the .csv before import. Each date must appear in YYYYMMDD format.
   • You can manually add the ga:medium and ga:source columns to the export, with respective values cpc and bing for each row.
   • The Search Term column in the AdWords export corresponds to the Matched Search Query dimension in GA.
4. Import the .csv file as in the previous examples.

Comparing Campaign Cost and Performance

Once you import campaign cost data, you’ll be able to access the Campaigns ˃ Cost Analysis report, as shown in Figure 17.12.

Notice, however, two types of data that the Cost Analysis report does not include:

• Postclick performance data, such as bounces and conversions, does not appear.
• While AdWords campaign data does also appear in the general All Campaigns report, AdWords cost data does not appear in the Cost Analysis report.

To incorporate cost and performance data for AdWords and non-AdWords campaigns into a single report, we can easily configure a custom report as shown in Figure 17.13.

Importing Product Data into Google Analytics

Product data import is quite straightforward and allows you to add or overwrite many Ecommerce and Enhanced Ecommerce dimensions, as well as the price metric, using product SKU as the key.

Product SKU or name is, in fact, the only product detail that you are required to provide when you’re coding your actual Enhanced Ecommerce interactions on your website; you could potentially provide SKU only at transaction time and match to imported Ecommerce dimensions.

In addition to the dedicated Ecommerce dimensions, you can import custom dimensions—such as size, color, warranty level, or any other product or service descriptor—per SKU, as the custom report in Figure 17.14 illustrates. Keep in mind that you could also populate any of these built-in or custom dimensions while the user is interacting with your Web pages or mobile app; the product data import is a different option for associating this extra data with each SKU.

Importing Geo Data into Google Analytics

In addition to the geographic hierarchy GA offers by default, you can create your own geographic divisions by importing against city, region (which usually corresponds to province or state), country, or subcontinent.

For instance, you could create a custom dimension named US Regional and import a value such as Northeast, Southeast, and so on against the built-in Region ID dimension value for each U.S. state and then report performance by these imported dimension values, as in Figure 17.15.